代码之家 › 专栏 › 技术社区 › Bob Horn

效率低下的linq-知道sql应该是什么-无法实现

linq-to-sql sql-server sql c#

Bob Horn · 技术社区 · 8 年前

我知道我要生成的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语句。对于qdt,每个分组的值在查询中都得到自己的select语句,如下所示:

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?

2 回复 | 直到 8 年前

Camilo Terevinto Chase R Lewis 8 年前

我发现你的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。

-4

MikeS 8 年前

在linq中,您似乎没有在所需的sql中按列分组;

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

});

我不能真正测试它,但这看起来有点像您在linq中试图复制的sql。