Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 41 ยท Performance and Concurrency

Query Performance and the N+1 Problem

Make EF Core queries fast: spot N+1 queries, project with Select, use AsNoTracking, paginate with keyset instead of offset, choose Any over Count, add indexes and log SQL, with measurable stand-ins.

EF Core is fast enough for almost every application, when you use it with the database in mind. Slow EF Core apps are nearly always slow for the same few reasons: too many queries, too much data per query, missing indexes, or work done in memory that the database should do. This lesson gives you a checklist and the habit of looking at the SQL.

The EF Core code is read-only. The runnable blocks are stand-ins that measure the ideas: query counts and rows scanned.

Step zero: look at the SQL

You cannot tune what you cannot see.

C#
// Log every command (development only: it can print parameter values)
options.UseSqlServer(cs)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging();

// Or look at one query
var query = db.Products.Where(p => p.Price > 10).OrderBy(p => p.Name);
Console.WriteLine(query.ToQueryString());

ToQueryString() shows the SQL without running it. Combined with your database's execution plan (EXPLAIN / "Include Actual Execution Plan") it answers most "why is this slow?" questions.

1. The N+1 problem

You met it in explicit and lazy loading: one query for the parents and one more per parent. It also happens with explicit code:

C#
var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
    order.Customer = await db.Customers.FindAsync(order.CustomerId);   // one query per order
}

Fixes, in order of preference: a projection that includes the needed columns, Include, or one batched query for all keys:

C#
var ids = orders.Select(o => o.CustomerId).Distinct().ToList();
var customers = await db.Customers.Where(c => ids.Contains(c.Id)).ToDictionaryAsync(c => c.Id);   // 1 query: WHERE Id IN (...)
C# Stand-in: N+1 versus one batched lookup

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.

2. Fetch less: projection

Loading an entity reads every column and allocates a tracked object. If a list screen shows a name and a price, say so:

C#
var rows = await db.Products
    .Where(p => p.Stock > 0)
    .OrderBy(p => p.Name)
    .Select(p => new ProductListItem(p.Id, p.Name, p.Price))   // SELECT Id, Name, Price only
    .ToListAsync();

A projection also skips tracking (nothing to track: it is not an entity) and avoids pulling large nvarchar(max) or image columns.

3. Read-only means AsNoTracking

For queries that return entities you will not modify, AsNoTracking() removes snapshot and identity-map work (see change tracking). On big result sets it commonly saves a large share of the time and memory.

4. Let the database do the work

Anything after ToList() runs in your process. Compare:

C#
// Bad: loads every order, filters and sums in memory
var total = (await db.Orders.ToListAsync()).Where(o => o.Year == 2026).Sum(o => o.Total);

// Good: one SQL SUM
var total = await db.Orders.Where(o => o.Year == 2026).SumAsync(o => o.Total);

Related habits:

  • Any() instead of Count() > 0: Any stops at the first match (EXISTS).
  • FirstOrDefaultAsync with a filter rather than loading a list and taking [0].
  • Avoid methods the provider cannot translate; EF Core throws an exception rather than silently running them client-side (since 3.0), which protects you. Switching to AsEnumerable() deliberately moves the rest of the query into memory.

5. Paginate properly

Showing page 500 with Skip/Take (offset pagination) makes the database read and throw away 500 pages of rows:

C#
db.Posts.OrderBy(p => p.Id).Skip(10_000).Take(20)    // scans 10,020 rows to return 20

Keyset pagination remembers the last key and asks for "the next 20 after it", which uses an index seek and has constant cost:

C#
db.Posts.Where(p => p.Id > lastSeenId).OrderBy(p => p.Id).Take(20)

Keyset has trade-offs (no "jump to page 50", needs a unique, stable sort key, usually a combination like (CreatedAt, Id)), but it is the right choice for feeds and APIs that page through large data.

C# Stand-in: offset versus keyset pagination

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.

6. Indexes

An index is a sorted structure the database can seek in. A query that filters or sorts on an unindexed column scans the table. Add indexes for columns you filter, join and sort by:

C#
modelBuilder.Entity<Order>().HasIndex(o => o.CustomerId);
modelBuilder.Entity<Order>().HasIndex(o => new { o.CustomerId, o.CreatedAt });   // composite: order matters
modelBuilder.Entity<User>().HasIndex(u => u.Email).IsUnique();

Foreign keys get an index by convention. More indexes slow down writes and use space, so index what your real queries need, guided by execution plans, not by guesses. See migrations for how index changes reach the database.

7. Other quick wins

  • Select only what you need for large text/blob columns; or split them into a separate table or use table splitting.
  • Use async end to end in web apps so threads are freed while waiting for the database (it improves scalability, not single-query speed).
  • Do not create a new DbContext per row; use one per unit of work (or pooling).
  • Batch writes with one SaveChanges and consider ExecuteUpdate (bulk operations).
  • Cache reference data that rarely changes (countries, settings) in memory instead of querying it on every request.

A tuning checklist

  1. Measure first: log SQL, count queries, watch durations.
  2. Remove N+1 loops.
  3. Project to what you need; AsNoTracking for reads.
  4. Filter, sort and aggregate in the database.
  5. Paginate with keyset for deep pages.
  6. Check the execution plan; add the index it asks for.
  7. Re-measure.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which is the best fix for fetching each order's customer inside a loop?

    Moving the work into one query removes the per-row round trips.
  2. 2. Why is Skip(100000).Take(20) slow?

    Offset pagination has to walk past all the skipped rows.
  3. 3. Which is more efficient to test whether any row exists?

    Any translates to EXISTS and stops at the first match.
  4. 4. What does ToQueryString() do?

    It returns the SQL text so you can inspect and tune it.
  5. 5. Which habit moves work from the database into your app and usually hurts performance?

    Materializing first means everything after runs in memory over all the rows.

Summary

  • Always look at the SQL: LogTo, ToQueryString, execution plans.
  • Remove N+1, project to what you need, use AsNoTracking for read-only data.
  • Filter, aggregate and Any/Count in the database; do not materialize first.
  • Use keyset pagination for deep pages and add indexes for the columns your queries use.

Next: Compiled queries, split queries and DbContext pooling