Avoiding N+1 selects using business rules in NHibernate

Question

I know the basic ways to avoid the N+1 selects problem in Hibernate/NHibernate, but have run into a variant of the problem that I can't find a good solution to.

I have the following three entities mapped: Item, Category and Customer. An item is associated many-to-many to Category, and a Category is mapped many-to-one to Customer. So far, nothing special.

A standard query in my application is to get all the items for a given customer. I do this using the following criteria, trying to fetch the items' categories eagerly, to avoid N+1 selects when examining the items' Categories property:

ICriteria criteria = mySession.CreateCriteria(typeof(Item));
    .CreateCriteria("Categories", NHibernate.SqlCommand.JoinType.InnerJoin)
        .Add(Expression.Eq("Customer", c));
criteria.SetFetchMode("Categories", FetchMode.Eager);

return criteria.List();

However, this doesn't work, NHibernate still fetches the categories with one select per item later on.

What I believe is going on is that NHibernate knows that the result from the first query is filtered (on Customer), and that the categories returned by the query might not be complete, hence it later has to do a separate query to get the categories. (Is this assumption correct? It seems reasonable to me that NHibernate must work this way to ensure correct results.)

However, according to my business rules (or what you want to call them), an item can't belong to categories from more than one customer, so in reality I know the result from the first query is actually complete.

My question is: can I tell NHibernate about this business rule in any way? Is there another way to avoid N+1 selects in this type of situation (which seems pretty common)?

Answer 1

Will try to answer my own question, since I haven't got any answer so far.

My solution is to divide the problem in to two queries: first get the IDs of the items belonging to the customer in question:

IQuery query = mySession.CreateQuery("select item.Id from Item as item "
    + "join item.Categories as category "
    + "join category.Customer customer "
    + "where customer.id=:id")
    .SetInt32("id", c.Id);
IList itemIds = query.List();

I then fetch the actual items without involving any restriction on customer, just using the IDs. This way NHibernate knows it can get all categories from a single join, avoiding the N+1 selects mentioned in the questions:

ICriteria criteria = mySession.CreateCriteria(typeof(MapItem))
    .SetFetchMode("Categories", FetchMode.Eager)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .Add(Expression.In("Id", itemIds));
IList items = criteria.List();

I couldn't come up with any solution that reduced this to a working, single query. Also, this approach forces the programmer to know a little too much about NHibernate's inner workings, and it's really easy to miss when you're writing a new query or criteria. A more general solution would be preferable.