在Linq to Entities中生成高效的LEFT JOIN with COUNT

Question

我有这个模式：

Lists ( ListId, Name, DateCreated, ... )
ListItems( ListId, Text, Foo, Baz, Qux, ... )

我有一个IQueryable<List>，它表示返回一些List实体的另一个Linq查询。

我想用ListItems的一些聚合数据来JOIN它，但事实证明这很困难，因为Linq生成的SQL效率很低，但我也想使查询可组合。

这里有一些类似于我希望Linq生成的SQL的内容：

选择*来自(--此部分代表IQueryable：选择ListId，名称。。。来自列表订购人列表创建日期偏移0行只能获得接下来的25行--林克的。跳过(0)。拿下(25))AS列表结果左联接(--这是我希望Linq生成的聚合数据查询：选择ListId，COUNT(1)为[COUNT]，COUNT(当Foo='bar'时为CASE，然后1为空结束)为CountFooBar，计数(当Baz>5时大小写，然后1以空结束)为CountBaz5来自ListItems其中Qux不为空分组依据ListId)AS ItemsStats ON ListResults.ListId=ItemsStats.ListId

这是我的Linq-我更喜欢扩展方法语法：

IQueryable lists=GetLists(0，25)；var stats=this.dbContent.ListItemsWhere((ListItemli)=>li.Qux！=null).GroupBy((ListItem li)=>li.ListId)。选择(grp=>new{grp。钥匙计数=grp。Count()，CountFooBar=grp。计数((ListItemli)=>li.Foo=="bar")CountBaz5=组。计数((ListItemli)=>li.Baz>5)})退货清单.加入(内部：统计数据，outerKeySelector:(列表l)=>l.ListId，innerKeySelector:grp=>grp。钥匙resultSelector:(list，itemStats)=>new｛list，itemsStats｝)

然而，这会生成如下所示的SQL(此查询显示了我的真实表和列名，这比我之前发布的模式稍微复杂一些：)

选择[项目13]。[C2]作为[C1]，[项目13]。[ListId]AS[ListId]，[项目13]。[C1]作为[C2]，[项目13]。[C3]作为[C3]，[项目13]。[C4]作为[C4]，[项目13]。[C5]作为[C5]，[项目13]。[C6]作为[C6]，[项目13]。[C7]为[C7]来自(选择[项目11]。[C1]作为[C1]，[项目11]。[ListId]AS[ListId]，[项目11]。[C2]作为[C2]，[项目11]。[C3]作为[C3]，[项目11]。[C4]作为[C4]，[项目11]。[C5]作为[C5]，[项目11]。[C6]作为[C6]，(选择计数(1)为[A1]FROM(选择[Project12].[ListId]作为[ListId]来自(选择[范围11]。[ListId]AS[ListId]，[范围11]。[创建]AS[创建]自[dbo]。[Lists]AS[Extent11]WHERE([Extent11].[TenantId]=8)AND([Extend11].[BlarghId]=8))AS[项目12]订单方[项目12]。[创建]DESC偏移0行仅获得接下来的25行)作为[限制6]内部联接[dbo]。[ListItems]AS[Extent12]ONWHERE(([Extent12].[Baz]>0)OR((LEN([Extend12].[Notes]))>0)OR((LEN([Extenti12].[Value])>0)AS[C7]来自(选择[项目9]。[C1]作为[C1]，[项目9]。[TenantId]AS[TenantId]，[项目9]。[BarghId]AS[BarghID]，[项目9]。[ListId]AS[ListId]，[项目9]。[C2]作为[C2]，[项目9]。[C3]作为[C3]，[项目9]。[C4]作为[C4]，[项目9]。[C5]作为[C5]，(选择计数(1)为[A1]FROM(选择[Project10].[TenantId]AS[TTenantId]，[Project10%.[BlarghId]AS[BlarghId]，[Product10].ListId]AS[ListId]来自(选择[Extent9]。[TenantId]AS[TenantId]，[Extent9]。[BarghId]AS[BarghID]，[Extent9]。[ListId]AS[ListId]，[Extent9]。[创建]AS[创建]自[dbo]。[Lists]AS[Extent9]WHERE([Extent9].[TenantId]=8)AND([Extend9].[BlarghId]=88))AS[项目10]订单方[项目10]。[创建]DESC偏移0行仅获得接下来的25行)作为[限制5]内部联接[dbo]。[ListItems]AS[Extent10]ONWHERE(([Extent10].[Baz]>0)OR((LEN([Extend10].[Notes]))>0)或(LEN[Extent10].Value])>0[C6]来自(选择[项目7]。[C1]作为[C1]，[项目7]。[TenantId]AS[TenantId]，[项目7]。[BarghId]AS[BarghID]，[项目7]。[ListId]AS[ListId]，[项目7]。[C2]作为[C2]，[项目7]。[C3]作为[C3]，[项目7]。[C4]作为[C4]，(选择计数(1)为[A1]FROM(选择[Project8].[TenantId]AS[TTenantId].[Project8].[BlarghId]AS[BlarghId]，[Project8].[ListId]AS[ListId]来自(选择[范围7]。[TenantId]AS[TenantId]，[范围7]。[BarghId]AS[BarghID]，[范围7]。[ListId]AS[ListId]，[范围7]。[创建]AS[创建]自[dbo]。[Lists]AS[Extent7]WHERE([Extent7].[TenantId]=8)AND([Extend7].[BlarghId]=8))AS[项目8]按[项目8]订购。[创建]DESC偏移0行仅获得接下来的25行)作为[限制4]内部联接[dbo]。[ListItems]AS[Extent8]ON([Limit4].[TenantId]=[Extent8].[Tenant Id])ANDWHERE(([Extent8].[Baz]>0)OR((LEN([Extend8].[Notes]))>0)OR((LEN([Extent 8].[Value])>0[C5]来自(选择[项目5]。[C1]作为[C1]，[项目5]。[TenantId]AS[TenantId]，[项目5]。[BarghId]AS[BarghID]，[项目5]。[ListId]AS[ListId]，[项目5]。[C2]作为[C2]，[项目5]。[C3]作为[C3]，(选择计数(1)为[A1]FROM(选择[Project6].[TenantId]AS[TTenantId][Project6].[BlarghId]AS[BlarghId]，[Project6].[ListId]AS[ListId]来自(选择[范围5]。[TenantId]AS[TenantId]，[范围5]。[BarghId]AS[BarghID]，[范围5]。[ListId]AS[ListId]，[范围5]。[创建]AS[创建]自[dbo]。[Lists]AS[Extent5]WHERE([Extent5].[TenantId]=8)AND([Extend5].[BlarghId]=88))AS[项目6]订单方[项目6]。[创建]DESC偏移0行仅获得接下来的25行)作为[限制3]内部联接[dbo]。[ListItems]AS[Extent6]ON([Limit3].[TenantId]=[Extent6].[Tenant Id])ANDWHERE(([Extent6].[Baz]>0)OR((LEN([Extend6].[Notes]))>0)OR((LEN([Extent 6].[Value])>0[C4]来自(选择[项目3]。[C1]作为[C1]，[项目3]。[TenantId]AS[TenantId]，[项目3]。[BarghId]AS[BarghID]，[项目3]。[ListId]AS[ListId]，[项目3]。[C2]作为[C2]，(选择计数(1)为[A1]FROM(选择[Project4].[TenantId]AS[TTenantId][项目4].[BlarghId]AS[BlarghId]，[Project4].[ListId]AS[ListId]来自(选择[扩展3]。[TenantId]AS[TenantId]，[扩展3]。[BarghId]AS[BarghID]，[扩展3]。[ListId]AS[ListId]，[扩展3]。[创建]AS[创建]自[dbo]。[Lists]AS[Extent3]WHERE([Extent3].[TenantId]=8)AND([Extend3].[BlarghId]=88))AS[项目4]订单方[项目4]。[创建]DESC偏移0行仅获得接下来的25行)作为[限制2]内部联接[dbo]。[ListItems]AS[Extent4]ON([Limit2].[TenantId]=[Extent4].[TenantId])ANDWHERE(([Extent4].[Baz]>0)OR((LEN([Extend4].[Notes]))>0)OR((LEN([EExtent4].[Value])>0[C3]来自(选择[分组依据1]。[A1]作为[C1]，[分组依据1]。[K1]AS[TenantId]，[分组依据1]。[K2]AS[BlarghId]，[分组依据1]。[K3]AS[ListId]，[分组依据1]。[K4]为[C2]来自(选择[项目2]。[K1]作为[K1]，[项目2]。[K2]作为[K2]，[项目2]。[K3]作为[K3]，[项目2]。[K4]作为[K4]，计数([Project2].[A1])为[A1]来自(选择[项目2]。[TenantId]AS[K1]，[项目2]。[BarghId]AS[K2]，[项目2]。[ListId]AS[K3]，1作为[K4]，1作为[A1]来自(选择[Extent2]。[ListId]AS[ListId]FROM(选择[Project1].[ListId]作为[ListId]来自(选择[延伸1]。[ListId]AS[ListId]，[延伸1]。[创建]AS[创建]自[dbo]。[Lists]AS[Extent1]WHERE([Extent1].[TenantId]=8)AND([Extentr1].[BlarghId]=88))AS[项目1]按[项目1]订购。[创建]DESC偏移0行仅获得下25行)为[限制1]内部联接[dbo]。[ListItems]AS[Extent2]ON([Limit1].[ListId]=[Extent2].[ListId])WHERE([Extent2].[Baz]>0)OR((LEN([Extend2].[Notes]))>0)OR((LEN([EExtent2].[Value]))>0))AS[项目2])AS[项目2]按[K1]、[K2]、[K3]、[K4]分组)AS[分组依据1])AS[项目3])AS[项目5])AS[项目7])AS[项目9])AS[项目11])AS[Project13]

它根本不将COUNT()语句组合在一起，而是将COUNT谓词移动到单独的WHERE子句中。还要注意重复的分页子查询(其中使用OFFSET 0 ROW FETCH NEXT 25)，而我的手写查询只执行一次。

Answer 1

这里有一个半解决方法：

我意识到最好的短期解决方案是在数据库中包含SQL(作为UDFFUNCTION或VIEW)，这意味着一些数据代码必须在数据库中(而不是将数据库用作"哑存储")。

我首先创建了一个表值UDF，它将接受表值参数，推理将允许组合，代价是需要生成输入参数表(来自分页查询的ListId值的数组)。然而，在这样做的过程中，我意识到Linq-to-Entities(版本6)还不支持Function Imports中的表值参数。

然后，我推断出一个更好的方法是将COUNT操作移动到VIEW(它代表我手写查询中的LEFT JOIN的一半)，然后我可以让Linq将其与现有的IQueryable进行JOIN，从而保持了可组合性，并生成了高效的运行时查询(事实上，当我运行它时，根据SQL Server Profiler，查询需要34ms才能执行，而旧的Linq生成的低效查询需要830ms)。

这是我使用的：

CREATE VIEW ListItemStatistics AS选择ListId，COUNT(*)为[CountAll]，计数(CASE WHEN…)为计数。。。来自ListItems其中Foo="bar"分组依据ListId

然后从Linq:

IQueryable lists=GetListsQuery(0，25)；var listsWithItemsStats=列表。.加入(inner:this.dbContent.ListItemStatistics，outerKeySelector:list=>列表。ListId，innerKeySelector:row=>row。ListId，resultSelector:(list，row)=>new｛list，row｝)

但是，因为这确实使用了数据库端逻辑(在VIEW中)，所以并不理想。

Answer 2

根据我的经验，当您在案例中使用聚合函数(如Count(predicate)应用过滤时，EF会生成这样的查询。如果将Count(condition)构造替换为条件和(Sum(condition ? 1 : 0))，您将获得更好的SQL查询，如下所示：

var stats = db.ListItems
.Where(li => li.Qux != null)
.GroupBy(li => li.ListId)
.Select(grp => new
{
grp.Key,
Count = grp.Count(),
CountFooBar = grp.Sum(li => li.Foo == "bar" ? 1 : 0),
CountBaz5 = grp.Sum(li => li.Baz > 5 ? 1 : 0)
});

其他部分保持不变。在mod之前，我得到了与发布的类似的SQL，下面是我在这个小mod之后得到的：

SELECT 
[Limit1].[ListId] AS [ListId], 
[Limit1].[Name] AS [Name], 
[Limit1].[DateCreated] AS [DateCreated], 
[GroupBy1].[K1] AS [ListId1], 
[GroupBy1].[A1] AS [C1], 
[GroupBy1].[A2] AS [C2], 
[GroupBy1].[A3] AS [C3]
FROM   (SELECT [Extent1].[ListId] AS [ListId], [Extent1].[Name] AS [Name], [Extent1].[DateCreated] AS [DateCreated]
FROM [dbo].[List] AS [Extent1]
ORDER BY [Extent1].[DateCreated] ASC
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY  ) AS [Limit1]
INNER JOIN  (SELECT 
[Filter1].[K1] AS [K1], 
COUNT([Filter1].[A1]) AS [A1], 
SUM([Filter1].[A2]) AS [A2], 
SUM([Filter1].[A3]) AS [A3]
FROM ( SELECT 
[Extent2].[ListId] AS [K1], 
1 AS [A1], 
CASE WHEN (N'bar' = [Extent2].[Foo]) THEN 1 ELSE 0 END AS [A2], 
CASE WHEN ([Extent2].[Baz] > 5) THEN 1 ELSE 0 END AS [A3]
FROM [dbo].[ListItem] AS [Extent2]
WHERE [Extent2].[Qux] IS NOT NULL
)  AS [Filter1]
GROUP BY [K1] ) AS [GroupBy1] ON [Limit1].[ListId] = [GroupBy1].[K1]

UPDATE：以上只是LINQ等效于手动SQL查询(当您调整联接代码以生成LEFT OUTER JOIN时)。但是，考虑到主数据分页，OUTER APPLYSQL查询可能会执行得更好。您可以让LINQ to Entities生成这样类型的查询：

var listsWithItemsStats = lists
.SelectMany(l => db.ListItems
.Where(li => li.ListId == l.ListId && li.Qux != null)
.GroupBy(li => li.ListId)
.Select(grp => new
{
grp.Key,
Count = grp.Count(),
CountFooBar = grp.Sum(li => li.Foo == "bar" ? 1 : 0),
CountBaz5 = grp.Sum(li => li.Baz > 5 ? 1 : 0)
})
.DefaultIfEmpty(),
(list, itemsStats) => new { list, itemsStats });