+972 52-548-6969
NHibernate Queries – Should I use HQL or Criteria?
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.
Comments
For fixed criteria and if performance of a query is a big issue it is not only a matter of taste.
With HQL we can use a named query so the NH parsing of the query is done once at the beginning and you can easily replace the HQL with an SQL if needed.
The big problem I see for using HQL is the lack of Intellisense...
What about LINQ, where does that fit in?
Ayende,
So the performance are equal?
Yes
Personally I think if linq matures for nhibernate then it would should aim to replace hql.. not that I dislike hql but why wouldn't we want strongly typed queries of a strong typed domain model?
Anything that can turn a:
compile, run tests.. wait .. FAIL!
into a:
compi- FAIL
Is a ++ for me, but of course theres potential problems with how expressive linq can be compared to hql given that hql can refer to 'things' that arent part of the type system like linq does.
Totally agreed with Stephen, strongly typed queries is a big win (that's the reason I try to avoid HQL always).
I don't see LINQ replacing HQL. There are some operations like outer joins or batch DML operations that are not easily possible in LINQ. LINQ would be more of a Criteria replacement.
One of the issues with NHibernate query API's is you cannot combine/mix them. A good LINQ provider could make it possible to convert the results from each API into IQueryable. This way as much query code as possible can be strongly typed even if LINQ cannot support 100% of the operations. For example, eSQL and LINQ to Entities in EF can be combined in this way.
The first query in the article can be problematic for get/load, if one wants to use multiquery capabilities for example, we have:
Parent: Id, ParentName, ParentTitle, Child1s, Child2s
Child1: Id, Child1Name, Child1Title, Parent
Child2: Id, Child2Name, Child2Title, Parent
And you want to optimze loading upper relations to the following SQLs (as far as i know, none of the eager loading types can do that):
select Id, ParentName, ParentTitle from Parent where Id = 1
select Id, Child1Name, Child1Title, IdParent from Child1 IdParent = 1
select Id, Child2Name, Child2Title, IdParent from Child2 IdParent = 1
As far as I can see, you can only use HQL to achieve that result, am i mistaken?
There is another downside of this - if you want to navigate chilndren collection on parent like Parent.Childs1[0] after you've loaded all the entities with above queries, NHibernate doesn't know that there is already entire Child collection loaded via multiquery and is going to use another select to fetch that...
can you issue group by statements with Criteria API?
Yes
And what about Or statement from different levels with CriteriaAPI?
AFAIK in NH 1.2 this was not possible
example HQL:
select company
left join company.Manager as manager
from Company company
where c.Name = 'myCompanyName'
or manager.Name = 'myBossName'
yes
There is another difference.
Using named HQL.
@Stephen : BuildSessionFactory - FAIL
@All
Cache stuff stay outside the C# code.
personally I prefer criteria expressions because it I can chain them together easily and I can also encapsulate reusable criterias more easily as well.
Comment preview