[Active Record] How to get a list of objects with more then one initialized collection using Eager Loading and Detached Criteria?

Question

I have a class with two many-to-many associations. After reading the AR docs I realised that I'm allowed to get only one collection at a time using eager loading. So how is it possible to get a list of objects with both initialized collections for each object using eager loading and DetachedCriteria?

So far...

DetachedCriteria dc = DetachedCriteria.For();
dc.SetResultTransformer(new DistinctRootEntityResultTransformer());
dc.SetFetchMode("ComplaintCause", FetchMode.Eager);
dc.SetFetchMode("InsuranceType", FetchMode.Eager);
dc.SetFetchMode("Applicants", FetchMode.Eager); // MANY-TO-MANY COLLECTION

Now I would like to get another collection:

dc.SetFetchMode("Insurers", FetchMode.Eager); // THE ADDITIONAL COLLECTION TO EAGER LOAD 

This gives me a strange cartesian product.

How to use a MultiQuery or MultiCriteria to get the results with both initialized collections?

Answer 1

See this article for reference, it explains both MultiQuery and MultiCriteria to solve this problem.

Answer 2

I too am having this problem.

I have Parent 1..N ListItems and I also have Parent 1..N (HasMany) Child and Child has 1..N ListItem (HasAndBelongsToMany)

I can get Parent to Eagerly load all instances of child but the HasAndBelongsToMany relations between all the different Childs and their ListItems I cannot get to eagerly load.

This is fine when we're loading one parent... the lazy hits aren't that bad but If I load a big result set of 100 parents... that's 100 queries for each type of child when they try to get their ListItems.

The MultiQuery example in the link above is for NHibernate... is the idea that we should bypass DetachedCriteria and use NHibernate session directly?