Query the database with NHibernate 3.0

Introduction

NHibernate gives you many alternative APIs for querying database. There is HQL, which is similar to SQL, Linq to NHibernate and finally ICriteria.
And once you start to use NHibernate in your project, you have to decide which API to use. Since NH 3.0 you have one more option. It is QueryOver API.

In this post I want to compare a usage of different APIs for a simple query.

For this sample I created a console application with embedded database. Full source code is available at google code.
In my sample I have a database with one table Emploee (Id, Name, Age, Salary) and I want to calculate average salary of employees who's between 25 to 30 years old.

Lets start!

HQL

Since I was not familiar with NH (but had an experience with SQL), I found HQL is easiest way to get started with.
There is query:

var salary = session.CreateQuery(
@"
select
    avg(employee.Salary)
from
    Employee employee
where
    employee.Age >= 25 and employee.Age <=30
"
).UniqueResult<double>();

Looks good, readable but not strongly typed.

Go next one.

ICriteria

The ICriteria API is NHibernate's implementation of Query Object.

var salary = session.CreateCriteria(typeof(Employee))
                    .Add(Restrictions.Between("Age", 25, 30))
                    .SetProjection(Projections.Avg("Salary"))
                    .UniqueResult<double>();

Much better, but still has some "magic strings". I am looking for strongly typed code where refactoring tools like 'Find All References', and 'Refactor->Rename' will work perfectly.

Linq

NHibernate 3.0 has a built-in Linq provider.

var query = from employee in session.Query<Employee>()
            where employee.Age >= 25 && employee.Age <= 30
            select employee.Salary;

var salary = query.Average();

Pretty good! Strongly typed, readable. But it uses the fact that Enumerable has Average in this case. Would we need a more complicated projection, it would be not available in LINQ. Also, LINQ syntax is still confusing for some people. It just looks different than surrounding C# code.

Keeping looking for a perfect.

QueryOver

QueryOver combines the use of Extension Methods and Lambda Expressions to provide a statically typesafe wrapper round the ICriteria API.

var salary = session.QueryOver<Employee>()
                    .WhereRestrictionOn(e => e.Age).IsBetween(25).And(30)
                    .Select(e1 => e1.SelectAvg(e2 => e2.Salary))
                    .List<double>()
                    .Single();

Perfect!

In my opinion this one is the best.