Using ‘Func’s and extension methods to dynamically concatenate query filters

Lately I was working on a piece of software which has a data repository with a method that composes a SQL query on the basis of some input parameters. Some functionality was added to the software which includes the addition of a multi-selection filter where the filtering criteria should be applied with a logical OR (e.g. isOnSale OR hasSpecialOffer OR isVATExcempt, etc) There could be several filtering criteria to concatenate in this way. The manual and boring way of doing this in code would be to calculate all the combinations of all the possible filtering criteria and create IF blocks for each of them. However, I thought of a lazier and smarter way of having the code do that automatically. Funcs came in handy. Here is what I did.

Extension method for IEnumerable<T>

Create an extension method which calculates all the possible combinations of a collection of items.

public static IEnumerable> GetCombinations(this IEnumerable source)
{
    if (null == source)
        throw new ArgumentNullException(nameof(source));
    
    T[] data = source.ToArray();

    return Enumerable
        .Range(0, 1 << (data.Length))
        .Select(index => data
            .Where((v, i) => (index & (1 << i)) != 0)
        .ToArray())
        .Where(x => x.Any());
}

Given a collection of items “A”, “B”, “C”, this method would produce “A”, “AB”, “AC”, “ABC”, “B”, “BC”, “C”.

Create a helper method which adds an OR block to a query

public static string AppendOrBlockToQuery(string query, Dictionary<string, bool> orFilters)
{
    if (!orFilters?.Any() ?? true)
        return query;

    if (string.IsNullOrWhiteSpace(query))
        return string.Empty;

    var criteria = orFilters.Select(x => Tuple.Create<Func<bool>, string>(() => x.Value, $"{x.Key} = 1"));

    return $"{query} AND ({GetOrBlock(criteria)})";
}

The key in the dictionary is the name of the column in the database table, the value is the corresponding filter value. If true, it means that the filtering criterion was selected. From the dictionary I created an IEnumerable<Tuple<Func<bool>, string>>. The Func in the Tuple is the condition that needs to be verified, the string is the corresponding query part that needs to be appended to the query.

Use the extension method to calculate the filter combinations

private static string GetOrBlock(IEnumerable, string>> criteria)
{
    string orBlock = "";
    var filterCombinations = criteria.GetCombinations().OrderByDescending(x => x.Count());

    foreach (var filterCriteria in filterCombinations)
    {
        if (filterCriteria.Select(x => x.Item1).All(x => x()))
        {
            orBlock += string.Join(" OR ", filterCriteria.Select(x => x.Item2));
            break;
        }
    }

    return orBlock;
}

I applied the GetCombinations extension method to the IEnumerable<Tuple<Func<bool>, string>> to calculate all the filter combinations. Then, with a very simple foreach loop I was able to build the query using 1 single If.

The tests

To make sure that I got everything right, I created unit tests (using xUnit) for my method.

public static IEnumerable<object[]> TestCases()
{
    var query = "select * from items where active = 1";

    // ...

    yield return new object[] {
        query,
        new Dictionary<string, bool>() {
            { "columnA", true },
            { "columnB", false },
            { "columnC", true },
            { "columnD", false },
            { "columnE", true },
            { "columnF", true }
        },
        $"{query} AND (columnA = 1 OR columnC = 1 OR columnE = 1 OR columnF = 1)"
    };

    yield return new object[] {
        query,
        new Dictionary<string, bool>() {
            { "columnA", true },
            { "columnB", true }
        },
        $"{query} AND (columnA = 1 OR columnB = 1)"
    };
}

[Theory()]
[MemberData(nameof(TestCases))]
public void AppendOrBlockToQueryTest(string query, Dictionary<string, bool> orFilters, string expectedResult)
{
    var result = SqlQueryHelper.AppendOrBlockToQuery(query, orFilters);

    Assert.Equal(expectedResult, result);
}

The repo

The repo with a sample solution is on https://github.com/erionpc/FuncSample