Linq to NHibernate ThenFetch multiple properties

Question

I've got this object graph:

// Lots of stuff omitted for brevity; these are all virtual properties and there
// are other properties which aren't shown on all classes.
class A {
    B b;
    C c;
    DateTime timestamp;
}
class B {
    X x;
    Y y;
}
class X {
    int id;
}
class C { }
class Y { }

or to put it more simply,
a = {
   b: {
      x { id: int },
      y: { }
   },
   c: { },
   timestamp: DateTime
}      

Now I'm making a query where I'm going to return a list of As and I need all their Bs, Cs, Xs and Ys. I'm also going to group them by B into a lookup.

ILookup<B, A> GetData(List<int> ids) {
    using (ISession session = OpenSession()) {
        var query = from a in session.Query<A>()
                    where ids.Contains(a.b.x.id)
                    orderby A.timestamp descending
                    select a;

        query = query
            .Fetch(a => a.b)
            .ThenFetch(b => b.x)
            .Fetch(a => a.b)
            .ThenFetch(b => b.y)
            .Fetch(a => a.c);

       return query.ToLookup(a => a.b);
   }
}

A few things to note:

1. This is a report where all data needs to be returned - unbounded results is not a problem.
2. I'm doing the grouping by using ToLookup because using group by seems to be more complicated when you need all the actual values - you'd need to query the database for the groups and then for their actual values.

My question is how to specify the fetching strategy properly. The way I've done it is the only way I found for this to run (having fetched all of the b.x and b.y values) - but it produces SQL which seems wrong:

select  /* snipped - every mapped field from a0, b1, x2, b3, y4, c5 - but not b6 */
from     [A] a0
         left outer join [B] b1
           on a0.B_id = b1.BId
         left outer join [X] x2
           on b1.X_id = x2.XId
         left outer join [B] b3
           on a0.B_id = b3.BId
         left outer join [Y] y4
           on b3.Y_id = y4.YId
         left outer join [C] c5
           on a0.C_id = c5.CId,
         [B] b6
where    a0.B_id = b6.BId
         and (b6.X_id in (1, 2, 3, 4, 5))
order by a0.timestamp desc

As you can see it's getting the value for a.b 3 times - b1 and b3 for the fetching, and b6 for the where clause.

1. I assume this has a negative impact on DB performance - am I correct?
2. Is there a way to modify my .Fetch calls so it only fetches a.b once?
3. Is this a good approach to my problem?

Answer 1

If you do multiple fetches of one-to-many properties in one query, you get a cartesian product. NHibernate doesn't handle this - AFAIK, it was done deliberately to make it behave like a real SQL join. HQL does the same thing.

You don't need to do all fetches in one go. Split the query and execute each one-to-many fetch/join in a separate query. Each will cache its data in the session and connect all the object references properly. (Note: I never tried this with LINQ, but it does work in HQL, and the principle is the same)

Off the top of my head, it could look something like this:

ILookup<B, A> GetData(List<int> ids) {
using (ISession session = OpenSession()) {
    var query = from a in session.Query<A>()
                where ids.Contains(a.b.x.id)
                orderby A.timestamp descending
                select a;

    query
        .Fetch(a => a.b)
        .ThenFetch(b => b.x)
        .ToList();
    query
        .Fetch(a => a.b)
        .ThenFetch(b => b.y)
        .Fetch(a => a.c)
        .ToList();

   return query.ToLookup(a => a.b);
}

There is one further optimization you could do, use ToFuture() method instead of ToList()... I'm not sure how it works with LINQ and ToLookup methods, but it shouldn't be too hard to get right. ToFuture() will queue the queries and execute them as one sql command instead of doing separate database connections for each one.