Some time ago, I wrote a brief cookbook of NHibernate query patterns. In this post I’m going to look at the same query patterns, but using some of the new features of NHibernate 3 – specifically the baked in Linq provider that makes querying a breeze. I’ve published the code for this to codeplex here.
If you want to follow along, you’ll need the adventure works sample database set up on your SQL server. I’m also going to ignore the mapping configuration for now, but in the sample code, I’ve used the HBM mapping format – if I were doing this “for real” though, I’d be using the new “Loquacious” configuration features to map up my domain classes – why it’s called Loquacious, I have no idea, but it’s basically a baked in replacement for fluent NHibernate.
Ok, so the tables we’re interested in for this exercise are shown below;
Find all addresses in the city of London.
Starting out nice and easy;
Find all addresses in London, where postcode starts with SW
Still pretty simple, but a good test of how the provider translates the string operation “StartsWith” into SQL;
Find all addresses with a parent state/province that has a country/region code of GB
Querying down a join is simplistic;
Find all addresses with a parent state/province that has a country/region code of GB or FR
Still straight forward;
Find all customer accounts with a "Home” address in the region of GB
Getting a bit more complex, we’re joining in the chain from customer to address to do some filtering. The query this generates is still nice and efficient;
Find all customer accounts with a “Home” address in the region of GB with more than one order
Now, this sounds complex, but actually, it’s the same query as above, but with some projections. No need to join the orders table in;
Find all customer accounts with a “Home” address in the region of GB with more than 2 orders and a total spend over $6000
Building on the last query, let’s do some more projections for our query.
In my opinion, this makes life much easier than trying to remember how to use aliases, detached criteria and so on – although if you prefer that mechanism, it’s all still there, please yourself