Select(x=>new { a=Sum(NullableField)??null }) generate COALESCE error

[Cricle]

File a bug

Include your code

public class DataSetA
{
    public string Name { get; set; }

    [Key]
    public int Id { get; set; }

    public DateTime Time { get; set; }

    public int? L { get; set; }
}

var q2 = set.GroupBy(x => x.Id).Select(x => new { d = x.Sum(q => q.L) ?? null });
var q4 = set.GroupBy(x => x.Id).Select(x => x.Sum(q => q.L)??null);

//SELECT COALESCE(COALESCE(SUM("d"."L"), 0), NULL) AS "d"
//FROM "DataSetAs" AS "d"
//GROUP BY "d"."Id"
var q2Sql = q2.ToQueryString();

//SELECT COALESCE(COALESCE(SUM("d"."L"), 0), NULL)
//FROM "DataSetAs" AS "d"
//GROUP BY "d"."Id"
var q4Sql = q4.ToQueryString();

Include stack traces

No

Include verbose output

No

Include provider and version information

EF Core version:
Database provider: (Microsoft.EntityFrameworkCore.Sqlite)
Target framework: (.NET 6.0)
Operating system:
IDE: (e.g. Visual Studio 2022 17.3.3)

So how can i do make new {q=q.Sum(xxx)??null} generate COALESCE(SUM("d"."L"), NULL)

[Cricle]

#27825 How to avoid COALESCE in SUM operations
In fact, there is a big difference between null and 0.
I think the problem is that the sum result is null but+1.
In mysql

In +1

Null+1 still null

This is also true in c#.

Can see #17492

#12657

from c in cs
select new
{
   Sum = c.Orders.Sum(o => o.OrderID) + 1
}

Want generate sql(mysql)

SELECT COALESCE(SUM(OrderID),0)+1 AS SUM from cs

But this expression was used c.Orders.Sum(o => o.OrderID)??0 make OrderID to Nullable<T> is used to generate

SELECT COALESCE(SUM(OrderID),0)+1 AS SUM from cs

By default generate sql(mysql)

SELECT COALESCE(SUM(OrderID),null)+1 AS SUM from cs

This will solve the current problem.
I don't know if I'm right, but I feel that's what I mean.
Thinks!

[Cricle]

Or give us the way we can always generate this statement. Like use DefaultIfEmpty(null) or others.

efcore/src/EFCore.Relational/Query/SqlNullabilityProcessor.cs

Line 925 in 3ad8296

_sqlExpressionFactory.Constant(0, sqlFunctionExpression.TypeMapping),

Or whether the user can change 0 to other reasonable values.