NHibernate eager loading collections with RootEntityResultTransformer and SetMaxResults


NHibernate eager loading collections with RootEntityResultTransformer and SetMaxResults

by Blake Anderton

Chris Kaukis and I ran into an “issue” with NHibernate yesterday that really threw us for a loop.  Consider we have 2 entity types “Order” and “Item” with a Many-to-Many relationship between them.  Say we want to query all the orders for a given item.  The most direct way is to come at it backwards from the item:


    return session.CreateCriteria(typeof(Item)).Add(Restrictions.IdEq(itemId)).UniqueResult().Orders;


But say we want to come at it from the order side. Maybe we want to eager load another association for the order or something.  No problem, we can do that as well:


    ICriteria criteria = session.CreateCriteria(typeof(Order));

    criteria.CreateAlias(“Items”, “Items”, JoinType.InnerJoin);

    criteria.SetResultTransformer(new RootEntityResultTransformer());

    criteria.Add(Restrictions.Eq(“Items.Id”, itemId));

    return criteria.List();


This will eager load the Items collection and use it to filter the results.  For those that don’t know the RootEntityResultTransformer allows NHibernate to take a result set returned by SQL like this:


    OrderId  OrderNumber  ItemId  Item Name

    1           123               1          Gundanium

    1           123               2          Boxes

    1           123               3          Widgets

    2           456               1          Gundanium


and find that it should return 2 Order objects, with 3 Item objects in order #123’s Items collection and 1 in #456’s collection.  Say we want to page our results, though.  So we add the following to the second code block above:




Now as a smart human you would think the results unchanged – 2 orders is still less than the page size of 3.  But since the machine is stupid it “correctly” only returns order #123, as it will only take the first 3 rows from the result set, not entities.  This is obviously just a case of “Do what I mean not what I say” that we all fall into from time to time, but neither I nor Chris was able to find this documented anywhere in the NHibernate docs or forums.


So now you are warned!  RootEntityResultTransformer and SetMaxResults do not play nice together.  In fact the bludgeon each other to death.  There is a way around this, of course, using DetachedCriteria to filter using a subquery instead of a join, but you lose the eager loading.  What really gets me is the lack of documentation though – that would have saved us a lot of grief had we known about this ahead of time.

More About the Author

Blake Anderton

Software Architect
Querying Specifc Inheritance Types in Entity Framework The Problem When using Entity Framework let’s say you set up a model with inheritance. Let’s make ourselves a simple ...
Simple Long Polling in WCF – Server Recently I’ve been working on a RESTful HTTP server in WCF for a really cool project that isn’t public yet, but should be ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!