When EF Core's Include Quietly Explodes Your Query: Reaching for AsSplitQuery

You add a second Include to a LINQ query — say, loading each customer's orders and their support tickets — and suddenly the endpoint that felt fine last sprint is dragging. The database is healthy, the indexes are right, and yet SQL Server is returning thousands of rows for what should be a few dozen customers. This is the cartesian explosion problem, and EF Core's AsSplitQuery() is the targeted fix.

The takeaway up front: when you eager-load two or more collection navigations in one query, EF Core's default single-query mode multiplies rows across those collections. Splitting the query into one SELECT per collection usually fixes it with a one-line change — but it trades round trips and introduces a consistency caveat worth understanding.

Why two Includes multiply your rows

Relational joins are flat. When EF Core loads customers with their orders in a single SQL statement, each customer row repeats once per order. That's manageable. Add a second collection — tickets — and now every customer row repeats for every combination of order and ticket. A customer with 10 orders and 10 tickets produces 100 rows, not 20. With three collections, it gets worse fast.

The consequences are real but easy to misdiagnose:

• Network transfer inflates — the same customer columns (including any large text fields) travel over the wire dozens of times.
• Materialization cost grows — EF Core has to deduplicate all those rows to rebuild the object graph.
• It looks like a database problem — CPU and I/O spike on a query that "should be simple."

EF Core actually tries to warn you: when a single query loads multiple collections, it logs a warning suggesting split queries. If you see that in your logs, treat it as a pointer, not noise. (Exact warning text varies by EF Core version, so don't grep for a specific string.)

What AsSplitQuery actually does

Available since EF Core 5, AsSplitQuery() changes the SQL generation strategy. Instead of one big JOIN, EF Core emits:

1. One query for the root entities (customers, with any filters and reference includes).
2. One separate query per included collection, filtered by the root's primary keys.

EF Core stitches the results together in memory using the key values, exactly as it would with joined rows — your object graph looks identical to calling code. The difference is purely in how data crosses the wire.

A worked example

Assume an EF Core 8 context with Customer, Orders, and Tickets:

// Before: single query, cartesian explosion risk
var customers = await context.Customers
    .Include(c => c.Orders)
    .Include(c => c.Tickets)
    .Where(c => c.IsActive)
    .ToListAsync();

// After: one query for customers, one for orders, one for tickets
var customers = await context.Customers
    .AsSplitQuery()
    .Include(c => c.Orders)
    .Include(c => c.Tickets)
    .Where(c => c.IsActive)
    .ToListAsync();

To verify the change did what you expect, enable EF Core logging (e.g., options.LogTo(Console.WriteLine, LogLevel.Information) in development) or call ToQueryString() on the query. You should see three separate SELECT statements instead of one statement with multiple JOINs. Comparing total rows returned — via a profiler or COUNT on the generated SQL — is the clearest before/after signal.

If most queries in your application benefit, you can make split the default at configuration time and opt back out per query:

options.UseSqlServer(connectionString, sql =>
    sql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

// Per-query escape hatch when a single round trip matters more:
var small = await context.Customers
    .AsSingleQuery()
    .Include(c => c.Orders)
    .ToListAsync();

Run this in your DbContext's OnConfiguring or wherever you register the context in Program.cs. No special permissions are needed beyond what your app already has — it's purely a client-side query-generation setting. The provider-specific method name differs (PostgreSQL's Npgsql provider has its own equivalent), so check the docs for your provider and EF Core major version.

The trade-offs you should actually weigh

Split queries are not free:

• More round trips. Three queries over a high-latency connection can be slower than one fat join, especially when collections are small (a handful of child rows each). If latency dominates your workload, measure before switching globally.
• Consistency gaps. Because the SELECTs run as separate statements, a concurrent write between them can produce a graph that never existed at a single instant — e.g., an order appears whose customer row has since changed. For most read models this is acceptable; where it isn't, wrap the read in a transaction or use snapshot isolation.
• It doesn't fix N+1. AsSplitQuery only changes how explicitly Included collections load. If your problem is lazy loading firing per entity, the fix is adding Includes or projections, not splitting.

Also note that a single collection include rarely needs splitting — the duplication factor is linear there, and the extra round trip often isn't worth it. The sweet spot is two or more collections, or collections paired with wide rows.

A practical decision rule

Don't apply AsSplitQuery blindly. Instead: turn on EF Core logging in a staging environment, find queries with multiple collection Includes (the warning helps), capture the generated SQL and row counts in both modes, and compare elapsed time under realistic load. Split the queries where rows returned drop dramatically and latency doesn't regress. If that describes most of your data access, flip the global default and keep AsSingleQuery() in your pocket for the exceptions.