Building Dynamic Queries with a Criteria API

One of the biggest strengths of O/R mappers is that many have object-oriented "query by criteria" API. I will show what I mean by that in a bit. But to explain why I think a criteria API is such a great thing I will show the alternatives first.

Lets take a common search form scenario where a user can specify a number of customer filtering options like date added, name, the customer should have an address, the customer should be linked to a specific salesman, etc. If you were forced to use a stored procedure how would you implement this query?

I have seen a couple of solutions to problems like this, here is one that is really bad:

IF @name = ''
BEGIN 
     --- the whole query for this case 
END    
ELSE IF @name <> '' AND @shouldHaveAddress IS NOT NULL 
BEGIN 
    --- the whole query for this case 
END 
ELSE IF @name <> '' AND @shouldHaveAddress IS NOT NULL AND @salesmanNr <> '' 
    --- the whole query for this case 
END

Here the whole query is duplicated for each possible parameter mutation. I have seen a stored procedure like this recently that duplicated a very long query about 10 times (with very small variations in the where and join clauses).

Another solution is to complicate the query with embedded IFs, CASE and additional OR statements. But this solution does not only make the query unintelligible but also I think has some limitations. The solution that I see many arrive at is to build the query using string concatenation.

SET @sqlSelect = 'SELECT distinct ' + @NEWLINE + ' ' + @returnColumns + @NEWLINE
SET @sqlFrom = ' FROM Customers cust' + @NEWLINE
IF @companies is NOT NULL
BEGIN
  SET @sqlFROM = @sqlFROM + ' JOIN Companies comp on comp.Id=cust.CompanyId' + @NEWLINE
  SET @sqlFROM = @sqlFROM + ' LEFT JOIN Addresses addr on addr.Id=cust.AddressId ' + @NEWLINE  
END
IF @salesmanId IS NOT NULL
BEGIN
  SET @sqlFROM = @sqlFROM + ' JOIN Salesmen sal on sal.Id=' + @salesmanId 
END
...
..
.

I actually think that building up the query like this is not all that bad, however I would do it in C# and skip the stored procedure. The reason I prefer this approach is that it there is less duplication and because it is somewhat easier to maintain. But it is far from what we want!

So how do can we handle this via the nhibernate query api?

ICriteria query = session.CreateCriteria(typeof(Employee));

if (searchOptions.FirstName != null)
{
  query.Add(Expression.Eq("FirstName", searchOptions.FirstName));
}

if (!searchOptions.LastName != null)
{
  query.Add(Expression.Eq("LastName", searchOptions.LastName));
}

if (searchOptions.PhoneNumber != null)
{
  query.CreateCriteria("PhoneNumbers")
    .Add(Expression.Like("Number", searchOptions.PhoneNumber + "%"));
}
return query.List<Employee>();

The nhibernate criteria api might be verbose for simple scenarios compared to a SQL query in a string, but it is scenarios like the above that it really shines :) But wait it can be better. If you use Ayende's great NHibernate Query Generator (NHQ) you can do this:

QueryBuilder<Employee> query = new QueryBuilder<Employee>();

if (options.FirstName != null)
{
  query &= Where.Employee.FirstName == options.FirstName;
}

if (options.LastName != null)
{
  query &= Where.Employee.LastName == options.LastName;
}

if (options.PhoneNumber != null)
{
  query &= Where.Employee.PhoneNumbers.Number.Like(options.PhoneNumber, MatchMode.Start);
}            

return Repository<Employee>.FindAll(query, OrderBy.Employee.LastName.Desc);

NHQ is a very clever code-gen util that you setup as a post-build step. It generates the Where/QueryBuilder classes from the nhibernate mapping files. The result is that you do not need any strings in your queries! More commonly you use it like this:

return Repository<User>.FindAll(Where.User.Name == name);

I think it comes very close to using LINQ. So to end this post I guess I need to show how the above query would look with LINQ, because LINQ to SQL handles the above scenario pretty well.

IQueryable<Employee> query = linqContext.Employees;

if (options.FirstName != null)
{
  query = query.Where(emp => emp.FirstName == options.FirstName);
}

if (options.LastName != null)
{
  query = employees.Where(emp => emp.LastName == options.LastName);
}

if (options.PhoneNumber != null)
{
  query = from emp in query
	  from phoneNr in emp.PhoneNumbers
	  where phoneNr.Number.StartsWith(options.PhoneNumber)
	  select emp;
}            

return employees.ToList();

The above code is nice, but I think querying across relations is handled nicer in the nhibernate critiera api especially when using NHQ. Well that is all, now you know why I think a criteria API is are one of the mayor reasons to use an O/R mapper.