LINQ: failed because the materialized value is null

Question

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?

Answer 1

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();

Answer 2

You may not use an aggregate function returning a non nullable type on an empty collection. In your case, Sum() fails when the where clause on db.Product_Order returns no elements. Following Solution, defining 0 as the default value, should work:

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