Best practices for querying with NHibernate

Question

I've come back to using NHibernate after using other technologies (CSLA and Subsonic) for a couple of years, and I'm finding the querying a bit frustrating, especially when compared to Subsonic. I was wondering what other approaches people are using?

The Hibernate Query Language doesn't feel right to me, seems too much like writing SQL, which to my mind is one of the reason to use an ORM tools so I don't have to, furthermore it's all in XML, which means it's poor for refactoring, and errors will only be discovered at runtime?

Criteria Queries, don't seem fluid enough.

I've read that Ayende's NHibernate Query Generator, is a useful tool, is this what people are using? What else is out there?

EDIT: Worth a read http://www.ayende.com/Blog/archive/2007/03/17/Implementing-Linq-for-NHibernate-A-How-To-Guide--Part.aspx

Answer 1

You could try Linq for NHibernate.

Here's a nice introduction:
http://frickinsweet.com/ryanlanciaux.com/post/RhinoCommons2c-NHibernate-and-ASPNET-MVC-Part-5---LINQ-to-NHibernate.aspx

Answer 2

The thing with LINQ for NHibernate is still in beta; I'm looking forward to NHibernate 2.1, where they say it will finally make the cut.

I made a presentation on LINQ for NHibernate around a month ago, you might find it useful. I blogged about it here, including slides and code:

LINQ for NHibernate: O/R Mapping in Visual Studio 2008 Slides and Code

Answer 3

To rid yourself of the XML, try Fluent NHibernate

Linq2NH isn't fully baked yet. The core team is working on a different implementation than the one in NH Contrib. It works fine for simple queries though. Use sparingly if at all for best results.

As for how to query (hql vs. Criteria vs. Linq2NH), expose intention-revealing methods (GetProductsForOrder(Order order), GetCustomersThatPurchasedProduct(Product product), etc) on your repository interface and implement them in the best way. Simple queries may be easier with hql, while using the specification pattern you may find the Criteria API to be a better fit. That stuff just stays encapsulated in your repository, and if your tests pass it doesn't much matter how you implement.

I've found that the Criteria API is cumbersome and limiting but flexible. HQL is more my style (and it's better than SQL - it's object based, not schema based) and seems to work better for me for simple GetX methods..

Answer 4

I use Linq for NHibernate by default. When I hit bugs or limitations, I switch to HQL.

It's a clean approach if you keep all your queries together in a data access class, such as a Repository.

public class CustomerRepostitory()
{ 
  //LINQ for NHibernate     
  public Customer[] FindCustomerByEmail(string email)
  {
     return (from c in _session.Linq<Customer>() where c.Email == email).FirstOrDefault();
  }

  //HQL
  public Customer[] FindBestBuyers()
  {
    var q = _session.CreateQuery("...insert complex HQL here...");
    return q.List<Customer>();
  }
}

You asked about refactoring. LINQ is obviously taken care of by the IDE, so for any remaining HQL, it's fairly easy to scan these repository classes and change HQL by hand.

Putting HQL in XML files is a good practice, maybe see if the ReSharper NHIbernate plugin can handle the query refactoring by now?

A big improvement when writing or refactoring queries (HQL or LINQ) is to put finder methods under unit test. This way you can quickly tweak the HQL/LINQ until you get a green bar. The compile/test/feedback loop is very fast, especially if you use an in-memory database for testing.

Also, if you forget to edit the HQL after refactoring, the unit tests should let you know about your broken HQL very quickly.

Answer 5

An alternative to LINQ-to-NHibernate and Ayende's NHQG is to generate NHibernate Expressions/Restrictions from C#3 Expressions. This way you get a more strongly-typed Criteria API.

See:

• http://bugsquash.blogspot.com/2008/03/strongly-typed-nhibernate-criteria-with.html
• http://www.kowitz.net/archive/2008/08/17/what-would-nhibernate-icriteria-look-like-in-.net-3.5.aspx
• http://nhforge.org/blogs/nhibernate/archive/2009/01/07/typesafe-icriteria-using-lambda-expressions.aspx

Answer 6

scrap nHibernate and go back to Subsonic if you can. In my opinion, Subsonic is a far more fluent and testable ORM/DAL. I absolutely hate HQL what's the point of a weakly typed query in an ORM? And why would I use Linq/nH/SQL when I can just use Linq to SQL and cut out a layer?

nHibernate was a good ORM when Subsonic wasn't around, but now, it's just plain awful to work with in comparison. It easily takes me 2 times longer to do stuff with nHibernate vs Subsonic. Testing is a pain since nHibernate is runtime, so now I need to employ a few QA engineers to "click" around the site instead of getting a compile time error.