Boost Your EF Core Performance with AsSplitQuery

When working with Entity Framework Core, especially when eager-loading related data using Include, you might run into performance bottlenecks or even cartesian explosion problems. This is where the AsSplitQuery() method becomes extremely valuable.

In this article, we’ll break down what AsSplitQuery() is, when and why to use it, and how it impacts performance in real-world scenarios.

🔍 What is AsSplitQuery?

By default, when you use Include or ThenInclude to eager-load related entities, EF Core generates a single SQL query that joins all the required tables. This is known as a single query execution strategy.

var orders = context.Orders .Include(o => o.OrderItems) .ToList(); 

This is fine for simple relationships, but when you include multiple collections or large data sets, it can produce a large result set with many duplicate records due to SQL joins. This causes poor performance and high memory usage.

AsSplitQuery() changes this behavior.

var orders = context.Orders .Include(o => o.OrderItems) .AsSplitQuery() .ToList(); 

Now, EF Core generates multiple SQL queries: one for the root entity and one for each related collection. It then assembles the results in memory.

⚡ Benefits of AsSplitQuery

✅ Avoid Cartesian Explosion
✅ Improved Performance in Many Cases
✅ Better for Pagination

🛑 When Not to Use AsSplitQuery

Although useful, AsSplitQuery may not always be the best option:

• Increased DB Round Trips: More queries = more latency.
• Data Consistency: Changes between queries might lead to inconsistent results.
• Client-side Stitching Overhead: More processing is done by EF Core.

🧪 Example: With and Without AsSplitQuery

// Default behavior (Single query) var students = context.Students .Include(s => s.Courses) .Include(s => s.Grades) .ToList(); // With AsSplitQuery (Recommended for complex includes) var students = context.Students .Include(s => s.Courses) .Include(s => s.Grades) .AsSplitQuery() .ToList(); 

⚙️ Global Configuration (EF Core 5+)

You can apply the split query behavior globally:

optionsBuilder .UseSqlServer(connectionString) .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery); 

Or force the opposite if needed:

.UseQuerySplittingBehavior(QuerySplittingBehavior.SingleQuery); 

🧠 Final Thoughts

EF Core 5+ gives you more control over query strategies. AsSplitQuery() is a must-have tool when optimizing complex includes and large datasets. But like any optimization, test and measure before applying it broadly.

📎 TL;DR

I’m Morteza Jangjoo and “Explaining things I wish someone had explained to me”

📄 This article was originally published on my Hashnode blog:

👉 AsSplitQuery in EF Core – Original Article