Eager loading child collection with NHibernate

Question

I want to load root entities and eager load all it's child collection and aggregate members.

Have been trying to use the SetFetchMode in FluentNHibernate, but I am getting duplicates in one of the child collection since I have a depth of 3 levels. DistinctRootEntityResultTransformer unfortunately only removes the root duplications.

return Session.CreateInvoiceBaseCriteria(query, archived)
    .AddOrder(new Order(query.Order, query.OrderType == OrderType.ASC))
    .SetFetchMode("States", FetchMode.Eager)
    .SetFetchMode("Attestations", FetchMode.Eager)
    .SetFetchMode("AttestationRequests", FetchMode.Eager)
    .SetFetchMode("AttestationRequests.Reminders", FetchMode.Eager)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .List<Invoice>();

Could I use multi queries or something similar to archive this?

Furthermore, wouldn't this approach result in unnecessarily huge result sets from the database?

Any suggestions?

Kristoffer Kristoffer · Accepted Answer · 2009-06-04T23:00:33

Found a solution but it isn't pretty. First I go and find all the Invoice IDs, then I use them in the multiquery and then at the end filtering the results through a HashedSet. Because of the large number of items sometimes i couldn't use the normalt Restriction.In and was forced to send it as a string.

Any suggested tweaks?

var criteria = Session.CreateInvoiceBaseCriteria(query, archived)
    .SetProjection(Projections.Id());

var invoiceIds = criteria.List<int>();
if (invoiceIds.Count > 0)
{
    var joinedIds = JoinIDs(criteria.List<int>()); // To many ids to send them as parameters.

    var sql1 = string.Format("from Invoice i inner join fetch i.States where i.InvoiceID in ({0}) order by i.{1} {2}", joinedIds, query.Order, query.OrderType.ToString());
    var sql2 = string.Format("from Invoice i inner join fetch i.AttestationRequests where i.InvoiceID in ({0})", joinedIds);
    var sql3 = string.Format("from Invoice i inner join fetch i.Attestations where i.InvoiceID in ({0})", joinedIds);

    var invoiceQuery = Session.CreateMultiQuery()
        .Add(sql1)
        .Add(sql2)
        .Add(sql3);

    var result = invoiceQuery.List()[0];

    return new UniqueFilter<Invoice>((ICollection)result);
}

return new List<Invoice>();

Eager loading child collection with NHibernate

3 Answers