Sum() Returns null in Entity Framework Query

Question

I have a big Entity Framework query that includes these lines.

var programs = from p in Repository.Query<Program>()
               where p.OfficeId == CurrentOffice.Id
               let totalCharges = p.ProgramBillings.Where(b => b.Amount > 0 && b.DeletedDate == null).Select(b => b.Amount).Sum()
               let totalCredits = p.ProgramBillings.Where(b => b.Amount < 0 && b.DeletedDate == null).Select(b => -b.Amount).Sum()
               let billingBalance = (totalCharges - totalCredits)

When I materialize the data, I get the following error:

The cast to value type 'Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

If I change my query as follows (added in two type casts), the error goes away.

var programs = from p in Repository.Query<Program>()
               where p.OfficeId == CurrentOffice.Id
               let totalCharges = (decimal?)p.ProgramBillings.Where(b => b.Amount > 0 && b.DeletedDate == null).Select(b => b.Amount).Sum()
               let totalCredits = (decimal?)p.ProgramBillings.Where(b => b.Amount < 0 && b.DeletedDate == null).Select(b => -b.Amount).Sum()
               let billingBalance = (totalCharges - totalCredits)

I do not understand this. ProgramBilling.Amount is a non-nullable Decimal. If I hover over the Sum() call, Intellisense says it returns type Decimal. And yet additional tests confirmed that, in my second version, totalCharges and totalCredits are both set to null for those rows where ProgramBillings has no data.

Questions:

1. I understood Sum() returned 0 for an empty collection. Under what circumstances is this not true?

2. And if sometimes that is not true, then why when I hover over Sum(), Intellisense shows it returns type Decimal and not Decimal? It appears Intellisense had the same understanding that I had.

EDIT:

It would seem that an easy fix is to do something like Sum() ?? 0m. But that's illegal, giving me the error:

Operator '??' cannot be applied to operands of type 'decimal' and 'decimal'

Answer 1

I understood Sum() returned 0 for an empty collection. Under what circumstances is this not true?

When you're not using LINQ to objects, as is the case here. Here you have a query provider that is translating this query into SQL. The SQL operation has different semantics for its SUM operator.

And if sometimes that is not true, then why when I hover over Sum(), Intellisense shows it returns type Decimal and not Decimal? It appears Intellisense had the same understanding that I had.

The C# LINQ SUM operator doesn't return a nullable value; it needs to have a non-null value, but the SQL SUM operator has different semantics, it returns null when summing an empty set, not 0. The fact that the null value is provided in a context where C# requires a non-null value is the entire reason everything is breaking. If the C# LINQ SUM operator here returned a nullable value, then null could just be returned without any problems.

It is the differences between the C# operator and the SQL operator it is being used to represent that is causing this error.

Answer 2

I've got the same issue in one of my EF queries when the collection is empty, one quick fix for this is to cast to nullable decimal :

var total = db.PaiementSet.Sum(o => (Decimal?)o.amount) ?? 0M;

hope it helps.