When one EF Core query returns a million rows

Here's a query that looks perfectly reasonable:

var order = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .Include(o => o.Notes)
    .SingleAsync(o => o.Id == orderId, ct);

It's fast in development. In production, for a large order, it takes seconds and moves megabytes over the network. The cause is how EF Core loads several collections at once.

#The cartesian explosion

By default, EF Core turns this into one SQL query with a JOIN for each included collection. A join between one order and its related rows returns one row for every combination:

Every one of those rows repeats all the order's columns and the columns of the other collections. EF Core removes the duplicates on the client, so the result looks right. But the database still builds, and sends over the network, thousands of rows to describe one order with 80 child records.

That's the cartesian explosion. EF Core logs a warning when it detects a query that includes more than one collection this way. It's worth turning that warning into an error in development so you notice.

#Split queries

AsSplitQuery() tells EF Core to load each collection with a separate SQL query instead:

var order = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .Include(o => o.Notes)
    .AsSplitQuery()
    .SingleAsync(o => o.Id == orderId, ct);

Now SQL Server returns 1 + 50 + 10 + 20 rows across four queries, instead of 10,000 rows in one. EF Core stitches the results together into the same object graph.

You can make it the default for a DbContext and opt out per query with AsSingleQuery() where a single query is better:

options.UseSqlServer(connectionString,
    sql => sql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

#The trade-offs

Split queries aren't free:

• More round trips. Four queries instead of one. When the database is far away or the collections are small, a single query can still be faster.
• No single snapshot. Each query runs separately, so if another process changes the data between them, the parts you load can be slightly inconsistent with each other. Run the queries inside a transaction with a suitable isolation level, such as snapshot isolation, if that matters.
• Paging needs a unique sort order. When you use Skip and Take with split queries, order by something unique, such as the primary key. Otherwise each query may page differently and the collections won't line up with their parents.

#Often the best fix: load less

Before reaching for split queries, ask whether you need entire entities at all. For a page that shows an order summary, a projection loads just the columns you need, in one query, with no explosion:

var summary = await db.Orders
    .Where(o => o.Id == orderId)
    .Select(o => new OrderSummary(
        o.Id,
        o.Lines.Count,
        o.Lines.Sum(l => l.Price * l.Quantity),
        o.Payments.Sum(p => p.Amount)))
    .SingleAsync(ct);

Keep Include for when you're going to change the entities, and use projections for reading.

#Takeaway

Including more than one collection in a single EF Core query can multiply the rows returned. Use AsSplitQuery(), or make it the default, when you need several collections, keep the consistency and paging caveats in mind, and use projections for reads that don't need full entities.