When to Use AsSplitQuery() in EF.core

Question

When working with Entity Framework Core (EF Core), queries that involve multiple Include() statements can result in a single large query with multiple joins. This can cause performance issues due to data duplication and increased memory usage.

What is AsSplitQuery()?

AsSplitQuery() tells EF Core to fetch related data using separate SQL queries instead of one large join query. This helps optimize performance for complex queries.

Step 1: Setup Your Model

Let’s create a simple e-commerce scenario with Order, Customer, and OrderItem entities.

public class Order
{
    public int Id { get; set; }
    public string OrderNumber { get; set; }
    public Customer Customer { get; set; }
    public List<OrderItem> OrderItems { get; set; } = new();
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    public string ProductName { get; set; }
    public int Quantity { get; set; }
}

Step 2: Configure the Database Context

We define our DbContext to include the necessary DbSet properties.

public class AppDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=app.db");
    }
}

Step 3: Query Without AsSplitQuery()

Let's say we want to fetch orders with their customers and order items.

**var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .ToListAsync();**

What Happens Here?
EF Core generates a single SQL query with multiple JOIN operations.
If there are many orders and items, this can cause performance issues due to data duplication.

Step 4: Optimize with AsSplitQuery()

Now, let's improve performance by using AsSplitQuery().

var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .AsSplitQuery() // Fetches data using multiple queries instead of joins
    .ToListAsync();

What Happens Now?

EF Core executes multiple separate SQL queries instead of a single large join.
Less memory is used since duplicate data is avoided.
Performance is improved when dealing with large datasets.

Step 5: Understanding the SQL Queries

Without AsSplitQuery()
EF Core runs one big SQL query:

SELECT o.*, c.*, oi.*
FROM Orders o
LEFT JOIN Customers c ON o.CustomerId = c.Id
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId;

This may lead to repetitive customer data for each order item.

With AsSplitQuery()
EF Core runs separate queries:

SELECT * FROM Orders;
SELECT * FROM Customers WHERE Id IN (...);
SELECT * FROM OrderItems WHERE OrderId IN (...);

This reduces duplication and improves performance.

When to Use AsSplitQuery()

✅ Use AsSplitQuery() when:

You include multiple navigation properties (Include()).
You experience performance issues with large datasets.
You want to avoid data duplication.

❌ Avoid AsSplitQuery() when:

Your queries are already small and fast.
Your database round trips increase due to multiple queries.

Conclusion

Using AsSplitQuery() in EF Core can boost performance and reduce memory usage when working with complex queries. It’s a simple yet powerful optimization that can make your applications more efficient.