Entity Framework 5.0 Contains/Equals/StartsWith/EndWith in Any cannot be translated in SQL as it was-CodePudding

I am trying to migrate a Linq query from EF Core 2.2 to EF Core 5.0 it was works perfect in 2.2

var foo = await _baseRepository.GetQueryable<User>().Where(i => new[] { "os", "man" }.Any(j => i.Email.Contains(j))).ToListAsync();

Expected SQL Query

SELECT * FROM User WHERE Email IN ("os","man")

Error

The LINQ expression 'DbSet<User>()
    .Where(u => string[] { "os", "man", }
        .Any(j => u.Email.Contains(j)))' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Also I tried new[] { "os", "man" }.ToList()/AsEnumerable()

Also this works

var foo = await _baseRepository.GetQueryable<User>().Where(i => new[] { "os" }.Any(j => i.Email.Equals(j))).ToListAsync();

Result Query

SELECT * FROM User WHERE Email = "os"

but this not equals (... !Equals(..)) does not work

var foo = await _baseRepository.GetQueryable<User>().Where(i => new[] { "os" }.Any(j => !i.Email.Equals(j))).ToListAsync();

also i tried StartsWith/EndsWith but i got error

CodePudding user response：

It was awhile ago (2 years); but a lot of LINQ statements break from 2.2 to 3.0. Your link lists both the reason (Core 3 throws an exception if it cannot convert LINQ to SQL statement and needs to pull records to memory without explicit ToEnumerable()) and suggested solutions.

So, it is not clear what you are asking. Unfortunately, these errors only come at runtime; so you need to go through all of them and - preferably - to use LINQ options that map to SQL; or - if it is not possible - to explicitly call ToEnumerable()

And if you need to fix them before upgrading, you can put a call in DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
   .ConfigureWarnings(warnings => warnings.Log(RelationalEventId.QueryClientEvaluationWarning));

CodePudding user response：

As mentioned, this worked in EF Core 2.2 because they allowed client-side evaluation which effectively did a big 'ol "SELECT *" or at least applied what filtering they could, then did the unsupported evaluations in memory.

In your example in EF Core 2.2:

var foo = await _baseRepository.GetQueryable<User>()
    .Where(i => new[] { "os", "man" }
        .Any(j => i.Email.Contains(j)))
    .ToListAsync();

Would be this in EF Core 5:

var foo = await _baseRepository.GetQueryable<User>()
    .ToListAsync() // <- materialize for client-side evaluation.
    .Where(i => new[] { "os", "man" }
        .Any(j => i.Email.Contains(j)))
    .ToListAsync();

**Disclaimer: I'm not 100% sure that even works. You may need to await the ToListAsync() on the Get all, and then do the filtering as a separate operation..

var users = await _baseRepository.GetQueryable<User>()
    .ToListAsync(); // <- materialize for client-side evaluation.
var foo = await users.Where(i => new[] { "os", "man" }
        .Any(j => i.Email.Contains(j)))
    .ToListAsync();

While EF can understand string.Contains(), it cannot parse that with a .Any() against an in-memory set. Though this is certainly not ideal as you are loading all users before filtering.

To fix it so EF can translate it would be this:

var foo = await _baseRepository.GetQueryable<User>()
    .Where(i=> i.Email.Contains("os") 
        || i.Email.Contains("man")
    .ToListAsync();

However, where you want to be more dynamic like having that in-memory list composed of a variable/configurable set, then you need to look at Dynamic Linq. (https://dynamic-linq.net/) Specifically it's Nuget package to help you build an expression that EF can execute. This will allow you to compose an OR expression dynamically from a set of values to execute as part of your Where clause.