Query Splitting: Taming the Cartesian Explosion in EF Core

When you eagerly load multiple collections with .Include(), EF Core generates a single SQL query with JOINs. The problem? If an order has 10 items and 5 notes, the resulting rows multiply — producing a cartesian product of 50 rows instead of 15 distinct records. This is the cartesian explosion problem.

The Problem in Practice

Consider a simple order model:

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; } = string.Empty;
    public List<OrderItem> Items { get; set; } = [];
    public List<OrderNote> Notes { get; set; } = [];
}

A straightforward query:

var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Notes)
    .ToListAsync();

EF Core translates this into a single query with LEFT JOINs. For an order with 10 items and 5 notes, the database returns 50 rows. Each OrderItem row is duplicated five times (once per note), and each OrderNote is duplicated ten times. The data transferred over the wire grows multiplicatively, not additively.

Split Queries to the Rescue

EF Core 5 introduced AsSplitQuery(), which breaks the single query into multiple SQL statements — one for the root entity and one per included collection:

var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Notes)
    .AsSplitQuery()
    .ToListAsync();

This generates three separate queries:

1. SELECT ... FROM Orders
2. SELECT ... FROM OrderItems WHERE OrderId IN (...)
3. SELECT ... FROM OrderNotes WHERE OrderId IN (...)

The total data transferred drops from 50 rows to 16 (1 + 10 + 5). EF Core stitches the results together in memory using the foreign keys.

Configuring Split Queries Globally

If your application frequently loads multiple collections, you can set split queries as the default:

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString,
        sqlOptions => sqlOptions.UseQuerySplittingBehavior(
            QuerySplittingBehavior.SplitQuery)));

You can then opt back into single queries where needed:

var orders = await context.Orders
    .Include(o => o.Items)
    .AsSingleQuery()
    .ToListAsync();

Trade-offs to Consider

Split queries are not universally better. There are important trade-offs:

Consistency. A single query executes atomically — you get a consistent snapshot of the data. Split queries execute multiple round trips, so data could change between queries. If another process inserts a new OrderItem between the first and second query, you might get an inconsistent view.

Round trips. Each split query is a separate database round trip. On high-latency connections or when including many navigations, the accumulated latency can exceed the cost of transferring extra data in a single query.

Single collection includes. When you're only including one collection, there's no cartesian explosion — a single query is fine and avoids the extra round trip.

When to Use Each Approach

// Single collection — no explosion, single query is fine
var orders = await context.Orders
    .Include(o => o.Items)
    .ToListAsync();

// Multiple collections — use split query
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Notes)
    .Include(o => o.StatusHistory)
    .AsSplitQuery()
    .ToListAsync();

Measuring the Difference

You can observe the difference by enabling simple logging:

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information));

Watch the generated SQL and row counts. For a real application I worked on, switching a particularly heavy query from single to split reduced the result set from 12,000 rows to 800 — and cut response time by 60%.

Filtering Included Collections

From EF Core 5 onwards, you can also filter what gets included, which reduces data further:

var orders = await context.Orders
    .Include(o => o.Items.Where(i => i.Quantity > 0))
    .Include(o => o.Notes.Where(n => n.IsPublic))
    .AsSplitQuery()
    .ToListAsync();

Summary

Use AsSplitQuery() when loading multiple collections to avoid the cartesian explosion. Stick with single queries for single includes or when transactional consistency matters. Profile your queries, measure the row counts, and pick the strategy that fits your data shape.