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.