Query NHibernate – Simple query patterns

There are a variety of ways to query NHibernate, including using HQL, the criteria API, Linq2NH, and the criteria API tied up with the Lambda extensions. My preference is the criteria API as it’s a powerful way of expressing dynamic queries. I’m going to explore how we go about implementing some of the more basic query patterns using this API;

Given the following database as an example, where a location can be split into multiple sub-locations, each sub-location belonging to a cost centre, we’ll drive out some simple queries;

 

First off, a simple location query by name;

ICriteria c =
    session.CreateCriteria(typeof(Location))
    .Add(Restrictions.Eq("Name", "Manchester"));

Next, a query that will check the name contains particular text and the postcode starts with text provided.

ICriteria c =
    session.CreateCriteria(typeof(Location))
    .Add( Restrictions.Conjunction()
        .Add(Restrictions.Like("Name", "%manchester%"))
        .Add(Restrictions.Like("Postcode", "M60%")));

How about looking for all sub locations where it’s parent location has a postcode of M60*?

ICriteria c = session.CreateCriteria(typeof(SubLocation))
    .CreateAlias("Location", "l")
    .Add(Restrictions.Like("l.Postcode", "M60%"));

Going further, how about finding all sub locations where some text can be found either in the sub location name, the location name, location address, location postcode or cost centre name? Simples….

ICriteria criteria = session.CreateCriteria(typeof(SubLocation))
    .CreateAlias("Location", "l")
    .CreateAlias("CostCentre", "cc")
    .Add(Restrictions.Disjunction()
        .Add(Restrictions.Like("Name", freeTextToMatch))
        .Add(Restrictions.Like("l.Name", freeTextToMatch))
        .Add(Restrictions.Like("l.Address", freeTextToMatch))
        .Add(Restrictions.Like("l.Postcode", freeTextToMatch))
        .Add(Restrictions.Like("cc.Name", freeTextToMatch)));

What about finding all locations that have more than one sub location?

DetachedCriteria subquery = DetachedCriteria.For(typeof(SubLocation))
   .Add(Restrictions.EqProperty("l.Id", "Location.Id"))
    .SetProjection(Projections.Count("Id"));

ICriteria criteria = session.CreateCriteria(typeof(Location), "l")
    .Add(Subqueries.Lt(1, subquery));

It’s a bit more complicated, but the first bit creates a reusable query that will look for sub locations where the sub location’s parent location Id is of the same value as the Id property of the aliased object “l” passed into the query. It then projects the results into a count against the Id property.

The second part then creates a criteria that will find all locations, aliased as “l” (for the sub query above), and execute the given subquery, ensuring that the result is > 1.

Finally, getting a bit more complex, find all locations having more than one sub location in cost centre named “ENGINEERING”?

DetachedCriteria subquery = DetachedCriteria.For(typeof(SubLocation))
        .CreateAlias("CostCentre", "cc")
        .Add(Restrictions.EqProperty("l.Id", "Location.Id"))
        .Add(Restrictions.Eq("cc.Name", "ENGINEERING"))
        .SetProjection(Projections.Count("Id"));

ICriteria criteria = session.CreateCriteria(typeof(Location), "l")
        .Add(Subqueries.Lt(1, subquery));