EF Core AsSplitQuery: Preventing Cartesian Explosion When Loading Related Collections

The Problem: One Query, Many Duplicated Rows

When you load an entity graph with multiple collection includes, Entity Framework Core defaults to a single SQL query with JOINs. This works well for small graphs, but it breaks down when a parent has several large collections. If a Blog has 200 Posts and 100 Tags, the JOIN produces 20,000 rows. Every Post row is repeated for every Tag, and every Tag row is repeated for every Post. The database transfers far more data than necessary, and EF Core must deduplicate the results in memory.

The useful takeaway: .AsSplitQuery() changes execution so EF Core issues one SQL statement per included collection instead of one giant JOIN. The provider returns multiple result sets, and EF Core correlates them by primary key so your object graph still looks normal in memory.

How Split Queries Work

A split query does not change the shape of your LINQ expression. It changes the execution plan. Instead of a single SELECT that joins Blogs, Posts, and Tags, EF Core sends separate queries: one for the root entities, one for each collection, and one for each reference. The relational provider must support multiple result sets. EF Core then stitches the rows together on the client using primary keys.

The result is that row multiplication disappears. A Blog with 200 Posts and 100 Tags still loads 200 Post rows and 100 Tag rows, but the Blog row is not duplicated 20,000 times.

Worked Example: Blog, Post, and Tag

Assume a simple model where a Blog has many Posts and many Tags:

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Post> Posts { get; set; }
    public List<Tag> Tags { get; set; }
}

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

public class Tag
{
    public int Id { get; set; }
    public string Label { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Loading the graph with a single query:

var blog = context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .First(b => b.Id == blogId);

By default, EF Core generates one SQL statement with JOIN clauses. If the Blog has many Posts and Tags, the result set contains the Cartesian product of those collections.

Loading the same graph as a split query:

var blog = context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()
    .First(b => b.Id == blogId);

With .AsSplitQuery(), EF Core executes multiple SELECT statements: one for the Blog, one for the Posts, and one for the Tags. The client correlates them by BlogId and Id, so blog.Posts and blog.Tags are populated exactly as before.

Configuring the Behavior

You can control splitting at three levels:

• Per query: call .AsSplitQuery() or .AsSingleQuery() on the LINQ query.
• Globally: configure the DbContext in OnConfiguring or AddDbContext:

options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);

• Override a global default: use .AsSingleQuery() on a specific query when the global behavior is split.

Limits and Common Mistakes

Consistency Across Result Sets

Because each collection is loaded by an independent query, a split query is not guaranteed to see the same database snapshot. If another transaction inserts a Post between the Blog query and the Posts query, your in-memory graph may reflect a state that never existed at a single point in time. For strict consistency, wrap the query in a serializable or snapshot transaction. For most read-only scenarios, eventual consistency within a single request is acceptable.

Round Trips and Latency

Split queries trade data transfer for round trips. A single JOIN query makes one network round trip; a split query makes at least one per included collection. On a high-latency connection—say, a cloud database across regions—the extra round trips can make split queries slower despite the reduced payload. Benchmark both approaches with your actual data shape and network conditions.

Small Collections and Overhead

If a Blog typically has two Posts and one Tag, the Cartesian product is tiny. The overhead of managing multiple result sets and client-side correlation may exceed the savings. Prefer split queries when at least one included collection is large relative to the parent, or when multiple collections multiply into a significant row count.

Pagination After Include

Applying Skip and Take after an Include can produce unexpected results with split queries. EF Core applies pagination to the root query, but the included collections are loaded separately. If you expect the collections themselves to be paginated, that is not how split queries work. Apply pagination to the root entities before includes, or project into a DTO with explicit paging logic.

Provider and Version Support

AsSplitQuery was introduced in EF Core 5.0. It requires a relational provider that supports multiple result sets. Some third-party providers or older database engines may not support it, or may behave differently. Check the EF Core version in your project file and confirm provider support before relying on the feature.

Client Evaluation and Raw SQL

Split queries assume the provider can translate the entire expression into multiple SQL statements. Mixing split queries with client evaluation, custom FromSqlRaw projections that assume a single result set, or LINQ operators that do not compose cleanly can cause errors or silently incorrect results. Always inspect the generated SQL.

How to Check the Result

You do not need to guess whether splitting is active. Use EF Core logging or the ToQueryString() method to inspect the SQL:

var sql = context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()
    .Where(b => b.Id == blogId)
    .ToQueryString();

Look for multiple SELECT statements rather than a single SELECT with JOIN clauses. You can also enable SQL logging in your DbContext configuration to see the statements in the Output window during debugging.

To measure the practical impact, seed a dataset where one parent has hundreds of children in two or more collections. Compare the number of rows returned and the execution time with and without .AsSplitQuery(). If the single-query row count is the product of the collection sizes, split queries will usually reduce both network payload and memory pressure.

When to Choose Split Queries

Scenario	Recommendation
Parent with multiple large collections	Use .AsSplitQuery()
Parent with small, bounded collections	Default single query is usually fine
High-latency database connection	Test both; extra round trips may dominate
Strict snapshot consistency required	Use a serializable or snapshot transaction, or avoid split queries
Pagination applied to root entities	Safe, but verify generated SQL

Split queries are a targeted optimization, not a universal default. They solve a specific problem—Cartesian explosion from multiple collection includes—and they introduce a different trade-off in round trips and consistency. Match the behavior to the shape of your data and the guarantees your application needs.