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.

Answer 2

Addition to @DavidBrowne-Microsoft answer.

Suppose we have following entities & navigations in the model

Customer
Customer.Address (reference nav)
Customer.Orders (collection nav)
Order.OrderDetails (collection nav)
Order.OrderDiscount (reference nav)

You would need to rewrite only for collection navigations, reference navigations can be part of same query.

var baseQuery = db.Customers.Include(c => c.Address).Where(c => c.CustomerName == "John");
var result = baseQuery.ToList(); // Or async method, If doing FirstOrDefault, add Take(1) to base query
baseQuery.Include(c => c.Orders).ThenInclude(o => o.OrderDiscount).SelectMany(c => c.Orders).Load();
baseQuery.SelectMany(c => c.Orders).SelectMany(o => o.OrderDetails).Load();

This will generate 3 queries to server. It would be slightly more optimized SQL then what EF Core 2.2 generated. And StateManager will fix up navigations. It also avoids duplicating any records coming from server to client.

Generated SQL:

// Customer Include Address
SELECT [c].[Id], [c].[CustomerName], [a].[Id], [a].[City], [a].[CustomerId]
FROM [Customers] AS [c]
LEFT JOIN [Address] AS [a] ON [c].[Id] = [a].[CustomerId]
WHERE ([c].[CustomerName] = N'John') AND [c].[CustomerName] IS NOT NULL

// Order Include Order discount
SELECT [o].[Id], [o].[CustomerId], [o].[OrderDate], [o0].[Id], [o0].[Discount], [o0].[OrderId]
FROM [Customers] AS [c]
INNER JOIN [Order] AS [o] ON [c].[Id] = [o].[CustomerId]
LEFT JOIN [OrderDiscount] AS [o0] ON [o].[Id] = [o0].[OrderId]
WHERE ([c].[CustomerName] = N'John') AND [c].[CustomerName] IS NOT NULL

// OrderDetails
SELECT [o0].[Id], [o0].[OrderId], [o0].[ProductName]
FROM [Customers] AS [c]
INNER JOIN [Order] AS [o] ON [c].[Id] = [o].[CustomerId]
INNER JOIN [OrderDetail] AS [o0] ON [o].[Id] = [o0].[OrderId]
WHERE ([c].[CustomerName] = N'John') AND [c].[CustomerName] IS NOT NULL

Edit: If you do not have CLR navigation to use in SelectMany then you can use EF.Property to reference the collection navigation.

Source: https://github.com/aspnet/EntityFrameworkCore/issues/18022#issuecomment-537219137

Answer 3

Yo need to change your code like this, then it will generate more query and avoids time out

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

remember that query should executed in Tracking mode. if it doesn't load childs you can add asTracking like this:

db.Profiles.AsTracking().Where(........