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).

This has been asked and answered hundreds of times. LINQ works with expressions, not functions. I translates those functions to SQL. You can't translate a compiled client-side query to SQL or call that function from the database. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 11, 2024 at 17:06
Besides, this function is simply wrong. If you don't want to use the filter condition, don't call Where. All LINQ operators return a new query. You can append conditions as needed eg if (filter.MinValue!=null){ query=query.Where(item=>item.a >= filter.MinValue);}. You can put that into a function that receives IQueryable<T> query as input and returns the final query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 11, 2024 at 17:08
Check this sample with LINQKit. It uses ExpandableAttribute to mark methods, which needs Expression substitution. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Jan 11, 2024 at 17:20
Thanks both for the LinqKit suggestion, looks like it does what I need. — PentagonalBishop
– PentagonalBishop, Commented Jan 12, 2024 at 9:01

Corey · Accepted Answer · 2024-01-12 06:13:34Z

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.

answered Jan 12, 2024 at 6:13

Corey

16.6k2 gold badges38 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PentagonalBishop Over a year ago

Thanks - this looks like a great solution. Are the any performance implications of using .AsExpandable()?

Svyatoslav Danyliv Over a year ago

@PentagonalBishop, It has small effect on performance. Expression Tree is quickly scanned for such functions and replaced with another expression. Let say you may loss up to millisecond.

PentagonalBishop Over a year ago

Thanks - a millisecond in this context is not a problem!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related