Two Includes, 2,880 Rows: EF Core's Quiet Cartesian Tax

Every EF Core codebase I've worked in has a query shaped like this: load the products, Include the reviews, Include the images, ship the page. It reads like exactly what you mean, it returns exactly the right objects, and nothing about it looks expensive. This week I finally counted what happens between the database and the materializer. My demo catalog page returns 12 products. The database handed EF 2,880 rows to build them.

The setup

A catalog that could be any shop on the internet: 12 products, each with 30 reviews and 8 images. That's 468 rows of actual data across three tables. The query is the one you've written a hundred times:

var products = ctx.Products
    .Include(p => p.Reviews)
    .Include(p => p.Images)
    .AsNoTracking()
    .ToList();

To see what it really does, I registered a DbCommandInterceptor that captures the exact SQL EF executes, then replayed that SQL on a raw SQLite connection and counted rows, cells, and the text length of everything that came back. Wall time is the median of 9 runs, allocations come from GC.GetAllocatedBytesForCurrentThread. All of it runs against in-process SQLite in a small Linux container, so there's no network in these numbers — treat the payload column as a floor. A real connection makes this worse, not better.

Where 2,880 comes from

Here's the single statement EF generates by default:

SELECT "p"."Id", "p"."Description", ..., "r"."Comment", ..., "p0"."Url", ...
FROM "Products" AS "p"
LEFT JOIN "Review" AS "r" ON "p"."Id" = "r"."ProductId"
LEFT JOIN "ProductImage" AS "p0" ON "p"."Id" = "p0"."ProductId"
ORDER BY "p"."Id", "r"."Id"

Two LEFT JOINs against sibling collections, and that's the whole problem. A JOIN doesn't know Reviews and Images are unrelated, so it produces every combination per product: 30 reviews × 8 images = 240 rows per product, times 12 products.

[single-query (default)]
  SQL statements : 1
  rows read      : 2,880
  cells read     : 46,080
  payload (approx): 1,042 KB
  median time    : 18.6 ms
  allocated      : 2,095 KB

468 entities exist. 2,880 rows arrive. Every review comment crosses the wire 8 times, once per image it happened to get paired with, and every image URL arrives 30 times. That's how roughly 69 KB of actual data becomes a 1 MB result set, and why materializing 468 small objects allocates 2 MB. EF deduplicates by identity afterwards, so the object graph you get looks perfectly innocent. The waste is upstream of your code, which is exactly why nobody sees it.

And it compounds quietly. The multiplier is the product of your collection sizes, so 30 × 8 today is 300 × 20 after two good years of business, and nobody will have touched that file.

One method call later

.AsSplitQuery()

[split-query]
  SQL statements : 3
  rows read      : 468
  cells read     : 3,156
  payload (approx): 69 KB
  median time    : 4.5 ms
  allocated      : 509 KB

Three statements instead of one — products, then reviews joined through products, then images the same way. 468 rows total, which is exactly the number of entities that exist. Nothing is duplicated. Payload drops 15×, allocations drop 4×, and even wall time drops about 4× here, mostly because there's a tenth of the data to shovel. Time is the number I trust least in a container, but the ratios have been stable run after run, and rows read is just arithmetic.

Why it isn't the default

Because it isn't free, and I'd rather say that plainly than sell you a method call.

Three statements means three sequential round trips. In-process SQLite doesn't care; a database 30 ms away charges you three times before the first product materializes. If your collections are small — a handful of order lines, two addresses — the duplication is pocket change and the single round trip wins. Don't sprinkle AsSplitQuery on every query in the codebase. One collection Include has no cartesian problem at all, and splitting it just buys you an extra round trip for nothing.

There's also a consistency catch people skip: the three queries run separately, so a concurrent write can land between them. You can materialize a product carrying tomorrow's reviews and yesterday's images. A single query can't do that to you. If that matters for the screen you're building, wrap the read in a transaction or stay single.

If you decide split should be your project-wide default, it's one line — UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery) in the provider options — and AsSingleQuery() opts individual queries back out.

The page never needed the graph

My actual opinion, having stared at this for an evening: the interesting fix isn't the split. The product listing needed a name, a price, a review count, an average rating, and one thumbnail. It never needed 360 review entities.

var cards = ctx.Products
    .Select(p => new
    {
        p.Id, p.Name, p.Price,
        ReviewCount = p.Reviews.Count,
        AvgStars = p.Reviews.Average(r => (double?)r.Stars),
        Thumbnail = p.Images.OrderBy(i => i.SortOrder)
                            .Select(i => i.Url).FirstOrDefault(),
    })
    .ToList();

[projection]
  SQL statements : 1
  rows read      : 12
  cells read     : 72
  payload (approx): 1 KB
  median time    : 1.1 ms
  allocated      : 83 KB

12 rows. 1 KB. One statement, with the counting and averaging pushed into SQL where the database is happy to do it. A lot of the Include usage I see in read paths is habit — we load full graphs because the navigation properties are right there, then serialize a tenth of it into a DTO anyway. Include earns its keep when you genuinely need entities: updates, domain logic that walks the graph. For lists, project.

One more thing, and it's a little embarrassing. EF Core has been logging this the whole time:

warn: Compiling a query which loads related collections for more than one
collection navigation ... which can potentially result in slow query performance.

That's MultipleCollectionIncludeWarning, and it has scrolled past me in console noise for years. You can even configure it to throw. The diagnosis was free; I just wasn't reading it.

Full runnable sample, including the interceptor and the row-counting harness: https://github.com/ssukhpinder/dev-to-code-samples/tree/main/009-efcore-split-query

What's the biggest row multiplier you've caught in one of your own queries? Mine was 6× and I only found it because I went looking. I'd bet money there's a worse one in something I shipped years ago.

— Sukhpinder, still counting rows nobody asked me to count