我正在尝试让实体框架(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]
如果它不一遍又一遍地撞击相同的桌子,那么嵌套本身就不会太糟糕。添加的聚合列越多,此问题就越严重。
我在这里没有发现任何类似的问题,所以我确定我做错了什么。
当我想返回多个聚合列时,让实体框架生成高效投影的正确方法是什么?
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]