/optimizing-ef-core-queries

name: optimizing-ef-core-queries
description: "Optimize and improve the performance of slow Entity Framework Core (EF Core) queries: make them generate less SQL, make fewer database round-trips, and return results faster. Use whenever an EF Core or DbContext query or data-access path is slow or should be made faster — whether or not EF Core owns the database schema. For EF Core, not Dapper or raw ADO.NET."
license: MIT

Optimizing EF Core Queries

Diagnose and fix slow Entity Framework Core (EF Core) queries. Start from the generated SQL/logs, apply the smallest change that removes the bottleneck, and confirm the fix by re-reading the SQL and the query count. Prefer changes that reduce round-trips, duplicated rows, scans, or per-call translation cost over micro-optimizations. Apply one change at a time and re-measure.

When to Use

• EF Core queries are slow or emit far more SQL statements than expected
• The same query repeats once per row (N+1 / lazy loading)
• Multiple collection `Include`s blow up or duplicate rows
• Deep pages slow down as `Skip` grows, or bulk updates load rows just to modify them
• A filtered/sorted query scans **even though the column is indexed**, or a filtered/sorted column has no supporting index
• A hot, frequently-executed query pays EF Core's LINQ-translation cost on every call

When Not to Use

• **The code uses Dapper or raw ADO.NET, not EF Core.** Answer the SQL/indexing/query-plan question directly; do not introduce a `DbContext` or recommend `AsNoTracking`, `Include`, `AsSplitQuery`, or other EF Core APIs.

First: capture the generated SQL

You cannot optimize what you cannot see. Turn on command logging and read the SQL and query count before changing anything:

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
// or set "Microsoft.EntityFrameworkCore.Database.Command": "Information" in appsettings.json

Tag a query with `.TagWith("...")` to find it in the log. Count how many statements a slow operation runs, and how many rows each returns, before and after each change.

Fixes

Keep predicates sargable — never wrap an indexed column in a function

An index can only be used when the indexed column appears **bare** on one side of the comparison. Wrapping it in a function or arithmetic — `CreatedAt.Year == y`, `CreatedAt.Date == d`, `ToLower(Name) == n`, `Price * 1.1 > x`, or a leading-wildcard `LIKE '%foo'` — forces a per-row computation the index cannot satisfy, so the query **scans the whole table even though the index exists**. Adding another index changes nothing. Rewrite the predicate so the column stays bare, usually as a half-open range:

// Non-sargable: a function is computed for every row → full scan
db.Logs.Where(l => l.CreatedAt.Year == year);

// Sargable: bare column compared to constants → index seek
var start = new DateTime(year, 1, 1);
db.Logs.Where(l => l.CreatedAt >= start && l.CreatedAt < start.AddYears(1));

The same rule covers several common shapes:

• **Case-insensitive text** — compare a stored normalized column instead of `ToLower(...)`/`ToUpper(...)`.
• **Computed expressions** — compare against the precomputed constant, not `column * k > x`.
• **Converting the column to another type** — a predicate over `column.ToString()` (for example matching the *text form* of a number or date, `total.ToString().StartsWith(p)`) applies a function to every row and often can't be translated to SQL at all, forcing a client-side evaluation that pulls the whole table into memory. Filter on the typed column with a real comparison or range instead.
• **Substring search** — `name.Contains(term)` becomes an unanchored `LIKE '%term%'` that can't seek an index and scans the table; a trailing-wildcard prefix (`name.StartsWith(term)` → `'term%'`) can seek. Anchor the search when a prefix match is acceptable — this changes which rows match, so confirm the behavior first — and put real substring or fuzzy search behind a full-text index on large tables.

**Verify:** the plan shows a seek/index instead of a scan and duration drops. If the column genuinely has no index, add one (see below) — but only after the predicate is sargable.

Compile hot, frequently-executed queries

On a very hot path that runs the *same* query shape thousands of times over a reused context, EF Core re-parses the LINQ expression tree and probes its query cache on every call. When the query is already minimal (an indexed lookup or a small projection) and read-only tweaks such as `AsNoTracking` buy nothing, that per-call translation is the remaining cost. Compile the query once with `EF.CompileQuery` / `EF.CompileAsyncQuery` and reuse the delegate:

private static readonly Func<AppDbContext, int, ProductListItem> GetProduct =
    EF.CompileQuery((AppDbContext db, int id) =>
        db.Products.Where(p => p.Id == id)
                   .Select(p => new ProductListItem(p.Id, p.Name, p.Price))
                   .First());

public ProductListItem Lookup(AppDbContext db, int id) => GetProduct(db, id);

The delegate is `static` (compiled once) and takes the `DbContext` plus each parameter as arguments. Use it for endpoints or loops that execute one query shape at very high frequency; it does nothing for one-off queries.

**Verify:** the hot loop's mean time drops with identical results.

Remove N+1 and avoid lazy loading

The same `SELECT` repeated once per row (a navigation accessed inside a loop) is an N+1. Load the related data in one round-trip — project the aggregates with `Select`, or eager-load with `Include`:

var summaries = await db.Orders
    .Select(o => new OrderSummary(o.Id, o.Items.Count, o.Items.Sum(i => i.Price)))
    .ToListAsync();

Prefer projection or `Include` over lazy loading: lazy loading is a leading cause of N+1 and forces synchronous I/O. In server apps, don't enable `Microsoft.EntityFrameworkCore.Proxies` or mark navigations `virtual` for lazy loading.

**Ver