NHibernate Query Generator in Action

Andrew.Clancy — Fri, 10 Oct 2008 10:07:40 GMT

NHibernate querying is great – it gives us the ability to query objects directly without the need to know anything about the underlying relational schema. It has a couple of major flaws though: 1. property names are accessed via strings, so we don’t know until runtime whether they exist, or are mapped 2. it’s ugly and cumbersome – frankly probably one of the major barriers I’ve had to ORM until now.

Ayende’s NHibernate Query Generator fixes both of these things. Mapped properties are accessed via properties as nature intended, and querying is a lot easier to read and write. Not quite as neat as Linq-to-NHibernate will be, but so much better than native NHibernate querying that I wonder why it wasn’t included in the trunk.

Having said that – here’s why you shouldn’t wait for the linq version if you’re doing loads of NHib querying right now. The following checks for city & state in a person’s address using native NHibernate criteria - note especially the strings for property names if not the unreadable syntax & subquery.

DetachedCriteria addressCrit = DetachedCriteria.For<Address>()
.SetProjection(Projections.Id())
.Add(Restrictions.Like(“City”, "Brisbane", MatchMode.Anywhere)
|| Restrictions.Eq(“State”, "NSW"));
DetachedCriteria personQuery = DetachedCriteria.For<Person>()
.CreateCriteria(“Addresses”)
.Add(Subqueries.PropertyIn("ID", addressCrit));

I reckon the NHQG version speaks for itself:

Where.Person.Addresses .Add(Where.Address.City.Contains("Brisbane")
|| Where.Address.State == "NSW")

Most of the stuff you’d hope for is in the NHQG.
All of the NHibernate functions are there (including ordering, aggregates etc), eg:

Where.Order.StatusID.InG(statusList);

Including chaining:

Where.Address.City.Contains("Brisbane")
|| Where.Address.State == "NSW") // && works too

Loads of expressions are overloaded:

Where.Comment.RecordedDate <= endDate // ==, !=, <, <=, >, >=, &&, ||, &=, |=

If you need a property of a property (to any level) you’ve got it:

Where.Person.PhoneNumber.AreaCode.StartsWith(“02”) // Like, Contains

I’ve sent Ayende a NHQGH patch for a few of my own additions – here’s where these start …

If you don’t have a mapping between properties, matching is still easy:

Where.Address.CountryID.Matches(Where.Country.Name.Contains(value))

That’ll match using Address.CountryID. Another match, this time using a different match id on the target:

Where.Person.Matches(Get.KnownAs.PersonID,
Where.KnownAs.IsKnownAs.Contains(forename))

Like/Contains/Starts with on lists:

Where.Person.Bio.ContainsList(new List<string>{“zodie”, “loves”, “nhqg”})

This is built using the To*Junction() extension methods:

keywords.Select(s => Where.Person.Bio.Contains(s)).ToDisjunction()

You can of course mix & match with standard NHibernate criteria, but pretty much everything I’ve found sofar is do-able using pure NHQG.

Where.Person.Addresses.Add(
Restrictions.Eq(Get.Address.Town.Like(“Brisbane”, MatchMode.Anywhere)

So, the key is here that all of your querying can now be done using properties on the NHQG rather than strings, allowing you to validate changes to your objects & mappings at compile time, rather than when you run your unit tests. That’s a pretty quick summary, but if you’re not convinced yet I can only recommend you give it a go – I’ve just applied it on our project and we’re already feeling the time saving, and more importantly extra confidence when refactoring.

The NHQG can be added to your solution as a Custom Tool so every time your mapping file is updated, the NHQG layer (it basically works by generating some C# for you) is updated. If anyone’s interested in a how-to on this, drop me a line. The NHQG can be found in Rhino tools here, hopefully including my extensions soon!

Andrew Clancy's Blog

NHibernate Query Generator in Action