c# - 低效的 LINQ - 知道 SQL 应该是什么 - 无法到达那里

Question

我知道我要生成的 SQL:

SELECT qdt, pbn, cid, pid, SUM(Amount) AS TotalAmount
FROM SomeDb.SomeTable
WHERE Status = 'Open'
GROUP BY cid, pid, qdt, pbn
ORDER BY qdt

我有 LINQ，我希望它能产生像上面的 SQL 一样干净的东西:

var query = (
  from someTable in SomeTable
  where someTable.Status == "Open"
  group someTable by new { someTable.cid, someTable.pid } into grouped
    select new
    {
      lid = grouped.FirstOrDefault().lid,
      qdt = grouped.FirstOrDefault().qdt,
      pbn = grouped.FirstOrDefault().pbn,
      cid = grouped.FirstOrDefault().cid,
      cn = grouped.FirstOrDefault().cn,
      pid = grouped.FirstOrDefault().pid,
      amount = grouped.Sum(o => o.Amount),
      Status = grouped.FirstOrDefault().Status
});

但这会产生许多令人讨厌的 SQL 行。每个分组值最终在查询中获得自己的 SELECT 语句，对于 qdt 如下:

SELECT [t5].[qdt]
FROM (
    SELECT TOP (1) [t4].[qdt]
    FROM [SomeDb].[SomeTable] AS [t4]
    WHERE ([t1].[cid] = [t4].[cid]) AND ([t1].[pid] = [t4].[pid]) AND ([t4].[Status] = @p0)
    ) AS [t5]
) AS [qdt]

有没有办法更改 LINQ 以生成更简单的 SQL？

Answer 1

我发现您的 LINQ 尝试存在两个问题。对于初学者，您没有按 SQL 查询中与 LINQ 中相同的字段进行分组。然后，您将对组的结果使用 FirstOrDefault，而不是选择组的关键数据。

您的查询应如下所示:

var query = 
(
    from someTable in SomeTable
    where someTable.Status == "Open"
    group someTable by new 
    { 
        someTable.lid, 
        someTable.qdt,
        someTable.pbn,
        someTable.cid,
        someTable.cn,
        someTable.pid,
        someTable.Status,
    } into grouped
    select new
    {
        lid = grouped.Key.lid,
        qdt = grouped.Key.qdt,
        pbn = grouped.Key.pbn,
        cid = grouped.Key.cid,
        cn = grouped.Key.cn,
        pid = grouped.Key.pid,
        amount = grouped.Sum(o => o.Amount),
        Status = grouped.Key.Status
    }
);

我现在无法测试它是否会生成完全相同的 SQL。