Entity Framework Core is a powerful and feature-rich ORM that simplifies working with relational databases in .NET.

However, to truly unlock its full potential, a solid understanding of how it works under the hood is essential.

One common pitfall developers encounter is Cartesian explosion, which can occur when EF Core loads related entities using joins in a single query.

In today’s blog post, we’ll explore what Cartesian explosion is, why it happens and how to avoid it to keep your queries performant and efficient.

Cartesian Explosion

A Cartesian explosion occurs when combining multiple sets of data results in an exponentially large number of combinations.

Here's an example:

var blog = await dbContext.Blogs
    .Include(b => b.Tags)
    .Include(b => b.Posts)
    .ToListAsync();

SELECT b."Id", b."CreatedAt", t."Id", t."BlogId", t."Name", p."Id", p."BlogId", p."Titles"
FROM "Blogs" AS b
LEFT JOIN "Tags" AS t ON b."Id" = t."BlogId"
LEFT JOIN "Posts" AS p ON b."Id" = p."BlogId"
ORDER BY b."Id", t."Id"

In this example, both Posts and Tags are collection navigations of Blog, and they're loaded at the same level. This results in the generation of every combination of posts and tags per blog.

If a blog has 5 tags and 10 posts, the result will contain 5 × 10 = 50 rows per blog.

With such duplicated rows, your performance suffers and memory usage increases significantly.

Split Query

Luckily for us we can easily overcome this issue, from EF Core 5 we can create split queries.

Split queries allow EF Core to break a single LINQ query into multiple SQL queries, one for each included collection navigation.

This approach avoids the massive row duplication caused by join-heavy queries when loading multiple collections:

var blog = await dbContext.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()
    .ToListAsync();

By simply adding AsSplitQuery method, instead of generating one large SQL query with multiple joins, EF Core executes separate queries to load Blogs, Tags, and Posts:

1. Query for Blogs

SELECT b."Id", b."CreatedAt"
FROM "Blogs" AS b
ORDER BY b."Id"

This retrieves the main Blogs entities.

2. Query for Tags

SELECT t."Id", t."BlogId", t."Name", b."Id"
FROM "Blogs" AS b
INNER JOIN "Tags" AS t ON b."Id" = t."BlogId"
ORDER BY b."Id"

This retrieves the Tags associated with each Blog.

3. Query for Posts

SELECT p."Id", p."BlogId", p."Titles", b."Id"
FROM "Blogs" AS b
INNER JOIN "Posts" AS p ON b."Id" = p."BlogId"
ORDER BY b."Id"

This retrieves the Posts associated with each Blog.

These results are then combined in memory, significantly reducing redundancy in the result set and improving performance.

Split queries are especially useful when dealing with many-to-many or one-to-many relationships, where the likelihood of Cartesian explosion is high.

Additionally, if you are fetching blog by id and it doesn't exist, EF Core will not even send queries for tags and post, which is great, or is it?

This is because EF Core is sending these SQLs sequentially, which can be problematic in highly concurrent systems, where related data may change between query executions.

Additionally, since the data is fetched in multiple roundtrips to the database, network latency can become a bottleneck, especially in cloud environments.

There’s no one-size-fits-all strategy when it comes to loading related entities. Use split queries when the benefit outweighs the cost and always benchmark both approaches.

Configure Split Queries Globally

If your application frequently needs split queries, you can configure them as the default behavior:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server=(localdb)mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;ConnectRetryCount=0",
            o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}

With this configuration, all queries that include multiple collection navigations will use split queries by default.

You can still override this behavior for specific queries by explicitly requesting a single query using the AsSingleQuery method:

var blog = await dbContext.Blogs
    .Include(b => b.Tags)
    .Include(b => b.Posts)
    .AsSingleQuery()
    .ToListAsync();

Conclusion

Cartesian explosion is an impactful issue that can degrade performance when loading multiple collection navigations in a single query.

Fortunately, split queries offer a powerful solution by separating collection includes into distinct queries, reducing row duplication and improving efficiency.

That said, split queries come with their own trade-offs, such as increased round-trips to the database and potential consistency issues in concurrent environments.

Understanding these trade-offs is key.

If you want to check out examples I created, you can find the source code here:

I hope you enjoyed it, subscribe and get a notification when a new blog is up!