MVC linq to sql sum

Question

Trying to get the values returned from a database based on the sum of a field.

But getting this message:

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

It is valid for the database to contain no records for that user for that day, hence I went down the nullable route. In the good old days I would have built a Stored Procedure with `ISNULL` in it!!!

This is the basic expression I have:

decimal? foodCount = dbContext.fad_userFoods.Where(uf => uf.dateAdded == thisDate && uf.userID == thisGuid).Sum(uf=>(decimal?)uf.quantityAmount ?? 0m);

Googling it came up with the nullable definitions and use of the ?? with the "m" as it's decimal. But still the error persists!

Your collective help will be invaluable as ever. Thanks in advance.

Answer 1

Use the DefaultIfEmpty method. This will fill in a 0 if no value at all can be found.

decimal foodCount = dbContext.fad_userFoods
    .Where(uf => uf.dateAdded == thisDate && uf.userID == thisGuid)
    .Select(uf => uf.quantityAmount)
    .DefaultIfEmpty()
    .Sum();

Answer 2

Since it's a sum and not average you don't really mind null-values? Why not simply removing the null-values?

    decimal? foodCount = dbContext.fad_userFoods
      .Where(uf => 
         uf.dateAdded == thisDate && 
         uf.userID == thisGuid && 
         uf.quantityAmount != null)
      .Sum(uf=> uf.quantityAmount);