EF Core Split Query — Complete Guide

In this tutorial, you'll learn about EF Core Split Query. We cover key concepts, practical examples, and best practices.

You load an order with 10 line items and 5 shipments using Include. The generated SQL uses a single query with JOIN — producing 50 rows (10 × 5). Each row repeats the order data. This cartesian explosion wastes bandwidth and memory. Split queries issue one query per included collection.

Wrong

var order = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .FirstAsync(o => o.Id == 1);

Output: Single SQL query: SELECT * FROM Orders LEFT JOIN Items ... LEFT JOIN Shipments ... — 50 rows. Order data repeated 50 times.

Right

var order = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .AsSplitQuery()
    .FirstAsync(o => o.Id == 1);

Output: Three SQL queries:

1. SELECT * FROM Orders WHERE Id = 1
2. SELECT * FROM Items WHERE OrderId = 1
3. SELECT * FROM Shipments WHERE OrderId = 1

Total rows: 1 + 10 + 5 = 16 rows instead of 50. No repeated data.

Configure globally:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString, o =>
        o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}

Prevention

• Use AsSplitQuery() for queries with multiple Include or ThenInclude collections.
• Use split queries by default for complex queries with multiple collections.
• Use single query (default) for simple queries with one collection or reference navigation.
• Be aware that split queries require multiple round trips — latency matters.
• Wrap split queries in a transaction for consistency: AsSplitQuery() already does this internally.
• Use AsSingleQuery() to revert to default for specific queries.
• Profile both approaches — single query may be faster for small result sets.

Common Mistakes with core split query

1. Misunderstanding that String is [Char] with poor performance for large text operations
2. Using foldl instead of foldl' causing stack overflow on large lists
3. Forgetting deriving (Show, Eq) on custom data types needed for debugging

These mistakes appear frequently in real-world EF code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

Split queries optimize data loading in DodaTech's reporting engine. For more EF Core, visit DodaTech.