How to modify expression-based filters to avoid client-side evaluation in Entity Framework Core 3.0

Question

I have the following code that used to convert Func-based filters to Expression and filter the data in Entity Framework Core 2.2:

public async Task<TType> GetDataAsync<TType>(Func<TType, bool> filtering = null) where TType : class
{
  Expression<Func<TType, bool>> filteringExpression = (type) => filtering(type);
  if (filtering != null)
    //return await myContext.Set<TType>().FirstOrDefaultAsync(filteringExpression);
    return await myContext.Set<TType>().Where(filteringExpression ).FirstOrDefaultAsync();
  return await myContext.Set<TType>().FirstOrDefaultAsync();
}

This is how I use it:

public async Task<DataLog> GetDataLogByID(Guid dataLogID) => await GetDataAsync<DataLog>(dataLog => dataLog.ID == dataLogID);

(Un)fortunately, when I upgraded to Entity Framework Core 3.0, the code threw an InvalidOperationException as the expression can't be turned into SQL query (although it filters only a property that matches a database column):

System.InvalidOperationException: 'The LINQ expression 'Where( source: DbSet, predicate: (f) => Invoke(__filtering_0, f[DataLog]) )' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

So can you tell me, how I should modify the code to make sure that all (most) of the processing stay on the server side? What is the best practice to keep the generic code yet comply with the standards?

Your filtering expression could by anything since you've specified a delegate, so it depends on what you throw at it really - do you have an example of the delegate you passed to the `GetDataAsync` method? As you said, if it's just a simple property filter then I can't see why 3.0 wouldn't be able to translate it. I'd say the only reason this would work in 2.2 is that 2.2 automatically switched to client evaluation when queries couldn't be translated. Did you check that 2.2 was actually executing on the server? 3 now throws an exception to prevent unintended consequences/performance. — Charleh, Oct 09 '19 at 10:43
@Charleh I modified the initial post to show how I use it. I checked and in 2.2 it had quite a lot of warnings like this: _"[WRN] The LINQ expression 'Skip(__p_2)' could not be translated and will be evaluated locally."_ It is a SQLite database but I think, it supports these kind of queries. — Nestor, Oct 09 '19 at 11:31
Instead of `Func filtering` use `Expression> filtering`. In general follow `Queryable` methods pattern, i.e. use `Expression>` instead of `Func<…>` - the former can be translated, the later cannot. — Ivan Stoev, Oct 09 '19 at 12:04
@IvanStoev Converting the parameter to `Expression` helped in this particular case so I try to modify the data handler methods accordingly. If you promote your comment to answer, I'll accept it. — Nestor, Oct 09 '19 at 12:38
Discovered the same and I was going to suggest - if you use `Expression>` rather than `Func<>` it works. The exp. tree you are building by wrapping the `Func` in another expression actually creates a chain to original delegate - i.e. LINQ just can't decipher it as it doesn't know how to translate the invoke. You can see that here in your original lambda: `t => Invoke(value(ConsoleApp1.Program+<>c__DisplayClass2_01[ConsoleApp1.GuidTable]).filtering, t)` vs what you get when you take an expression as a parameter `t => (t.Guid == value(ConsoleApp1.Program+<>c__DisplayClass1_0).g)` — Charleh, Oct 09 '19 at 13:30
Also, further to that, it's difficult to go from `Func` to `Expression>`, especially for EF - more here: https://stackoverflow.com/questions/9377635/create-expression-from-func/9377714 - technically you will get the same result at runtime from calling that expression, but what EF needs to see will be in a "black box" so to speak. — Charleh, Oct 09 '19 at 13:52

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

Congratulations, you've discovered one of the breaking changes in EF Core 3.0 - LINQ queries are no longer evaluated on the client

Old behavior

Before 3.0, when EF Core couldn't convert an expression that was part of a query to either SQL or a parameter, it automatically evaluated the expression on the client. By default, client evaluation of potentially expensive expressions only triggered a warning.

New behavior

Starting with 3.0, EF Core only allows expressions in the top-level projection (the last Select() call in the query) to be evaluated on the client. When expressions in any other part of the query can't be converted to either SQL or a parameter, an exception is thrown.

See the docs (link above) for more info, but the warnings you experienced before upgrading are now generating InvalidOperationExceptions and has nothing to do with SQLite, you'd get the same issues with SQL Server.

Only way around this is to ensure that your filtering expression/func can be converted to appropriate SQL... or revert to EF Core < 3.0

UPDATE

You could try not wrapping the passed Func and change the parameter type to Expression<Func<TType, bool>> (it shouldn't require any changes to code calling the method)

public async Task<TType> GetDataAsync<TType>(Expression<Func<TType, bool>> filter = null)
    where TType : class
{
    var query = myContext.Set<TType>();

    if (filter != null)
        query = query.Where(filter);

    return await query.FirstOrDefaultAsync();
}

Just noticed that the call to GetDataAsync appears to be incorrect and has an extra type parameter Guid which should be removed from this example.

public async Task<DataLog> GetDataLogByID(Guid dataLogID) =>
    await GetDataAsync<DataLog>(dataLog => dataLog.ID == dataLogID);

Thanks, I know the reason why I hit the problem but I needed a hint of where and what to change in my current code. _Replacing_ the parameter is the key to the solution. — Nestor, Oct 09 '19 at 12:41
I'm just having a look now to see if your expression is actually being evaluated or if it's an issue post-evaluation. — Charleh, Oct 09 '19 at 12:55
What do you mean that is has an extra type parameter? I use that parameter for filtering the data so it must be in the signature of that method. — Nestor, Oct 09 '19 at 16:26
@Nestor `GetDataAsync`(Func filtering = null)` only has 1 generic type parameter `TType` but you're calling it with 2 `await GetDataAsync(dat...`, this doesn't seem right. `GetDataAsync` doesn't need to know anything more about the contents of the passed Func/Expression — phuzi, Oct 10 '19 at 07:04

How to modify expression-based filters to avoid client-side evaluation in Entity Framework Core 3.0

1 Answers1