优化/重写LINQ查询与GROUP BY和COUNT

Id  |   IsActive    |   Name    |   Foo     |   Bar
1   |       1       |   A       |   11      |   null
2   |       1       |   A       |   11      |   null
3   |       1       |   A       |   null    |   123
4   |       1       |   B       |   null    |   321

Expected:
A = 2;
B = 1;

db.MyEntity
    .Where(x => x.IsActive)
    .GroupBy(x => new { x.Name, x.Foo, x.Bar })
    .GroupBy(x => new { x.Key.Name, Count = x.Count() })
    .ToDictionary(x => x.Key, x => x.Count);

db.MyEntity
    .Where(x => x.IsActive)
    .GroupBy(x => x.Name)
    .ToDictionary(x => x.Key,
        x =>
            x.Where(y => y.Foo != null).Select(y => y.Foo).Distinct().Count() +
            x.Where(y => y.Bar != null).Select(y => y.Bar).Distinct().Count());

public class MyEntity
{
    public int Id { get; set; }
    public bool IsActive { get; set; }
    public string Name { get; set; }
    public int? Foo { get; set; }
    public int? Bar { get; set; }
}

db.MyEntity
    .Where(x => x.IsActive)
    .GroupBy(x => new { x.Name, x.Foo, x.Bar })
    .GroupBy(x => x.Key.Name)
    .ToDictionary(x => x.Key, x => x.Count());

查询效率极低，因为您在客户端做了很多工作(构建字典所涉及的所有工作)，而无法使用数据库进行预测。这是一个问题，因为数据库(特别是如果这些值被索引)可以比客户端更快地完成这项工作，也因为在数据库上进行预测涉及到通过网络发送的数据要少得多。

所以在分组数据之前，简单地做你的预测。

var activeItems = db.MyEntity.Where(x => x.IsActive);
var query = activeItems.Select(x => new { Name, Value = x.Foo}).Distinct()
    .Concat(activeItems.Select(x => new { Name, Value = x.Bar}).Distinct())        
    .Where(x => x != null)
    .GroupBy(pair => pair.Name)
    .Select(group => new { group.Key, Count = Group.Count()})
    .ToDictionary(pair => pair.Key, pair => pair.Count);

您的目标是生成以下查询:

select Name, count(distinct Foo) + count(distinct Bar)
from myEntity
where IsActive = 1
group by Name

这是获得所需内容的最小查询。但是LINQ似乎把一切都尽可能地复杂化了:)

您的目标是在数据库级别尽可能多地完成任务。现在您的查询被翻译为:

SELECT 
    [Project2].[C1] AS [C1], 
    [Project2].[Name] AS [Name], 
    [Project2].[C2] AS [C2], 
    [Project2].[id] AS [id], 
    [Project2].[IsActive] AS [IsActive], 
    [Project2].[Name1] AS [Name1], 
    [Project2].[Foo] AS [Foo], 
    [Project2].[Bar] AS [Bar]
    FROM ( SELECT 
        [Distinct1].[Name] AS [Name], 
        1 AS [C1], 
        [Extent2].[id] AS [id], 
        [Extent2].[IsActive] AS [IsActive], 
        [Extent2].[Name] AS [Name1], 
        [Extent2].[Foo] AS [Foo], 
        [Extent2].[Bar] AS [Bar], 
        CASE WHEN ([Extent2].[id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
        FROM   (SELECT DISTINCT 
            [Extent1].[Name] AS [Name]
            FROM [dbo].[SomeTable] AS [Extent1]
            WHERE [Extent1].[IsActive] = 1 ) AS [Distinct1]
        LEFT OUTER JOIN [dbo].[SomeTable] AS [Extent2] ON ([Extent2].[IsActive] = 1) AND ([Distinct1].[Name] = [Extent2].[Name])
    )  AS [Project2]
    ORDER BY [Project2].[Name] ASC, [Project2].[C2] ASC

它从数据库中选择所有内容，并在应用层进行分组，这是低效的。

@Servy的查询:

var activeItems = db.MyEntity.Where(x => x.IsActive);
var query = activeItems.Select(x => new { Name, Value = x.Foo}).Distinct()
.Concat(activeItems.Select(x => new { Name, Value = x.Bar}).Distinct())        
.Where(x => x != null)
.GroupBy(pair => pair.Name)
.Select(group => new { group.Key, Count = Group.Count()})
.ToDictionary(pair => pair.Key, pair => pair.Count);

被翻译成:

SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [C2], 
[GroupBy1].[A1] AS [C3]
FROM ( SELECT 
    [UnionAll1].[Name] AS [K1], 
    COUNT(1) AS [A1]
    FROM  (SELECT 
        [Distinct1].[Name] AS [Name]
        FROM ( SELECT DISTINCT 
            [Extent1].[Name] AS [Name], 
            [Extent1].[Foo] AS [Foo]
            FROM [dbo].[SomeTable] AS [Extent1]
            WHERE ([Extent1].[IsActive] = 1) AND ([Extent1].[Foo] IS NOT NULL)
        )  AS [Distinct1]
    UNION ALL
        SELECT 
        [Distinct2].[Name] AS [Name]
        FROM ( SELECT DISTINCT 
            [Extent2].[Name] AS [Name], 
            [Extent2].[Bar] AS [Bar]
            FROM [dbo].[SomeTable] AS [Extent2]
            WHERE ([Extent2].[IsActive] = 1) AND ([Extent2].[Bar] IS NOT NULL)
        )  AS [Distinct2]) AS [UnionAll1]
    GROUP BY [UnionAll1].[Name]
)  AS [GroupBy1]

好多了。

我已经试过了:

var activeItems = (from o in db.SomeTables
                   where o.IsActive
                   group o by o.Name into gr
                   select new { gr.Key, cc = gr.Select(c => c.Foo).Distinct().Count(c => c != null) + 
                                             gr.Select(c => c.Bar).Distinct().Count(c => c != null) }).ToDictionary(c => c.Key);

翻译成:

SELECT 
1 AS [C1], 
[Project5].[Name] AS [Name], 
[Project5].[C1] + [Project5].[C2] AS [C2]
FROM ( SELECT 
    [Project3].[Name] AS [Name], 
    [Project3].[C1] AS [C1], 
    (SELECT 
        COUNT(1) AS [A1]
        FROM ( SELECT DISTINCT 
            [Extent3].[Bar] AS [Bar]
            FROM [dbo].[SomeTable] AS [Extent3]
            WHERE ([Extent3].[IsActive] = 1) AND ([Project3].[Name] = [Extent3].[Name]) AND ([Extent3].[Bar] IS NOT NULL)
        )  AS [Distinct3]) AS [C2]
    FROM ( SELECT 
        [Distinct1].[Name] AS [Name], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM ( SELECT DISTINCT 
                [Extent2].[Foo] AS [Foo]
                FROM [dbo].[SomeTable] AS [Extent2]
                WHERE ([Extent2].[IsActive] = 1) AND ([Distinct1].[Name] = [Extent2].[Name]) AND ([Extent2].[Foo] IS NOT NULL)
            )  AS [Distinct2]) AS [C1]
        FROM ( SELECT DISTINCT 
            [Extent1].[Name] AS [Name]
            FROM [dbo].[SomeTable] AS [Extent1]
            WHERE [Extent1].[IsActive] = 1
        )  AS [Distinct1]
    )  AS [Project3]
)  AS [Project5]

与第二版基本相同，但没有联合。

结论:

我将创建一个视图，并将其导入模型，如果表是相当大的，性能是至关重要的。否则，坚持第三版或第二版@Servy。当然应该测试性能

我认为你可以稍微修改一下你的初始查询，以得到你想要的:

db.MyEntity
    .Where(x => x.IsActive)
    .GroupBy(x => new { x.Name, x.Foo, x.Bar })
    .GroupBy(x => x.Key.Name)
    .ToDictionary(x => x.Key, x => x.Count());

当您将Count()添加到第二个分组时，您正在计算由三部分组成的键的重复值。您只希望计算每个由三部分组成的键的不同值，因此在按Name分组的之后计算。

只有关于问题的建议可以不使用DISTINCT以获得更好的性能。使用分组。

请查看此链接