How to reuse a field filter in LINQ to Entities

Question

I am using EF and have a database table which has a number of date time fields which are populated as various operations are performed on the record. I am currently building a reporting system which involves filtering by these dates, but because the filters (is this date within a range, etc...) have the same behavior on each field, I would like to reuse my filtering logic so I only write a single date filter and use it on each field.

My initial filtering code looks something like:

DateTime? dateOneIsBefore = null;
DateTime? dateOneIsAfter = null;

DateTime? dateTwoIsBefore = null;
DateTime? dateTwoIsAfter = null;

using (var context = new ReusableDataEntities())
{
    IEnumerable<TestItemTable> result = context.TestItemTables
        .Where(record => ((!dateOneIsAfter.HasValue
                || record.DateFieldOne > dateOneIsAfter.Value)
            && (!dateOneIsBefore.HasValue
                || record.DateFieldOne < dateOneIsBefore.Value)))
        .Where(record => ((!dateTwoIsAfter.HasValue
                || record.DateFieldTwo > dateTwoIsAfter.Value)
            && (!dateTwoIsBefore.HasValue
                || record.DateFieldTwo < dateTwoIsBefore.Value)))
        .ToList();

    return result;
}

This works fine, but I would prefer to reduce the duplicated code in the 'Where' methods as the filter algorithm is the same for each date field.

What I would prefer is something that looks like the following (I will create a class or struct for the filter values later) where I can encapsulate the match algorithm using maybe an extension method:

DateTime? dateOneIsBefore = null;
DateTime? dateOneIsAfter = null;

DateTime? dateTwoIsBefore = null;
DateTime? dateTwoIsAfter = null;

using (var context = new ReusableDataEntities())
{
    IEnumerable<TestItemTable> result = context.TestItemTables
        .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter)
        .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter)
        .ToList();

    return result;
}

Where the extension method could look something like:

internal static IQueryable<TestItemTable> WhereFilter(this IQueryable<TestItemTable> source, Func<TestItemTable, DateTime> fieldData, DateTime? dateIsBefore, DateTime? dateIsAfter)
{
    source = source.Where(record => ((!dateIsAfter.HasValue
            || fieldData.Invoke(record) > dateIsAfter.Value)
        && (!dateIsBefore.HasValue
            || fieldData.Invoke(record) < dateIsBefore.Value)));

    return source;
}

Using the above code, if my filtering code is as follows:

IEnumerable<TestItemTable> result = context.TestItemTables
    .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter)
    .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter)
    .ToList();

I get the following exception:

A first chance exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

Additional information: LINQ to Entities does not recognize the method 'System.DateTime Invoke(RAC.Scratch.ReusableDataFilter.FrontEnd.TestItemTable)' method, and this method cannot be translated into a store expression.

The problem I have is the use of Invoke to get the particular field being queried as this technique does not resolve nicely to SQL, because if I modify my filtering code to the following it will run without errors:

IEnumerable<TestItemTable> result = context.TestItemTables
    .ToList()
    .AsQueryable()
    .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter)
    .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter)
    .ToList();

The issue with this is that the code (using ToList on the entire table before filtering with the extension method) pulls in the entire database and queries it as objects instead of querying the underlying database so it is not scaleable.

I have also investigated using the PredicateBuilder from Linqkit, but it could not find a way to write the code without using the Invoke method.

I know there are techniques where one can express parts of the query as strings which include field names, but I would prefer to use a more type safe way of writing this code.

Also, in an ideal world I could redesign the database to have multiple 'date' records related to a single 'item' record, but I am not at liberty to change the database schema in this way.

Is there another way I need to write the extension so it doesn't use Invoke, or should I be tackling reuse of my filtering code in a different way?

Answer 1

In your case there is not much duplication, but still I'll show how you can do what you want with raw expressions (as an example):

internal static class QueryableExtensions {
    internal static IQueryable<T> WhereFilter<T>(this IQueryable<T> source, Expression<Func<T, DateTime>> fieldData, DateTime? dateIsBefore, DateTime? dateIsAfter) {
        if (dateIsAfter == null && dateIsBefore == null)
            return source;
        // this represents you "record" parameter in lambda
        var arg = Expression.Parameter(typeof(T), "record");
        // this is the name of your field ("DateFieldOne")
        var dateFieldName = ((MemberExpression)fieldData.Body).Member.Name;
        // this is expression "c.DateFieldOne"
        var dateProperty = Expression.Property(arg, typeof(T), dateFieldName);
        Expression left = null;
        Expression right = null;
        // this is "c.DateFieldOne > dateIsAfter"
        if (dateIsAfter != null)
            left = Expression.GreaterThan(dateProperty, Expression.Constant(dateIsAfter.Value, typeof(DateTime)));
        // this is "c.DateFieldOne < dateIsBefore"
        if (dateIsBefore != null)
            right = Expression.LessThan(dateProperty, Expression.Constant(dateIsBefore.Value, typeof(DateTime)));
        // now we either combine with AND or not, depending on values
        Expression<Func<T, bool>> combined;
        if (left != null && right != null)
            combined = Expression.Lambda<Func<T, bool>>(Expression.And(left, right), arg);
        else if (left != null)
            combined = Expression.Lambda<Func<T, bool>>(left, arg);
        else
            combined = Expression.Lambda<Func<T, bool>>(right, arg);
        // applying that to where and done.
        source = source.Where(combined);
        return source;
    }
}

Call is as you will expect:

WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter)

Working with expressions might look weird at first, but at least for simple cases there is nothing too complex.

Answer 2

Yes, LinqKit is the way to go here. But, you're missing some pieces in your extension method:

internal static IQueryable<TestItemTable> WhereFilter(this IQueryable<TestItemTable> source, Expression<Func<TestItemTable, DateTime>> fieldData, DateTime? dateIsBefore, DateTime? dateIsAfter)
{
    source = source.AsExpandable().Where(record => ((!dateIsAfter.HasValue
            || fieldData.Invoke(record) > dateIsAfter.Value)
            && (!dateIsBefore.HasValue
            || fieldData.Invoke(record) < dateIsBefore.Value)));

    return source;
}

I changed the 2nd parameter to Expression<Func<TestItemTable, DateTime>> and added the missing call to LinqKit's AsExpandable() method. This way, Invoke() would be calling LinqKit's Invoke() that is then able to do its magic.

Usage:

IEnumerable<TestItemTable> result = context.TestItemTables
    .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter)
    .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter)
    .ToList();