skip to content

Navigations & Loading

Relationships in EF Core and how related data arrives: foreign keys and cascade delete, then eager, explicit or lazy loading. Interviewers dig here because N+1 queries start in these choices.

part ofEntity Framework Coreread it →
on this pageshow

explore

questions

11

In EF Core 10, how do Include and ThenInclude load related entities, and what does a navigation hold when a query omits them?

level: juniorimportance: must knowfreq 80%

answer

  1. loaded in the same round trip
  2. IIncludableQueryable<TEntity, TProperty>
  3. ThenInclude continues from the previous level
  4. a sibling branch restarts from the root
  5. lazy loading is opt-in

basics

~20 s

Include loads a navigation in the same query as its owner; ThenInclude goes one level deeper. A navigation left out is not queried: it stays null or empty unless the context already tracks those rows or lazy loading is on.

solid answer

~40 s

`db.Playlists.Include(p => p.Tracks).ThenInclude(t => t.Artist)` loads the playlists, their tracks and each track's artist in one round trip - by default a single SQL statement with joins. `Include` returns `IIncludableQueryable<TEntity, TProperty>`, which is what lets `ThenInclude` continue from the included type; a second branch at the same depth (the album) needs a fresh `.Include(p => p.Tracks).ThenInclude(t => t.Album)` from the root, and in most cases EF Core combines the shared joins instead of repeating them. A navigation the query never names is simply not loaded: a reference stays `null` and a collection keeps whatever the constructor assigned, unless entities the context already tracks are fixed up into it. Lazy loading is opt-in in EF Core, so touching that property later fetches nothing by itself.

code

csharp · 14 lines
csharp
app.MapGet("/admin/playlists", async (MusicDb db) =>
{
    var playlists = await db.Playlists
        .Include(p => p.Tracks).ThenInclude(t => t.Artist)
        .Include(p => p.Tracks).ThenInclude(t => t.Album)
        .AsNoTracking()
        .ToListAsync();

    return playlists.Select(p => new
    {
        p.Name,
        Tracks = p.Tracks.Select(t => $"{t.Title} / {t.Artist.Name} / {t.Album.Title}")
    });
});

go deeper

for a junior

Recall the shape: Include from the root, ThenInclude one level deeper, a fresh Include for each sibling branch, and that an omitted navigation is null or empty rather than an error.

for a middle

Explain that Include returns IIncludableQueryable so ThenInclude can continue, why EF6's Select-inside-Include fails, and how already-tracked rows can make a navigation look loaded.

for a senior

Show you choose include paths deliberately: no catch-all includes, awareness of the row multiplication from several collections, and a review habit of checking every navigation a handler reads.

for a principal

Frame the trade-off between declaring load shape per query and hiding it in the model, and how a team keeps read paths explicit as the schema grows.

## What eager loading means in EF Core An **entity** is a class EF Core maps to a table; a **navigation** is a property on it that points at related entities - a **reference navigation** such as `Track.Artist` holds one object, a **collection navigation** such as `Playlist.Tracks` holds many. A LINQ query over a `DbSet<T>` materializes the root entities and, by default, nothing else. **Eager loading** means asking EF Core to fetch named navigations in the same query as their owner, so the data is already in memory when the code reads it. `Include` and `ThenInclude` are the operators that say which navigations to fetch. In a music-streaming admin, a playlist page needs each playlist's tracks and, for every track, its artist and album. The model is: - `Playlist` with `List<Track> Tracks` - `Track` with `Artist Artist` and `Album Album` - `Artist` and `Album` with their own scalar columns ## Include and ThenInclude, step by step 1. `Include(p => p.Tracks)` takes a lambda that returns a navigation of the **root** type and returns `IIncludableQueryable<Playlist, List<Track>>`. 2. `ThenInclude(t => t.Artist)` is only callable on that includable query; its lambda parameter is the **previously included** type (`Track`), so it reaches one level deeper. 3. Another `ThenInclude` after that continues from `Artist`, not from `Track`. 4. A new `Include` always starts again from the root type. 5. When the query runs, EF Core translates the include tree into SQL - by default **one statement** with joins - and populates the navigations as it materializes rows. ```csharp var playlists = await db.Playlists .Include(p => p.Tracks).ThenInclude(t => t.Artist) .Include(p => p.Tracks).ThenInclude(t => t.Album) .ToListAsync(); ``` Repeating `Include(p => p.Tracks)` for the album branch does not join the tracks twice; the EF Core docs say that in most cases EF combines the joins for paths that share a prefix. ## Other ways to write an include path - **A chain of references in one call:** a single `Include` can walk several navigations when they are all references, or when only the last one is a collection - for example `db.Tracks.Include(t => t.Album.Artist)` if `Album` had an `Artist` reference. - **The string overload:** `Include("Tracks.Artist")` names the path with dots; it is useful when the path is built at runtime, at the cost of compile-time checking. - **Derived types:** a navigation that exists only on a derived class is reached with a cast or `as` inside the lambda, for example `Include(item => ((PodcastEpisode)item).Show)`. ## What a navigation holds when the query does not include it | Situation | Reference navigation (`track.Artist`) | Collection navigation (`playlist.Tracks`) | |---|---|---| | Fresh context, no Include, lazy loading off | `null` | whatever the constructor assigned (`null` or an empty list) | | Related rows already tracked by the context | set by **fix-up** | contains the tracked rows that belong to it | | Lazy loading enabled and the navigation can be lazy-loaded | loaded on first access | loaded on first access | | Included in the query | loaded | loaded | Two consequences matter in interviews. First, reading a navigation that was not included does **not throw**: the code quietly sees `null` or an empty list, which is why a missing `Include` shows up as a blank column or a `NullReferenceException` further down, not as an EF Core error. Second, a navigation can look loaded by accident because an earlier query in the same `DbContext` brought the related rows into the change tracker - code that works in one handler can fail in another that runs on a fresh context. ## Mistakes that come up - **Writing EF6's form.** `Include(p => p.Tracks.Select(t => t.Artist))` was the Entity Framework 6 way to go a level deeper. EF Core rejects `Select` inside `Include`; the lambda may only access navigations and, for collections, compose `Where`, `OrderBy`, `OrderByDescending`, `ThenBy`, `ThenByDescending`, `Skip` or `Take`. - **Chaining `ThenInclude` for siblings.** `.ThenInclude(t => t.Artist).ThenInclude(...)` continues from `Artist`, so the album branch must restart with `Include`. - **Placing `Include` after a `Select` that projects another type.** EF Core throws an `InvalidOperationException` telling you to apply `Include` before the `Select`. - **Including everything "just in case".** Each include widens the rows and columns read; including two collections at one level multiplies rows, and EF Core logs `MultipleCollectionIncludeWarning` when no splitting behaviour is configured. The rule to carry into code review: every navigation a handler reads must be loaded on purpose - by `Include`, by explicit loading, or by a projection that names the columns it needs.

  • Can one Include call load a chain of navigations, such as a track's album and that album's artist?
    Yes, when the chain is all reference navigations, or ends with a single collection: `db.Tracks.Include(t => t.Album.Artist)` loads both levels. Once a collection sits in the middle of the path you need `Include` for the collection and `ThenInclude` for the step after it, because a property access on a collection does not compile.
  • What happens if you call Include after a Select that projects a different entity type?
    EF Core throws an `InvalidOperationException` saying `Include` cannot be applied there because the source is not a queryable of a known entity type or `Include` came after a `Select` projecting another type. Move the `Include` before the `Select`, or let the projection name the related data directly.

  • EF Core loads collection navigations automatically whenever the parent entity is queried.
  • A second ThenInclude in the chain starts again from the root entity.
  • Include(p => p.Tracks.Select(t => t.Artist)) is how EF Core reaches a second level.
  • Reading a navigation that was not included throws an exception.
  • Repeating Include(p => p.Tracks) for a second branch joins the Tracks table twice.
open as a page

In EF Core 10, how do you configure a one-to-many relationship with HasOne, WithMany and HasForeignKey, and what makes it required or optional?

level: juniorimportance: must knowfreq 72%

basics

~10 s

In OnModelCreating, call HasOne(c => c.Department).WithMany(d => d.Courses).HasForeignKey(c => c.DepartmentId) on the dependent. A non-nullable foreign key makes the relationship required; a nullable one such as int? makes it optional.

open as a page

An EF Core 10 admin page lists a playlist's tracks with artist and album and logs one SQL query per track - how do lazy-loading proxies cause that, and how do you fix it?

level: middleimportance: must knowfreq 70%

basics

~20 s

With UseLazyLoadingProxies, each virtual navigation loads on first access, so reading track.Artist and track.Album in the loop sends queries per track. Load them up front with Include/ThenInclude or a Select projection, and switch lazy loading off.

open as a page

In EF Core 10, what happens to a course's enrolments when the course is deleted, and how do OnDelete and DeleteBehavior change that?

level: middleimportance: must knowfreq 66%

basics

~20 s

With the default required relationship (Cascade), EF Core deletes tracked enrolments itself and the database's ON DELETE CASCADE removes untracked ones. OnDelete(DeleteBehavior...) changes both what EF Core does to tracked rows and the constraint migrations create.

open as a page

In EF Core 10, how does explicit loading through Entry().Collection() and Entry().Reference() work, and when do you call Query() instead of Load()?

level: middleimportance: should knowfreq 45%

basics

~20 s

Explicit loading fetches one navigation of an entity you already hold: db.Entry(x).Collection(...) or .Reference(...), then LoadAsync(). Query() instead returns an IQueryable over that navigation, to count, filter or page related rows without loading them all.

open as a page

In EF Core 10, when is a Student-Course many-to-many mapped with skip navigations alone, and when does it need an Enrollment join entity through UsingEntity?

level: middleimportance: should knowfreq 55%

basics

~20 s

With only Student.Courses and Course.Students, EF Core creates a hidden shared-type join entity and a CourseStudent table. Once the link carries data such as an enrolment date or grade, map an Enrollment class with UsingEntity<Enrollment>().

open as a page

In EF Core 10, what can a filtered Include such as Include(p => p.Tracks.Where(...).Take(10)) express, and why can a tracking query still return tracks the filter excluded?

level: seniorimportance: should knowfreq 35%

basics

~20 s

A filtered Include composes Where, OrderBy(Descending), ThenBy(Descending), Skip and Take on a collection navigation inside Include. In a tracking query, tracks the context already holds are fixed up into the collection even when the filter excludes them.

open as a page

For a read-only EF Core 10 endpoint returning playlist DTOs, why project related data with Select instead of Include, and what happens to an Include placed before that Select?

level: seniorimportance: should knowfreq 55%

basics

~20 s

A Select projection reads only the columns the DTO needs, follows navigations without Include, returns untracked objects and cannot lazy-load. An Include before it has no effect when the final projection returns no entity, so it should be removed.

open as a page

In EF Core 10, why does removing an enrolment from course.Enrollments delete its row on SaveChanges, and how do you move it to another course instead?

level: seniorimportance: should knowfreq 38%

basics

~20 s

The Course-Enrollment relationship is required and cascades, so an enrolment removed from the collection becomes an orphan that EF Core marks Deleted. Move it by setting enrollment.Course or CourseId to the new course, not by removing it first.

open as a page

Deleting an EF Core 10 student who mentors other students throws DbUpdateException although Student.MentorId is nullable - why, and how do you fix it?

level: seniorimportance: should knowfreq 30%

basics

~20 s

The self-referencing Mentor relationship is optional, so its default ClientSetNull nulls MentorId only on tracked mentees; the constraint has no ON DELETE action, so unloaded mentees still reference the row. Load the mentees, or configure OnDelete(DeleteBehavior.SetNull).

open as a page

In EF Core 10, what does Navigation(t => t.Album).AutoInclude() configure in OnModelCreating, and how does a single query opt out of it?

level: middleimportance: nice to knowfreq 20%

basics

~20 s

AutoInclude marks a navigation to load as if every query returning that entity had included it, however the entity reached the result. A query opts out with IgnoreAutoIncludes(), which drops all configured auto-includes for that query; owned navigations still load.

open as a page