Query the database with NHibernate 3.0

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!

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:
{syntaxhighlighter brush: xml} var salary = session.CreateQuery(@" select avg(employee.Salary) from Employee employee where employee.Age >= 25 and employee.Age <=30") .UniqueResult();{/syntaxhighlighter}
Looks good, readable but not strongly typed.
Go next one.

The ICriteria API is NHibernate's implementation of Query Object. {syntaxhighlighter brush: xml}var salary = session.CreateCriteria(typeof(Employee)) .Add(Restrictions.Between("Age", 25, 30)) .SetProjection(Projections.Avg("Salary")) .UniqueResult();{/syntaxhighlighter} 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.

NHibernate 3.0 has a built-in Linq provider.
{syntaxhighlighter brush: xml} var query = from employee in session.Query() where employee.Age >= 25 && employee.Age <= 30 select employee.Salary; var salary = query.Average();{/syntaxhighlighter} 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 combines the use of Extension Methods and Lambda Expressions to provide a statically typesafe wrapper round the ICriteria API.
{syntaxhighlighter brush: xml} var salary = session.QueryOver() .WhereRestrictionOn(e => e.Age).IsBetween(25).And(30) .Select(e1 => e1.SelectAvg(e2 => e2.Salary)) .List() .Single();{/syntaxhighlighter} Perfect!
In my opinion this one is the best.</p>