EF Core Loading Cheat Sheet: Eager, Lazy, Explicit, Split Queries and AsNoTracking

Learning outcome

By the end of this cheat sheet you should be able to choose between eager, explicit and lazy loading, between single and split queries, and between tracking and no-tracking queries. You should also be able to spot an N+1 pattern in code review. If you want the production diagnosis story (logs, traces and fixes), continue with How to Diagnose and Fix EF Core N+1 Queries in Production.

Intuition

EF Core doesn't load related data unless you ask for it. Every loading strategy answers two questions:

1. When is the related data fetched? Up front, on demand by your code, or silently when a navigation property is touched?
2. How many round trips does it cost? One query, one query per collection, or one query per parent row (N+1)?

Tracking is a separate dial. It controls whether EF tracks entity instances for SaveChanges; the default snapshot strategy records original values, while notification-based strategies can avoid snapshots.

Quick reference: loading strategies

Filtered include (EF Core 5+): Include(b => b.Posts.Where(p => p.Published).OrderBy(p => p.Id).Take(5)).

Single vs split queries

EF Core logs a warning when a query loads more than one collection navigation as a single query and you haven't configured a splitting behaviour.

Tracking vs no-tracking

You can set the default with UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking) and opt in with .AsTracking(). A projection that returns only scalar values or DTOs is not tracked. Entity instances returned inside a projection (for example new { Blog = b, b.Name }) are still tracked.

N+1 in one picture

// N+1: 1 query for orders + 1 query per order
var orders = await db.Orders.ToListAsync(ct);
foreach (var o in orders)
    await db.Entry(o).Collection(x => x.Lines).LoadAsync(ct);

// Fix A: eager load (1 query, or 2 with AsSplitQuery)
var orders2 = await db.Orders.Include(o => o.Lines).ToListAsync(ct);

// Fix B: project only what the endpoint returns (1 query)
var dto = await db.Orders
    .Select(o => new { o.Id, LineCount = o.Lines.Count, Total = o.Lines.Sum(l => l.Price * l.Qty) })
    .ToListAsync(ct);

How to detect it: log SQL (LogTo(Console.WriteLine) or the Microsoft.EntityFrameworkCore.Database.Command category), count commands per request in tests with a DbCommandInterceptor, or look at the database spans in your traces.

Other facts interviewers like

• Client evaluation: since EF Core 3.0, only the final projection may run on the client. A predicate EF can't translate throws instead of silently loading the whole table.
• ToList() too early: db.Orders.ToList().Where(...) filters in memory after fetching every row.
• Lazy loading + serialization: a JSON serializer walking navigations triggers one query per navigation and can loop on cycles.
• Find/FindAsync check the change tracker before going to the database. FirstOrDefault always queries.
• Compiled queries (EF.CompileAsyncQuery) bypass the normal query-cache lookup for hot paths; EF already caches query compilation by expression-tree shape. They don't fix a bad query shape.
• DbContext is not thread-safe: no parallel queries on one context. Use one context per unit of work (scoped in ASP.NET Core) or IDbContextFactory<T>.

Failure modes

• Three sibling Includes on collections: the joined row count is the sum, over parents, of max(1, A) × max(1, B) × max(1, C) for those LEFT JOINs.
• Before EF Core 10, AsSplitQuery() with Skip/Take and a non-unique OrderBy can return inconsistent pages across split statements. Keep ordering fully unique for deterministic paging.
• Lazy loading enabled "for convenience" in an API: an N+1 in every endpoint that returns a graph.
• Tracking read-only results can add snapshot and bookkeeping overhead and grow long-lived contexts; identity reuse can sometimes offset that cost.
• AsNoTracking() followed by Update(entity) on a graph: existing entities are generally marked Modified (not their key properties); entities with unset generated keys can be Added, and duplicate instances can conflict when attached.

Interview drill

1. What is the N+1 problem, and give two fixes.
2. When would you choose a split query, and what do you give up?
3. Why is projection often better than Include for API responses?
4. What does AsNoTrackingWithIdentityResolution add over AsNoTracking?
5. Why is lazy loading risky in ASP.NET Core APIs?
6. How would you prove in a test that an endpoint issues a bounded number of SQL commands?

Revision checklist

• Can I name the three loading strategies and the API for each?
• Can I explain cartesian explosion and the split-query trade-off?
• Do I know the tracking options and their identity behaviour?
• Can I rewrite an N+1 loop as an Include and as a projection?
• Can I explain the EF Core 3.0+ client-evaluation rule?

Worked executable example

The program seeds three blogs with two posts each in an in-memory SQLite database, then counts the SQL commands each loading strategy sends, using a DbCommandInterceptor. It finishes by comparing the change tracker after a no-tracking query and after a tracking query.

Run this example

Use the .NET 10 SDK. In a new empty folder, save the project below as Example.csproj and the listing as Program.cs. Run dotnet run --project Example.csproj.

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>disable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.0" />
  </ItemGroup>
</Project>

Program.cs

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

using var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
var counter = new CommandCounter();

using (var db = Create())
{
    db.Database.EnsureCreated();
    for (var i = 1; i <= 3; i++)
    {
        var blog = new Blog { Name = $"Blog {i}" };
        blog.Posts.Add(new Post { Title = "A" });
        blog.Posts.Add(new Post { Title = "B" });
        db.Blogs.Add(blog);
    }
    db.SaveChanges();
}

// N+1: one query for blogs, then one explicit load per blog.
Measure("N+1 (explicit load per blog)", db =>
{
    foreach (var blog in db.Blogs.ToList())
        db.Entry(blog).Collection(b => b.Posts).Load();
});

// Eager loading, single query (JOIN).
Measure("Include, single query", db => db.Blogs.Include(b => b.Posts).ToList());

// Eager loading, split query (one query per collection include).
Measure("Include + AsSplitQuery", db => db.Blogs.Include(b => b.Posts).AsSplitQuery().ToList());

// Projection: only the columns you need, nothing tracked.
Measure("Projection (Select)", db => db.Blogs.Select(b => new { b.Name, PostCount = b.Posts.Count }).ToList());

// Tracking vs no tracking.
using (var db = Create())
{
    db.Blogs.AsNoTracking().ToList();
    Console.WriteLine($"Tracked after AsNoTracking: {db.ChangeTracker.Entries().Count()}");
    db.Blogs.ToList();
    Console.WriteLine($"Tracked after tracking query: {db.ChangeTracker.Entries().Count()}");
}

void Measure(string label, Action<AppDb> action)
{
    using var db = Create();
    counter.Count = 0;
    action(db);
    Console.WriteLine($"{label}: {counter.Count} SQL command(s)");
}

AppDb Create() => new(new DbContextOptionsBuilder<AppDb>()
    .UseSqlite(connection)
    .AddInterceptors(counter)
    .Options);

sealed class CommandCounter : DbCommandInterceptor
{
    public int Count;
    public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
    {
        Count++;
        return result;
    }
}

sealed class AppDb(DbContextOptions<AppDb> options) : DbContext(options)
{
    public DbSet<Blog> Blogs => Set<Blog>();
}

sealed class Blog
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Post> Posts { get; } = new();
}

sealed class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public int BlogId { get; set; }
}

Expected output

N+1 (explicit load per blog): 4 SQL command(s)
Include, single query: 1 SQL command(s)
Include + AsSplitQuery: 2 SQL command(s)
Projection (Select): 1 SQL command(s)
Tracked after AsNoTracking: 0
Tracked after tracking query: 3

With N blogs the first line grows to N + 1. The others stay constant.

Related on TechInterviewNotes

• How to Diagnose and Fix EF Core N+1 Queries in Production
• EF Core Query Performance and N+1 Diagnosis (Production-Grade .NET Backend Interview Path, lesson 3)
• IQueryable and EF Core: When the Cost Moves to SQL (Time and Space Complexity in LINQ, lesson 16)
• EF Core Data Correctness and Safe Schema Delivery (course)
• Stable Pagination in .NET APIs: Offset Drift, Keyset Cursors, and Tie Breakers

Sources

Microsoft Learn: Loading related data (EF Core)

Microsoft Learn: Eager loading of related data

Microsoft Learn: Lazy loading of related data

Microsoft Learn: Explicit loading of related data

Microsoft Learn: Single vs. split queries

Microsoft Learn: Tracking vs. no-tracking queries

Microsoft Learn: Client vs. server evaluation

Microsoft Learn: Efficient querying