Efcore: Server Side Evaluation

[nimeshvaghasiya]

EF Core 3.1.x:

I would not like to load all products in memory, which below queries do!
Guess what happen if i do have millions of products in table?

var products = context.Products.ToList();
products = products.Where(p => p.Name.Contains("xxx")).ToList();

And below query throws
The LINQ expression 'DbSet-Product-
.Where(b => b.Name.Contains(
value: "xxx",
comparisonType: InvariantCultureIgnoreCase))' 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().

var products = context.Products.Where(p => p.Name.Contains("xxx", StringComparison.InvariantCultureIgnoreCase)).ToList();

Related Issue: #19087

Can anyone help me out. how to filter data with server side evaluation with ef core 3.1.x?

[roji]

Duplicate of #1222

[roji]

EF Core does translate Contains for server-side evaluation - but not the overload that accepts StringComparison.InvariantCultureIgnoreCase (or any other StringComparison). In other words, your query above should be working as-is, can you please double-check that are you aren't getting the translation exception from another query which passes InvariantCultureIgnoreCase?

For more context on why we don't translate StringComparison, see https://github.com/dotnet/efcore/issues/1222#issuecomment-611113142. EF Core 5.0 is bringing collation support which would allow you to do context-insensitive operations, but via other ways.

[nimeshvaghasiya]

Thanks @roji