EF Core 2.1进行多个DB调用
EF Core 2.1进行多个DB调用
提问于 2018-06-07 20:25:11
有没有办法防止EF Core在单个枚举函数调用中执行多个DB往返？
考虑到这个相对简单的LINQ表达式：
var query2 = context.CheckinTablets.Select(ct => new
{
Id = ct.Id,
DeviceName = ct.Name,
Status = ct.CheckinTabletStatuses
.OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault()
}).ToList();过去的解释是“一个枚举调用转换为一个DB调用”(如果您禁用延迟加载)。在EF Core中，情况已经不再是这样了！
在EF 6.2.0中，此LINQ被翻译为
SELECT [Extent1].[CheckinTabletID] AS [CheckinTabletID],
[Limit1].[TimestampUtc] AS [TimestampUtc]
--...
FROM [dbo].[CheckinTablet] AS [Extent1] OUTER APPLY (
SELECT TOP (1) [Project1].[CheckinTabletStatusID] AS [CheckinTabletStatusID],
[Project1].[CheckinTabletID] AS [CheckinTabletID],
[Project1].[TimestampUtc] AS [TimestampUtc]
FROM (
SELECT [Extent2].[CheckinTabletStatusID] AS [CheckinTabletStatusID],
[Extent2].[CheckinTabletID] AS [CheckinTabletID],
[Extent2].[TimestampUtc] AS [TimestampUtc]
--...
FROM [dbo].[CheckinTabletStatus] AS [Extent2]
WHERE [Extent1].[CheckinTabletID] = [Extent2].[CheckinTabletID]
) AS [Project1] ORDER BY [Project1].[TimestampUtc] DESC
) AS [Limit1];虽然很难看，但它是POLA之后的一个很好的东西。更重要的是，我们可以使用它来优化DB端(索引)。
在EF Core 2.1.0中，我们得到了如下内容：
SELECT [ct].[CheckinTabletID] AS [Id], [ct].[strName] AS [DeviceName] FROM [CheckinTablet] AS [ct]
exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=1
exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=2
exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=3
exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=4
exec sp_executesql N'SELECT TOP(1) [cts].[CheckinTabletStatusID], [cts].[CheckinTabletID], [cts].[TimestampUtc] FROM [CheckinTabletStatus] AS [cts] WHERE @_outer_Id = [cts].[CheckinTabletID] ORDER BY [cts].[TimestampUtc] DESC',N'@_outer_Id int',@_outer_Id=5是的，这是一个调用，首先获取所有实体(CheckinTablets)，然后每行调用以获取每个实体的状态……
因此，在一次调用中，ToList()实体框架对数据库进行了n+1调用。这是非常不受欢迎的，有没有办法禁用这种行为或解决办法？
编辑1:
.Include()对这个问题没有帮助...它仍然会发出n+1 DB请求。
信用编辑2 (credit ):
不是返回对象，而是简单的值，只调用一次！当然，如果你不想扁平化你的实体，或者你想从第二个表中得到多个值，这并不是很有帮助。很高兴知道这一点！
var query2 = _context.CheckinTablets.Select(ct => new
{
Id = ct.Id,
DeviceName = ct.Name,
Status = new CheckinTabletStatus
{
Id = ct.CheckinTabletStatuses.OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault().Id,
CheckinTabletId = ct.CheckinTabletStatuses.OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault().CheckinTabletId,
}
}).ToList();生成一个对DB的调用：
SELECT [ct].[intCheckinTabletID] AS [Id0],
[ct].[strName] AS [DeviceName],
(
SELECT TOP (1) [cts].[intCheckinTabletStatusID]
FROM [tCheckinTabletStatus] AS [cts]
WHERE [ct].[intCheckinTabletID] = [cts].[intCheckinTabletID]
ORDER BY [cts].[dtmTimestampUtc] DESC
) AS [Id],
(
SELECT TOP (1) [cts0].[intCheckinTabletID]
FROM [tCheckinTabletStatus] AS [cts0]
WHERE [ct].[intCheckinTabletID] = [cts0].[intCheckinTabletID]
ORDER BY [cts0].[dtmTimestampUtc] DESC
) AS [CheckinTabletId]
FROM [tCheckinTablet] AS [ct];回答 1
Stack Overflow用户
发布于 2018-06-07 22:21:12
我注意到当你试图返回嵌套的对象时，它会这样做。
您可以尝试在投影中展平状态对象，例如。类似于：
var query2 = context.CheckinTablets.Select(ct => new
{
Id = ct.Id,
DeviceName = ct.Name,
StatusName = ct.CheckinTabletStatuses
.OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault().Name
}).ToList();页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50741185
复制相关文章
相似问题