Nhibernate: problem eager loading two child collections (one being a component list)

Question

i have a Parent class that has two child collections ChildCollectionA and ChildCollectionB. ChildCollectionA is mapped as an association, and has its own ID:

            HasMany(parent => parent.ChildCollectionA )
            .KeyColumn("IDParent")
            .AsBag().Cascade.AllDeleteOrphan();

and ChildCollectionB is mapped has a component list:

    HasMany(parent => parent.ChildCollectionB )
        .Table("ChildCollectionBTable")
        .KeyColumn("IDParent")
        .Component( m=>
                        {
                            m.References(childB => childB.Task, "IDTask").Not.LazyLoad().Not.Nullable();
                            m.Map(childB  => childB.Date, "Date").Not.Nullable();

                        } 
            )
        .AsBag().Cascade.AllDeleteOrphan();

I need now all the Parents in the DataBase, because i will have to perform some operations that need both ChildCollectionA and ChildCollectionB.

So i had to eager load them, i used fetch mode to eager load ChildCollectionA first:

        var queryParents = session.CreateCriteria<Parent>()
            .SetFetchMode("ChildCollectionA", FetchMode.Eager)
            .Add(Expression.Le("ParentDate",endDate));

It returned 492 Parents (should be 481), the total value of the operation i performed was 32,847.46€ (should be 30,790.87€). So i has to eliminate Parent duplicates:

        var queryParents = session.CreateCriteria<Parent>()
            .SetFetchMode("ChildCollectionA", FetchMode.Eager)
            .Add(Expression.Le("ParentDate",endDate))
            .SetResultTransformer(new DistinctRootEntityResultTransformer());

I tried the same eager loading with just the ChildCollectionB

        var queryParents = session.CreateCriteria<Parent>()
            .SetFetchMode("ChildCollectionB", FetchMode.Eager)
            .Add(Expression.Le("ParentDate",endDate))
            .SetResultTransformer(new DistinctRootEntityResultTransformer());

In both cases returned 481 parents OK, and the value was 30,790.87€ OK.

But i needed to eager load both collections at the same time, i did this:

        var queryParents = session.CreateCriteria<Parent>()
            .SetFetchMode("ChildCollectionA", FetchMode.Eager)
            .SetFetchMode("ChildCollectionB", FetchMode.Eager)
            .Add(Expression.Le("ParentDate",endDate))
            .SetResultTransformer(new DistinctRootEntityResultTransformer());

It returned 481 parents OK, and the value was 32,602.57€ (should be 30,790.87€).

Now the number of parents returned is correct, but there are duplicates somewhere else, the values depend on the collections and not the parent, so the duplicates must be somewhere in the ChildCollections.

Right now i'm using an ugly solution:

var queryParents = session.CreateCriteria<Parent>()
    .SetFetchMode("ChildCollectionA", FetchMode.Eager)
    .Add(Expression.Le("ParentDate",endDate))
    .SetResultTransformer(new DistinctRootEntityResultTransformer());

parents= queryParents.List<Parent>();

  foreach (Parent p in parents)
  {
      NHibernateUtil.Initialize(p.ChildCollectionB);
  }

It returned 481 parents OK, and the value was 30,790.87€ OK.

The problem happens when i eager load both collections, if i eager load just one, and then force the lazyload to the other, its works. I don't know if the mapping of ChildCollectionB being a component list instead of an association has something to do with this...

Any clues?

Thanks

Answer 1

I believe the problem is the Cartesian product between the two collections is not handled by the DistintRootEntityTransformer...so you'll have duplicated data in the collections somewhere.

To load multiple collections eagerly it's going to take multiple queries.

var queryParents = session.CreateCriteria<Parent>()
    .SetFetchMode("ChildCollectionA", FetchMode.Eager)
    .Add(Expression.Le("ParentDate",endDate))
    .SetResultTransformer(new DistinctRootEntityResultTransformer());

//load CollectionB in second query...it's linked together by NH
session.CreateCriteria<Parent>()
    .SetFetchMode("ChildCollectionB", FetchMode.Eager)
    .Add(Expression.Le("ParentDate",endDate))
    .List();

What I normally do is fetch the main entity along with all the required many-to-one associations. Then I'll eagerly fetch required collections into the session with a MultiCriteria. If it's only two collections sometimes I do like above and fetch the first collection with distinctrootentity.

For more info see Eager loading aggregate with many child collections