Split Queries for Large Results

In this lesson, you will learn how to prevent "Cartesian explosion" in your database queries by splitting a single large operation into multiple efficient fetches.

The Problem: Cartesian Explosion

When you use .Include() in Entity Framework Core to load multiple collection navigations, EF Core typically generates a single JOIN query. If you have an Author with 10 Books and 10 Awards, the database produces a result set where every book is joined with every award. This creates 100 rows (10x10) for a single author. If you scale this up to thousands of authors, the result set size grows exponentially, leading to massive memory usage and poor performance. This phenomenon is called Cartesian explosion.

The Solution: Split Queries

EF Core allows you to change the strategy from a single query to multiple queries, ensuring that the database sends back only the data necessary for each relationship, rather than every possible combination of related data.

To use this, you apply the .AsSplitQuery() operator to your LINQ statement:

var authors = context.Authors
    .Include(a => a.Books)
    .Include(a => a.Awards)
    .AsSplitQuery()
    .ToList();

How It Works

When you call .AsSplitQuery(), EF Core will execute three separate SQL statements:

1. One to fetch the Authors.
2. A second to fetch all Books related to those authors.
3. A third to fetch all Awards related to those authors.

EF Core then handles the "re-hydration" of these objects in memory, stitching them back together into your entity graph.

When to Use It

Split queries are best used when:

• You are performing Include operations on multiple collections.
• The volume of data being returned is large.
• You notice performance degradation caused by massive, redundant result sets coming from your database.

By using .AsSplitQuery(), you trade a small increase in network round-trips for a significant reduction in database processing overhead and memory usage.

Key Takeaway: Use .AsSplitQuery() to prevent Cartesian explosion when loading multiple collections, ensuring your database only returns the specific related records rather than an exponential combination of rows.