EF Core Query Splitting: Fix Slow Queries and Cartesian Explosion

A photo problem at school

Imagine you are a school clerk. Your principal asks: "For our classroom, give me the list of students and the list of textbooks."

There are 30 students and 50 textbooks. The simple, correct answer has just 80 items — 30 names and 50 book titles.

But suppose you only know how to make one big table where every row must contain a classroom, a student, and a book together. To fit everything in that one table, you end up writing every student next to every book: student 1 with book 1, student 1 with book 2, student 1 with book 3… all the way to student 30 with book 50.

That is 30 × 50 = 1,500 rows — to describe only 80 real things. Most of that paper is wasted, repeating the same names and titles again and again.

This wasteful "multiply everything together" table is exactly what EF Core can accidentally create. It is called the cartesian explosion, and query splitting is the cure. Let us see how.

How the explosion happens in EF Core

Say you have a Blog that has many Posts and many Tags. You write a normal query with two Include calls:

var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .ToListAsync();

This looks harmless. But behind the scenes, EF Core turns it into one SQL query that JOINs blogs to posts and to tags at the same time. The relational database has no choice — to put posts and tags in one flat result, it pairs every post with every tag.

If a blog has 10 posts and 10 tags, you do not get 20 rows. You get 10 × 10 = 100 rows, where each post is repeated 10 times and each tag is repeated 10 times.

Now scale that up. A blog with 100 posts and 50 tags would return 5,000 rows. Add a third collection and the numbers get terrifying. In one real-world example, a query returned over 133,000 rows when only about 1,000 were actually needed. The database does extra work, the network carries piles of duplicated data, and EF Core spends time throwing the duplicates away. Your endpoint feels slow for no obvious reason.

The fix: split the query

EF Core gives you a one-line fix: AsSplitQuery().

var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()        // 👈 the magic line
    .ToListAsync();

With this, EF Core stops trying to cram everything into one JOIN. Instead, it sends separate SQL queries — one for the blogs, one for the posts, one for the tags — and then stitches the results together in memory.

Now the numbers are sane again:

• 1 query returns the blogs
• 1 query returns the 10 posts (10 rows)
• 1 query returns the 10 tags (10 rows)

That is 20 rows total instead of 100. No multiplication, no waste.

Real performance numbers

Splitting is not always faster — it depends on whether an explosion is actually happening. Here is what measurements typically show:

Read that second row carefully. When a cartesian explosion is present, the split query is about 5–6× faster and uses roughly 5× less memory. In the extreme real-world case mentioned earlier, dropping from 133,000 rows to about 1,000 rows — a 99% reduction — made the query run almost instantly.

But notice the first row too: when there is no explosion, the single query is slightly faster because it only makes one trip to the database. Splitting added a tiny cost there. This is the key trade-off.

The trade-off: round-trips and consistency

Split queries are powerful, but they are not free. Keep two things in mind.

1. More round-trips. Each separate query is another trip to the database. If your database is far away (high network latency), several trips can cost more than one big trip. On a fast local network this rarely matters; over a slow connection it can.

2. Less consistency. A single query reads everything in one snapshot, so the data is perfectly consistent. With split queries running as separate statements, it is possible for data to change between them — for example, a tag gets added after the posts query but before the tags query.

A simple rule of thumb

You do not need to overthink this. Here is a clear guide:

• One collection or none? Use a normal single query. There is nothing to explode.
• Two or more one-to-many collections in the same query? This is where explosions happen — reach for AsSplitQuery().
• Not sure? Measure both with production-like data. The numbers will tell you instantly.

optionsBuilder.UseSqlServer(connectionString,
    o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

A real-world example: an order with items and payments

Let us make this concrete with something you would actually build — an Order screen that shows the order's line items and its payment attempts.

var order = await context.Orders
    .Include(o => o.Items)        // one-to-many collection #1
    .Include(o => o.Payments)     // one-to-many collection #2
    .FirstOrDefaultAsync(o => o.Id == orderId);

Say this order has 8 items and 3 payment attempts (the customer's card failed twice, then succeeded). The real data is just 11 things. But because both are collections in one JOIN, the database returns 8 × 3 = 24 rows, repeating every item 3 times and every payment 8 times.

With only one order this is small. But now imagine a report that loads 500 orders, each with items and payments. The multiplication happens for every single order, and the row count balloons into the hundreds of thousands. The report crawls.

Adding one line fixes it:

var order = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()               // 8 + 3 = 11 rows, not 24
    .FirstOrDefaultAsync(o => o.Id == orderId);

Now EF Core sends three tidy queries — the order, its items, and its payments — and joins them in memory. The duplicated flood disappears.

Another fix: only select what you need

Splitting is not the only tool. Often the deeper problem is that you are loading whole entities when you only need a few fields. A projection with Select can sidestep the explosion entirely by shaping a flat result:

var summary = await context.Orders
    .Where(o => o.Id == orderId)
    .Select(o => new OrderSummary
    {
        OrderId = o.Id,
        ItemCount = o.Items.Count,            // computed in SQL
        TotalPaid = o.Payments.Sum(p => p.Amount),
    })
    .FirstOrDefaultAsync();

Here EF Core asks the database to count items and sum payments directly, returning a single small row. No collections are loaded, so there is nothing to explode. When a screen only needs numbers or a few fields rather than the full child lists, projection is often even faster than a split query.

How to spot the problem in your own app

You usually discover cartesian explosion when an endpoint feels slow even though the data is small. To confirm it:

• Log the SQL. Turn on EF Core query logging and look at the generated SQL. If you see one query JOINing several collections, and the row count is far higher than the real data, that is the explosion.
• Watch the row counts. Tools like SQL Server Profiler or your database's query stats show how many rows actually came back. A surprising, large number is the giveaway.
• Check memory. A sudden jump in memory use during a query often means EF Core is loading a flood of duplicated rows.

Once you see it, adding AsSplitQuery() is often a one-line win that turns a 200 ms query into a 35 ms one.

How EF Core runs each style

It helps to picture the two execution styles side by side. A single query sends one SQL statement with JOINs; a split query sends several smaller statements:

And here is the decision in a simple flow:

This table sums up the trade-off at a glance:

One important gotcha: always order before you page

There is a subtle trap when you combine split queries with pagination (Skip and Take). Because a split query runs as several separate SQL statements, each statement must agree on which rows belong together. If your query is not ordered, the database is free to return rows in different orders for each part, and EF Core may stitch the wrong children to the wrong parent.

The rule is simple: when you use split queries with paging, always add a stable OrderBy.

var orders = await context.Orders
    .Include(o => o.Items)
    .OrderBy(o => o.Id)     // 👈 required for correct, repeatable paging
    .Skip(page * size)
    .Take(size)
    .AsSplitQuery()
    .ToListAsync();

EF Core will even warn you about this in its logs if you forget. Treat that warning seriously — without a stable order, your paged results can come out wrong in ways that are very hard to debug later.

Quick recap

• Cartesian explosion happens when EF Core loads two or more collections in a single JOIN, multiplying the rows (10 posts × 10 tags = 100 rows).
• AsSplitQuery() loads each collection in its own SQL query, so you get 10 + 10 = 20 rows instead — far less data over the wire.
• It can be 5–6× faster and use 5× less memory when an explosion is present.
• The cost is extra round-trips and a small consistency trade-off, so it is not always the right choice.
• Rule of thumb: two or more collections → split; otherwise keep a single query. And always measure with real data.

Just like our school clerk should write two short lists instead of one giant multiplied table, EF Core does its best work when you let it load each collection separately. One small method call can quietly turn a sluggish screen into a snappy one.

References and further reading

• Single vs. Split Queries — EF Core (Microsoft Learn) — the official documentation, with detailed examples.
• Efficient Querying — EF Core (Microsoft Learn) — Microsoft's broader performance guidance.
• EF Core 7 Performance: Cartesian Explosion — Thinktecture — a respected deep dive with benchmarks.