NHibernate Pitfalls: HQL Queries With Joins

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

Normally, in an HQL query, you can omit the select clause, that is, the two queries are identical:

   1: var q1 = session.CreateQuery("from Product"); //both return IList<Product>

   2: var q2 = session.CreateQuery("select p from Product p");

However, if you add joins, then it’s a whole different matter:

   1: var q3 = session.CreateQuery("from Product p left join p.OrderDetails");             //returns IList<Object[]>

   2: var q4 = session.CreateQuery("select p from Product p left join p.OrderDetails");    //returns IList<Product>

   3:  

Worse, queries q3 and q4 will not filter distinct root entities, meaning, you will get the cartesian product of Product x OrderDetail.

So, you will need to select the root entity (select p), plus add a distinct entity result transformer (Transformers.DistinctRootEntity) to get what you want:

   1: var q5 = session.CreateQuery("select p from Product p left join p.OrderDetails").SetResultTransformer(Transformers.DistinctRootEntity);    //IList<Product>

This doesn’t happen with LINQ, it always performs a distinct root entity selection, but it also happens with Criteria and QueryOver.

Thanks to Alexander Zaytsev (@hazzik) who reminded me of this!