LINQ .SUM() and nullable db values

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

Currently I have:

var e开发者_开发技巧xpense = 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?

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.

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);

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;

Which O/R mapper are you using, and which DB are you using? (Linq to SQL/Entity Framework/SQL Server)?

Since the expression is executed in the DB as a SQL statement, I would think it would work without the coalescing operator:

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

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

var expense = (from e in db.I_ITEM
              where e.ExpenseId == expenseId
              select e.Mileage??0D);
return expense.Sum();

Nullable Field: If the calculating field is nullable then no need to do anything special. Just use sum as it is, like below:

var summation = expense.Sum(x => x.Mileage);

Here, the summation is nullable (double?). If you make it not nullable then use the null coalescing operator and set a default value 0(zero), like below:

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

Not Nullable Field: But, if the calculating field is not nullable, then you need to cast nullable first for summation, like this below:

var summation = expense.Sum(x => (double?)x.Mileage);

Also here, the summation is nullable (double?). If you make it not nullable then use the null coalescing operator, like below:

var summation = expense.Sum(x => (double?)x.Mileage)?? 0d;