Skip to content

How to do Inner Join in NHibernate (Part 3)

October 2, 2012

In the previous part, we talked about doing an inner join from two tables.

Here we talk about how to do a row count for that inner join, firstly I would show you how not to do.

So you wish to count the number of suppliers by the product category name from the Supplier table using the join for Product.Category.

Logically the code below should be correct but NHibernate computes the count as 0.-

public int LoadSuppliersBy(string productCategoryName) {
return (int)SessionFactoryContext.GetCurrentSession().CreateCriteria<Supplier>()
.CreateAlias(“Products”, “product”)
.CreateAlias(“product.Category”, “productCategory”)
.Add(Expression.Eq(“productCategory.Name”, productCategoryName))
.SetResultTransformer(new DistinctRootEntityResultTransformer())

This doesn’t work!! So the proper way to do is simply to use the code for the inner join as fro the previous code but add the count() at the end to count the items on the list, its that simple.

Heres how: –

public int LoadSuppliersBy(string productCategoryName) {

ICriteria criteria;

criteria = SessionFactoryContext.GetCurrentSession().CreateCriteria<Supplier>();

criteria.CreateAlias(“Products”, “product”)
criteria.CreateAlias(“product.Category”, “productCategory”)
criteria.Add(Expression.Eq(“productCategory.Name”, productCategoryName))
criteria.SetResultTransformer(new DistinctRootEntityResultTransformer())

return (IList<Supplier>)criteria.List<Supplier>().Count;

So you omit the SetProjection, and refer to SetResultTransformer! And then add .Count to the List!

From → Uncategorized

Leave a Comment

Leave a Reply

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

You are commenting using your 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: