Loading Related Data in EF Core: Include, Explicit and Lazy Loading, Split Queries

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: How Many Books Does Ursula K. Le Guin Have?

The database holds three books by Ursula K. Le Guin. What does this code print?

var authors = db.Authors.OrderBy(a => a.Id).ToList();
foreach (var author in authors)
{
    Console.WriteLine($"{author.Name}: {author.Books.Count} books");
}

Ursula K. Le Guin: 0 books
Isaac Asimov: 0 books
Stanislaw Lem: 0 books

No error, no warning, just a wrong result. EF Core loads only what you ask for; this query asked for the Authors table, and the SQL that ran does not even mention the Books table. Since the Books property is initialized with new(), it is not null but an empty list. At a bookstore checkout, "this author has no books" and "I did not look at their books" are very different answers; here the difference becomes invisible.

There are three ways to bring in related data: eager loading (load it with the query), explicit loading (ask for it later, on purpose) and lazy loading (load it automatically when the property is touched). In this post we run all three, and at the end we talk about choosing between a single query and a split query.

The Model: Authors, Books, Tags and Orders

All five entities of the Bookstore model are here. Book is connected to Tags through a many-to-many relationship and to OrderItems through a one-to-many relationship:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace Bookstore;

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = null!;
    public int PublishedYear { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
    public List<Tag> Tags { get; set; } = new();
    public List<OrderItem> OrderItems { get; set; } = new();
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public List<Book> Books { get; set; } = new();
}

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; } = null!;
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
    public int Quantity { get; set; }
}

public class BookstoreContext : DbContext
{
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Tag> Tags => Set<Tag>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .UseSqlite("Data Source=bookstore.db")
            .LogTo(Console.WriteLine, new[] { RelationalEventId.CommandExecuted, RelationalEventId.MultipleCollectionIncludeWarning });
}

A seed method adds the data: three authors, four books, three tags and three orders. A Wizard of Earthsea has two tags and appears in three orders; this detail will matter at the end of the post. Each example runs against a clean database with a new BookstoreContext.

Eager Loading: Include

To ask for a relationship together with the query, use Include:

var authors = db.Authors.Include(a => a.Books).OrderBy(a => a.Id).ToList();

SELECT "a"."Id", "a"."Name", "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title"
FROM "Authors" AS "a"
LEFT JOIN "Books" AS "b" ON "a"."Id" = "b"."AuthorId"
ORDER BY "a"."Id"

Ursula K. Le Guin: 3 books
Isaac Asimov: 1 books
Stanislaw Lem: 0 books

It is a LEFT JOIN so that Stanislaw Lem, who has no books, is not lost. EF Core groups the incoming rows by author and turns the three rows for the same author into a single Author object and its Books list. This is called fix-up: the change tracker gathers objects with the same key into a single instance and wires the navigations together. The books' Author property comes back filled in too; you do not need to Include the other direction.

One More Level: ThenInclude

If you also want the books' tags, you go down a level with ThenInclude:

var authors = db.Authors
    .Include(a => a.Books)
        .ThenInclude(b => b.Tags)
    .OrderBy(a => a.Id)
    .ToList();

A Wizard of Earthsea: fantasy, classic
The Left Hand of Darkness: classic, science-fiction
The Dispossessed: science-fiction
Foundation: classic, science-fiction

The generated SQL now has two nested LEFT JOINs and an INNER JOIN through the BookTag join table. It is long, so I am not putting it here; you can look at it yourself with ToQueryString(). There is also a warning in the log before the SQL; we will talk about it at the end of the post.

If you want more than one path at the same level, you repeat Include: .Include(b => b.Tags).Include(b => b.OrderItems). For a deep path the chain gets longer: .Include(o => o.Items).ThenInclude(i => i.Book).ThenInclude(b => b.Author). There is also a string-based Include("Books.Tags"), but prefer the lambda version since the string one escapes the compiler's checks.

Filtered Include (EF Core 5+)

Sometimes you want part of a relationship, not all of it: only each author's books published before 1970, ordered by title. Since EF Core 5 you can write Where, OrderBy, Skip and Take inside Include:

var authors = db.Authors
    .Include(a => a.Books.Where(b => b.PublishedYear < 1970).OrderBy(b => b.Title))
    .OrderBy(a => a.Id)
    .ToList();

SELECT "a"."Id", "a"."Name", "t"."Id", "t"."AuthorId", "t"."PublishedYear", "t"."Title"
FROM "Authors" AS "a"
LEFT JOIN (
    SELECT "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title"
    FROM "Books" AS "b"
    WHERE "b"."PublishedYear" < 1970
) AS "t" ON "a"."Id" = "t"."AuthorId"
ORDER BY "a"."Id", "t"."Title"

Ursula K. Le Guin: A Wizard of Earthsea, The Left Hand of Darkness
Isaac Asimov: Foundation
Stanislaw Lem: 

The filter narrows the loaded books, not the authors. An author with no matching books stays in the list. EF Core 10 produces the same SQL; only the subquery's alias becomes "b0" instead of "t". Alias differences like this are common between versions and do not change the meaning.

Explicit Loading: Later, on Purpose

If you have already loaded the main record and only need the relationship when some condition holds, you can load it later with the Entry API:

var author = db.Authors.Single(a => a.Name == "Ursula K. Le Guin");
db.Entry(author).Collection(a => a.Books).Load();
Console.WriteLine($"{author.Name}: {author.Books.Count} books");

var book = db.Books.Single(b => b.Title == "Foundation");
db.Entry(book).Reference(b => b.Author).Load();
Console.WriteLine($"{book.Title} by {book.Author.Name}");

SELECT "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."AuthorId" = @__p_0

Ursula K. Le Guin: 3 books
Foundation by Isaac Asimov

Collection for a collection, Reference for a single relationship. Every Load() is a separate SQL command. The handier one is Query(): it lets you build a query on top of the relationship without loading it.

var count = db.Entry(author).Collection(a => a.Books).Query().Count(b => b.PublishedYear > 1970);
Console.WriteLine($"After 1970: {count}");

SELECT COUNT(*)
FROM "Books" AS "b"
WHERE ("b"."AuthorId" = @__p_0) AND ("b"."PublishedYear" > 1970)

The books did not come into memory, only their count did. On EF Core 6, explicit loading requires the entity to be tracked. If you call Load() on an author loaded with AsNoTracking():

System.InvalidOperationException: The navigation 'Author.Books' cannot be loaded because the entity is not being tracked. Navigations can only be loaded for tracked entities.

EF Core 8 removed this restriction; the same code loads the books on EF Core 10 and the author stays Detached. We will look at tracking in detail in the next post.

Lazy Loading: The Magic and the Bill

Lazy loading solves the problem from the start of the post "automatically": the moment you touch a navigation property, EF Core goes and fetches the data. EF Core needs a separate package for this:

dotnet add package Microsoft.EntityFrameworkCore.Proxies --version 6.0.36

The package generates proxy classes that derive from your entities and override the navigation properties. That is why two changes are needed: adding UseLazyLoadingProxies() to the options and making every navigation virtual.

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public virtual List<Book> Books { get; set; } = new();
}

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options
        .UseSqlite("Data Source=bookstore.db")
        .UseLazyLoadingProxies()
        .LogTo(Console.WriteLine, new[] { RelationalEventId.CommandExecuted, RelationalEventId.MultipleCollectionIncludeWarning });

If you forget to make a navigation virtual, the app stops not at the first query but while building the model:

Unhandled exception. System.InvalidOperationException: Property 'Author.Books' is not virtual. 'UseChangeTrackingProxies' requires all entity types to be public, unsealed, have virtual properties, and have a public or protected constructor. 'UseLazyLoadingProxies' requires only the navigation properties be virtual.

Now let us run the loop from the start of the post again, without changing it. For readability I kept only the SQL from the log:

SELECT "a"."Id", "a"."Name"
FROM "Authors" AS "a"
ORDER BY "a"."Id"

SELECT "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."AuthorId" = @__p_0
Ursula K. Le Guin: 3 books

SELECT "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."AuthorId" = @__p_0
Isaac Asimov: 1 books

SELECT "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."AuthorId" = @__p_0
Stanislaw Lem: 0 books

The result is correct, but the bill is four queries: one for the authors and one more for each author. This is the N+1 problem. Four queries for three authors look innocent; a list of a thousand authors means a thousand and one queries, each a separate round trip over the network. Worse, nothing in the code shows it. author.Books.Count looks like an ordinary property read. author.GetType().Name returns AuthorProxy, too; the object in your hand is no longer your class but a proxy derived from it.

The second bill arrives after the context is closed. Because the loading moment is unpredictable, when you carry an object beyond the lifetime of the context, for example during serialization in a web request, touching a navigation that has not been loaded yet throws:

System.InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Infrastructure.LazyLoadOnDisposedContextWarning': An attempt was made to lazy-load navigation 'BookProxy.Tags' after the associated DbContext was disposed. This exception can be suppressed or logged by passing event ID 'CoreEventId.LazyLoadOnDisposedContextWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

The Books loaded while the context was open can still be read, but Tags was never loaded, so it blew up. You can use lazy loading in a desktop app with a long-lived context, keeping an eye on the number of queries. In Web APIs, keep it off by default; stating explicitly in the query what to load is more predictable.

A Single Query or a Split Query?

Now back to that warning. EF Core warns when a single query loads more than one collection navigation; in the ThenInclude example those were Books and Tags. The truly dangerous case is loading two collections at the same level. Let us ask for both a book's tags and its order items:

var books = db.Books
    .Include(b => b.Tags)
    .Include(b => b.OrderItems)
    .Where(b => b.Title == "A Wizard of Earthsea")
    .ToList();

warn: 4.11.2026 01:03:14.032 RelationalEventId.MultipleCollectionIncludeWarning[20504] (Microsoft.EntityFrameworkCore.Query) 
      Compiling a query which loads related collections for more than one collection navigation, either via 'Include' or through projection, but no 'QuerySplittingBehavior' has been configured. By default, Entity Framework will use 'QuerySplittingBehavior.SingleQuery', which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'.

SELECT "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title", "t0"."BooksId", "t0"."TagsId", "t0"."Id", "t0"."Name", "o"."Id", "o"."BookId", "o"."OrderId", "o"."Quantity"
FROM "Books" AS "b"
LEFT JOIN (
    SELECT "b0"."BooksId", "b0"."TagsId", "t"."Id", "t"."Name"
    FROM "BookTag" AS "b0"
    INNER JOIN "Tags" AS "t" ON "b0"."TagsId" = "t"."Id"
) AS "t0" ON "b"."Id" = "t0"."BooksId"
LEFT JOIN "OrderItem" AS "o" ON "b"."Id" = "o"."BookId"
WHERE "b"."Title" = 'A Wizard of Earthsea'
ORDER BY "b"."Id", "t0"."BooksId", "t0"."TagsId", "t0"."Id"

The book has 2 tags and 3 order items. Run the same JOINs with sqlite3, selecting only four columns for readability, and look at how many rows come back:

Title|Tag|OrderId|Quantity
A Wizard of Earthsea|fantasy|1|1
A Wizard of Earthsea|fantasy|2|1
A Wizard of Earthsea|fantasy|3|3
A Wizard of Earthsea|classic|1|1
A Wizard of Earthsea|classic|2|1
A Wizard of Earthsea|classic|3|3

Six rows for five objects, that is 2 × 3. The two sibling collections multiply with each other and the book's columns repeat on every row. This is called a cartesian explosion. With 10 tags and 1,000 order items, a single book would bring back 10,000 rows. Add AsSplitQuery() to the same query:

var books = db.Books
    .Include(b => b.Tags)
    .Include(b => b.OrderItems)
    .Where(b => b.Title == "A Wizard of Earthsea")
    .AsSplitQuery()
    .ToList();

SELECT "b"."Id", "b"."AuthorId", "b"."PublishedYear", "b"."Title"
FROM "Books" AS "b"
WHERE "b"."Title" = 'A Wizard of Earthsea'
ORDER BY "b"."Id"

SELECT "t0"."BooksId", "t0"."TagsId", "t0"."Id", "t0"."Name", "b"."Id"
FROM "Books" AS "b"
INNER JOIN (
    SELECT "b0"."BooksId", "b0"."TagsId", "t"."Id", "t"."Name"
    FROM "BookTag" AS "b0"
    INNER JOIN "Tags" AS "t" ON "b0"."TagsId" = "t"."Id"
) AS "t0" ON "b"."Id" = "t0"."BooksId"
WHERE "b"."Title" = 'A Wizard of Earthsea'
ORDER BY "b"."Id"

SELECT "o"."Id", "o"."BookId", "o"."OrderId", "o"."Quantity", "b"."Id"
FROM "Books" AS "b"
INNER JOIN "OrderItem" AS "o" ON "b"."Id" = "o"."BookId"
WHERE "b"."Title" = 'A Wizard of Earthsea'
ORDER BY "b"."Id"

Three queries, 1 + 2 + 3 rows in total. The product turned into a sum. The warning disappeared as well, because you made your choice explicitly.

A split query is not free. There are three separate round trips; with high network latency it can be slower than a single query for small data. More importantly, the three queries do not read the same snapshot: if someone adds a tag between the first and the second query, the results may be inconsistent with each other. If you cannot accept that risk, you need to run the queries inside a transaction. Before EF Core 3.0 the default behavior was already similar; 3.0 switched to translating every LINQ query into a single SQL statement, and split queries came back as an opt-in in 5.0.

As a rule: a single query is fine for reference navigations (Author, Book) and a single collection. If there are several collections at the same level or a large chain of collections, try a split query and measure. If you want to change the default for the whole app, you can write UseSqlite("...", o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)) in the provider configuration, and switch back for individual queries with AsSingleQuery().

Common Pitfalls

Filtered include and previously tracked objects. If you load all books in the same context first and then run a filtered include, the filter appears not to work:

var allBooks = db.Books.ToList();
var authors = db.Authors
    .Include(a => a.Books.Where(b => b.PublishedYear < 1970))
    .OrderBy(a => a.Id)
    .ToList();

Ursula K. Le Guin: 3 books
Isaac Asimov: 1 books
Stanislaw Lem: 0 books

The WHERE "b"."PublishedYear" < 1970 in the SQL worked correctly. But the change tracker already knew The Dispossessed from 1974, and during fix-up it wired that book into the author's list as well. A filtered include does not guarantee "only these will be in the list"; it says "fetch only these from the database". If you need that guarantee, use a new context or AsNoTracking().

Include in a projection. The query db.Books.Include(b => b.Tags).Select(b => b.Title) produces SELECT "b"."Title" FROM "Books"; the Include is silently ignored. Include only means something when the query returns entities. If you are writing a projection, ask for the relationship you need directly inside the Select.

Including everything. Every Include added "just in case" means another JOIN and more columns. If a screen only shows the author's name, do not load the whole book tree; for most read screens, the projection from the previous post is cheaper.

Lazy loading and serialization. With lazy loading on, turning an entity straight into JSON makes the serializer touch every navigation, which triggers dozens of queries or the disposed context error. This is one of the reasons to return DTOs, not entities, from APIs.

A Small Experiment

★☆☆ Load the orders together with their items and each item's book: db.Orders.Include(o => o.Items).ThenInclude(i => i.Book). Print each order with the customer name and the book titles, and count how many JOINs the generated SQL contains.

★★☆ Fix the "0 books" loop from the start of the post in three different ways: with Include, with explicit loading for each author, and with a projection that returns the author's name and the number of books. Compare the number of SQL commands in the logs of all three.

★★★ Add 200 order items and 10 tags for A Wizard of Earthsea to the seed. Measure the same Include(b => b.Tags).Include(b => b.OrderItems) query as a single query and as a split query with Stopwatch, and verify with sqlite3 how many rows the single query returns.

Throughout this post we kept referring to the change tracker: fix-up, Find not going to the database, the unexpected result of the filtered include. In the next post we go inside that mechanism: entity states, AsNoTracking, identity resolution and DetectChanges.