The Catch Block #98 - Dapper Where Clause Builder

Welcome to the 98th edition of The Catch Block!

In this edition, we build a class that can generate relatively-complicated SQL WHERE clauses from a group of parameters.

Plus: you should deploy more; feature flags; designing better APIs; cancellation; and VS's 25th anniversary!

Dapper Where Clause Builder

My team and I made the commitment to use Dapper entirely for our major project, and so far it's going pretty well. But occasionally we are running across things like enormously complicated queries, which Entity Framework can do fairly simply but Dapper has a harder time with.

Queries such as this, from one of our pages that has a bunch of search fields:

public List<LocationDetail> SearchForLocations(LocationSearchVM viewModel)
{
    var resultList 
        = _locationEntities.PickupLocations
          .Where(x => viewModel.Id == null || x.id == viewModel.Id)
          .Where(x => viewModel.LocationID == null || x.locationid == viewModel.LocationID)
          .Where(x => viewModel.LocationName == null || x.locationname == viewModel.LocationName)
          .Where(x => viewModel.Phone == null || x.phone == viewModel.Phone)
          .Where(x => viewModel.Address1 == null || x.address1 == viewModel.Address1)
          .Where(x => viewModel.Address2 == null || x.address2 == viewModel.Address2)
          .Where(x => viewModel.PostalCode == null || x.postalcode == viewModel.PostalCode)
          .Where(x => viewModel.Active || !x.active)
          .Take(1000)
          .ToList();
}

Notice that this particular query has to do a null check, and if the property in question is null, the item gets included in the result set.

This is a terribly complicated query at first glance, but peer a little closer and you start to see the similarities. I decided that it might be possible to create a class which generates the above code as a SQL WHERE clause, and parameters, for Dapper to consumer. Turns out, it IS possible.

In this post and the next (which will come out in two weeks, see below), we'll show how to build the DapperWhereClauseBuilder class for a few types of queries, and how to use it to replace the above query. Let's go!

The Internals of DapperWhereClauseBuilder

The DapperWhereClauseBuilder class needs to do the following things:

1. Accept a string parameter name and a value for any given parameter.
2. Accept "is not null" parameters.
3. Accept "is in this collection" parameters.
4. Generate the WHERE clause that results from all the given parameters.

We're going to do 1 and part of 4 in this post, and we'll do the others in the next one.

So, let's start with a new DapperWhereClauseBuilder class and an internal collection of parameters, which we will make a SortedDictionary<string, object> type:

public class DapperWhereClauseBuilder
{
    private readonly SortedDictionary<string, object> singleValues = new SortedDictionary<string, object>();
}

We now need accessor methods to add parameters of different types to the where clause builder. Let's start with a simple one, for int:

public class DapperWhereClauseBuilder
{
        private readonly SortedDictionary<string, object> singleValues = new SortedDictionary<string, object>();

    public DapperWhereClauseBuilder AddValue(string parameterName, int? value)
    {
        if (value.HasValue && !singleValues.ContainsKey(parameterName))
            singleValues.Add(parameterName, value.Value);

        return this;
    }
}

Note that we're doing two kinds of checks on the passed-in value: confirming that the nullable value actually has a value, and confirming that the value parameterName does not already exist in our dictionary of parameters. We also made the method return DapperWhereClauseBuilder so it can be used in a fluent manner.

We will need quite a few of this basic AddValue() methods; here's two more:

public class DapperWhereClauseBuilder
{
    //...Rest of implementation

    public DapperWhereClauseBuilder AddValue(string parameterName, double? value)
    {
        if (value.HasValue && !singleValues.ContainsKey(parameterName))
            singleValues.Add(parameterName, value.Value);

        return this;
    }

    public DapperWhereClauseBuilder AddValue(string parameterName, long? value)
    {
        if (value.HasValue && !singleValues.ContainsKey(parameterName))
            singleValues.Add(parameterName, value.Value);

        return this;
    }
}

For the majority of these methods, the logic is the same even as the type of the second parameter changes: ensure that the value is not null, and ensure that the parameterName does not already exist in the dictionary. The implementation of that logic changes, very slightly, when we get to adding a value of type string: