AsSplitQuery() in EF

Simple Query with JOINs

When you write a query like this in EF Core:

var result = await _context.TestTables
                .Include(t => t.Category)
                .Include(b => b.TestTableItems)
                .Where(b => b.CreatedAt > startDate && b.CategoryId == 3)
                .OrderBy(b => b.CreatedAt)
                .Take(100)
                .ToListAsync(cancellationToken);

EF Core by default generates one large SQL query using multiple JOINs. That’s great for minimizing database round-trips, but there’s a catch...

SELECT [t1].[TestTableId], [t1].[CategoryId], [t1].[CreatedAt], [t1].[Name], [t0].[Id], [t0].[CreatedAt], [t0].[Name], [t2].[ItemId], [t2].[CreatedAt], [t2].[TestTableId], [t2].[Title]
      FROM (
          SELECT TOP(100) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt], [t].[Name]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]
      INNER JOIN [TestTableCategories] AS [t0] ON [t1].[CategoryId] = [t0].[Id]
      LEFT JOIN [TestTableItems] AS [t2] ON [t1].[TestTableId] = [t2].[TestTableId]
      ORDER BY [t1].[CreatedAt], [t1].[TestTableId], [t0].[Id]

SQL server returns a big dataset with all required properties. In my example, code contains limit 100 entities - .Take(100). But SQL Server returns 600+ records due to a phenomenon known as 'Cartesian Explosion'. It's because every record of "TestTables" contains multiple items in "TestTableItems".

In most scenarios, it won't be an issue. But when entities grows and more .Include() sections are added - it can cause some issues:

• Memory bloat due to row duplication
• Slower query materialization
• Increased deserialization overhead

AsSplitQuery() helps

This instructs EF Core to split the query into separate SQL commands — one for the main entity, and one per collection navigation:

var result = await _context.TestTables
                .Include(t => t.Category)
                .Include(b => b.TestTableItems)
                .Where(b => b.CreatedAt > startDate
                    && b.CategoryId == 3)
                .AsSplitQuery()
                .OrderBy(b => b.CreatedAt)
                .Take(100)
                .ToListAsync(cancellationToken);
And this EF query is converted into two separate SQL queries:

-- The First Query:
SELECT [t1].[TestTableId], [t1].[CategoryId], [t1].[CreatedAt], [t1].[Name], [t0].[Id], [t0].[CreatedAt], [t0].[Name]
      FROM (
          SELECT TOP(@__p_0) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt], [t].[Name]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]
      INNER JOIN [TestTableCategories] AS [t0] ON [t1].[CategoryId] = [t0].[Id]
      ORDER BY [t1].[CreatedAt], [t1].[TestTableId], [t0].[Id]

-- The Second:
      SELECT [t2].[ItemId], [t2].[CreatedAt], [t2].[TestTableId], [t2].[Title], [t1].[TestTableId], [t0].[Id]
      FROM (
          SELECT TOP(@__p_0) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]
      INNER JOIN [TestTableCategories] AS [t0] ON [t1].[CategoryId] = [t0].[Id]
      INNER JOIN [TestTableItems] AS [t2] ON [t1].[TestTableId] = [t2].[TestTableId]
      ORDER BY [t1].[CreatedAt], [t1].[TestTableId], [t0].[Id]

As you can see, the second query avoids duplicating information from [TestTables], potentially reducing memory usage when there are a lot of properties in the TestTables. You can find more in Microsoft arcticle "Single vs. Split Queries"

Caution

As you saw above, EF Core will repeat the base query for each collection navigation. That means if your base query is expensive, using split queries might actually make things slower, not faster.

For example, this part of the query is reused:

FROM (
          SELECT TOP(@__p_0) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]

If your filter or sort involves heavy computation, indexing, or joins — that cost gets multiplied for each split query. So always benchmark both approaches (.AsSingleQuery() vs .AsSplitQuery()) with real production-like data before making a decision.

When to Use vs. When to Avoid

Use AsSplitQuery() When:

• Queries involve multiple Include() statements
• You experience Cartesian Explosion due to related collections (e.g., one-to-many or many-to-many)
• The primary entity has many properties and duplicating them across joined rows leads to high memory usage
• You want to reduce materialization time and improve overall performance for large result sets

Avoid AsSplitQuery() When:

• Queries are simple with minimal joins
• Main Entity has only few properties (columns)
• Included tables has small amount of records, so JOINs are efficient