Skip to content
Katabench
Try free
9 min read The Katabench team

EF Core cartesian explosion: Include, split, projection

An EF Core cartesian explosion multiplies sibling collections: one listing with 40 photos, 60 reviews and 100 bookings returned 240,000 rows.

One listing page returned 240,000 rows. It is one statement with no N+1 in sight:

C#
var listings = await db.Listings
    .Include(l => l.Photos)
    .Include(l => l.Reviews)
    .Include(l => l.Bookings)
    .Where(l => l.Id == id)
    .ToListAsync();

That is a seeded listing with 40 photos, 60 reviews and 100 bookings, on EF Core 10 and the Npgsql provider against PostgreSQL 17. The entity graph comes out with exactly 40, 60 and 100 children, so an assertion on the output has nothing to catch.

This is the cartesian explosion, and it looks like the cure for a different problem. The N+1 query makes too many round trips, and folding everything into one Include chain feels like the disciplined fix. It does remove the round trips. The cost moves into a single statement that returns far more rows than the data contains.

The SQL EF Core generates

This is the statement EF Core sent:

SQL
SELECT l.id, l.description, l.review_count, l.title, p.id, p.listing_id, p.url,
       r.id, r.body, r.created_at, r.listing_id, r.rating,
       b.id, b.check_in, b.guest, b.listing_id
FROM listings AS l
LEFT JOIN photos AS p ON l.id = p.listing_id
LEFT JOIN reviews AS r ON l.id = r.listing_id
LEFT JOIN bookings AS b ON l.id = b.listing_id
WHERE l.id = @id
ORDER BY l.id, p.id, r.id

Photos, reviews and bookings all join on the listing and have no relationship with each other, so relational algebra gives you each combination: 40 x 60 x 100. Each of those rows repeats every column of the parent, including a 2 KB description, along with the body of the review. Rendered as text, the result came to about 557 MB, to describe a graph of 201 rows.

One listing with 40 photos, 60 reviews and 100 bookings

Three collection Includes, one statement

40 x 60 x 100

240,000 rows

Every photo pairs with every review and every booking.

AsSplitQuery, four statements

1 + 40 + 60 + 100

201 rows

Rows add up, but each statement sees its own moment in time.

Projection with counts and Take(5)

5 x 5

25 rows

Still a product, on a page's worth of data.

Row counts from EF Core 10 and Npgsql against PostgreSQL 17. Projecting all three full collections in one statement still returned 240,000 rows.

The database was not the slow part. EXPLAIN ANALYZE on the same statement finished in 533 ms, spilling an 86 MB sort to disk to satisfy the ORDER BY, which EF Core needs in order to group the rows back into objects. The client took a median of 26.8 seconds to receive the rows and allocated 238 MiB. Adding AsNoTracking() changed the allocation to 232 MiB, because it still reads the same 240,000 rows.

Rows per parent are the product of sibling sizes: 40 x 60 x 100 = 240,000.

The growth is what makes this hard to catch in testing. With two of the three collections the same listing returned 2,400 rows in 124 ms, and a fixture with three children of each kind returns 27 rows.

Siblings multiply, chains do not

Only collections at the same level multiply. Loading a chain behaves differently:

C#
var listings = await db.Listings
    .Include(l => l.Bookings)
    .ThenInclude(b => b.Payments)
    .Where(l => l.Id == id)
    .ToListAsync();

With 100 bookings and 2 payments per booking, that returned 200 rows, one per leaf. The EF Core documentation on single and split queries makes the same distinction, and it names the smaller problem that remains: parent columns repeat on every row, which it calls data duplication and treats as significant mainly when the parent has a big column.

EF Core logs a warning for these shapes:

Text
warn: RelationalEventId.MultipleCollectionIncludeWarning[20504]
      Compiling a query which loads related collections for more than one collection
      navigation, either via 'Include' or through projection, but no
      'QuerySplittingBehavior' has been configured.

It fired on the 240,000-row query and also on the 200-row chain, so it tells you to look at the shape and says nothing about row counts. Calling AsSingleQuery() or AsSplitQuery() on a query silences it, as does configuring a default, so an explicit choice is what a reviewer should expect to see.

AsSplitQuery and what it costs

AsSplitQuery() asks EF Core to send one statement per collection:

C#
var listings = await db.Listings
    .Include(l => l.Photos)
    .Include(l => l.Reviews)
    .Include(l => l.Bookings)
    .Where(l => l.Id == id)
    .AsSplitQuery()
    .ToListAsync();

The first statement loads the listing, and each following one joins back to it:

SQL
SELECT r.id, r.body, r.created_at, r.listing_id, r.rating, l.id
FROM listings AS l
INNER JOIN reviews AS r ON l.id = r.listing_id
WHERE l.id = @id
ORDER BY l.id

That made 4 statements and 201 rows, with a median of 31 ms and 0.35 MiB allocated. The price is that those are now four separate reads, and nothing ties them to one moment in time. The docs say most databases guarantee consistency within a single query, and no such guarantee exists across several.

This test gives Listing a ReviewCount column that a writer always updates in the same transaction as inserting a review. A DbCommandInterceptor commits one such transaction just before the split query's second statement, and the output compares the stored count with the loaded collection. Each line ran after the previous line's write, so the numbers climb:

Text
split, no transaction:         ReviewCount=60 Reviews.Count=61
split, read committed:         ReviewCount=61 Reviews.Count=62
split, repeatable read:        ReviewCount=62 Reviews.Count=62
single statement (afterwards): ReviewCount=63 Reviews.Count=63

The writer's transaction was atomic and the reader still saw half of it. The interleaving was forced for the demonstration, but production can produce the same one whenever a write lands between statements. A plain transaction does not help, because each statement under read committed gets a fresh snapshot. Repeatable read closes the gap on PostgreSQL:

C#
await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.RepeatableRead);
var listing = await db.Listings
    .Include(l => l.Reviews)
    .Where(l => l.Id == id)
    .AsSplitQuery()
    .SingleAsync();
await tx.CommitAsync();

That holds a snapshot open for the whole read, so keep it short, and be ready for the costs described in transaction isolation levels. The docs list two other costs: each statement is another round trip, which adds up when the database is far away, and on databases without concurrent result sets, such as PostgreSQL, earlier results are buffered in memory. They also warn that, before EF 10, split queries with Skip and Take need a fully unique ordering. EF 10 generated ORDER BY l.title, l.id from a LINQ OrderBy on the title alone, but keep the ordering unique on purpose anyway.

Projection helps when it asks for less

A projection that loads every collection keeps the product. This one still returned 240,000 rows and fired the same warning:

C#
var pages = await db.Listings
    .Where(l => l.Id == id)
    .Select(l => new
    {
        l.Title,
        Photos = l.Photos.Select(p => p.Url).ToList(),
        Reviews = l.Reviews.Select(r => r.Rating).ToList(),
        Guests = l.Bookings.Select(b => b.Guest).ToList()
    })
    .ToListAsync();

Dropping the wide columns took the text size from 557 MB to 15 MB and the time from 26.8 s to 2 s, on the same join shape. The row count only falls when the projection matches what a page shows, which is rarely every booking a listing has ever had:

C#
var pages = await db.Listings
    .Where(l => l.Id == id)
    .Select(l => new ListingPage(
        l.Title,
        l.Photos.OrderBy(p => p.Id).Select(p => p.Url).Take(5).ToList(),
        l.Reviews.OrderByDescending(r => r.CreatedAt).ThenBy(r => r.Id)
            .Select(r => r.Rating).Take(5).ToList(),
        l.Bookings.Count))
    .ToListAsync();

EF Core turned each Take into a ROW_NUMBER() subquery and the count into a correlated subquery, and the statement returned 25 rows, 5 x 5. It is still technically a product, and at this size that does not matter. Every shape side by side, as medians of five runs after two warm-ups:

Query shape Rows Statements Median time Allocated
3 Includes 240,000 1 26.8 s 238 MiB
3 Includes, no tracking 240,000 1 37.5 s 232 MiB
Project 3 full collections 240,000 1 2.0 s 143 MiB
3 Includes, split 201 4 31 ms 0.35 MiB
Project 2 Take(5) plus a count 25 1 4.1 ms 0.08 MiB
Same projection, split 11 3 7.2 ms 0.09 MiB
Project counts and an average 1 1 4.0 ms 0.06 MiB

Row counts are exact. The times come from a laptop sharing its Docker host with other containers, so only their magnitudes carry over: the no-tracking row came out slower than the tracking row, which says nothing about tracking.

Choosing a loading shape for each page

For read-only pages, start from a projection that matches the screen, with counts and aggregates computed in the database and Take on anything that can grow. Most list and detail views end up here, and the EF Core performance habits post covers the wider set of related choices.

When you need tracked entities because you will modify and save the graph, use AsSplitQuery() if there are two or more sibling collections that can grow, and decide deliberately whether that read needs a snapshot. Setting UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery) as a default is reasonable in a codebase that rarely relies on cross-collection consistency, and AsSingleQuery() remains available for the exceptions.

To catch the problem during development, promote the warning with ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning)) in test configuration, as the warning's own text suggests, and assert on row counts where a page matters. An output assertion cannot see 240,000 rows, so the EF Core exercises on Katabench grade this query family against row and allocation budgets, one of the gates grading describes.

A single query is still the right call when collections are known to be small, or when you need one consistent read and can afford the rows. The same multiplication corrupts SUM totals when you aggregate over it, so that post is a useful companion if your query computes as well as loads.

Practice Databases & EF Core

More like this: EF Core practice →

Get new katas and .NET tips in your inbox

A short note when fresh kata land, plus the C# and performance tricks behind the grading. No spam, unsubscribe anytime.