CODEWITHSUNDEEP
c#linqasp.net-core-3.0ef-core-3.0
Tofetopo
Tofetopo

Reputation: 496

How to rewrite EF LINQ query that used to work in .NET Core 2.0 but doesn't in 3.0?

I am developing an online shop in .NET Core 3, following some tutorials delevoped with .NET CORE 2. EF LINQ is erroring after trying to execute this simple line of code:

var stocksToUpdate = _context.Stock
                             .Where(x => request.Stocks
                                                .Any(y => y.StockId == x.Id))
                             .ToList();

I got the following error:

InvalidOperationException: The LINQ expression 'DbSet<Stock> .Where(s => __request_Stocks_0 .Any(y => y.StockId == s.Id))' 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().

I honestly don't know how to write that query in a different way. I have tried to replace the .ToList() for .AsEnumerable as suggested, but it still erroring, same error. Does anyone know whats the fix in .NET Core 3? Thanks in advance!

Upvotes: 1

Views: 803

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

It was "working" in EF Core 2.x because of the implicit client evaluation, which has been removed in 3.0. You can get the previous behavior by inserting AsEnumerable() before the reported problematic operator - in your case, Where:

var stocksToUpdate = _context.Stock
    .AsEnumerable()
    .Where(x => request.Stocks.Any(y => y.StockId == x.Id))
    .ToList();

But the main reason for removing the implicit client evaluation is because it's inefficient - the filtering happens in memory after retrieving the whole table.

So rather than switching to client evaluation, it's always better to find a translatable construct.

The problem with your query is that request.Stocks is a in-memory collection, and EF Core supports only in-memory collections of primitive values, and basically Contains method. So the following works

var stocksToUpdate = _context.Stock
    .Where(x => request.Stocks.Select(y => y.StockId).Contains(x.Id))
    .ToList();

You could also use

.Where(x => request.Stocks.Select(y => y.StockId).Any(y => y == x.Id))

The essential part here is to convert the in-memory complex object enumerable to single primitive value enumerable before "calling" it with Contains or Any on it.

Upvotes: 4

Related Questions