Avoiding Cartesian Explosion in EF Core: Use AsSplitQuery for Collection Includes

Why Collection Includes Can Hurt Performance

EF Core’s default strategy for Include on collection navigations is to emit one SQL statement that JOINs the root table with every included collection. If a root entity has two collections, the resulting rows are the Cartesian product of those collections. For example, a Blog with 10 Posts and each Post having 5 Comments produces 50 rows for that single Blog record, duplicating the Blog columns 50 times. The extra data inflates network traffic, memory usage, and materialization cost.

Split Queries to the Rescue

EF Core 5+ introduces AsSplitQuery() (or the global UseSplitQueryBehavior option) which changes the execution plan: EF Core emits one query for the root entity and one additional query per collection navigation. The results are then merged in memory by the foreign‑key relationships.

Worked Example

Consider a simple model:

public class Blog
{
    public int Id { get; set; }
    public string Url { get; set; }
    public ICollection<Post> Posts { get; set; } = new List<Post>();
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
    public ICollection<Comment> Comments { get; set; } = new List<Comment>();
}

public class Comment
{
    public int Id { get; set; }
    public string Text { get; set; }
    public int PostId { get; set; }
    public Post Post { get; set; }
}

Query without split:

var blogs = await context.Blogs
    .Include(b => b.Posts)
    .ThenInclude(p => p.Comments)
    .ToListAsync();

EF Core emits a single SQL similar to:

SELECT b.*, p.*, c.* FROM Blogs AS b
LEFT JOIN Posts AS p ON p.BlogId = b.Id
LEFT JOIN Comments AS c ON c.PostId = p.Id
WHERE b.Id IN (...)

Result set size: rows = Posts × Comments per Blog. Duplicate Blog columns appear for every combination.

With split query:

var blogs = await context.Blogs
    .Include(b => b.Posts)
    .ThenInclude(p => p.Comments)
    .AsSplitQuery()
    .ToListAsync();

EF Core now emits three statements:

-- Root
SELECT * FROM Blogs WHERE Id IN (...);
-- Collection 1
SELECT * FROM Posts WHERE BlogId IN (...);
-- Collection 2
SELECT * FROM Comments WHERE PostId IN (...);

EF Core joins the three result sets in memory using the foreign‑key columns. Each row in the root set appears once, and the collections are attached without duplication.

When Split Queries Pay Off

• Large collections: When a collection has dozens or hundreds of rows, the JOIN amplification can be orders of magnitude larger than the root set.
• High‑latency databases: Even with an extra round‑trip, the reduced payload can outweigh the network cost.
• Complex object graphs: Multiple nested collections exacerbate the Cartesian effect.

When They Might Hurt

• Small collections: The overhead of an additional round‑trip can dominate when the joined data is tiny.
• Transactions: By default, split queries run outside a single transaction. If you need a consistent snapshot across all collections, wrap the call in a transaction (e.g., RepeatableRead).
• Pagination: When combined with Skip/Take, EF Core 5+ applies pagination to the root query only. The collection queries are unpaginated, potentially returning many more rows than expected. Verify against the exact EF Core version you use.
• Filtered Include: ThenInclude(...).Where(...) still uses the same split‑query logic, but the filter is applied in the collection query. Ensure the filter is efficient.
• Reference navigations: Split queries affect only collection navigations; reference navigations remain JOINed.

Common Mistakes

1. Enabling globally without profiling: options.UseSplitQueryBehavior(SplitQueryBehavior.SplitQuery) changes every query. A blanket switch can degrade performance for queries that benefit from a single JOIN. Prefer per‑query AsSplitQuery() unless you have data‑driven evidence.
2. Assuming automatic consistency: Split queries do not provide a transaction boundary. If your application reads data that can change between round‑trips, you might see stale or inconsistent related records.
3. Ignoring version differences: Split queries were introduced in EF Core 5. In EF Core 3.x or EF6, this option is unavailable and the JOIN strategy is inevitable.

How to Verify the Effect

1. Enable logging:

   optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging();
   

2. Check emitted SQL:

   var sql = context.Blogs
       .Include(b => b.Posts)
       .ThenInclude(p => p.Comments)
       .AsSplitQuery()
       .ToQueryString();
   Console.WriteLine(sql);
   

   This prints the three separate SELECT statements.
3. Benchmark: Run both the default and split‑query variants with a realistic data set, measuring elapsed time and bytes transferred. Use Stopwatch for timing and DbCommandInterceptor to capture payload sizes.
4. Validate data integrity: If you wrap the call in a transaction, verify that no related entity is missing or duplicated in the resulting object graph.

Conclusion

Split queries are a powerful tool in EF Core for preventing cartesian explosion when loading multiple collections. They are most beneficial for large, nested collections and high‑latency environments, but can hurt performance for small includes or when strict transactional consistency is required. Use AsSplitQuery() judiciously, profile your queries, and remember that reference navigations are unaffected. With the right settings, you can keep your data transfer lean and your application responsive.