Optimizing EF Core Queries

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 Includes 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);