Billy McCafferty

A common scenario that arises when using NHibernate concerns performing join queries across tables via collection properties. For instance, assume a supplier sells a number of products, each of which belongs to a category. Furthermore, assume that you own an up and coming grocery store set to take over the world if only you could find the right suppliers. To help in your search for the appropriate supplier, you're wanting to perform a query that will accept a category ID and return a list of suppliers who carry products within that category. While there are a number of ways to do this, let's assume that the database will be leveraged to do the filtering for us.

To begin, what follows is the domain model which includes the objects we're interested in. As shown, each supplier has a one-to-many association with products as an IList . Each product then has a many-to-one relationship with a category.

The database model to support this domain is then as follows:

Finally, HBMs exist are defined to tie the tables to the classes:

Supplier.hbm.xml

Product.hbm.xml

Category.hbm.xml

The challenge is to create an NHibernate ICriteria to perform a join query from suppliers to a category name, via the suppliers' product collection. As a first attempt, one would typically try to compare the category name to "Products.Category.Name." The reason this approach fails is that NHibernate assumes you're referring to an explicit property name and is unclear as to how the object model should be traversed. The key to the solution is in the use of NHibernate's CreateAlias. CreateAlias allows you to create a simple alias for a collection or object traversal. The following code demonstrates how to solve our current issue using this technique:

The more interesting lines are in the creation of the aliases. The first creates an alias for the products collection and the second creates an alias to the category associated with the product. An interesting thing to note is that if the goal was, instead, to match the category's ID property, then the second alias would not be necessary as you could compare "product.Category.ID" directly; but any property other than ID will require an additional alias. Finally, since we're performing inner joins, the result set may contain duplicate supplier records. Accordingly, DistinctRootEntityResultTransformer ensures that the result set contains distinct suppliers. The resulting SQL for the above query would be akin to the following:

SELECT this_.SupplierID as SupplierID3_2_, this_.CompanyName as ... FROM Suppliers this_ inner join Products product1_ on this_.SupplierID=product1_.SupplierID inner join Categories productcat2_ on product1_.CategoryID=productcat2_.CategoryID WHERE productcat2_.CategoryName = 'Seafood'

The returned listing from the DAO method would then include only those suppliers who have products belonging to the specified category name.  To see this code in action, download the latest S#arp Architecture release, v 0.6.3, which includes a unit test to demonstrate this technique against the Northwind database.  As demonstrated, NHibernate's built in capabilities for traversing associations via CreateAlias provides a very powerful mechanism for leveraging the database's filtering capabilities from your DAO.

Billy McCafferty