NHibernate Pitfalls: Outer Joins of Unrelated Entities

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

NHibernate does not support outer (left, right, full) joins in most of its querying APIs, namely, Criteria, Query Over, LINQ and HQL. Of course, LINQ is translated into HQL, so as long as HQL does not support it, nothing really can be done about it. Do note that this problem also happens in Hibernate for Java, it has to do with the way NHibernate/Hibernate is implemented, both can only join entities that are related through some navigation property.

It is, though, possible to perform left joins with SQL. One example is:

   1: var leftJoinAAndB = session.CreateSQLQuery("SELECT {a.*}, {b.*} FROM EntityA a LEFT JOIN EntityB b ON a.SomeProperty = b.SomeProperty")
   2:     .AddEntity("a", typeof(EntityA))
   3:     .AddEntity("b", typeof(EntityB))
   4:     .List<Object[]>();

As you can see, we are using a specially formatted SQL and we tell NHibernate what entity types its columns refer to. The result is an array of two entities, the second being possibly null.


No Comments