Nhibernate - eager fetching collections in collections with linq

Question

I'm pretty new to NHibernate and want to optimize the loading of lists of objects via the linq-extensions at a few places. I'm running into an issue when trying to eager load subcollections in collections. I think pseudo code is better for describing my problem, so let's say we got the following setup:

public interface IRepository<T> {
    IQueryable<T> GetAll();
}

public class A {
    public virtual int Id { get; set;}
    public virtual IList<B> ListOfBs { get; set; } // mapped with HasMany and LazyLoad
    public virtual IList<D> ListOfDs { get; set; } // mapped with HasMany and LazyLoad
}

public class B {
    public virtual IList<C> ListOfCs { get; set; } // mapped with HasMany and LazyLoad
}

public class C {   
    public virtual int MyProperty { get; set; }
}

public class D {

}

I want to retrieve all rows of class A with Ids 1,2,3 and want all subcollections to be eager loaded despite them being mapped as lazy:

var repoA = Ioc.GetInstance<IRepository<A>>();
IQueryable<A> result = repoA.GetAll().Where(a => a.Id == 1 || a.Id == 2 || a.Id == 3);

According to NHibernate Docs using FetchMany more than once in a query may cause a cartesian product which can be avoided by using future results. The following works and produces 2 additional queries to fetch both collections for all As in result. (in a single roundtrip to the database), but of course doesn't fetch B.ListOfCs.

result.FetchMany(a => a.ListOfBs).ToFuture();
result.FetchMany(a => a.ListOfDs).ToFuture().GetEnumerable();

How do I also fetch the ListOfCs of all Bs? I tried using FetchMany with ThenFetchMany but this results in an error.

// the following results in "Error: "Cannot simultaneously fetch multiple bags""
result
  .FetchMany(a => a.ListOfBs)
  .ThenFetchMany(b => b.ListOfCs);

foreach(var a in result) {
    // do something with a.ListOfBs[0].ListOfCs[0].MyProperty
}

Is there any way to also get all related ListOfCs in a single additional database call using linq? (I read there is using session.QueryOver but the session is hidden in the layer I'm working in)

Thanks in advance for any help.

Answer 1

result .FetchMany(a => a.ListOfBs) .ThenFetchMany(b => b.ListOfCs);

Try using SelectMany for already fetched collections:

result.FetchMany(a => a.ListOfBs).ToFuture();
result.SelectMany(a => a.ListOfBs).FetchMany(b => b.ListOfCs).ToFuture();