I’ve got the following linq query:

return _db.LocationPolygons.Include(x => x.LocationPoints.OrderBy(y => y.SortOrder)).Where(x => x.LocationId == locationId && x.DeletedDateTime == null).ToList();

It used to run extremely fast under EF Core 6.0 / .NET 6.

I’ve just migrated to .NET 8 and EF Core 8 and now it takes 17 seconds to execute.

But the underlying SQL being generated takes < 1 second directly in SSMS.

This is the underlying SQL being produced:

DECLARE @__locationId_0 int = 11967;

SELECT [l].[LocationPolygonId], [l].[CentroidLat], [l].[CentroidLong], [l]. 
[DeletedDateTime], [l].[Description], [l].[Editable], [l].[LocationId], [l0]. 
[LocationPointId], [l0].[Latitude], [l0].[LocationPolygonId], [l0].[Longitude], [l0]. 
[SortOrder]
FROM [LocationPolygon] AS [l]
LEFT JOIN [LocationPoint] AS [l0] ON [l].[LocationPolygonId] = [l0].[LocationPolygonId]
WHERE [l].[LocationId] = @__locationId_0 AND [l].[DeletedDateTime] IS NULL
ORDER BY [l].[LocationPolygonId], [l0].[SortOrder]

• I have int primary keys on both tables
• I have an index on (LocationPoint) LocationPolygonId and SortOrder
• I have an index on (LocationPolygon) LocationId and DeletedDateTime
• I have the correct foreign key/constraint between LocationPoint.LocationPolygonId and LocationPolygon.

In terms of results, there is one LocationPolygon being returned and 44,000 LocationPoints which are related child entities that sit inside the LocationPolygon record.

Why would it take 17 seconds for EF Core to execute this?

17 seconds feels like it’s doing some kind of N+1 separate call for every single one of the 44,000 child rows or something?

I’ve also tried .AsSplitQuery() and it had a negligible effect.