NHibernate : Queries

How you can query your repository using NHibernate ?

In the NHibernate box you actually having seven ways:

1. HQL
2. Criteria
3. QueryOver
4. LINQ
5. H-SQL
6. SQL
7. Custom DSL

HQL

HQL is the anachronism of : Hibernate Query Language. HQL, perhaps, is one of the most old and powerful ObjectOriented query system (at list in .NET). In JAVA HQL can be used to query entities persisted even in XML.

In my opinion HQL is the most powerful query system specific for a persistence-layer. Unfortunately, so far, we can’t change the .NET compiler integrating HQL to C# so, HQL is expressed in a string. In JAVA world they have an Eclipse plug-in to support intellisense and refactoring even for HQL but… in our world (.NET) we haven’t it, at least so far (look here ).

HQL does support even DML (obviously all still OO).

You can easy extend HQL through a custom implementation of Dialect.

Criteria

Criteria is a OO query API and is the better solution for dynamic queries (to avoid string concatenation of HQL).

Criteria is even extensible through implementations of ICriterion and IProjection. Using Criteria you can solve the problem of string-concatenation but you still have strings to specify properties names so: you can use Criteria for dynamic queries but there is no reason to use it for static queries.

QueryOver

QueryOver is a new way that will be available in NH3.0.0 (the next mayor release). QueryOver is based on Criteria (with same limitations/power) but, using lambdas expressions, the result clause is more readable and it support property-renaming because it does not suffer the “string” problem.

An example is here.

LINQ

Well… you should know what is LINQ. With NH2.1.0GA (the last release) we have deployed one implementation of a LINQ-provider based on Criteria (with the same limitations Criteria has). Steve Strong is working in a new provider and it will be available with NH3.0.0.

LINQ is a very powerful OO query system but not so specific for persistence. This fact give you a powerful system in memory but make the translation to SQL something complicated.

An example of this fact was reported by Steve in our dev-list

var q = from o in db.Orders
select new
{
o.OrderId,
DiscountedProducts =
from od in o.OrderLines
where od.Discount > 0.0m
select od, FreeShippingDiscount = o.Freight
};

to be efficiently executed we need a part translated to SQL and executed in one db-roundtrip and the other part should be executed in memory. The problem here is that you can’t optimize which should be the behavior with the persistent part as you can do in HQL using the “join fetch” clause.

H-SQL

An H-SQL is basically an standard SQL with some special tags. An example will be better than many words:

var q = session.CreateSQLQuery("select {sim.*} from Simple {sim} where {sim}.date_ = :fred")
.AddEntity("sim", typeof(Simple))
.SetTimestamp("fred", sim.Date);

Specials tags are “{sim.*}” and “{sim}”. The advantage of H-SQL is that you can use any kind of sentence supported by your specific RDBMS mixed we some more OO “tags”.

SQL

SQL is perhaps the most powerful DataCentric language. As HQL and H-SQL you can write your SQL in the mappings and use it through Named-Queries feature so you can write different SQL for different RDBMS without touch your C# code.

If you have a pure DataCentric task to do and you know how do it using SQL you don’t have a valid reason to find its translation using an OO query. For example:

SELECT ID,ClientID,
(SELECT COUNT(*) FROM Clock WHERE ClientID=c.ID AND Status='ON') AS ClocksUp
(SELECT COUNT(*) FROM Clocks WHERE ClientID=c.ID AND Status='OFF') AS ClocksDown
FROM Client AS c

Obviously you can use SQL even as DML

session.CreateSQLQuery("delete from VEHICLE where (TofC = 21) and (Owner = :owner)")
.SetString("owner", "NotThere")
.ExecuteUpdate();

DSL

You can even define your own DSL, to query your domain, and inject the translator through NHibernate’s configuration… but it is absolutely not a trivial task.

NHibernate actually have two available translators: one named “Classic query translator” and the other based on “HQL ANTLR-parser”.

Conclusion

When you have an issue using a “query system” leave the style/taste/fashion matters to Fashion TV and, as a good IT guy, have a look in your tool-box avoiding any kind of fundamentalism.