Unable to use simple function in Linq-to-entities query

Question

I have lots of tabular data that needs to be filtered, filtering is supposed to be available for each column in the data. The data is simple types, int, double, string, bool, date. Users specify the filtering conditions in the front end.

I'll just give the example for ints because the problem is the same no matter the data type.

I have lots of int columns across lots of different tables in the db. The filtering condition is to be able to apply a minimum filter to the data, so if the minimum is specified, no data smaller than the minimum gets through. Very simple.

I can do this easily with Linq and Entity Framework 6.

IQueryable<Data> data = db.Data;

data = data.Where(item => (filter.MinValue == null || filter.MinValue <= item.a))

With this solution I have to write this code snippet hundreds of times across the application. I would rather not do that. Worth noting at this point that there are some more complex cases, which are a pain to write out over and over again.

I have a function which contains the same logic.

public bool Min(int minValue, int value)
{
    return minValue == null || value >= minValue;
}

If I try and use the function like this it will work happily.

List<Data> data = db.Data.ToList();
data = data.Where(item => Min(filter.MinValue, item.a))

I believe this uses Linq-to-objects.

If I try and use the function like this, it does not work.

IQueryable<Data> data = db.Data;
data = data.Where(item => Min(filter.MinValue, item.a))

I believe this uses Linq-to-entities.

I get this error:

System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean Min([System.Int], [System.Int])' method, and this method cannot be translated into a store expression.

This a well known bit of behaviour. The question I have is: is there a way I can package up the logic into something function-esque, that I can use with Linq-to-Entities in a repeatable way rather than just having to write out almost the same thing for every filter condition.

I need to be able to use Linq-to-Entities because filtering the data in memory is noticeably less performant.

I've looked at using predicates, but they seem to require me to know the object that the filter will be applied to, whereas I'm looking for a method I can apply to objects of any type (as long as they have the relevant field to filter by).

Answer 1

This a well known bit of behaviour. The question I have is: is there a way I can package up the logic into something function-esque, that I can use with Linq-to-Entities in a repeatable way rather than just having to write out almost the same thing for every filter condition.

There's no simple way to achieve this, but as with most things the hard work has already been kind of done for you.

As Pangiotis mentioned in comments, LINQKit provides a mechanism to handle this kind of problem by letting you provide both a native method and an equivalent Expression<Func<...>> that is substituted into the expression before SQL is generated.

For instance you could write your code like this:

public static class DataFilters
{
    [Expandable(nameof(_exprAtLeast))]
    public static bool AtLeast(int minValue, int value) 
        => minValue is null || value >= minValue

    static Expression<Func<int, int, bool>> _exprAtLeast =
        (minValue, value) => minValue == null || value >= minValue;
}

(Sorry, your method name made no sense to me.)

LINQKit uses an IQueryable<> wrapper implementation to substitute method calls with their expanded expressions in your code, transforming the query expression before passing it up the chain. All you have to do to access this functionality is call AsExpandable() on your query.

So you write this:

var query = db.Data.AsExpandable()
    .Where(row => DataFilters.AtLeast(filter.minValue, item.a));

When you execute the query, LINQKit transforms it by replacing the method call with the body of the expanded expression, making it equivalent to writing this:

var query = db.Data
    .Where(row => filter.MinValue == null || item.a >= minValue);

That's a fairly trivial example, but it shows the general idea.

---

If you have some experience with LINQ Expressions and ExpressionVisitors you can duplicate bits of this functionality by building a wrapper class around an existing IQueryable<T> that modifies the query expression just prior to execution.

As you can imagine this is more than a few lines of code. So here's a fiddle with example code. It's not perfect, but it illustrates a method for doing this. It (mostly, kinda) works. I don't guarantee more than that.