LINQ .SUM() and nullable db values

Question

I know why this is happening but can somebody point me in the right direction of syntax?

Currently I have:

var expense = from e in db.I_ITEM
              where e.ExpenseId == expenseId
              select e;

return expense.Sum(x => x.Mileage ?? 0);

My problem is that x.Mileage is of type "double?" and has null values in the db.

The error I get is:

Exception Details: System.InvalidOperationException: The cast to value type 'Double' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

What would be the correct syntax?

Answer 1

I'm surprised that fails, but an alternative which might work is simply to sum the nullable values and then use the null coalescing operator:

return expense.Sum(x => x.Mileage) ?? 0d;

Certainly in LINQ to Objects this would do the right thing, ignoring null values and giving you a null result (before the null coalescing operator) if there were no non-null values in the sequence.

Answer 2

What about excluding the nulls, ie

var expense = 
          from e in db.I_ITEM
          where (e.ExpenseId == expenseId) && (e.Mileage.HasValue)
          select e;

 return expense.Sum(x => x.Mileage);

Answer 3

may give you an opinion...

    decimal depts = 0;

    var query = from p in dc.Payments
                where p.UserID == UserID
                select p.Amount;

    if (query.Count() > 0)
    {
        depts = query.Sum();
    }

    return depts;