C# – LINQ: failed because the materialized value is null

clinq

I'm trying to use sum in code below but I get the error:

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

Product_Order:
    ---------------- ----------- ---------
    |   ProductId   | OrderId   | Quantity |
    ---------------- ----------- ---------

I get the error at "let quantity"

  var fullInfo = (from product in allProdcts
                 let quantity = db.Product_Order.Where(x=> x.ProductId == product.ID).Sum(x => x.Quantity)
                select new ReportVm
                    {                          
                        ProductId = product.ID,
                        ProductName = product.Name,
                        AmountProduct = quantity,
                        TotPrice = (quantity)*(product.Price)
                    }).ToList();

This is my Product_Order table(M-M relation):

Product_Order:
    ---------------- ----------- ---------
    |   ProductId   | OrderId   | Quantity |
    ---------------- ----------- ---------

Any idea how to solve this?

Best Answer

You need allow a nullable Quantity, You can achieve it using ?? expression and cast to int? when you use Sum().

.Sum(x => (int?)x.Quantity)??0

Your query should look like

var fullInfo = (from product in allProdcts
            let quantity = db.Product_Order.Where(x => x.ProductId == product.ID).Sum(x => (int?)x.Quantity)??0
            select new ReportVm
            {
                ProductId = product.ID,
                ProductName = product.Name,
                AmountProduct = quantity,
                TotPrice = (quantity)*(product.Price)
            }).ToList();

Related Solutions

C# – The cast to value type ‘Int32’ failed because the materialized value is null

A linq-to-sql query isn't executed as code, but rather translated into SQL. Sometimes this is a "leaky abstraction" that yields unexpected behaviour.

One such case is null handling, where there can be unexpected nulls in different places. ...DefaultIfEmpty(0).Sum(0) can help in this (quite simple) case, where there might be no elements and sql's SUM returns null whereas c# expect 0.

A more general approach is to use ?? which will be translated to COALESCE whenever there is a risk that the generated SQL returns an unexpected null:

var creditsSum = (from u in context.User
              join ch in context.CreditHistory on u.ID equals ch.UserID                                        
              where u.ID == userID
              select (int?)ch.Amount).Sum() ?? 0;

This first casts to int? to tell the C# compiler that this expression can indeed return null, even though Sum() returns an int. Then we use the normal ?? operator to handle the null case.

Based on this answer, I wrote a blog post with details for both LINQ to SQL and LINQ to Entities.

C# – The cast to value type ‘Double’ failed because the materialized value is null

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;

Best Answer

Related Solutions

C# – The cast to value type ‘Int32’ failed because the materialized value is null

C# – The cast to value type ‘Double’ failed because the materialized value is null

Related Topic