在EF核心中,我尝试使用一个子集合来投影一个结果集。
ctx.As.Select(a => new
{
AId = a.Id,
BNames = a.Bs.Select(x=>x.Name) //SubCollection
})
但是,在执行此操作时,BNames集合是惰性的,因此当枚举它时,它会为每一行触发一个单独的查询。例如,B的集合中的2个项目。
[a].[Id] AS [AId] FROM [As] AS [a]
p_executesql N'SELECT [x].[Name]
FROM [Bs] AS [x]
WHERE @_outer_Id = [x].[AId]',N'@_outer_Id int',@_outer_Id=1
p_executesql N'SELECT [x].[Name]
FROM [Bs] AS [x]
WHERE @_outer_Id = [x].[AId]',N'@_outer_Id int',@_outer_Id=2
在EF6中,相同的查询结果如下所示(正如我所期望的):
SELECT
[Extent1].[Id] AS [Id],
[Project1].[C1] AS [C1],
[Project1].[Name] AS [Name]
FROM [dbo].[A] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[Name] AS [Name],
[Extent2].[A_Id] AS [A_Id],
1 AS [C1]
FROM [dbo].[B] AS [Extent2] ) AS [Project1] ON [Extent1].[Id] = [Project1].[A_Id]
ORDER BY [Extent1].[Id] ASC, [Project1].[C1] ASC
如何让EF自动加载子集合 BNames
**?**
注意:这与使用.Include
包含子实体不是一个问题,因为这是一个自定义投影,而不是一个导航属性。在这里使用.Include
会导致错误。
完整的回购代码如下:https://gist.github.com/lukemcgregor/692834629da09b21d5a35515e86c9002
发布于 2018-05-28 13:30:16
在EF Core 2.1中,这是可能的,使用它的相关子查询优化。
您需要更新到EF Core2.1,并通过向子查询添加一个.ToList()
来进行小的修改,以选择加入优化:
foreach (var thing in ctx.As
.Select(a => new
{
AId = a.Id,
BNames = a.Bs.Select(x => x.Name).ToList()
}))
请参见New features in EF Core 2.1中的Optimization of correlated subqueries。
https://stackoverflow.com/questions/50559240
复制相似问题