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)?
Fluent NHibernate / NHibernate Inheritance Question
Delete from <Map>
How To - Simple Nhibernate Join
My solution is to divide the problem in to two queries: first get the IDs of the items belonging to the customer in question:.
Mapping a derived class with additional collection property in nhibernate
I then fetch the actual items without involving any restriction on customer, just using the IDs.
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();
Fluent Nhibernate WrongClassException with Polymorphic Parent
This way NHibernate knows it can get all categories from a single join, avoiding the N+1 selects mentioned in the questions:.
NHibernate config problem
I couldn't come up with any solution that reduced this to a working, single query.
ICriteria criteria = mySession.CreateCriteria(typeof(MapItem)) .SetFetchMode("Categories", FetchMode.Eager) .SetResultTransformer(new DistinctRootEntityResultTransformer()) .Add(Expression.In("Id", itemIds)); IList items = criteria.List();
Persist a top-level collection?
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.
How do I configure FluentNHibernate to not overwrite an existing SQLite db file?
A more general solution would be preferable..