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:

    criteria.SetMaxResults(3);

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.