In EF Core, AsSplitQuery() tells Entity Framework Core to load collection navigation data with separate SQL queries instead of combining all included collections into one large joined query. It can prevent duplicated rows and cartesian explosion when loading multiple collections, but it adds database round trips and can affect consistency, so it is a trade-off rather than an automatic performance fix.
What AsSplitQuery Means
EF Core normally uses a single SQL query for a LINQ query that includes related data. With collection navigations, that query can join the parent table to one or more child tables. Calling AsSplitQuery() changes how EF Core obtains those related collections: it runs a query for the root entities and additional queries for included collections, then assembles the results into the object graph.
The feature is available starting with EF Core 5.0. It applies to relational database providers; the exact SQL and execution characteristics depend on the provider. The method changes query execution strategy, not the shape of the LINQ result you asked for. For example, a blog with its posts and contributors is still materialized as a blog object with both collections. The difference is how the database rows are retrieved.
A basic example is:
var blogs = await context.Blogs
.Include(blog => blog.Posts)
.Include(blog => blog.Contributors)
.AsSplitQuery()
.ToListAsync();
Without the call, EF Core generally translates this into one query unless the context has been configured to use split queries globally. You can also use AsSingleQuery() to request a single query for a particular LINQ query when the default or global setting is split.
Why a Single Query Can Produce Too Many Rows
The main reason to consider split queries is the cartesian explosion that can arise from joining multiple sibling collections. Suppose one blog has 10 posts and 8 contributors. A query joining both collections can return up to 80 combinations for that blog: each post is paired with each contributor in the result rows. EF Core recognizes repeated blog data and reconstructs the related objects, but the database still has to produce and send those rows.
That multiplication can grow quickly when collections are larger or more numerous. It may increase database work, network traffic, and the amount of data EF Core must process. Even with only one collection, a single query can repeat the parent columns for every child row. If the parent contains large text or binary columns, that repeated payload can matter.
Split queries avoid joining sibling collections together in the same result set. Instead, EF Core retrieves the root records and loads each included collection separately. In the blog example, the post rows no longer need to be paired with every contributor row. This often reduces duplicated data, though it does not mean the query is free: the database still reads and returns all the requested entities.
How EF Core Executes a Split Query
Conceptually, a split query has a root query followed by separate queries for included collection navigations. EF Core uses the returned keys to associate each collection row with its parent while materializing the results. Nested collection includes may require further queries. The precise count and SQL depend on the LINQ expression, provider, and EF Core version, so do not assume a fixed number of statements for every query.
For instance, including both Posts and Contributors normally means EF Core retrieves the blogs and then retrieves data for those collections in separate statements. Related reference navigations can still be joined into the relevant statements. In some query shapes, reference-navigation joins are repeated in each collection query; if there are many such references, inspect the generated SQL and measure rather than assuming splitting will always be cheaper.
To inspect what a query is expected to run, call ToQueryString() on an EF Core query before executing it, or enable logging for database commands. The generated SQL is useful for seeing joins and selected columns, but it is not a substitute for observing actual execution time, row counts, and database query plans. Different providers may generate different SQL.
When AsSplitQuery Is a Good Choice
Splitting is worth evaluating when a query loads more than one collection, especially when those collections are independent siblings and can multiply each other’s rows. It can also help when a joined query transfers a large amount of repeated parent data. The relevant question is not simply whether a query has an Include; it is whether the single-query form is doing avoidable work for the data and workload involved.
- Consider it for pages or API operations that need several sizable collections on each parent.
- Compare it when logs or query plans show a large joined result with repeated data.
- Test it with realistic collection sizes, since a small development dataset can hide row multiplication.
- Keep a single query when the joined result is modest and one database request is more valuable than reducing returned rows.
AsSplitQuery is not a replacement for limiting data. If a screen needs only a few fields or a small number of children, consider projecting into a DTO with Select, filtering the included collection where appropriate, or applying pagination. A projection can avoid fetching entire entities and columns that the caller never uses. Choose the query shape that matches what the application actually needs.
Performance Costs and Trade-Offs
A split query usually requires more than one database command. That can mean extra network round trips and command overhead, which may outweigh the savings from sending fewer rows—particularly when the database is remote or each query is very fast. Conversely, avoiding a huge joined result can be a substantial win. There is no universal rule that split queries are faster; benchmark the competing forms in the real application environment.
Split-query execution can also require EF Core to buffer results from earlier commands before later commands run, depending on the provider and connection capabilities. SQL Server with Multiple Active Result Sets (MARS) enabled is an important exception to some of that buffering behavior. Buffering can increase application memory use for large result sets. It is therefore sensible to consider both database payload and client-side memory, rather than looking only at the number of SQL statements.
Tracking behavior is a separate choice. A split query can still be tracked by the context under the usual tracking rules, and AsNoTracking() can still be applied when the results are read-only. Splitting does not inherently disable tracking, make the query asynchronous, or reduce the number of entities returned. Those concerns should be evaluated independently.
Consistency and Concurrent Updates
A single SQL statement observes its data according to the database’s statement-level behavior and isolation configuration. A split query issues multiple statements, so another transaction could modify related data between them. The resulting object graph might then combine a root result from one moment with collection data from a later moment. Whether this can happen, and what it means for the application, depends on the database and isolation level.
If the operation requires a consistent view across all statements, consider running it in a transaction with an appropriate isolation level, such as snapshot or serializable where supported and suitable. Stronger isolation can have its own costs, including locking or version-store pressure, so it should be selected deliberately. For ordinary screens where a small timing difference is acceptable, a transaction may not be necessary. The key point is that splitting changes the number of statements and therefore can change consistency considerations.
Pagination, Ordering, and Filtering
When combining split queries with Skip and Take, make the ordering fully unique, especially in EF Core versions before 10. An ordering such as OrderBy(blog => blog.DateCreated) is not unique if multiple blogs share a timestamp. Add a unique tie-breaker, such as the primary key: OrderBy(blog => blog.DateCreated).ThenBy(blog => blog.Id). Otherwise, separate statements could select different rows among tied values. EF Core 10 addresses the earlier split-query pagination concern, but a deterministic order remains good practice for paginated results generally.
Filtered includes can be used to constrain a collection, for example to load only published posts. That can reduce work, but be mindful of tracking behavior: in a tracking query, navigation fix-up can populate a navigation with previously tracked entities that do not satisfy the current filter. For a clean filtered result, a fresh context or a no-tracking query may be more appropriate, depending on the use case.
Configuring Split Queries Globally
Use AsSplitQuery() for a local decision on one query. If split behavior is appropriate as the default for an application’s relational queries, it can be configured in the provider options, for example with SQL Server:
optionsBuilder.UseSqlServer(
connectionString,
sqlOptions => sqlOptions.UseQuerySplittingBehavior(
QuerySplittingBehavior.SplitQuery));
Once a global default is set, use AsSingleQuery() on a specific query that should use a single statement. If no splitting behavior is configured and a query includes multiple collections, EF Core can issue a warning because it cannot know whether a single or split query is preferable. Treat that warning as a prompt to make an explicit, measured choice—not as proof that the query is necessarily slow.
How to Choose and Verify
A practical decision process is to start with the actual query requirements and inspect the SQL and data volume. Compare the single-query and split-query forms with representative data, and include the effects of network latency, database load, and application memory. Look at both response time and the number of rows transferred; a faster result on a tiny local database may not predict production behavior.
- Identify which collection navigations the caller truly needs, and remove unnecessary includes.
- Check whether multiple sibling collections create a large joined result or repeated wide columns.
- Compare
AsSplitQuery()andAsSingleQuery()while measuring representative workloads. - Check pagination ordering, consistency requirements, and tracking behavior.
- Keep the choice local unless measurements support a global default across the application.
In short, use split queries to address a demonstrated cost of joining collections, not merely because a query contains several includes. A projection, filtered collection, or narrower result may be better still. Generated SQL, command logs, and realistic benchmarks help distinguish those cases.
Frequently Asked Questions
Is AsSplitQuery enabled by default in EF Core?
Not generally. EF Core uses single-query behavior by default unless split-query behavior has been configured globally for the provider. You can choose split behavior for an individual LINQ query with AsSplitQuery().
Does AsSplitQuery always make a query faster?
No. It can prevent row multiplication and reduce repeated data, but it also runs multiple statements and can add round-trip and buffering costs. Compare it with a single query using realistic data and the application’s actual database connection.
How many SQL queries does AsSplitQuery run?
There is no fixed count for every query. EF Core generally runs a root query plus separate queries for included collections, with details affected by the query shape, provider, and EF Core version. Inspect command logging or generated SQL for a specific query.
Can I use AsSplitQuery with AsNoTracking?
Yes. They control different things: AsSplitQuery() affects how related data is fetched, while AsNoTracking() affects whether returned entities are tracked by the context.
Should I use AsSplitQuery for every Include?
No. A single collection or a small result may work well as one query. Consider splitting when joined collections cause excessive rows or repeated data, and account for extra commands and consistency needs before choosing it.
Related
- JavaScript split() to Array: How It Works and Examples
- Balance of Payments: Meaning, Accounts, and Examples
- How to Split an Array in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete a Particular Page in Microsoft Word SafelyTo delete a particular page in Word, go to that page, select its contents with the Go To…
- How to Change Outlook View Back to Normal in Any VersionTo return classic Outlook for Windows to its usual layout, select the mail folder that looks wrong, open…
- How to Combine Cells in Excel Into One Cell EasilyTo combine the contents of Excel cells into a new cell, join them with the ampersand operator (&)…
- Excel Shortcut Keys for Fill Color: Windows and MacIn Excel for Microsoft 365 on Windows, press Alt, then H, then H to open the Fill Color…
- Google Sheets Jira Add-On: Setup, Features, and LimitsYes—Atlassian’s Jira Cloud for Sheets add-on connects Jira Cloud with Google Sheets so you can bring Jira issues…
- Change Text to Lowercase in Excel: Steps and Tips That WorkTo change text to lowercase in Excel, enter =LOWER(A2) in a blank helper cell when the text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.