Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Performing Join Queries with NHibernate CreateAlias

[Updated 6/16/08:  A bug has been fixed in the Supplier.hbm.xml; the current version is now 0.6.3.]

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.

Domain Model

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

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Northwind.Core" namespace="Northwind.Core"> <class name="Supplier" table="Suppliers"> <id name="ID" column="SupplierID" unsaved-value="0"> <generator class="identity" /> </id> <property name="CompanyName" column="CompanyName"></property> <bag name="Products" inverse="true"> <key column="SupplierID" /> <one-to-many class="Product" /> </bag> </class> </hibernate-mapping>

Product.hbm.xml

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Northwind.Core" namespace="Northwind.Core"> <class name="Product" table="Products"> <id name="ID" column="ProductID" unsaved-value="0"> <generator class="identity" /> </id> <property name="Name" column="ProductName"></property> <many-to-one name="Supplier" column="SupplierID" class="Supplier" /> <many-to-one name="Category" column="CategoryID" class="Category" /> </class> </hibernate-mapping>

Category.hbm.xml

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Northwind.Core" namespace="Northwind.Core"> <class name="Category" table="Categories"> <id name="ID" column="CategoryID" unsaved-value="0"> <generator class="identity" /> </id> <property name="Name" column="CategoryName"></property> </class> </hibernate-mapping>

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:

public List<Supplier> LoadSuppliersBy(string productCategoryName) { ICriteria criteria = Session.CreateCriteria(typeof(Supplier)) .CreateAlias("Products", "product") .CreateAlias("product.Category", "productCategory") .Add(Expression.Eq("productCategory.Name", productCategoryName)) .SetResultTransformer(new DistinctRootEntityResultTransformer()); return criteria.List<Supplier>() as List<Supplier>; }

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


Posted 06-16-2008 9:07 AM by Billy McCafferty
Filed under:

[Advertisement]

Comments

Staxmanade wrote re: Performing Join Queries with NHibernate CreateAlias
on 06-16-2008 11:59 AM

Looks like your output joined SupplierId to ProductId as opposed to SupplierId to SupplierId?

inner join Products product1_ on this_.SupplierID=product1_.ProductID  

Typo?

Will Shaver wrote re: Performing Join Queries with NHibernate CreateAlias
on 06-16-2008 12:04 PM

Billy -

I wrote this quick extension method that has solved a lot of my createcriteria / subcriteria headaches... Details on my post today.

www.primedigit.com/.../criteriabylongalias

Billy McCafferty wrote re: Performing Join Queries with NHibernate CreateAlias
on 06-16-2008 12:34 PM

Good eye  Staxmanade, I had a problem in my HBM which I didn't notice...fixing now.

Dew Droplet - June 17, 2008 | Alvin Ashcraft's Morning Dew wrote Dew Droplet - June 17, 2008 | Alvin Ashcraft's Morning Dew
on 06-17-2008 11:41 AM

Pingback from  Dew Droplet - June 17, 2008 | Alvin Ashcraft's Morning Dew

Stuart C wrote re: Performing Join Queries with NHibernate CreateAlias
on 06-19-2008 7:42 AM

Do you know of anyway to get a distinct result set without using the ResultTransformer?

The ResultTransformer processes the result set after the fetch which could be very inefficient as many more rows may be returned than are necessary. Using HQL I would use the 'distinct' aggregate function to do this which would literally result in a SQL 'distinct' functional call.

i.e. hql = "select distinct cat from ...."

Billy McCafferty wrote re: Performing Join Queries with NHibernate CreateAlias
on 06-19-2008 8:13 AM

I don't know of a way to do what you suggest (as I was looking for this myself).  Please post back here if you find a way to perform this.

David Kemp wrote re: Performing Join Queries with NHibernate CreateAlias
on 08-22-2008 8:50 AM

At least in NHibernate 1.2.1, you can use CriteriaUtil.DistinctRootEntity instead of new DistinctRouteEntityTransformer.

Frank M wrote re: Performing Join Queries with NHibernate CreateAlias
on 10-08-2008 6:14 AM

This is great, it got me kickstarted pretty good! At some point I needed something like this as I didn't want to mix ICriteria and HQL inside the DAO's from your .net 2.0 architecture.

Daniel Ansari wrote re: Performing Join Queries with NHibernate CreateAlias
on 11-02-2008 4:47 PM

You can do this for hql queries too:

   IQuery query = session.CreateQuery(hql)

       .SetResultTransformer(new DistinctRootEntityResultTransformer());

Steve wrote re: Performing Join Queries with NHibernate CreateAlias
on 01-03-2009 5:42 PM

If you don't have products will it return null ?

What if you want to show suppliers even if no products?

Billy McCafferty wrote re: Performing Join Queries with NHibernate CreateAlias
on 01-09-2009 10:55 AM

You can also do out joins:  www.hibernate.org/.../html_single

Khotu wrote re: Performing Join Queries with NHibernate CreateAlias
on 04-14-2009 9:46 AM

Is there a way of doing this using the ICriterion rather that ICriteria?

Counter wrote re: Performing Join Queries with NHibernate CreateAlias
on 05-13-2009 3:53 AM

Is there a way to do a Count on this? To get the total number of records from the same query?

Billy McCafferty wrote re: Performing Join Queries with NHibernate CreateAlias
on 05-15-2009 7:12 PM

@Khotu, have you run into problems trying it with ICriterion.  I personally haven't tried it, but you may want to also ask at the NHibernate forum at forum.hibernate.org/viewforum.php

@Counter, you could use a projection, e.g., nhforge.org/.../nhibernate-futures.aspx

Frank wrote re: Performing Join Queries with NHibernate CreateAlias
on 06-25-2009 7:27 AM

I also ran into the problem of the non-distinct number of items (I use a fetch="join" in my mapping file). I used Linq To Nhibernate to solve the problem, which is used in the following way:

           var suppliers = (from supplier in session.Linq<Supplier>()

                            from product in supplier.Products

                            where product.Category.Name == produtCategoryName

                            select supplier).ToList().Distinct();

Tasos wrote re: Performing Join Queries with NHibernate CreateAlias
on 07-22-2009 6:02 AM

How would your query look like if you wanted the suppliers returned but the product collection from within the suppliers object should contain only those products of a specific category name rather than all the products.... is this something that can be done?

Thanks

Billy McCafferty wrote re: Performing Join Queries with NHibernate CreateAlias
on 09-22-2009 6:12 PM

@Tasos, you may want to post your question to the NHibernate forums at forum.hibernate.org/viewforum.php for a more expedient and comprehensive response...thanks!

JC wrote re: Performing Join Queries with NHibernate CreateAlias
on 11-11-2009 4:27 PM

I'm using CreateAlias in one of my queries, very similar to your example.

Now in my case, for every alias, all available columns are added to the SELECT statement when  only the columns of the entity specified on the CreateCriteria should be returned.

In your example you have truncated the select statement, is it happening the same?

Is there a way to return only the columns of the entity specified on the CreateCriteria?

Billy McCafferty wrote re: Performing Join Queries with NHibernate CreateAlias
on 11-23-2009 5:54 PM

JC, if you wanted only a subset of columns, you'd probably want to work with a stored procedure instead, and map the results to a DTO, so that you don't pull anything more than you'd want.

Júlio wrote re: Performing Join Queries with NHibernate CreateAlias
on 01-29-2010 7:51 AM

You just solved my problem, I was already going to implement this query using CreateSQLQuery when I stumbled on your site.

a wrote re: Performing Join Queries with NHibernate CreateAlias
on 02-25-2010 4:35 PM

a

Sumi wrote re: Performing Join Queries with NHibernate CreateAlias
on 12-10-2010 3:06 AM

very useful post, well explained..   solved my join problem..

thank u

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)