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:
{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.
ICriteria
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.
Linq
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
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.
Comments
Hi.
Very useful topic - currently we consider what to use for querying on NHibernate with complex filters and orders.
I thought about NHibernate.Linq project that provide Linq to NH provider. As I see the new version of Linq to NH provider is integrated in NHibernate 3 via Query<>. It based on IQueryable interface.
Linq to NH provides native LINQ syntax. I don't like SQL-like syntax too, I use it with external method calling.
Sample with LINQ:
double salary = session.Query<Employee>().Where(e=> e.Age > 25 && e.Age < 30 )
.Average(e => e.Salary);
LINQ limits you to work strongly-typed only with Expression but sometimes you want the flexibility to dynamically construct queries on the fly. To solve this problem MS provides Dynamic LINQ extension.
To download DLINQ (DynamicQueryable class)
http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx
With DLINQ you can do for ex. .Where("Age > 25 AND Age <30") and mix it with regular LINQ expression.
Sample with LINQ+DLINQ
double salary = session.Query<Employee>().Where("Age > 25 AND Age <30")
.Average(e => e.Salary);
In my opinion QueryOver is closer to SQL and has no limitation such Linq has.
It is important when you work with database (in case of NHibernate).
In additional QueryOver is extension of ICriteria. It means in first compatibility with existing ICriteria code, and in second if you have experience with ICriteria you will get started with QueryOver immediately, no need to learn Linq at all.
I agree that QueryOver is nice tool but it's part of NHibernate.
LINQ provides some abstraction level for querying and manipulation with collection data that can come from different sources via LINQ provider: XML, objects, ORM like NHibernate or LINQ-to-SQL\Entity FW, services like Amazon, Lucene, LDAP and etc.
LINQ is very extensible - for ex. Skip implementation in DLINQ
public static IQueryable Skip(this IQueryable source, int count){
if (source == null) throw new ArgumentNullException("source");
return source.Provider.CreateQuery(
Expression.Call(
typeof(Queryable), "Skip",
new Type[] { source.ElementType },
source.Expression, Expression.Constant(count)));
}
It's true that LINQ implementation is complicated a bit but for regular using is not necessary to understand it.
And don't forget about Parallel LINQ in FW 4.0 - maybe some of LINQ providers will support it.
A lot of thanks for topic - it's really burning issue.