c# - Entity Framework Core 3.0 查询导致 "SqlException: ' Execution Timeout Expired'"和 tempdb 变满。适用于 EF Core 2.2.6

Question

我正在 Microsoft Entity Framework Core 3.0 中运行一个相当简单的查询，如下所示:

var dbProfile = db.Profiles.Where(x => x.SiteId == Int32.Parse(id))
    .Include(x => x.Interests)
    .Include(x => x.Pets)
    .Include(x => x.Networks)
    .Include(x => x.PersonalityTraits)
    .SingleOrDefault();

它在 EF Core 2.2.6 上运行良好，但是当升级到 EF Core 3.0 时，此查询会立即针对 721 个配置文件运行，但对于至少一个配置文件，查询超时:

Microsoft.Data.SqlClient.SqlException: 'Execution Timeout Expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.'

然后我记录了发送到数据库服务器的实际查询:

https://stackoverflow.com/a/58348159/3850405

SELECT [t].[Id], [t].[Age], [t].[City], [t].[Country], [t].[County], [t].[DeactivatedAccount], [t].[Gender], [t].[HasPictures], [t].[LastLogin], [t].[MemberSince], [t].[PresentationUpdated], [t].[ProfileName], [t].[ProfilePictureUrl], [t].[ProfileText], [t].[SiteId], [t].[VisitorsCount], [i].[Id], [i].[Name], [i].[ProfileId], [p0].[Id], [p0].[Description], [p0].[Name], [p0].[ProfileId], [n].[Id], [n].[Name], [n].[NetworkId], [n].[ProfileId], [p1].[Id], [p1].[Name], [p1].[ProfileId]
FROM (
    SELECT TOP(2) [p].[Id], [p].[Age], [p].[City], [p].[Country], [p].[County], [p].[DeactivatedAccount], [p].[Gender], [p].[HasPictures], [p].[LastLogin], [p].[MemberSince], [p].[PresentationUpdated], [p].[ProfileName], [p].[ProfilePictureUrl], [p].[ProfileText], [p].[SiteId], [p].[VisitorsCount]
    FROM [Profiles] AS [p]
    WHERE ([p].[SiteId] = '123') AND '123' IS NOT NULL
) AS [t]
LEFT JOIN [Interests] AS [i] ON [t].[Id] = [i].[ProfileId]
LEFT JOIN [Pets] AS [p0] ON [t].[Id] = [p0].[ProfileId]
LEFT JOIN [Networks] AS [n] ON [t].[Id] = [n].[ProfileId]
LEFT JOIN [PersonalityTraits] AS [p1] ON [t].[Id] = [p1].[ProfileId]
ORDER BY [t].[Id], [i].[Id], [p0].[Id], [n].[Id], [p1].[Id]

然后我尝试在 SSMS 中运行实际的 SQL 并最终出现以下错误:

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140737692565504' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

我的 tempdb现在已经完全填满了数据库磁盘。我尝试了 10 个其他 ID，并且立即运行了相同的查询。

我尝试使用命令 DBCC SHRINKDATABASE(tempdb, 10); 再次缩小 tempdb它工作正常。然而，当我再次尝试运行查询时，同样的事情发生了。如果我跳过包括表格一切正常。这里可能有什么问题，我该如何解决？这是 EF Core 3.0 中的已知错误吗？查看 EF Core 2.2.6 中的查询，它对所有表执行这样的单独选择:

SELECT [x.Interests].[Id], [x.Interests].[Name], [x.Interests].[ProfileId]
FROM [Interests] AS [x.Interests]
INNER JOIN (
    SELECT TOP(1) [x0].[Id]
    FROM [Profiles] AS [x0]
    WHERE [x0].[SiteId] = '123'
    ORDER BY [x0].[Id]
) AS [t] ON [x.Interests].[ProfileId] = [t].[Id]
ORDER BY [t].[Id]

Answer 1

这是 EF Core 3 中记录的重大更改:Eager loading of related entities now happens in a single query
新行为类似于 EF6 中的查询生成，其中多个包含可以创建非常大且昂贵的查询。这些查询也可能由于超时、查询计划生成成本或查询执行资源耗尽而失败。
因此，就像在 EF6 中一样，您需要避免包含多个不相关的实体包含路径，因为它们会创建非常昂贵的查询。
相反，您可以使用延迟加载，或在单独的查询中显式加载实体图的一部分，并让更改跟踪器修复导航属性。
EF 5 添加了一个选项来关闭一个名为 Split Queries 的大查询生成。 .