NHibernate Queries – Should I use HQL or Criteria?

time to read 3 min | 420 words

This is a common question that I get asked, what is better? What should I use? The actual answer is complex, and some of it, at least, depends on personal preferences. More than that, it also depends on the type of queries that you have.

In general, all queries fall into one of the following distinctions:

  • Select by Id
    • select * from Blogs where id = @id
  • Select by fixed criteria (parameters may change, but the actual query itself is fixed)
    • select top 10 * from Posts where PublishDate <= @today order by PublishDate desc
  • Select by dynamic criteria
    • select * from SupportTickets where Title like @title
    • select * from SupportTickets join Resolutions on … where SupportTickets.Title = @title and Resulotions.Status = ‘Resolved’

The first is usually best served by using Get or Load, since they also check NHibernate’s caches and are intended to handle just this scenario.

The second is by far the most common, and it represent perform some kind of a query (not by id) using fixed logic. We may change the parameter values, but the query shape itself is fixed and cannot be changed. This is usually best served by using HQL. HQL is how NHibernate exposes most of its power, and it allow you to operate on your domain model using set based, object oriented, language. Its similarity to SQL means that it is often very intuitive when it comes the time to write and read it.

Its biggest weakness, however, is when you want to perform queries using some dynamic criteria. For example, if you want to have a search page with multiple optional search fields. For that scenario, we have the Criteria API, which allow us to dynamically, painlessly and easily compose queries.

There is also a slightly different side to that, since we can dynamically create queries, the Criteria API also allow us to programmatically create queries, which is very useful for things like query compositions, dynamically enhancing queries, etc.

Beyond those rough guidelines, you have to consider that HQL expose by far the most of NHibernate’s capabilities, but that the Criteria API is almost as complete. The rest is just a matter of personal taste.