EF Core: Single Query vs. Split Queries for Related Data

Loading Multiple Related Collections in EF Core

When a query loads more than one collection navigation with Include(), EF Core may combine them into a single SQL statement with multiple joins. The result set can contain a row for every combination of related rows—a cartesian product. For a parent with ten posts and ten tags, one query returns one hundred rows instead of twenty. That wastes memory, slows materialization, and breaks client-side pagination because Skip() and Take() apply to joined rows, not distinct parents.

Useful takeaway: if you are loading two or more collections in EF Core 5.0 or later, prefer AsSplitQuery() unless you have measured that a single joined query is faster for your specific data shape.

Decision and Constraints

Decision: choose how EF Core loads multiple related collections.

• Supported options: default single query with multiple Include() calls, or AsSplitQuery() to issue one query per collection navigation.
• EF Core version: 5.0 or later for AsSplitQuery().
• Scope: read-only eager loading inside a single DbContext instance. Eager loading means related data is loaded alongside the main entity in the same query. This operation does not modify data, so there is no transaction rollback to consider.

Option Comparison

Option	Behavior	Round trips	Row duplication	Best for
Default Include() (single query)	Joins all collections in one SELECT	One	Possible cartesian product across collections	One collection, or small related sets where one round trip matters most
AsSplitQuery()	Sends separate SELECT per collection navigation	One per collection plus parent	None	Two or more collections, pagination with Skip/Take, large related sets

Trade-offs

Single joined query

• Pros: one network round trip; simpler execution plan for simple graphs.
• Cons: joined rows multiply; EF Core materializes duplicates and deduplicates in memory; Skip/Take paginate rows, not entities; can consume excessive memory with wide graphs.

Split query

• Pros: no row duplication; correct entity-level pagination; lower memory for materialization.
• Cons: multiple round trips; queries run independently, so data can change between them unless wrapped in an explicit transaction; slightly more complex SQL log to review.

Concrete Implementation

Run this inside a service method or controller that already has an injected AppDbContext. No special database permissions beyond SELECT are required.

using Microsoft.EntityFrameworkCore;

// Context and entity placeholders; replace with your own types.
// Assumes EF Core 5.0+ and an injected AppDbContext named context.
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()
    .OrderBy(b => b.Name)
    .Skip(0)
    .Take(20)
    .ToListAsync();

Without AsSplitQuery(), EF Core generates one SELECT joining Blogs, Posts, and Tags. With it, EF Core generates separate SELECT statements: one for blogs, one for posts, and one for tags, then fixes up navigation properties in memory.

Verification

1. Configure EF Core logging for the Microsoft.EntityFrameworkCore.Database.Command category at Information level in your application startup.
2. Run the query above and inspect the log output.
3. Expected check: you should see separate SELECT statements for each collection rather than one multi-join statement.
4. Confirm the returned blog count equals the number of distinct blogs, not the product of posts and tags.

Risk

Because split queries run separately, a post or tag added between the parent query and the collection queries will not appear. If you need a point-in-time consistent read, wrap the call in an explicit transaction or use a snapshot isolation level.

Limitation

AsSplitQuery() splits collection navigations; reference navigations may still be joined into the parent query. Use AsSingleQuery() on a specific include to override split behavior for that navigation.

Practical Check

Before and after enabling AsSplitQuery(), compare query duration and memory allocation under realistic load. If your graph has only one collection, the default single query usually avoids an extra round trip with no downside.