DefaultIfEmpty Exception "bug or limitation" with EF Core

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:

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

Answer 1

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;