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?

Bialystok

Answer 1

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>();

Bialystok

That is odd, Attestations are fetched as single queries. – Bialystok

+1, It is actually (and unfortunately) the best way (performance-wise) to achieve this. – Blooded

I'm in a similar situation (trying to pull in 3 levels at once). Although your solution gets rid of the duplicates, it only appears to be 2 levels deep. Your original question included a 3rd level down called "AttestationRequests.Reminders" that is not included in your answer. – Gutta

Answer 2

To answer your question: yes, it results in huge resultsets.

I suggest to:

• just naively write your queries without eager fetching
• On certain places, put an eager fetch, but only one per query
• if you really get performance problems which you can't solve with indexes or by enhance queries and mapping strategies, use your solution with the multiple queries.

Blooded

Answer 3

While it might not be exactly what you are looking for, I would recommend looking at this article:

Eager loading aggregate with many child collections

If you look around the rest of that site you will find even more posts that discuss eager loading and other great nHibernate stuff.

Inconsumable

Indeed a good article, but unsure if I can apply it to my situation. The solution described in the article about eager loading a specific root entity, my problem is that I want to eager load a collection of root entities. If I would use MultiCritera I need to find a way to connect all the different queries without specifying a specific entity. Suggestions how that can be made? – Bialystok

That example has only one additional hierarchy level, but no grandchildren, for which MultiQuery/MultiCriteria seems to be quite useless, since later queries can't reference results from previous queries (like Query#1: select rootObjects r left join fetch children c where ...; Query#2: select grandchildren g where parent in c). – Mcmahon