Include and ThenInclude

Eager-load related entities with Include and ThenInclude, filter included collections, and avoid cartesian explosion with split queries.

Signature

public static IIncludableQueryable<TEntity, TProperty> Include<TEntity, TProperty>(this IQueryable<TEntity> source, Expression<Func<TEntity, TProperty>> navigationPropertyPath) where TEntity : class

Added in .NET EF Core 1.0

Parameters

Parameter	Type	Description
`$source`	`IQueryable<TEntity>`	The query whose root entities get the related data.
`$navigationPropertyPath`	`Expression<Func<TEntity, TProperty>>`	A navigation property, optionally followed by Where, OrderBy, OrderByDescending, ThenBy, Skip and Take on a collection (filtered include). A string overload, Include("Lines.Product"), takes a dot-separated path.

Return value

IIncludableQueryable<TEntity, TProperty>The query with the navigation marked for eager loading. ThenInclude, called on that result, extends the path one level from the last included navigation.

Errors

InvalidOperationException when the lambda is not a navigation (for example a computed property or a method call), or when the same collection is included twice with different filters.

Changelog

PHP EF Core 3.0Queries with collection includes are translated to a single SQL statement instead of one query per collection.
PHP EF Core 5.0Filtered include; AsSplitQuery and AsSingleQuery, and UseQuerySplittingBehavior on the provider options.
PHP EF Core 6.0Navigation(...).AutoInclude() in the model, and IgnoreAutoIncludes() on a query.

Include tells EF Core to load a navigation together with the root entities in the same query. References (Order.Customer) become JOINs; an optional reference or a collection (Order.Lines) becomes a LEFT JOIN. When a collection is included, EF Core orders by the keys of the root and the joined tables so it can stitch rows back into object graphs; reference-only includes add no ORDER BY. ThenInclude continues the path from the last navigation: Include(o => o.Lines).ThenInclude(l => l.Product).

Paths and branches

A chain of references can be written in one lambda: Include(o => o.Customer.Address).
To load two branches under the same collection, repeat the Include: Include(o => o.Lines).ThenInclude(l => l.Product).Include(o => o.Lines).ThenInclude(l => l.Discount). Lines are loaded once.
The string form, Include("Lines.Product"), is checked only at run time; keep it for paths built dynamically.

Filtered include

Inside the lambda a collection can take Where, OrderBy/OrderByDescending/ThenBy, Skip and Take: Include(c => c.Orders.Where(o => o.Status == "Open").OrderByDescending(o => o.PlacedAt).Take(5)). Two traps. In a tracking query, fix-up still adds any related entities the context already tracks, so the collection can hold more than the filter allowed. And a navigation included twice must use the same filter.

When Include is ignored

Include shapes entity results. If the query ends in a Select that returns a DTO or anonymous type, Include is ignored: the projection itself decides which related data is read. For API responses, a projection is usually the better tool because it reads only the columns the response needs. Use Include when you will modify the graph, or when you need entities for domain logic.

Single query versus split query

Mode	SQL	Risk
Single (default)	One statement with JOINs	Two sibling collections multiply rows: 50 lines times 20 payments returns 1,000 rows per order, each repeating the order columns
`AsSplitQuery()`	One statement per collection	More round trips; without a transaction, rows can change between the statements

EF Core logs MultipleCollectionIncludeWarning when a single query loads more than one collection and no splitting behaviour was chosen. Treat it as a review item: add AsSplitQuery(), set it as the default with UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery) in the provider options, or project instead. Split queries with Skip/Take also need a fully unique OrderBy, otherwise the separate statements can page differently.

Production failure mode

A list endpoint that was fine with one include grows a second collection include, and a page of 20 orders silently becomes a 20,000-row result set, transferring megabytes of duplicated columns. The query is correct, just slow, so it shows up as latency and database CPU rather than errors.

Example

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<ShopDb>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("Shop")));
var app = builder.Build();

// Reprice an open order: we modify the graph, so we load entities with Include.
app.MapPost("/orders/{id:int}/reprice", async (int id, ShopDb db, CancellationToken ct) =>
{
    var order = await db.Orders
        .Include(o => o.Lines).ThenInclude(l => l.Product)
        .Include(o => o.Payments.Where(p => p.Succeeded))
        .AsSplitQuery()                         // two collections: avoid lines x payments rows
        .SingleOrDefaultAsync(o => o.Id == id, ct);

    if (order is null) return Results.NotFound();
    if (order.Payments.Count > 0) return Results.Conflict("Order already has a successful payment.");

    foreach (var line in order.Lines)
        line.UnitPrice = line.Product.Price;
    order.Total = order.Lines.Sum(l => l.Quantity * l.UnitPrice);

    try
    {
        // The UPDATE of Orders checks xmin. Recording a payment also updates the order row,
        // so a payment that lands between the read and this save fails it instead of being repriced.
        await db.SaveChangesAsync(ct);
    }
    catch (DbUpdateConcurrencyException)
    {
        return Results.Conflict("The order changed while repricing. Reload and retry.");
    }
    return Results.Ok(new { order.Id, order.Total });
});

app.Run();

public class Product
{
    public int Id { get; set; }
    public string Sku { get; set; } = "";
    public decimal Price { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public string Status { get; set; } = "Open";
    public decimal Total { get; set; }
    public uint Version { get; set; }   // mapped to PostgreSQL xmin
    public List<OrderLine> Lines { get; } = [];
    public List<Payment> Payments { get; } = [];
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public Product Product { get; set; } = null!;
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

public class Payment
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public decimal Amount { get; set; }
    public bool Succeeded { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<Order>().Property(o => o.Version).IsRowVersion();
}

When to use it

Loading an order with its lines and each line's product so a reprice endpoint can modify the graph and save once
Showing each enterprise customer with only their five most recent open orders using a filtered include
Adding AsSplitQuery after a MultipleCollectionIncludeWarning reveals a 20,000-row result for a page of 20 orders

Frequently asked questions

Include or a Select projection?

Projection for read endpoints: it reads only the columns the response needs and is never tracked. Include when you need the entities themselves, usually because you are about to change them and call SaveChangesAsync.

Should split queries be the default?

Make them the default when most queries load several collections, with UseQuerySplittingBehavior on the provider options, and use AsSingleQuery where consistency within one statement matters. Split queries read each collection in a separate statement, so they can see different data unless run in a serializable or snapshot transaction.

Why does my filtered include return more rows than the filter allows?

In a tracking query EF Core also fixes up navigations from entities the context already tracks. Use AsNoTracking for the query, or a fresh context, when the collection must contain exactly the filtered rows.

More examples

Filtered include: the five latest open orders per customer

The filter and <code>Take</code> run per customer in SQL (a window function or lateral join, depending on the provider). <code>AsNoTracking</code> guarantees each <code>Orders</code> list holds exactly what the filter selected, not extra orders the context happened to track.

using Microsoft.EntityFrameworkCore;

public sealed class AccountManagerView(ShopDb db)
{
    public Task<List<Customer>> TopAccounts(CancellationToken ct) =>
        db.Customers
            .AsNoTracking()   // fix-up from tracked entities cannot add extra orders
            .Where(c => c.Tier == "Enterprise")
            .Include(c => c.Orders
                .Where(o => o.Status == "Open")
                .OrderByDescending(o => o.PlacedAt)
                .Take(5))
            .OrderBy(c => c.Name)
            .ToListAsync(ct);
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Tier { get; set; } = "";
    public List<Order> Orders { get; } = [];
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Status { get; set; } = "";
    public DateTimeOffset PlacedAt { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
}

AutoInclude in the model, and opting out

AutoInclude suits small references that every use of the entity needs, such as a currency or a unit of measure. Like <code>Include</code>, it only shapes entity results: <code>Any</code>, <code>Count</code> and projections never apply auto-includes, so they need no <code>IgnoreAutoIncludes</code>. Do not auto-include collections: every query of the entity then pays for them, and the cost is invisible at the call site.

using Microsoft.EntityFrameworkCore;

public class Invoice
{
    public int Id { get; set; }
    public string Number { get; set; } = "";
    public DateTimeOffset CreatedOn { get; set; }
    public Currency Currency { get; set; } = null!;
    public int CurrencyId { get; set; }
}

public class Currency
{
    public int Id { get; set; }
    public string Code { get; set; } = "";
    public int MinorUnits { get; set; }
}

public class BillingDb(DbContextOptions<BillingDb> options) : DbContext(options)
{
    public DbSet<Invoice> Invoices => Set<Invoice>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        // Every Invoice query now joins Currency, as if Include had been written.
        modelBuilder.Entity<Invoice>().Navigation(i => i.Currency).AutoInclude();
}

public sealed class InvoiceExport(BillingDb db)
{
    // A bulk export of Invoice entities that never reads Currency: skip the join.
    public Task<List<Invoice>> Since(DateTimeOffset from, CancellationToken ct) =>
        db.Invoices.AsNoTracking()
            .IgnoreAutoIncludes()
            .Where(i => i.CreatedOn >= from)
            .ToListAsync(ct);

    // No opt-out needed: AnyAsync returns a bool, not entities, so auto-includes never apply.
    public Task<bool> Exists(string number, CancellationToken ct) =>
        db.Invoices.AnyAsync(i => i.Number == number, ct);
}

Pitfall: Include with a projection does nothing

Navigations used inside a projection are translated directly into joins or subqueries, so Include adds nothing. Removing the dead <code>Include</code> stops readers assuming the full lines are loaded, and stops someone later adding a filter to it expecting it to change the result.

using Microsoft.EntityFrameworkCore;

public sealed class OrderSummaries(ShopDb db)
{
    // Include is ignored here; the Select alone decides which columns are read.
    public Task<List<OrderSummary>> Misleading(CancellationToken ct) =>
        db.Orders
            .Include(o => o.Lines)
            .Select(o => new OrderSummary(o.Id, o.Lines.Count, o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
            .ToListAsync(ct);

    // Same SQL, honest code: COUNT and SUM run in the database, no line rows are transferred.
    public Task<List<OrderSummary>> Clear(CancellationToken ct) =>
        db.Orders
            .Select(o => new OrderSummary(o.Id, o.Lines.Count, o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
            .ToListAsync(ct);
}

public record OrderSummary(int Id, int LineCount, decimal Total);

public class Order
{
    public int Id { get; set; }
    public List<OrderLine> Lines { get; } = [];
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
}

Discussion

Be the first to comment on this lesson.

Parameters

Return value

Errors

Changelog

See also

Paths and branches

Filtered include

When Include is ignored

Single query versus split query

Production failure mode

Example

When to use it

Frequently asked questions

Include or a Select projection?

Should split queries be the default?

Why does my filtered include return more rows than the filter allows?

More examples

Filtered include: the five latest open orders per customer

AutoInclude in the model, and opting out

Pitfall: Include with a projection does nothing

Related

Discussion