🚀 Optimize EF Core Queries with AsSplitQuery()

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 with multiple related entities.

✅ 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

Define the DbContext to include 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.UseSqlServr("set connection string");
    }
}

🧪 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?

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 and increased memory usage.

🚀 Step 4: Optimize with AsSplitQuery()

Now, let's improve performance using AsSplitQuery():

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

What Happens Now?

• EF Core executes multiple separate SQL queries instead of a single large join.
• Less memory is used.
• Duplicate data is avoided.
• Performance improves for large datasets.

🔍 Step 5: Understanding the SQL Queries
Without AsSplitQuery() (default behavior)

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 can lead to repeated rows for the same order due to the join on OrderItems.

With AsSplitQuery()

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

This results in more efficient memory usage and cleaner result sets.

🌐 Configure AsSplitQuery() Globally

Instead of calling .AsSplitQuery() on every query, you can make it the default behavior for your entire app.

✅ Add this to OnModelCreating in your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.UseSplitQueryBehavior(SplitQueryBehavior.SplitQuery);
}

⚠️ Requires EF Core 6.0+

📌 Notes:

All queries with .Include() will now use split queries by default.

You can override this behavior per query using .AsSingleQuery() if needed.

📈 When to Use AsSplitQuery()

✅ Use it when:

• You include multiple navigation properties.
• You're querying large datasets.
• You want to avoid duplicated data and reduce memory load.

❌ Avoid it when:

• Your queries are small/simple.
• Network latency makes multiple queries less efficient.
• You require strict control over transaction boundaries.

🧠 Conclusion

Using AsSplitQuery() in EF Core is a simple yet powerful optimization. Whether you configure it per-query or globally, it can significantly reduce memory usage and improve performance when working with complex data models and large datasets.

📚 References

EF Core documentation: Loading Related Data — Split Queries

EF Core documentation: Performance considerations for split vs single queries

Source code: Ef core examples