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:
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.
This is the statement EF Core sent:
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.idPhotos, 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.
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.
Only collections at the same level multiply. Loading a chain behaves differently:
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:
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() asks EF Core to send one statement per collection:
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:
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.idThat 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:
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=63The 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:
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.
A projection that loads every collection keeps the product. This one still returned 240,000 rows and fired the same warning:
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:
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.
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
- Open in the editor: Filter Before You Fetch
EF Core and SQL Easy Free, no account needed
Filter Before You Fetch
Filter measurements on the database, not after loading the whole table.
- Open the Pro kata: One Query, Sixty Thousand Rows
EF Core and SQL Hard Katabench Pro
One Query, Sixty Thousand Rows
Chained collection Includes multiply into a cartesian row explosion. Ask the database for the answer instead of the graph.
More like this: EF Core practice →