EF Core groupBy no SQL translation for null-coalescing operator (??). ISNULL is not being used to translate when doing ??0m

Question

Using EF Core 2.2 with

optionsBuilder.ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));

A working groupBy linq query to sql server database is failing to evaluate on server side by changing the Select(t=>t.Value) to Select(t=>t.Value??0m).

Why is it not being translated to SQL as it could become SUM(ISNULL(Value,0)) ?

I know it's not necessary but I would like to understand how it's working under the hood as I feel I am missing something.

Answer 1

This is EF Core 2.2 GroupBy query translator defect/limitation (already fixed in EF Core 3.x) which does not support aggregate methods expressions other that simple "property" access.

The workaround is to use intermediate (anonymous type) projection containing all the required expressions (so they become "properties") using the GroupBy overload with element selector.

For instance, given entity like this:

public class SomeEntity
{
    public int Id { get; set; }
    public int SomeKey { get; set; }
    public decimal? SomeValue { get; set; }
}

and desired, but failing EFC2.2 query like this:

var query = db.Set<SomeEntity>()
    .GroupBy(e => e.SomeKey)
    .Select(g => new
    {
        g.Key,
        Value = g.Sum(e => e.SomeValue ?? 0m) // <--
    });

the modified working EFC2.2 solution could be like this:

var query = db.Set<SomeEntity>()
    .GroupBy(e => e.SomeKey, e => new { SomeValue = e.SomeValue ?? 0m }) // <--
    .Select(g => new
    {
        g.Key,
        Value = g.Sum(e => e.SomeValue) // <--
    });

More general solution though is to just move the null coalescing operator out of aggregate function:

var query = db.Set<SomeEntity>()
    .GroupBy(e => e.SomeKey)
    .Select(g => new
    {
        g.Key,
        Value = g.Sum(e => e.SomeValue) ?? 0m // <--
    });