A neat query pattern written in C#

By using nuget packages such as the brilliant Dapper it is possible to create a very concise way to access your database without using very much code. I particularly like the query pattern I’m going to go through today, it’s lightweight, simple and encourages composition!

Lets work from the outside in. Clone the query pattern github repository so you can follow along. I have written the program in the repository to work against the Northwind example database. If you haven’t got the Northwind database installed you can find information on that over on msdn.

Take a look at the program.cs file the essence of which is captured in the code snippet below:

Console.WriteLine("Enter category to search for:");
var name = Console.ReadLine();

var categories = queryExector.Execute<GetCategoriesMatchingNameCriteria, GetCategoriesMatchingNameResult>(new GetCategoriesMatchingNameCriteria { Name = name }).Categories;

Console.WriteLine("categories found matching name: {0}", name);

foreach (var category in categories)

The program above takes an input from the user and then does a like match with any category from the Northwind database that matches the user’s input. You can see how few liens of code this has taken to achieve. Note nowhere do we have reams of ADO .net code cluttering up the joint.

We are modelling a query as something that takes TCriteria and returns TResult. The interface for a query is shown below:

public interface IQuery<in TCriteria, out TResult>
    TResult Execute(TCriteria criteria);

By representing a query in this way and using Dapper the implementation is very short and to the point:

public class GetCategoriesMatchingNameQuery : IQuery<GetCategoriesMatchingNameCriteria, GetCategoriesMatchingNameResult>
    private readonly IDbConnection _dbConnection;

    public GetCategoriesMatchingNameQuery(IDbConnection dbConnection)
        _dbConnection = dbConnection;

    public GetCategoriesMatchingNameResult Execute(GetCategoriesMatchingNameCriteria matchingNameCriteria)
        string term = "%" + matchingNameCriteria.Name.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]") + "%";

        var result = new GetCategoriesMatchingNameResult
            Categories = _dbConnection.Query<CategoryEntity>(@"select CategoryID, CategoryName, Description from categories where categoryName like @term", new { term })                

        return result;

Note that is the class above the actual query is 3 lines of code! It can be done on one line if you wanted but that would make the code harder to read. As the queries are created automatically using an abstract factory in Castle Windsor we can do things like decorate them to apply caching or logging across the board or even both. Any cross cutting concern you can think of can be done easily. Queries can also be composed together easily you just have a query take a dependency on another query or multiple queries, then simply chain them together.

I really love how clean and concise this code is. By letting Dapper do the heavy lifting we aren’t bogged down with lots of ADO .net code that isn’t part of the IP of your business application.