c# - 带有左连接和 sum() 的 Linq To SQL 抛出异常

Question

我有以下(简化的)数据库表:

Products
- ProductID, int, PK
- Name, varchar(50)

Orders
- OrderID, int, PK
- ProductID, int, FK
- Quantity, int, not null

我想读取所有产品，如果有订单，请计算该产品所有订单的数量。

SQL 查询看起来像这样:

select p.ProductID, p.Name, sum(o.Quantity) from Products p 
left join Orders o on p.ProductID = o.ProductID
group by p.ProductID, p.Name

现在，我想在 Linq-to-SQL 查询中转换它。到目前为止，我已经做到了这一点:

var query =
    from p in Products
    join o in Orders on p.ProductID equals o.ProductID into po
    from subOrder in po.DefaultIfEmpty()
    group subOrder by new
    {
      p.ProductID,
      p.Name,
    }
    into productGroup
    select new
    {
      productGroup.Key.ProductID,
      productGroup.Key.Name,
      Quantity = productGroup.Sum(subOrder => subOrder.Quantity)
    }; 

虽然生成的 SQL 看起来不错，但我得到以下异常:

InvalidOperationException: The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.

有人可以告诉我查询有什么问题吗？任何帮助将不胜感激!

Answer 1

改用 productGroup.Sum(subOrder => (int?)subOrder.Quantity)。