mysql - 使用 join、group by 和 Sum() 的 LINQ 查询

Question

我已经为此工作了一段时间，所以我想我应该在这里发帖，看看是否有人知道如何将如下所示的查询转换为 LINQ。

这是 MySQL 查询:

SELECT SUM(line_ord.itemqty) AS LineOrderQTY, SUM(assemblyNumber.qty) AS 
AssemblyQTY FROM line_ord 
LEFT JOIN
(
    SELECT sum(assemblyno.qty) AS qty, assemblyno.row_id FROM assemblyno 
    INNER JOIN line_ord ON assemblyno.row_id = line_ord.row_id
    WHERE line_ord.bdnum = 'S61460'
) AS assemblyNumber ON line_ord.row_id = assemblyNumber.row_id
WHERE line_ord.bdnum = 'S61460'

这就是我到目前为止的 LINQ 查询，但它没有返回正确的结果。

var items = (from c in Context.OrderLineItemData
        join e in Context.AssemblyLabelData on c.ID equals e.RowID
                     where c.BreakdownNumber == breakdownNumber

                     group c by c.BreakdownNumber into g
                     select new
                     {                             
                          AssemblyQuantity= g.Sum(x => x.Quantity),
                          LineOrdQuantity = g.Sum(**WHAT GOES HERE?**)
                     }).FirstOrDefault()

我确实设法让它像这样工作，但对我来说似乎有点困惑。

            var items = (from c in Context.OrderLineItemData
                     join e in Context.AssemblyLabelData on c.ID equals e.RowID
                     where c.BreakdownNumber == breakdownNumber

                     group c by c into g
                     select new
                     {                             
                          AssemblyQuantity= g.Sum(x => x.Quantity),
                          LineOrdQuantity = (from e in Context.OrderLineItemData where e.BreakdownNumber == breakdownNumber select e.Quantity).Sum()
                     }).FirstOrDefault();

有更好的方法吗？

Answer 1

得到了我的答案。似乎必须创建一个查询对象，然后我可以使用它来执行我的计算。

var items = (from od in Context.OrderLineItemData
             join ad in Context.AssemblyLabelData on od.ID equals ad.RowID into odGroup
             from g in odGroup.DefaultIfEmpty()
             where od.BreakdownNumber == breakdownNumber
             group g by new
             {
                  breakdown = od.BreakdownNumber,
                  LineOrd = od,
                  AssemblyQty = g == null ? 0 : g.Quantity
             }
             into groupped
             select new
             {
                   Breakdown = groupped.Key.breakdown,
                   AssemblyQty = groupped.Sum(x => x.Quantity),
                   lineOrdQty = groupped.Key.LineOrd.Quantity
             }
         );            
        int remainingQuantity = items.Sum(x => x.lineOrdQty) - items.Sum(x => x.AssemblyQty);