c# - 在 Entity Framework 中使用 Join、Group By 和 Sum

Question

我的模型看起来像:

public class ReturnItem
{
    public int returnItemId { get ; set; }
    public int returnRequestId { get; set; }
    public int quantity { get; set; }
    public string item { get; set; }
}

public class ReturnRequest
{
    public int returnRequestId { get; set; }
    public string orderNumber  { get; set; }
    public IEnumerable<ReturnItem> returnItems { get; set; }
}

我有以下查询:

SELECT item, sum(quantity)
FROM ReturnItem
JOIN ReturnRequest
ON ReturnRequest.returnRequestId = ReturnItem.returnRequestId
WHERE ReturnRequest.orderNumber = '1XX'
GROUP BY item

如何将查询转换为 Entity Framework 并返回 List<ReturnItem> ？我可以使用 .Include 吗？而不是 .Join ？

Answer 1

from ri in db.ReturnItems
join rr in db.ReturnRequests
   on ri.returnRequestId equals rr.returnRequestId
where rr.orderNumber == "1XX"
group ri by ri.item into g
select new {
   Item = g.Key,
   Quantity = g.Sum(i => i.quantity)
}

你不能使用 Include 而不是 Join 因为 Include 被翻译成 Left Outer Join 但你需要内部连接在这里。

但是您可以使用导航属性来隐式执行连接:

db.ReturnRequests
  .Where(rr => rr.orderNumber == "1XX")
  .SelectMany(rr => rr.returnItems)
  .GroupBy(ri => ri.item)
  .Select(g => new {
      Item = g.Key,
      Quantity = g.Sum(ri => ri.quantity)
  });