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 →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

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