Stop duplicated rows when EF Core loads multiple collections

The problem: one joined query can return the same order many times

Imagine an Order entity with two collection navigations: Items and Shipments. If you load both with Include, Entity Framework Core (EF Core) typically builds a single SQL statement that joins the root table to both child tables.

When one order has three items and two shipments, the join produces six rows for that order. EF Core still fixes up the navigation properties, but the result set contains duplicated parent data and can grow quickly as collections get larger. This is often called a Cartesian product or row multiplication.

What AsSplitQuery does

AsSplitQuery, available in EF Core 3.0 and later, tells EF Core to issue separate SQL statements instead of one joined statement. It sends one query for the root entities and one additional query for each collection navigation. EF Core then reassembles the object graph in memory.

Reference navigations, such as a single Customer property on an order, are usually still joined unless you configure the query otherwise. Split queries apply primarily to collections.

Worked example: orders with items and shipments

Start with a simple model. This code belongs in your application's data layer, not a migration or raw SQL script.

public class Order
{
    public int Id { get; set; }
    public string Number { get; set; }
    public List Items { get; set; } = new();
    public List Shipments { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string Sku { get; set; }
}

public class Shipment
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string Carrier { get; set; }
}

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions options) : base(options) { }
    public DbSet Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
    }
}

Enable logging with LogTo so you can inspect the generated SQL. Run this in a service or repository method where you need both collections:

using var context = new AppDbContext(options);

var orders = context.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .AsSplitQuery()
    .ToList();

Where to run: Any .NET host that can instantiate your DbContext, such as an API controller, background worker, or console application. Permissions: standard database SELECT rights; no schema changes are required. Placeholder: replace options with your configured DbContextOptions.

Without AsSplitQuery, expect one SQL statement with LEFT JOIN clauses to both child tables. With it, expect one SELECT for orders, one for order items, and one for shipments. Check your log output to confirm multiple statements appear.

Expected check: materialize the results and verify each order appears once in the root result set, with its collections populated separately. Do not rely on row count from a single joined result set, because that count will be inflated by duplication.

Trade-offs: extra round-trips and consistency

Split queries remove duplicated rows, but they add database round-trips. One query becomes N plus one queries, where N is the number of split collections. On high-latency connections, total elapsed time can increase even though less data travels per statement.

The separate queries are not automatically a transactionally consistent snapshot of the entire graph. If you need all collections to reflect the same point in time, wrap the call in an explicit transaction or choose an appropriate isolation level.

AsSplitQuery is not a replacement for AsNoTracking. They solve different problems and can be combined when you want split loading without change tracking.

Verify before enabling globally

EF Core lets you enable split queries globally on the context options, but it is safer to apply AsSplitQuery per query after measuring. If you enable it globally, use AsSingleQuery on individual queries where a single joined statement is preferable.

Use LogTo to compare SQL, compare execution timings, and confirm that change tracking still works for updates after loading a split-query graph. EF Core 5 and later maintain change tracking across split result sets, so loaded entities can be attached and updated normally.

Closing

When loading multiple collection navigations causes duplicated parent rows, AsSplitQuery is a targeted fix. Add logging, confirm the generated SQL splits into separate statements, measure round-trip cost, and apply it only where duplication is the actual bottleneck.