Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 41 ยท Relationships and Loading

Eager Loading with Include and ThenInclude

Learn EF Core eager loading with Include and ThenInclude, filtered includes, AutoInclude, cartesian explosion and when projection beats loading whole graphs, with runnable query-counting stand-ins.

When you load a Blog from the database, EF Core does not automatically load its posts, their comments or their author. Related data is only loaded when you ask for it. Eager loading means asking for it up front, in the same query, with Include. It is the loading style you will use most, because it is explicit, predictable and avoids the N+1 problem you will meet in query performance.

The EF Core code in this lesson is read-only. The runnable blocks are stand-ins: small in-memory "tables" with a query counter, to show the difference between one joined query and many small ones.

flowchart LR
    A["Need related data?"] --> B["Eager: Include in the query"]
    A --> C["Explicit: Load it later on demand"]
    A --> D["Lazy: Loaded on first access"]
    B --> E["1 query (or a few)"]
    C --> F["1 extra query per call"]
    D --> G["1 hidden query per access"]

Eager loading is the default recommendation: you see the cost in the code.

Include

C#
var blogs = await db.Blogs
    .Include(b => b.Posts)
    .ToListAsync();

EF Core generates a LEFT JOIN between Blogs and Posts and builds the object graph from the flat result. Each Blog comes back with its Posts filled in. Without Include, blog.Posts would be an empty collection (not null, not an error).

ThenInclude: going deeper

Include goes one level. To load posts and each post's comments, chain ThenInclude:

C#
var blogs = await db.Blogs
    .Include(b => b.Posts)
        .ThenInclude(p => p.Comments)
    .Include(b => b.Owner)          // a sibling branch: back at blog level
    .ToListAsync();

A string overload exists, Include("Posts.Comments"), handy when the path is built at runtime.

Filtered Include

Since EF Core 5 you can filter and order what is included. Only Where, OrderBy/ThenBy, Skip, Take (and their descending variants) are allowed inside:

C#
var blogs = await db.Blogs
    .Include(b => b.Posts
        .Where(p => p.Published)
        .OrderByDescending(p => p.CreatedAt)
        .Take(5))
    .ToListAsync();

Think of it as "load the blogs, plus their five latest published posts". The blogs themselves are not filtered; a blog with no published posts still appears with an empty list.

Include with Where on the parent

A common confusion: Where outside Include filters the parent rows.

C#
var cooking = await db.Blogs
    .Where(b => b.Title.Contains("cook"))     // filter blogs
    .Include(b => b.Posts)                    // then load their posts
    .ToListAsync();

To filter blogs by a property of their posts, use Any in the Where:

C#
db.Blogs.Where(b => b.Posts.Any(p => p.Published))

AutoInclude: always load a navigation

If you almost always need a navigation, configure it once in the model:

C#
modelBuilder.Entity<Order>().Navigation(o => o.Lines).AutoInclude();
// Opt out for one query:
db.Orders.IgnoreAutoIncludes().ToList();

Use sparingly. It hides cost; a query that looks like it loads only orders now also reads every line.

Cartesian explosion

Joining two collections in one query multiplies rows. A blog with 100 posts and 50 tags returns 5,000 rows for that blog in a single result set, with the blog's columns repeated each time. This is the cartesian explosion problem.

C#
db.Blogs.Include(b => b.Posts).Include(b => b.Tags)   // two collections: rows multiply

The fix is AsSplitQuery(), covered in compiled queries, split queries and pooling, which sends one SQL statement per collection and stitches them together.

The stand-in below shows the multiplication with plain loops:

C# Stand-in: why joins multiply rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Include versus Select (projection)

If you only need a few columns, do not load whole entities. A projection translates to a narrow SELECT and needs no Include at all, because the query itself walks the navigations:

C#
var summary = await db.Blogs
    .Select(b => new
    {
        b.Title,
        PostCount = b.Posts.Count,
        LatestHeading = b.Posts.OrderByDescending(p => p.CreatedAt).Select(p => p.Heading).FirstOrDefault()
    })
    .ToListAsync();

Rule of thumb: Include when you will modify or display the full related entities; Select when you only read some values.

Include is ignored by projections

If the query ends in a Select that does not return the entity itself, Include has no effect and EF Core ignores it. People often leave stale Include calls in such queries; remove them.

A query-counting stand-in

This stand-in has two tables and a counter. LoadLazily fetches posts per blog (N+1), while LoadEagerly performs one joined lookup. Compare the counts:

C# Stand-in: queries issued, eager versus per-row

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

With three blogs the per-row version needs four queries and the eager one needs one. With 1,000 blogs it is 1,001 versus one.

Common mistakes

  • Missing Include, then concluding the data does not exist. An empty Posts often means "not loaded".
  • Including everything "just in case". Each Include adds a join and more data over the wire.
  • Multiple collection includes in one query without considering split queries.
  • Including after ToList(). Include is a query operator; it must come before the query runs.
  • Using Include on a projection, where it has no effect.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You load a Blog without Include and read blog.Posts. What do you get?

    EF Core does not load related data unless asked; the collection exists but is empty.
  2. 2. Which method loads a collection nested inside an included collection?

    ThenInclude continues the path from the previously included navigation.
  3. 3. What does a filtered Include (Where inside Include) filter?

    It limits which related entities are loaded; parents are not filtered.
  4. 4. What is cartesian explosion?

    Each combination of rows from the joined collections becomes its own result row.
  5. 5. When is Select (projection) better than Include?

    Projections fetch only the columns you need and need no tracking.

Summary

  • Related data is not loaded unless you ask; Include loads it with the main query.
  • ThenInclude goes deeper; filtered Include can Where, order and Take the related rows.
  • AutoInclude makes a navigation always load; use it rarely.
  • Several collection includes can explode the row count; split queries fix it.
  • Use Select when you only need some data.

Next: Explicit and lazy loading