When developing applications that use a relational database we often spend a reasonable amount of time constructing queries for retrieving the data that we need out of the database. The way by which we (as developers) go about retrieving this data varies considerably across applications. Some people build Sql Command objects and add their parameters to it, others build a query object for their O/R Mapper and pass it on through, and still others build the SQL for retrieving their objects from scratch from within their C# code.
Over the course of the last several months we’ve changed the way we query for data a number of times. In the beginning we started with hand coding expressions in string literals like so:
List<Customer> customers = repository.FindAll(“Age > 20 AND Name = ‘foo’”);
While this did the job it wasn’t ideal since if a column name changed it wouldn’t be caught at compile time. We also ended up with some logic for dealing with certain cases spread across our code base. To help reduce the duplication of code that we started having we created “criteria” objects to encapsulate that logic. Our code became something like:
Criteria c = new AndCriteria(
new PropertyCriteria(Customer.Columns.Age, 20, Operator.GreaterThan),
new PropertyCriteria(Customer.Columns.Name, “bar”)
);
List<Customer> customers = repository.FindAll(c);
This helped reduce the amount of code that was scattered across our code base and got rid of the problems that having column names within string literals introduced, however, our query became pretty verbose. The next step in the evolution of our query API was to take the best of the first approach and combine it with the best of the second approach. In the end we ended up with this:
List<Customer> customers = repository.FindAll(Customer.Columns.Age == 20 & Customer.Columns.Name == “foo”);
Now we have the nice compact syntax of the original query, nice strongly “typed” columns (via code-gen), and the benefits of having all the custom logic that happens behind the scenes encapsulated in our base criteria class (which is what gets returned as a result of the expression above). The other benefit of this approach is that it isn’t tied to any particular O/R Mapper or data access strategy. If we decide we want to move from one O/R Mapper to the other its just a matter of writing a single criteria “parser” that can translate our criteria objects into OPath, HQL, OQL, or etc. The next step in our evolution will be getting the same criteria expression to also work with the various find operations we have on in memory collections such as List<T>.FindAll().