Sql很快，但转换为linq时就慢了

本文关键字：linq 很快 转换 Sql | 更新日期: 2023-09-27 18:16:26

速度很快

SELECT Foo,
       count(*)
FROM
  (SELECT Foo
   FROM MyTable
   GROUP BY Foo,
            Bar,
            Baz) AS Subquery
GROUP BY Foo

这是快

var query = from fooGrp in
    (from rv in _myRepository.AsQueryable()
        group rv by new {rv.Foo, rv.Bar, rv.Baz}
        into grp
        select grp)
    group fooGrp by fooGrp.Key.Foo
    into grp2
    select new {grp2.Key, Count = grp2.Count()};
query.ToDictionary(x => x.Key, x => x.Count);

这个很慢，真的很慢!

_myRepository.AsQueryable()
    .GroupBy(x => new { x.Foo, x.Bar, x.Baz })
    .GroupBy(x => x.Key.Foo)
    .ToDictionary(x => x.Key, x => x.Count());

我不明白这两个linq表达式的区别是什么?它们都返回预期的结果集。

为第一个表达式(fast)生成的SQL是:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [Foo], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Distinct1].[Foo] AS [K1], 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            [Extent1].[Foo] AS [Foo], 
            [Extent1].[Bar] AS [Bar], 
            [Extent1].[Baz] AS [Baz]
            FROM [dbo].[MyTable] AS [Extent1]
        )  AS [Distinct1]
        GROUP BY [Distinct1].[Foo]
    )  AS [GroupBy1]

第二个表达式(慢)生成的SQL是:

太长了，超过了这个帖子的字符限制，所以不能发布:/

Sql很快，但转换为linq时就慢了

所以结果是LINQ表达式不相同。

正确的linq表达式是:

_myRepository.AsQueryable()
    .GroupBy(x => new {x.Foo, x.Bar, x.Baz})
    .GroupBy(x => x.Key.Foo)
    .Select(x => new {x.Key, Count = x.Count()})
    .ToDictionary(x => x.Key, x => x.Count);

我错过了一个选择，这是我没有预料到的，因为在正常的SQL中，你只能选择group by子句中的列。但是LINQ做了各种各样的魔术来包含其余的列，除非您在select.

中限制它。
相关文章：