How to avoid COALESCE in SUM operations

[dmitriysidorov]

I have a question about SUM implementation

When I use the SUM function and in DB no records or all records have a NULL value in the column, I always get 0. I am confused by the result. After checking query translation, I see that COALESCE function is used, which is really strange for me because NULL and 0 are not the same value.

In my scenario, if I get a NULL, as it should be in my opinion, I understand that no data there (we have nothing to sum), but I get a 0 which for me means that data exists and the result of the sum is 0, but in fact, it is not.

What do you think? Is it possible to use SUM without COALESCE?

Include provider and version information

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

[smitpatel]

Sum function on LINQ returns 0 when the sequence is empty. Sum in database generally ignores null values. Both behavior and use of COALESCE by design. I believe your interpretation of null/0 as a result of sum is inconsistent with LINQ.