Include, Projection, and Split Query: How Much Data an Object Graph Really Moves

In the lab, the same page took 15 or 214 milliseconds depending on the variant, transferred 82 kilobytes or 15.9 megabytes, and caused 1 or 101 round trips. The variant that saves the most has a weakness that no warning reports: if someone changes the data at the same time, it can return wrong results.

The case

A customer has two lists of equal rank: orders and contacts. The page shows 50 customers of a region, sorted by name, and for each customer all orders (number, date, amount) and all contacts (name, email). This is the data contract: every variant must deliver exactly this data. In the lab, this was checked on every single execution against an independently computed expected result, without a single deviation.

Two data profiles with 20,000 customers each:

• small: about 3 orders and 2 contacts per customer,
• fan-out: about 40 orders and 8 contacts per customer.

Five ways to the same result

var baseQuery = db.Customers
    .Where(c => c.Region == region)
    .OrderBy(c => c.Name).ThenBy(c => c.Id)
    .Skip(50 * page).Take(50);

// 1 · Include, single query
var v1 = await baseQuery.Include(c => c.Orders).Include(c => c.Contacts).AsNoTracking().ToListAsync();

// 2 · Include as split query
var v2 = await baseQuery.Include(c => c.Orders).Include(c => c.Contacts).AsSplitQuery().AsNoTracking().ToListAsync();

// 3 · Projection, single query
var projection = baseQuery.Select(c => new CustomerDto {
        Id = c.Id, Name = c.Name, Region = c.Region,
        Orders = c.Orders.Select(o => new OrderDto { Id = o.Id, Date = o.OrderDate, Amount = o.Total }).ToList(),
        Contacts = c.Contacts.Select(k => new ContactDto { Name = k.Name, Email = k.Email }).ToList() });
var v3 = await projection.ToListAsync();

// 4 · Projection as split query
var v4 = await projection.AsSplitQuery().ToListAsync();

// 5 · Lazy loading: load customers, the lists are loaded one by one on first access

What this turns into in SQL:

What was measured

Fan-out profile, SQL Server 2022, median over 240 page requests, application and database on the same network (round trip 0.7 ms):

The row explosion (often called Cartesian explosion). When a single query loads two lists, SQL Server combines every order of a customer with every one of that customer's contacts: 40 orders times 8 contacts make 320 rows for one customer, and over 16,000 rows for 50 customers. What is needed is just under 2,500 records. EF builds the right objects from them, but SQL Server first has to produce and sort the rows, the network has to transfer them, and .NET has to process them.

The surprise: projection alone does not help. A projection to a few columns is considered the tool of choice. With two lists, however, it produces the same row explosion as Include, only narrower: the same 16,257 rows, but 2.4 instead of 15.9 MB. EF Core warns in both cases in the log with the same message (MultipleCollectionIncludeWarning); its text explicitly mentions projections as well. Only the projection as a split query avoids the product, and at 15 ms it was the fastest variant.

Lazy loading is slow, but not because of SQL Server. The statements themselves cost little CPU. The time goes into 101 round trips. How much, the next experiment shows.

When the network is one millisecond further away

In practice, there is often more than a switch between application and database: another data center, a cloud region, a firewall. In the lab, every packet was artificially delayed by one millisecond (round trip 1.7 instead of 0.7 ms):

Lazy loading pays the millisecond 101 times. The split variants pay it three times, which eats up the advantage on small pages: in the small profile with the delay, the simple projection in one query was faster than the same projection as a split query. With only one list (orders only, small profile), Include in a single query was unproblematic anyway at 4.1 ms. The row explosion only arises from the second list on.

Split query and consistency

A split query reads customers, orders, and contacts in three separate commands. If someone changes the data in between, the parts no longer fit together. Microsoft's documentation points this out. What this feels like, the experiment shows: while the page was read in a loop, a second process continuously changed data, each time in a clean transaction.

• One customer moves onto the page, another leaves it (for example through a name change that shifts the sort order): With the writer running continuously, the split variants returned wrong results in almost 100% of reads; with one change every 50 ms, in 13 to 20%. Wrong did not mean “one order is missing” here: often 49 of 50 customers had a wrong number of orders and contacts, in total 3 instead of about 150 orders. No exception, no warning. The variants with a single query were correct in all cases here.
• An order and a contact switch between two customers on the page: Here even the single queries showed wrong totals. A single query under the default READ COMMITTED is not a snapshot: rows can change position while being read.
• In a snapshot transaction, all variants were correct in all 32 cases and over 37,000 reads, on SQL Server 2022 as well as 2025.

// Prerequisite in the database: ALTER DATABASE … SET ALLOW_SNAPSHOT_ISOLATION ON;
await using var tx = await db.Database.BeginTransactionAsync(IsolationLevel.Snapshot);
var page = await projection.AsSplitQuery().ToListAsync();
await tx.CommitAsync();

Snapshot isolation is not free: SQL Server keeps row versions for it, in TempDB depending on the configuration. What that means and what a long-open transaction holds on to is described in Version store and transaction log. For a read query of a few milliseconds, the overhead is small.

How strongly this affects your application depends on how often the data being read is changed concurrently. The rates in the lab come from a deliberately aggressive write pattern and are not a measure for production systems. That it can happen, and silently at that, is the finding.

Aside: AsNoTracking

All variants except lazy loading ran with AsNoTracking(). The generated SQL was character-for-character identical with and without tracking, verified with ToQueryString() for Include and projection. Even so, AsNoTracking() was not faster in this case: with the row explosion of variant 1, it was 7 to 8% slower than tracking at the median, in all three repetitions on both versions. A plausible but untested explanation: without identity resolution, EF creates new objects for each of the 16,000 rows, whereas with tracking it recognizes the repeats. This is not an argument against AsNoTracking(), but for measuring assumptions.

When to use what

A decision aid from the measurements, not a rule for every application:

Two practical notes. First: the MultipleCollectionIncludeWarning warning in the log is a good search term for finding candidates. Second: split query can be switched on globally (UseQuerySplittingBehavior). Because of the consistency question, we recommend choosing it deliberately per query.

More EF topics: Overview · One Contains(), Four EF Core Versions

Sources

• Microsoft: Single vs. Split Queries
• Microsoft: MultipleCollectionIncludeWarning warning
• Microsoft: Lazy Loading
• Microsoft: Tracking vs. No-Tracking Queries
• Microsoft: Transaction locking and row versioning guide

More EF topics