如何让 EF6 生成包含多个聚合列的高效 SQL

本文关键字：SQL 高效 包含多 EF6 | 更新日期: 2023-09-27 18:34:12

我正在尝试让实体框架（v6.1.3）为具有多个聚合的查询生成高效的SQL。

下面是一个简化的示例。

桌子：

CREATE TABLE [dbo].[CaseAttorney](
[CaseAttorneyID] [int] IDENTITY(1,1) NOT NULL,
[CaseNumber] [varchar](30) NOT NULL,
[AttorneyID] [int] NOT NULL,
[DateAssigned] [datetime] NULL,
[DateUnassigned] [datetime] NULL,
 CONSTRAINT [PK_CaseAttorney] PRIMARY KEY CLUSTERED 
(
    [CaseAttorneyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

C#：

using (var cx = new DATA())
{
    var startDate = DateTime.Parse("1/1/2014");
    var endDate = startDate.AddDays(1);
    cx.Database.Log = Console.WriteLine;
    var res = cx.CaseAttorneys.
        GroupBy(o => new
        {
            AttorneyID = o.AttorneyID
        }).Select(g => new
        {
            AttorneyID = g.Key.AttorneyID,
            ActiveStart = g.Sum(item => (item.DateAssigned < startDate && (item.DateUnassigned == null || item.DateUnassigned >= startDate) ? 1 : 0)),
            Assigned = g.Sum(item => (item.DateAssigned >= startDate && item.DateAssigned <= endDate) ? 1 : 0)
        }).ToArray();
}

我没有得到一个包含多个嵌套表的非常低效的查询，而不是生成具有单个GROUP BY的查询。COUNT和SUM都会发生这种情况：

SELECT 
[Project3].[AttorneyID] AS [AttorneyID], 
[Project3].[C1] AS [C1], 
[Project3].[C2] AS [C2]
FROM ( SELECT 
    [Project2].[AttorneyID] AS [AttorneyID], 
    [Project2].[C1] AS [C1], 
    (SELECT 
        SUM([Filter2].[A1]) AS [A1]
        FROM ( SELECT 
            CASE WHEN (([Extent3].[DateAssigned] >= @p__linq__2) AND ([Extent3].[DateAssigned] <= @p__linq__3)) THEN 1 ELSE 0 END AS [A1]
            FROM [dbo].[CaseAttorney] AS [Extent3]
            WHERE [Project2].[AttorneyID] = [Extent3].[AttorneyID]
        )  AS [Filter2]) AS [C2]
    FROM ( SELECT 
        [Distinct1].[AttorneyID] AS [AttorneyID], 
        (SELECT 
            SUM([Filter1].[A1]) AS [A1]
            FROM ( SELECT 
                CASE WHEN (([Extent2].[DateAssigned] < @p__linq__0) AND (([Extent2].[DateUnassigned] IS NULL) OR ([Extent2].[DateUnassigned] >= @p__linq__1))) THEN 1 ELSE 0 END AS [A1]
                FROM [dbo].[CaseAttorney] AS [Extent2]
                WHERE [Distinct1].[AttorneyID] = [Extent2].[AttorneyID]
            )  AS [Filter1]) AS [C1]
        FROM ( SELECT DISTINCT 
            [Extent1].[AttorneyID] AS [AttorneyID]
            FROM [dbo].[CaseAttorney] AS [Extent1]
        )  AS [Distinct1]
    )  AS [Project2]
)  AS [Project3]

如果它不一遍又一遍地撞击相同的桌子，那么嵌套本身就不会太糟糕。添加的聚合列越多，此问题就越严重。

我

在这里没有发现任何类似的问题，所以我确定我做错了什么。

当我想返回多个聚合列时，让实体框架生成高效投影的正确方法是什么？

如何让 EF6 生成包含多个聚合列的高效 SQL

Count(predicate)（实际上任何涉及谓词的函数）似乎对生成的SQL查询都有这种影响。

但是，有条件Sum（即 Sum(predicate ? 1 : 0) ） 没有这种影响，因此以下内容将执行您想要的操作：

更新：事实证明，Sum技巧是必要的，但当谓词使用变量（如您的情况）时还不够。这很可能是 EF 错误，因为使用不同的GroupBy重载无济于事，除非您在执行GroupBy之前包含包含条件表达式的临时投影。

所以（最后）以下查询

db.CaseAttorneys.Select(item => new
{
    Item = item,
    ActiveStart = item.DateAssigned < startDate && (item.DateUnassigned == null || item.DateUnassigned >= startDate) ? 1 : 0,
    Assigned = item.DateAssigned >= startDate && item.DateAssigned <= endDate ? 1 : 0
})
.GroupBy(o => new
{
    AttorneyID = o.Item.AttorneyID
})
.Select(g => new
{
    AttorneyID = g.Key.AttorneyID,
    ActiveStart = g.Sum(item => item.ActiveStart),
    Assigned = g.Sum(item => item.Assigned)
}).ToArray();

生成所需的 SQL

SELECT 
    [GroupBy1].[K1] AS [AttorneyID], 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[A2] AS [C2]
    FROM ( SELECT 
        [Extent1].[K1] AS [K1], 
        SUM([Extent1].[A1]) AS [A1], 
        SUM([Extent1].[A2]) AS [A2]
        FROM ( SELECT 
            [Extent1].[AttorneyID] AS [K1], 
            CASE WHEN (([Extent1].[DateAssigned] < @p__linq__0) AND (([Extent1].[DateUnassigned] IS NULL) OR ([Extent1].[DateUnassigned] >= @p__linq__1))) THEN 1 ELSE 0 END AS [A1], 
            CASE WHEN (([Extent1].[DateAssigned] >= @p__linq__2) AND ([Extent1].[DateAssigned] <= @p__linq__3)) THEN 1 ELSE 0 END AS [A2]
            FROM [dbo].[CaseAttorneys] AS [Extent1]
        )  AS [Extent1]
        GROUP BY [K1]
    )  AS [GroupBy1]
相关文章：