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)
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
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
(await _dbContext.Customers.MaxAsync(x => (int?)x.CustomerNr)) ?? 0 + 1;