Exploring NHibernate Projections and Transformers

Its been a month since I am actively working with NHibernate queries using criteria API. Initially it was taking me long because I wasn't aware of many things but now I has started to prove its worth. I can feel that now I am more comfortable with NHibernate. As an attempt to preserve some of our legacy codes that contains SQL for reporting purposes I had an experience of writing some naughty queries around ICriteria API. During this porting work I was into a lot of NHibernate projections and Transformers.

In criteria API projections help you do the aggregation and grouping. For example if you need to do COUNT(), SUM(), AVG(), GROUP BY then you are going make use of Projections. They key to make use of projections in criteria API is SetProjection() method of ICriteria. Let's get to our first code example making use of Projections and see how it works. Suppose we have table called Product and we want to find number of products where product name contains word 'free' through criteria API. If we do it via standard SQL then our SQL is going to be something like this

SELECT COUNT(*)
FROM Product AS P
WHERE P.Name LIKE '%free%'

and with Nhibernate criteria API it will be

int count = NHibernateHelper.CreateCriteria<Product>("P")
            .Add(Restrictions.Like("P.Name", "free", MatchMode.Anywhere))
            .SetProjection(Projections.RowCount())
            .UniqueResult<int>();

If we look at the code what we have done is we used NHibernate fluent coding style to create a criteria object for Product entity. Then we added a restriction for name and finally for set the projection for RowCount. As the result of this query is going to be scaler value of integer type so we executed the query using UniqueResult generic method by asking to return us the scaler value as integer value.

So far we discussed very simple case of projection where we were needing number or rows. In real word queries are more complicated most of time then just having number of rows :). So before we go and discuss some complex cases of projections we need to have an idea of Nhibernate Transformers.

Probably you can understand from the name that they have to  do something with transforming stuff from one shape to another. Yes you are right transformers helps you transform the results. In order to set a transformer you need to make use SetResultTransformer() method of criteria API. For example let's say we have two table called Product and OrderItem where one product can belong to many order items. Suppose we need to get all distinct products that's been sold. The SQL to this going to be something like

SELECT DISTINCT P.*
FROM Product P JOIN OrderItems AS OI 
ON P.ProductId = OI.ProductId

Now we in order to do this with NHibernate criteria API we are going to make use of a Transformer to get distinct product objects.

IList products = NHibernateHelper.CreateCriteria<Product>("P")
                .CreateCriteria("P.OrderItems", "OI", NHibernate.SqlCommand.JoinType.InnerJoin)
                .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())
                .List<Product>();

In above code the line SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer()) is setting transformer on criteria object that will make sure to emit list of unique products.

Now that you have an idea of transformers we need to look at another and most probably the one that you are going to use a lot with projections is AliasToBeanResultTransformer. This is very powerful transformer and it lets you map your custom objects in NHibernate criteria API queries. For example you may came accross a situation where you will be needing a custom column set instead of just complete NHibernate entity object. You may find yourself in such situation quite often while doing reporting related stuff. For example let's say we need to do a report on our Order table where we have another table called Customer and one customer can have many orders. Suppose we want to find total number and amount of sales for every customer. The standard SQL to this is going to be something like this

SELECT COUNT(*)AS NumberOfSales, SUM(Charges) AS TotalSales, CustomerId AS CustomerId
FROM Order
GROUP BY CustomerId

Now when writing the corresponding NHibernate criteria query the challenge that we have is we got custom columns select clause. NHibernate doesn't allows to load unmapped data and in this case we have unmapped custom columns not NHibernate entities. So in order to get this done we are going to ask our new best friend AliasToBeanResultTransformer to help us out. So in order to use this transformer the first thing that we need to do is to create our custom wrapper class to hold the data. Its going to be something like

  public class SalesData 
        {
            public int NumberOfSales { get; set; }
            public decimal TotalSales { get; set; }
            public int CustomerId { get; set; }
        }

Now our Nhibernate query is going to be something like

IList<SalesData> sales = NHibernateHelper.CreateCriteria<Order>()
                .SetProjection(Projections.ProjectionList().Add(Projections.RowCount(), "NumberOfSales")
                .Add(Projections.Sum("Charges"), "TotalSales")
                .Add(Projections.Property("CustomerId"), "CustomerId")
                .Add(Projections.GroupProperty("CustomerId")))
                .SetResultTransformer(Transformers.AliasToBean(typeof(SalesData)))
                .List<SalesData>();

In above code the first thing that you will notice is Projections.ProjectionList(). ProjectionList let's you add multiple projections as in our case we need four projections. The next thing that you will notice will be Transformers.AliasToBean(typeof(SalesData)) where Transformers.AliasToBean is a quick way to get an instance of under lying AliasToBeanResultTransformer and secondly we are passing type of custom SalesData class. That's it. When executed this query will return list of SalesData objects filled with data from your custom unmapped columns.

Now that you have an idea of projections and transformers you will see that you will be able to do very complex reporting queries with these both awesome features of NHibernate criteria API.

NHibernate and select one column only

You can make use of Projections when it comes to selecting from specific columns. For example sometime you may need to select values only from Id column instead of loading complete matching rows.

Lets quickly take a look at an example. Suppose we have a table called Customer with columns Id, FirstName, LastName, Street, City, PostalCode, Country. Now when doing our query we only want to select all Ids less then value 10. We can accomplish this by doing something like this

IList<int> customerIds = session.CreateCriteria<Customer>()
                .Add(Restrictions.Lt("Id", 10))
                .SetProjection(Projections.Property("Id"))
                .List<int>();

Select by foreign key and NHibernate

Recently I  came across NHibernate. We were thinking to replace our home grown persistence code generator with an ORM. We had two options either Nhibernate or Entity Framework. After evaluating these two awesome frameworks we picked NHibernate.


So far we were using our custom written code generator which was emitting code modeled around Active Record pattern. During the development you will find yourself in a situation quite often where you will be needing to load some objects by some foreign key. This will be the case in many-to-one relation. For example load all products belonging to category with Id equal to 4. This is very easy when you are writing your custom query for example in our case before NHibernate we used to write text query. Then execute it as datareader, iterate over reader to create objects. Finally return collection of all  objects.


Now when defining NHibernate mappings you will map your foreign key columns to properties returning object/objects of that related entity instead of just an id value. The reason behind this is that Nhibernate wants to make sure that when you are relating to some object then that object must exist in database.


Anyway if you are reading this topic I am quite sure you are already aware of the case and are looking for solution. So let say we are making use of Northwind Database where we have two tables called Categories and Products. One category can have many products. We want to list all products belonging to category with id equal to 4. Here is how I managed to do it with Nhibernate.

 IList<Products> products = session.CreateCriteria<Products>("P")  
           .CreateCriteria("Category", "C", NHibernate.SqlCommand.JoinType.InnerJoin)  
           .Add(Restrictions.Eq("C.Id", 4))  
           .List<Products>();  

Here is the SQL generated by NHibernate

NHibernate: SELECT this_.ProductID as ProductID6_1_, this_.Discontinued as Disconti2_6_1_
, this_.ProductName as ProductN3_6_1_, this_.QuantityPerUnit as Quantity4_6_1_
, this_.ReorderLevel as ReorderL5_6_1_, this_.UnitPrice as UnitPrice6_1_
, this_.UnitsInStock as UnitsInS7_6_1_, this_.UnitsOnOrder as UnitsOnO8_6_1_
, this_.CategoryID as CategoryID6_1_, this_.SupplierID as SupplierID6_1_
, c1_.CategoryID as CategoryID10_0_, c1_.CategoryName as Category2_10_0_
, c1_.Description as Descript3_10_0_, c1_.Picture as Picture10_0_ 
FROM Products this_ inner join Categories c1_ on this_.CategoryID=c1_.CategoryID 
WHERE c1_.CategoryID = @p0;@p0 = 4