Loading multi-level collections without duplicates in NHibernate

Question

My question is very similar to this one (that wasn't really answered): Nhibernate: distinct results in second level Collection

I have this object model:

   class EntityA
   {
        ...
        IList<EntityB> BList { get; protected set; }
        ...
   }

   class EntityB
   {
       ... does NOT reference its parent EntityA...
       IList<EntityC> CList { get; protected set; }
   }

They are One-to-Many relations. EntityB and C do not have an object reference to its parent object.

I'd like to fully load the collections by performing something like the following three SQL queries to avoid to Cartesian join:

 SELECT id, ... FROM EntityA;
 SELECT id, idA, ... FROM EntityB;
 SELECT id, idB, ... FROM EntityC;

With that, the DAL has all the information to properly fill the objects. But since EntityB is not aware of who its parent is, it has to be nHibernate who takes care of filling the collections properly.

Can it be done ??

I could do this workaround with the Cartesian Product, but it requires modifying my model to provide a collection setter and that qualifies as a patch for a technical problem with the DAL in my mind.

     ICriteria criteria = session.CreateCriteria<EntityA>()
                         .SetFetchMode("BList", FetchMode.Join)
                         .SetFetchMode("BList.CList", FetchMode.Join)
                         .SetResultTransformer(new DistinctRootEntityResultTransformer());

     IList<EntityA> listA = criteria.List<EntityA>();

     foreach (EntityA objA in listA) {
        objA.BList = objA.BList.Distinct().ToList();
        foreach (EntityB objB in objB.BList) {
           objB.CList = objB.CList.Distinct().ToList();
        }
     }

That wouldn't be a Cartesian product. It'd just be a lot of replicated data. A duplicate of EntityB for every extra EntityC, EntityA would be good due to ResultTransformer. You could avoid those duped EntityB's by making that collection an `ISet<>`. It's still a lot of extra data across the wire though. — dotjoe, Sep 30 '11 at 20:30
Indeed, you're right about it not being a cartesian product since it's not N-to-N, sorry about that. An ISet is unordered, so it's not really an option for my scenario unfortunately. — dstj, Oct 03 '11 at 13:46

score 5 · Accepted Answer · edited Oct 17 '13 at 00:06

Have you tried this syntax:

var entities = session.QueryOver<EntityA>().Where(...).List();
var entityIds = entities.Select(e => e.Id).ToArray();
session.QueryOver<EntityA>()
    .WhereRestrictionOn(a => a.Id)
    .IsIn(entityIds)
    .Fetch(e => e.BList).Eager
    .List();

var bEntityIds = entities
    .SelectMany(e => e.BList)
    .Select(b => b.Id)
    .ToArray();

session.QueryOver<EntityB>()
    .WhereRestrictionOn(b => b.Id)
    .IsIn(bEntityIds).Fetch(e => e.CList).Eager
    .List();

This should fire the three selects that you mention. It might seem a wee bit convoluted, but it is taking advantage of the session's first level cache, which ensures that all the entities in the first collection is updated with the loaded collections as they are executed.

Also, you do not pay the penalty of whatever complex query-logic you may have in place for the second and third query. The DB should use the primary index for the second query (maybe even clustered depending on you settings) and a foreign key for the join for extremely low-cost queries.

Thanks Goblin!! I'm new to NHibernate, so I never would have figured this syntax out on my own! Using the profiler, I only have 3 queries now. They use a WHERE IN clause, but always on a primary key and foreign key index, so that should be quick. The last line in your code had a small error, this is what ended up working: `session.QueryOver().WhereRestrictionOn(b => b.Id).IsIn(bEntityIds).Fetch(e => e.CList).Eager.List();` — dstj, Oct 03 '11 at 14:00
No problem :-) Yeah - that was a bad combo of two different solutions... I'll update :-) If you are really interested in the convoluted ways of NHibernate, I strongly encourage you to take a look at ayende.com's blog archive. Just be sure to look at the latest posts first as he has some outdated info in the earlier posts. — Goblin, Oct 04 '11 at 08:30

Loading multi-level collections without duplicates in NHibernate

1 Answers1

Linked