SQL generation issue when doing a SUM with a column Coalesce function in it

[Tratcher]

From @grantnilsson on January 16, 2017 22:57

I have a class (called Transaction), which has a property defined as a nullable integer (int?). I then run a Linq command against this property to SUM all the values together. Note: some transactions have a NULL vaue for this field.

public class Transaction
{
    ...
    int? numberOfUnits {get; set;}
    ...
}

The Linq command looks like this

_context.Transactions
    .Sum(x => x.NumberOfUnits ?? 0);

This generates an invalid SQL command stating "'sum' is not a recognized built-in function name.". This is because the generated SQL is invalid. The SQL generated is as follows:

SELECT sum(COALESCE([x].[NumberOfUnits], 0) AS [Coalesce])
FROM Transaction AS [x]

NOTE: the issue is actually the fact that the coalesce generates an alias for itself. NO alias should be generated for the Coalesce at all

Copied from original issue: dotnet/aspnetcore#1901

[smitpatel]

duplicate of #7103 fixed in dev.