Skip to content

How to do Inner Join in NHibernate (Part 2)

October 1, 2012

From my previous post https://mashupweb.wordpress.com/2012/05/16/how-to-do-inner-join-in-nhibernate/, this inner join method works provided if you have created a getter and setter method for the class Subscriber, if this is not created with the class object, then it won’t work.
I hadn’t found and tested a solution using Create Alias until now, after finding a post from devlicio.us, if you imagine these classes are created where you a shop that sells different products based on different suppliers. Suppose you want to display only products belonging to a certain category, and list these suppliers (a query that will accept a category ID and return a list of suppliers who carry products within that category), you will need a join, I had tried the method on the post I written a few months back but this didn’t work. Using the analogy discussed, this solution worked : –

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 /> </id> <property name=”CompanyName” column=”CompanyName”></property> <bag name=”Products” inverse=”true”> <key column=”SupplierID” /> <one-to-many /> </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 /> </id> <property name=”Name” column=”ProductName”></property> <many-to-one name=”Supplier” column=”SupplierID” /> <many-to-one name=”Category” column=”CategoryID” /> </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 /> </id> <property name=”Name” column=”CategoryName”></property> </class> </hibernate-mapping>

Here is the NHibernate method: –

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>;
}

Here you can see that you create an alias that belongs to the class Products which should be in the mapping file for Suppliers, you can then drill down to the category which you call another alias ( from my findings, you cannot do this using criteria.CreateCriteria(“Products.Category”, JoinType.InnerJoin); (don’t ask why, perhaps its the wordings). This will list the suppliers by productCategory from the vaeriable name productCategoryName with the joins of Products and Products.Category.

Hope that helps!

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: