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 · Answer 1 · 2009-06-05T15:38:48.443

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

+1, It is actually (and unfortunately) the best way (performance-wise) to achieve this. — Stefan Steinegger, Oct 21 '09 at 08:54
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. — MylesRip, Jun 16 '11 at 16:43

score 2 · Answer 2 · answered Oct 21 '09 at 08:57

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.

score 0 · Answer 3 · edited Nov 24 '15 at 15:43

0

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.

edited Nov 24 '15 at 15:43

Owen Pauling

11,349
20
53
64

answered Jun 02 '09 at 08:39

tmatuschek

608
4
15

2

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? – Kristoffer Jun 02 '09 at 11:46
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). – Erik Hart May 30 '12 at 10:09

Eager loading child collection with NHibernate

3 Answers3

Linked