DefaultIfEmpty Exception "bug or limitation" with EF Core

c# entity-framework-core lambda linq

Question

I tried to execute the following code:

await _dbContext.Customers.Select(x => x.CustomerNr).DefaultIfEmpty(0).MaxAsync() + 1;

Essentially it has to get the highest customer number from the database and add 1 to it. If the customer table is empty, it should return 0 to which I add 1. Why is it giving me the following exception:

Linq error message

Edit: I am using .NET Core 3.1 and EF Core 3.0.1 (Same error for EF Core 3.1.0)

1
4
1/2/2020 5:54:00 AM

Accepted Answer

Avoid DefaultIfEmpty overload with default value - it's not supported by EF Core query translator.

Also in general avoid DefaultIfEmpty parameterless overload for anything else than left outer join pattern because while it is supported, SQL translation is quite weird.

To solve the problem with applying Max, Min and Average methods on empty sets, use the nullable overloads which return null for empty set, and convert null result to 0 (or other desired magic value) if needed.

Applying it to your scenario would be somethong like this (assuming the CustomerNr type is int):

(await _dbContext.Customers.MaxAsync(x => (int?)x.CustomerNr)) ?? 0 + 1; 
3
1/1/2020 9:20:13 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow