NHibernate LINQ Pitfalls: Too many joins with deep conditions

Although I've just discussed whether NHibernate became obsolete, it doesn't mean that I'm no longer maintaining or developing applications that use it. It'll take at least few years to completely phase it out and in the meantime we still have some problems with it and we still need to know how to use it.

One of recent surprises we had with NHibernate was when querying the database using LINQ provider and condition in our query was checking a reference value not directly in queried object, but in another object it references (yes, I know it is breaking the Law of Demeter), like this:

var firstQuery = sess.Query<RootNode>()
    .Where(x => x.Child.GrandChild.Id == 42)
    .FirstOrDefault();

The condition on GrandChild uses its key value only, so looking at the database tables, joining the GrandChildNode is not needed - all the information used by this query sits in RootNode. Surprisingly, NHibernate 3.2 not only joins GrandChildNode, but also joins RootNode for the second time, only to completely ignore it. That makes 4 tables total.

However, when we change the way we're looking for a grand child and use proxy object created by ISession's Load method, we get expected and optimal query with only 2 tables joined.

var secondQuery = sess.Query<RootNode>()
    .Where(x => x.Child.GrandChild == sess.Load(42))
    .FirstOrDefault();

This bug was already found and is fixed in version 3.3 (and surprisingly, was not present in 3.1) - so it affects only NHibernate 3.2. But I think it's worth mentioning as it may have potentially large performance impact if you're using that version.