LINQ to NHibernate: A Vast Improvement
Comments(11) In honor of the (relatively) new 1.0 status of LINQ to NHibernate, I’ve been spending the last few nights LINQifying some old NHibernate queries I’ve written and I must say that I’ve been very pleased.
There have traditionally been two ways of specifying NHibernate queries: HQL and the criteria query API.
Although I have an easier time deciphering HQL, which is basically a SQL-like string using classes instead of tables, I’ve tended to use the criteria API because it was somewhat strongly typed and thus easier to maintain with the help of refactoring tools like ReSharper.
Thanks to the new NHibernate LINQ provider, I can now work in a mode that is not only more type safe, but also much more readable.
Look at these before and after queries and judge for yourself:
Before (Criterion API)
1: public IList<Call> GetCallsByDate(DateTime beginDate, int interpreterId)
2: {
3: ICriteria criteria = Session.CreateCriteria(typeof(Call))
4: .CreateAlias("Customer", "Customer")
5: .Add(Restrictions.Gt("StartTime", beginDate))
6: .Add(
7: Restrictions.Or(
8: Restrictions.Lt("EndTime", DateTime.Now), Restrictions.IsNull("EndTime")
9: )
10: )
11: .Add(Restrictions.Eq("Interpreter.Id", interpreterId))
12: .AddOrder(Order.Desc("StartTime"))
13: .AddOrder(Order.Desc("Customer.Name"));
14:
15: return criteria.List<Call>() as List<Call>;
16: }
After (LINQ to NHibernate)
1: public IList<Call> GetCallsByDateWithLinq(DateTime beginDate, int interpreterId)
2: {
3: var query = from call in Session.Linq<Call>()
4: where call.StartTime > beginDate
5: && (call.EndTime == null || call.EndTime < DateTime.Now )
6: && call.Interpreter.Id == interpreterId
7: orderby call.StartTime descending, call.Customer.Name
8: select call;
9:
10: return query.ToList();
11: }
Apparently the NHibernate guys are still working on a full featured LINQ provider for a future version of NHibernate, but decided that the LINQ provider in the contrib project has been tested enough and used in enough production systems to promote it to RTM status.
The one thing I did notice when peeking at the SQL in Profiler is that the Linq provider produced an extra join that the regular Criteria API figured out wasn’t necessary because I was just referencing the foreign key column in the where clause. I’m guessing that minor differences like this will be addressed in the next version of the provider.
In the meantime, I’m still hooked enough to want to use this approach instead.
If you want to give LINQ to NHibernate a test run, just download and reference the one required dll here (make sure you’re using the same version of NHibernate).
If you’re still not comfortable with LINQ sytnax, here’s a simple example based MSDN tutorial to get you started.
Popularity: 16% [?]
It’s a debatable readability improvement, but you can treat each where condition separately, rather than ANDing them together. It’s sometimes more readable, and definitely makes it easier to add and remove lines in the future:
where call.StartTime > beginDate
where (call.EndTime == null || call.EndTime < DateTime.Now) // Reordered to put the null check in front, just in case the query is ever run against in-memory data
where call.Interpreter.Id == interpreterId
Thanks for catching my null bug!
Readability is definitely one of those subjective areas of software development. I’m sure my preference is partially because I’ve written more SQL queries in my life than criteria api based queries.
I actually am not very fond of using multiple wheres instead of &&, but then again that probably goes back to my sql biases.
Leaving the readability aspect aside, the linq query is strongly typed, while the criteria one isn’t. That’s also a big difference.
Definitely a win for type-safety and more natural expressions. This post’s title does not lie. Shame about the immediate query evaluation though.
Multiple where statements in a sample like this one would strike me as a sort of paranoid way of manually short circuiting. I could see it being handy if you’re building the query iteratively and any one of X steps might add a where clause though.
[...] link is being shared on Twitter right now. @ajlopez, an influential author, said RT @delicious_prog: [...]
[...] LINQ to NHibernate: A Vast Improvement – Russell Ball talks about his experience moving from the NHibernate criteria API to the newly RTM LINQ implementation. [...]
[...] LINQ to NHibernate: A Vast Improvement – Russell Ball [...]
I’d take LINQ over Criteria any day.
[...] how can help the supplier, if you’re user of NHibernate? The following example from Caffeinated Coder demonstrates how to query database can be simplified and made more readable using Linq and also [...]
That extra join is more of a concern than you make it out to be if you’re referencing more than one property across your query – it also pulls back all the data in the select clause too!
I did a write up of this in my blog linked above entitled “Why Linq2Nhibernate is not ready for production use”
http://blog.codeofrob.com/arch.....n-use.aspx
Very interesting. I think I’ll give it a try…