Taming Cartesian Explosion in EF Core with AsSplitQuery

The row multiplication nobody notices until production

You add two Include calls to an EF Core query, run it locally against a handful of rows, and everything looks fine. Then the data grows: 100 parent rows, each with 100 children in two separate collections. The database returns 10,000 rows for what is logically 200 entities. EF Core discards the duplicates during materialization, but the network transfer, SQL parsing, and client-side memory pressure have already happened. This is the cartesian explosion, and it is one of the most common performance surprises in eager-loaded object graphs.

The fix is not to abandon Include. It is to change how the provider executes the query. AsSplitQuery, available since EF Core 5.0, tells the database provider to issue separate SQL statements for each included collection and stitch the results together in memory. The object graph you get back is identical; the execution path is not.

Why a single query multiplies rows

When you chain multiple collection includes, EF Core generates one SQL statement with LEFT JOIN clauses. If a Blog has 10 Post rows and 10 Tag rows, the join produces 100 rows for that single blog. EF Core then walks the result set, recognizes that it has already seen BlogId 1 and PostId 1, and fixes up the navigation properties. The duplication is temporary, but it is expensive: every duplicated column is transferred over the wire and parsed by the provider.

With a single collection include, EF Core often avoids this problem because the join is one-to-many and the row count is linear. The explosion only becomes multiplicative when you add a second collection navigation.

How AsSplitQuery changes execution

AsSplitQuery splits the work into discrete round trips. For a query with two collection includes, the provider issues three statements: one for the root entities, one for the first collection, and one for the second. EF Core materializes each result set independently, then correlates them by primary key. Because the matching happens on the client, the final Blog object has its Posts and Tags collections populated exactly as they would be with a single joined query.

The trade is straightforward: you give up a single atomic result set in exchange for smaller, linear result sets per collection. That is usually a win when the multiplicative factor is large, but it introduces new variables—primarily latency and consistency—that a single query did not have.

A worked example

Assume a console application targeting EF Core 5.0 or later with SQL Server. The model is a standard one-to-many setup:

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<BlogTag> Tags { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public ICollection<Post> Posts { get; set; }
    public ICollection<BlogTag> Tags { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public int BlogId { get; set; }
    public string Title { get; set; }
}

public class BlogTag
{
    public int TagId { get; set; }
    public int BlogId { get; set; }
    public string Label { get; set; }
}

The default query and the split query differ by one method call:

// Default: single SQL statement with joins
var defaultBlogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .ToListAsync();

// Split: separate SQL statements per collection
var splitBlogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()
    .ToListAsync();

To see the difference, enable EF Core logging in OnConfiguring:

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information);

Run both queries against a dataset with meaningful volume—say, 100 blogs, each with 100 posts and 100 tags. The default query would produce a result set of 1,000,000 rows. The split query would return 100 blog rows, 10,000 post rows, and 10,000 tag rows across three round trips. The memory required to materialize the object graph is similar in both cases, but the split query avoids transferring and parsing the million-row intermediate result.

Consistency, latency, and other limits

Split queries do not run inside an implicit transaction. Each SELECT is its own statement, so a concurrent write between the first and third query can produce an object graph that never existed at a single point in time. If your application requires a consistent snapshot, wrap the query in an explicit transaction:

using var transaction = context.Database.BeginTransaction();
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()
    .ToListAsync();
transaction.Commit();

Latency is the other constraint. Three round trips on a high-latency network can easily cost more than one round trip that returns a larger payload. Split queries are not automatically faster; they are a tool for controlling result-set shape.

They also do not replace pagination. If a single split query returns 500,000 child rows, you have traded a cartesian explosion for a large linear result set. Memory pressure remains.

Deciding what to use

Start by measuring. If your SQL logs show row counts multiplying far beyond the number of entities you need, AsSplitQuery is worth testing. If you are on a low-latency network and your collections are small, the default single query may remain faster because it avoids extra round trips.

For read-only scenarios, combine AsSplitQuery with AsNoTracking to skip change-tracking overhead. For reporting or API responses where you do not need tracked entities, a projection with Select bypasses the problem entirely: you fetch only the columns you need into a DTO, and no object graph is materialized.

Verify the outcome by counting round trips in your logs and comparing total execution time and memory against the single-query baseline on production-like data. If the split query reduces row volume without introducing unacceptable latency, it is the right tool for that query.