NHibernate Non-Mapped Joins

Recently I received a question asking how to perform an HQL query which joined two entities which were not related in the mappings. I realized that this isn't a simple straight forward concept like most new NHibernate users would probably expect. It doesn't work the way I think 95% of users would initially try to accomplish it.

Some times we need such functionality because we are concerned of our collections growing too large if all collections are mapped. Lets say that for whatever reason we have a Customer class which is referenced by an Order class but the Customer does not have a collection of Orders. Lets say we also track wish list items to show items the customer would like to receive, but again do not have a collection of WishListItem objects on the Customer class.

Now we may think we would write a HQL query to show orders which were placed for an item in the customer's wish list like the following:

SELECT o
FROM Order o
INNER JOIN o.OrderLine ol
INNER JOIN WishListItem wli
ON (wli.Customer = o.Customer AND wli.Product = ol.Product)
WHERE o.Customer = :customer

Unfortunately we would be wrong. HQL is not SQL and does not follow the ANSI SQL syntax we have become so familiar with. Even though INNER JOIN is a keyword in HQL it does not work like it does in SQL. Instead a non-mapped join is represented much like the old style SQL joins. We would instead use the following HQL query:

SELECT o
FROM Order o
INNER JOIN o.OrderLine ol, WishListItem wli
WHERE o.Customer = :customer
AND wli.Product = ol.Product
AND o.Customer = wli.Customer

I hope this helps anyone struggling to create HQL queries between entities which are not explicitly mapped.

--John Chapman