Understanding AsSplitQuery in EF Core

---

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

1. Avoid Cartesian Explosion
   When multiple collections are included, a single query can lead to huge result sets with repeated data. AsSplitQuery() prevents this.

2. Improved Performance in Many Cases
   Especially when loading deep or wide object graphs, separate queries are often more efficient and memory-friendly.

3. Better for Pagination
   If you’re paginating the root entity (Skip / Take), split queries will fetch only the required related data for the selected page.

---

🛑 When Not to Use AsSplitQuery

While AsSplitQuery is useful, it's not always the better choice. Consider these scenarios:

• Increased Database Round Trips:
  More queries mean more communication with the database.

• Consistency Risk:
  If data changes between queries (in highly concurrent environments), results might not be perfectly consistent.

• Slightly Higher Complexity:
  Because the related entities are stitched together client-side, debugging can be a bit trickier.

---

🧪 Example: With and Without AsSplitQuery

// Default behavior (Single query)
var students = context.Students
    .Include(s => s.Courses)
    .Include(s => s.Grades)
    .ToList();

// Better with AsSplitQuery
var students = context.Students
    .Include(s => s.Courses)
    .Include(s => s.Grades)
    .AsSplitQuery()
    .ToList();

---

⚙️ Global Configuration (EF Core 5+)

You can make all queries split by default using:

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

You can also use QuerySplittingBehavior.SingleQuery if you want to enforce the opposite.

---

🧠 Final Thoughts

EF Core 5 and later versions give you more control over how queries are executed. AsSplitQuery() is a powerful tool in your performance tuning toolkit, especially when dealing with complex object graphs or collection navigation properties.

However, like every optimization, you should profile your application and measure performance before and after applying it.

---

📎 TL;DR

---

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