Entity Framework Core 3.0 query causes "SqlException: 'Execution Timeout Expired'" and tempdb become full. Works with EF Core 2.2.6

Question

I'm running a fairly simple query in Microsoft Entity Framework Core 3.0 that looks like this:

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();

It has worked fine with EF Core 2.2.6 but when upgrading to EF Core 3.0 this query runs instantly for 721 profiles but for at least one profile the query times out:

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

I then logged the actual query sent to the database server:

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]

I then tried to run the actual SQL in SSMS and ended up with the following error:

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.

My tempdb had now completely filled the database disk. I have tried 10 other ids and the same query runs instantly.

I tried to shrink the tempdb again with the command DBCC SHRINKDATABASE(tempdb, 10); and it worked fine. However when I tried running the queries again the same thing happened. If I skip including tables everything works out fine. What could be the trouble here and how do I fix it? Is this a known bug in EF Core 3.0? Looking at the query in EF Core 2.2.6 it performs individual selects like this for all tables:

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

This is a documented breaking change in EF Core 3: Eager loading of related entities now happens in a single query

The new behavior is similar to the query generation in EF6, where multiple includes can create very large and expensive queries. These queries can also fail due to timeouts, query plan generation costs, or query execution resource exhaustion.

So just like in EF6 you need to refrain from including multiple, unrelated, entity include paths, as these create very expensive queries.

Instead you can use Lazy Loading, or explicitly load parts of the entity graph in separate queries, and let the Change Tracker fix-up the Navigation Properties.

EF 5 has added an option to turn off the one-big-query generation called Split Queries.