我能否防止 EF Core 在单个枚举函数调用中进行多次数据库往返?
考虑这个相对简单的 LINQ 表达式:
var query2 = context.CheckinTablets.Select(ct => new
{
Id = ct.Id,
DeviceName = ct.Name,
Status = ct.CheckinTabletStatuses
.OrderByDescending(cts => cts.TimestampUtc).FirstOrDefault()
}).ToList();
过去的期望是,“一个枚举调用对应一个数据库调用”(如果禁用延迟加载)。在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。更重要的是,我们可以使用它来优化数据库端(索引)。
使用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()
Entity Framework 对数据库进行了 n+1
次调用。这是非常不可取的,有没有一种方法可以禁用此行为或解决方法?
编辑 1:
.Include() 不能解决这个问题... 它仍然会进行 n+1 次数据库请求。
编辑 2(由 @jmdon 提供信用):
不返回对象而是简单值只需要一次调用!当然,如果您不想扁平化实体,或者想从第二个表中获取多个值,则这并不真正有所帮助。尽管如此,这也是很好知道的!
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();
产生一次数据库调用:
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];
ToList()
选择时),但不是像这样的查询(使用subcollection.FirstOrDefault()
)。 - Ivan Stoev