NHibernate Pitfalls: SELECT N + 1

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

One of the recurring situations when accessing a list of entities and changing them is the SELECT N + 1 problem; it is an anti-pattern that, although won’t throw exceptions, and in this sense can be considered to be harmless, means accessing your database in a less than optimal way: one query for the list items without any of its lazy associations (1) and one additional query for each of the list items’ lazy associations (N). Of course, this only happens if you have lazy associations at all.

The way to go over it, when you absolutely know that you will need to access all associations for all entities is to preload them when you issue the query. Here’s how:

   1: IEnumerable<Order> ordersWithLinq = session.Query<Order>().Fetch(x => x.Customer).FetchMany(x => x.Details).ToList();
   2:  
   3: IEnumerable<Order> ordersWithHql = session.CreateQuery("from Order o inner join fetch o.Customer inner join fetch o.Details").List<Order>();
   4:  
   5: IEnumerable<Order> ordersWithCriteria = session.CreateCriteria<Order>().CreateAlias("Customer", "c", JoinType.InnerJoin).CreateAlias("Details", "d", JoinType.InnerJoin).List<Order>();
   6:  
   7: IEnumerable<Order> ordersWithQueryOver = session.QueryOver<Order>().Fetch(x => x.Customer).Eager.Fetch(x => x.Details).Eager.List<Order>();

                             

No Comments