EF Core 11 makes your split queries faster

If you use AsSplitQuery

anywhere in your codebase, EF Core 11 has a present for you: your queries get faster.

By default, EF Core loads everything in one query. If you Include

a collection, that means a JOIN and JOINs against collections duplicate the parent row for every child. The more stuff you join "the worse" it gets. AsSplitQuery

does tackle that: EF executes a query for the root entity and one for each additional collection. Of course this is not a silver bullet and can lead to:

• More roundtrips
• The chance that between root entity and children the state changed as this is not an atomic operation (as we have multiple transactions).

More info here: https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries

So what is the issue?

Here is the setup from the original issue: one reference navigation, one collection navigation:

var result = context.Blogs
.Include(x => x.BlogType) // reference navigation
.Include(x => x.Posts) // collection navigation
.AsSplitQuery()
.ToList();

Up to EF Core 10, this produces two queries:

SELECT [b].[Id], [b].[BlogDetailId], [b].[BlogTypeId], [b].[Name], [b0].[Id], [b0].[Type]
FROM [Blogs] AS [b]
LEFT JOIN [BlogType] AS [b0] ON [b].[BlogTypeId] = [b0].[Id]
ORDER BY [b].[Id], [b0].[Id]
SELECT [p].[Id], [p].[BlogId], [p].[Title], [b].[Id], [b0].[Id]
FROM [Blogs] AS [b]
LEFT JOIN [BlogType] AS [b0] ON [b].[BlogTypeId] = [b0].[Id]
INNER JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
ORDER BY [b].[Id], [b0].[Id]

Look at the second query: The one that fetches the posts. Why is BlogType

in there? It JOINs the table, selects its Id

, and orders by it. None of that is needed: to match a Post

to its Blog

, the blog's primary key is enough.

And it gets worse the more reference navigations you have. Five Include

s on reference navigations? Every single collection query drags along five JOINs and five extra ORDER BY columns. The database does the work, throws the result away, and your query plan suffers for it.

So what changed?

EF Core 11 (since preview 3) prunes the reference navigations from the collection queries. The second query now looks like what you would have written by hand:

SELECT [p].[Id], [p].[BlogId], [p].[Title], [b].[Id]
FROM [Blogs] AS [b]
INNER JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
ORDER BY [b].[Id]

Benchmarks

I will make a link to the whole setup at the end of the blog post. Here a small setup I was using. You can find alm