Diagnosing and Fixing EF Core Cartesian Explosion with AsSplitQuery

Recognizing the Problem

When you eager‑load a collection that has many items, you may notice:

• SQL Server profiler shows a single query that returns an enormous number of rows.
• The application consumes far more memory than expected.
• Query execution time spikes even though the data size on disk is moderate.

These symptoms usually point to a Cartesian explosion caused by a single SELECT that joins the parent table with the child collection.

Root Cause

EF Core’s default Include strategy is SingleQuery. The generated SQL looks like:

SELECT [p].[Id], [p].[Name], [c].[Id], [c].[ParentId], [c].[Description]
FROM [Parent] AS [p]
LEFT JOIN [Child] AS [c] ON [p].[Id] = [c].[ParentId]
WHERE [p].[Id] = @p0

If a parent has 1 000 children, the result set will contain 1 000 rows, each repeating the parent columns. EF Core then materializes the parent once and attaches all child rows. The duplicate parent data inflates the result size and can cause memory pressure.

Diagnostic Checklist

Check	How to Verify
Query shape	Run ToQueryString() and inspect the generated SQL.
Row count	Profile the query and note the number of rows returned.
Memory usage	Measure Process.PrivateMemorySize64 before and after the query.
Round‑trip count	Use EF Core logging to count CommandExecuted events.
Database support	Confirm the DBMS allows multiple statements per round‑trip.

Applying AsSplitQuery

When the checklist shows a large duplicate row count, switch to split query mode. The AsSplitQuery() extension tells EF Core to generate separate SELECTs for each navigation property.

var parent = await context.Parents
    .Where(p => p.Id == targetId)
    .Include(p => p.Children)
    .AsSplitQuery()  // Enable split query
    .FirstOrDefaultAsync();

Run the same ToQueryString() again; you should see two statements:

SELECT [p].[Id], [p].[Name]
FROM [Parent] AS [p]
WHERE [p].[Id] = @p0;

SELECT [c].[Id], [c].[ParentId], [c].[Description]
FROM [Child] AS [c]
WHERE [c].[ParentId] = @p0;

EF Core will now materialize the parent once and then hydrate the collection via a second query, eliminating row duplication.

Global Configuration

If you want split query behavior for all Include operations, set it in OnConfiguring or the DbContextOptionsBuilder:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString)
        .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
}

Or via dependency injection:

services.AddDbContext<MyDbContext>(options =>
    options.UseSqlServer(connStr)
           .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Measuring Impact

1. Baseline: Run the original query, capture execution time, row count, and memory usage.
2. After Split: Run the same query with AsSplitQuery(), capture the same metrics.
3. Compare:
   • Execution time: may increase slightly due to an extra round‑trip.
   • Row count: should be close to the number of parents plus the number of children (no duplication).
   • Memory usage: should drop noticeably for large collections.
4. Use a profiler to confirm the number of round‑trips matches the number of Include statements.

Escalation Criteria

If after applying split query the performance still degrades, consider:

• Re‑evaluating eager loading: load only the necessary navigation or use projection.
• Batching large collections with Take or Skip.
• Using a stored procedure or raw SQL for highly custom retrieval.
• Consulting the database administrator for indexing or query plan issues.

Caveats and Limitations

• Split queries add round‑trips; for very small result sets the overhead may outweigh the benefit.
• Some databases (e.g., older SQLite) may not support multiple statements per round‑trip; test accordingly.
• When using AsSplitQuery() with ThenInclude, EF Core will split each navigation level separately.
• Global split behavior may affect queries that rely on the original single‑query semantics, such as certain caching strategies.

Always validate in a staging environment that the application logic remains correct after switching to split queries.

Practical Check

After applying AsSplitQuery(), run:

var sql = context.Parents
    .Include(p => p.Children)
    .AsSplitQuery()
    .Where(p => p.Id == targetId)
    .ToQueryString();
Console.WriteLine(sql);

Verify that the output contains two separate SELECT statements. If not, the split query is not being applied (likely due to a missing AsSplitQuery() call or a global setting override).