The cast to value type 'Double' failed because the materialized value is null

Question

CODE:

double cafeSales = db.InvoiceLines     .Where(x =>         x.UserId == user.UserId &&         x.DateCharged >= dateStart &&         x.DateCharged <= dateEnd)     .Sum(x => x.Quantity * x.Price); 

ERROR:

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 I HAVE SEEN ALREADY:

The cast to value type 'Int32' failed because the materialized value is null

The cast to value type 'Decimal' failed because the materialized value is null

WHAT I HAVE TRIED:

double cafeSales = db.InvoiceLines     .Where(x =>         x.UserId == user.UserId &&         x.DateCharged >= dateStart &&         x.DateCharged <= dateEnd)     .DefaultIfEmpty()     .Sum(x => x.Quantity * x.Price); 

And:

double? cafeSales = db.InvoiceLines     .Where(x =>         x.UserId == user.UserId &&         x.DateCharged >= dateStart &&         x.DateCharged <= dateEnd)     .Sum(x => x.Quantity * x.Price); 

Neither of these work. I know the cause of the problem is that there are no rows in that table for the UserId I am passing in. In that case, I would prefer Sum() just returned a 0 to me. Any ideas?

Answer 1

Best Solution

double cafeSales = db.InvoiceLines                      .Where(x =>                                 x.UserId == user.UserId &&                                 x.DateCharged >= dateStart &&                                 x.DateCharged <= dateEnd)                      .Sum(x => (double?)(x.Quantity * x.Price)) ?? 0; 

Answer 2

You can check if the collection has any correct results.

double? cafeSales = null; var invoices = db.InvoiceLines     .Where(x =>         x.UserId == user.UserId &&         x.DateCharged >= dateStart &&         x.DateCharged <= dateEnd     )     .Where(x => x.Quantity != null && x.Price != null); if (invoices.Any()) {     cafeSales = invoices.Sum(x => x.Quantity * x.Price); }