MVC linq to sql sum

Question

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

But after receiving this message:

An attribute like "System.Decimal" is invalid because the materialized value is zero. Either the general parameter of the result type or the query should use a type with a null value.

Indeed, for the database there should be no entries for this user for this day, so I went down with the zero route. In the good old days, I would build a stored procedure with `ISNULL` in it !!!

This is the main expression that I have:

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

In Googling, he came up with null definitions and use with "m" since he is decimal. But still the error persists!

Your collective help will be invaluable, as always. Thanks in advance.

Answer 1

Use the DefaultIfEmpty method. This will be filled with 0 if no value is found.

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

Answer 2

Use Convert.ToDecimal() , this will handle your null issue.

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

LINQ to Entities does not recognize the 'System.Decimal ToDecimal (System.Decimal)' method, and this method cannot be translated into a storage expression.

Edit:

 decimal foodCount = dbContext.fad_userFoods.Where(uf => uf.dateAdded == thisDate && uf.userID == thisGuid) .Sum(uf=> { decimal result; decimal.TryParse(uf.quantityAmount,out result); return result;}); 

Answer 3

Since this is a sum , not average , do you really not mean zero values? Why not just remove the null values?

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

Answer 4

The confusion is due to the fact that Sum in LINQ To Entities is handled a little differently than in LINQ To Objects. Although from the declaration it looks like it is calling it, let it be said that decimal will return 0 when the target set is empty, the SQL Sum function returns NULL , even if the target column is not NULL.

Once you know this, there are two ways to solve it.

Say we have a table with a decimal column, and the original expression

 table.Sum(item => item.Column) 

The first way is to convert it using the template contained in Maarten's answer:

 table.Select(item => item.Column).DefaultIfEmpty().Sum() 

The second way is to explicitly convert the type with a null value to a NULL value inside the function, and then apply the null coalescing operator to the result :

 table.Sum(item => (decimal?)item.Column) ?? 0 

Both methods work and produce the same result, so use the one that best suits your personal preferences.

For completeness of application of the second approach in your case will basically move ?? 0 ?? 0 outside Sum call:

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