Linq error generic parameter or the query must use

Question

I got this error when i use sum function in LINQ:

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

GroupProduct.Where(a => a.Product.ProductID==1).Sum(Content => Content.Amount==null?0:Content.Amount),

Answer 1

This looks like it should work (and usually does) but fails when the Where() method returns null:

decimal sum1 = GroupProduct
    .Where(a => a.Product.ProductID == 1)
    .Sum(c => c.Amount ?? 0);

The error: "The cast to value type 'Decimal' failed because the materialized value is null" is due to the Sum() method returning null (not zero) when summing over an empty set.

Either of these work for me:

decimal? sum2 = GroupProduct
    .Where(a => a.Product.ProductID == 1)
    .Sum(c => c.Amount);

decimal sum3 = GroupProduct
    .Where(a => a.Product.ProductID == 1)
    .Sum(c => c.Amount) ?? 0;

Answer 2

This is what I usually use. This will cover the possibility of Amount being null and also cover the possibility of an empty set.

GroupProduct.Where(a => a.Product.ProductID == 1)
    .Select(c => c.Amount ?? 0) // select only the amount field
    .DefaultIfEmpty()  // if selection result is empty, return the default value
    .Sum(c => c)

DefaultIfEmpty() returns the default value associated with Amount's type, which is int, in which case the default value is 0.

Answer 3

Try this:

var sum = GroupProduct.Where(a => a.Product.ProductID==1).Sum(Content => (int?) Content.Amount);
sum = sum ?? 0;

Answer 4

Did you try the following:

GroupProduct.Where(a => a.Product.ProductID==1).Sum(Content => (decimal?)Content.Amount)

The code from my application looks like:

var data = query.AsEnumerable().Select(row => TaskInfo.FetchTaskInfo(row,
      ctx.ObjectContext.Hours.Where(hour => hour.TaskId == row.TaskId).Sum(hour => (decimal?)hour.Duration),
      ctx.ObjectContext.Notes.Count(note => note.SourceType == (int)SourceType.Task && note.SourceId == row.TaskId)));

Answer 5

You could exclude at source?

var sum = GroupProduct.Where(a => a.Product.ProductID==1 && a.Amount != null)
            .Sum(a => (decimal)a.Amount);