IntroductionNHibernate 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!
HQLSince 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.
ICriteriaThe 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.
LinqNHibernate 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.
QueryOverQueryOver 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.
