Nhibernate: distinct results in second level Collection

Question

I have an object model like this:

   class EntityA
   {
        ...
        IList<EntityB> BList;
        ...
   }

   class EntityB
   {
       ...
       IList<EntityC> CList;
   }

I have to fetch all the colelctions (Blist in EntityA and CList in EntityB), because if they all will be needed to make some operations, if i don't eager load them i will have the select n+1 problem. So the query was this:

  select a from EntityA a left join fetch a.BList b left join fetch b.CList c

The fist problem i faced with this query, was the return of duplicates from the DB, i had EntityA duplicates, because of the left join fetch with BList. A quick read through the hibernate documentation and there were some solutions, first i tried the distinct keyword that supposelly wouldn't replicate the SQL distinct keyword except in some cases, maybe this was one of those cases because i had a SQL error saying that i cannot select distict text columns (column [Observations] in EntityA table). So i used one of the other solutions:

  query.SetResultTransformer(new DistinctRootEntityResultTransformer());

This worked fine. But the result of the operations were still not passing the tests. I checked further and i found out that now there were duplicates of EntityB, because of the left join fetch with CList.

The question is, how can i use the distinct in a second level collection? I searched and i only find solutions for the root entity's direct child collection, but never for the second level child collections...

Thank you for your time

Answer 1

I have the same issue. Anyone?