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:

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.

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!

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:

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:

returnRepository<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.

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.