NHibernate query patterns redux– what does 3.2 bring to the table?

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;

   1: var query = from a in _session.Query<Address>() 

   2:             where a.City == "London" 

   3:             select a;

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;

   1: var query = from a in _session.Query<Address>() 

   2:             where a.City == "London" && a.PostCode.StartsWith("SW") 

   3:             select a;

Find all addresses with a parent state/province that has a country/region code of GB

Querying down a join is simplistic;

   1: var query = from a in _session.Query<Address>() 

   2:             where a.StateProvince.CountryCode == "GB" 

   3:             select a;

Find all addresses with a parent state/province that has a country/region code of GB or FR

Still straight forward;

   1: var query = from a in _session.Query<Address>() 

   2:             where a.StateProvince.CountryCode == "GB" || a.StateProvince.CountryCode == "FR" 

   3:             select a;

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;

   1: var query = from c in _session.Query<Customer>()

   2:             join ca in _session.Query<CustomerAddress>() on c equals ca.Customer

   3:             join a in _session.Query<Address>() on ca.Address equals a

   4:             where ca.Type.Name == "Home" && a.StateProvince.CountryCode == "GB"

   5:             select c;

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;

   1: var query = from c in _session.Query<Customer>()

   2:             join ca in _session.Query<CustomerAddress>() on c equals ca.Customer

   3:             join a in _session.Query<Address>() on ca.Address equals a

   4:             where ca.Type.Name == "Home" && a.StateProvince.CountryCode == "GB" && 

   5:                   c.Orders.Count() > 1

   6:             select c;

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.

   1: var query = from c in _session.Query<Customer>()

   2:             join ca in _session.Query<CustomerAddress>() on c equals ca.Customer

   3:             join a in _session.Query<Address>() on ca.Address equals a

   4:             where ca.Type.Name == "Home" && a.StateProvince.CountryCode == "GB" && 

   5:                   c.Orders.Count() > 2 && c.Orders.Sum( ov => ov.Total ) > 6000

   6:             select c;

In closing….

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