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 Includes 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 almost the same in the linked Github issue above.
DISCLAIMER: I checked EF 10 (in .net 10) against EF 11 (in .net 11). That said, not all of the benefits have to come necessarily from EF 11 alone as I changed two variables in the benchmark. In theory, it could be solely .net 10 to .net 11, but unlikely.
[MemoryDiagnoser]
[SimpleJob(RuntimeMoniker.Net10_0)]
[SimpleJob(RuntimeMoniker.Net11_0)]
public class SplitQueryBenchmarks
{
private SqliteConnection _connection = null!;
private DbContextOptions<BloggingContext> _options = null!;
[GlobalSetup]
public void Setup()
{
_connection = new SqliteConnection("Data Source=:memory:");
_connection.Open();
_options = new DbContextOptionsBuilder<BloggingContext>()
.UseSqlite(_connection)
.Options;
using var context = new BloggingContext(_options);
context.Database.EnsureCreated();
Seeder.Seed(context, blogCount: 5_000, postsPerBlog: 5);
}
[GlobalCleanup]
public void Cleanup() => _connection.Dispose();
[Benchmark]
public List<Blog> SplitQuery()
{
using var context = new BloggingContext(_options);
return BlogQuery(context).AsSplitQuery().ToList();
}
private static IQueryable<Blog> BlogQuery(BloggingContext context)
=> context.Blogs
.AsNoTracking()
.Include(b => b.Author)
.Include(b => b.Category)
.Include(b => b.Series)
.Include(b => b.Posts);
}
Results:
| Method | Runtime | Mean | Error | StdDev | Median | Gen0 | Gen1 | Gen2 | Allocated |
|----------- |---------- |---------:|---------:|---------:|---------:|----------:|----------:|---------:|----------:|
| SplitQuery | .NET 10.0 | 68.53 ms | 1.358 ms | 2.742 ms | 67.54 ms | 4375.0000 | 1000.0000 | 375.0000 | 33.35 MB |
| SplitQuery | .NET 11.0 | 62.07 ms | 1.213 ms | 2.788 ms | 61.10 ms | 4111.1111 | 1333.3333 | 444.4444 | 30.15 MB |
Less runtime and less allocations!