Diagnosing Slow EF Core Queries with SQL Server Execution Plans

Nine times out of ten, a slow EF Core query is one of three things: client-side evaluation (filtering in memory instead of in SQL), a missing index that shows up as a scan in the execution plan, or a query shape EF Core generates badly — usually a cartesian explosion from stacked Include calls. The SQL Server execution plan tells you which one you have, and you can usually classify it in under ten minutes. Here is the exact workflow I use.

Step 1: Capture the SQL EF Core actually runs

You cannot diagnose LINQ. You diagnose the SQL it becomes. Two built-in tools give you that with zero extra packages.

For a single query in development, ToQueryString() prints the translated SQL without executing it:

var query = db.Invoices
    .Where(i => i.Status == InvoiceStatus.Unpaid && i.DueDate < today)
    .OrderBy(i => i.DueDate)
    .Take(100);

logger.LogDebug("SQL: {Sql}", query.ToQueryString());

For everything a request executes — including the queries you forgot were there — wire up LogTo on the context:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging()); // dev only: exposes parameter values

Two things to check immediately, before you open a plan: how many queries one operation runs (a burst of near-identical statements is an N+1 problem, not a tuning problem), and whether the WHERE clause you wrote in LINQ actually appears in the SQL. If your filter is missing from the SQL, stop — you have failure class one, below.

Step 2: Read the actual execution plan

Paste the captured SQL into SSMS or Azure Data Studio, enable the actual execution plan (Ctrl+M in SSMS), and run it with realistic parameter values — SQL Server sniffs parameters at compile time, so a toy value can produce a plan production never sees. One wrinkle: ToQueryString emits parameters as DECLAREd local variables, which SQL Server does not sniff — add OPTION (RECOMPILE) to the pasted statement when testing, or pull the actual plan from Query Store, to see what production really compiled. Microsoft’s execution plan documentation covers every operator; in practice, four patterns account for nearly every slow query I chase:

• Clustered Index Scan / Table Scan. SQL Server reads the whole table to answer a filtered question. Fine on a 2,000-row lookup table; a fire on a large fact table. This is the signature of a missing or unusable index.
• Index Seek + Key Lookup. The seek found your rows cheaply, but the index doesn’t contain every requested column, so SQL Server pays an extra lookup per row into the clustered index. Cheap at 50 rows, brutal at 500,000 — the fix is a covering index or a narrower Select.
• Sort or Hash Match with a warning triangle. The operator spilled to tempdb because its memory grant was wrong — which usually means the row estimates were wrong. Look upstream for stale statistics or a bad query shape.
• Table Spool / Lazy Spool. SQL Server is caching intermediate rows because the plan replays them — often a sign the query shape (frequently EF-generated nested subqueries or OR predicates) is fighting the optimizer.

Also compare estimated versus actual rows on the plan arrows. When the estimate says 12 rows and the actual is 40,000, every downstream decision — join strategy, memory grants — was made on bad information.

The three failure classes

1. Client-side evaluation: SQL never saw your filter

Modern EF Core throws on most untranslatable expressions instead of silently pulling the table — which is exactly why this failure survives in a sneakier form. Someone “fixes” the translation exception with AsEnumerable() or an early ToListAsync(), and from that point every operator runs in memory:

// Before: fetches EVERY sale, then filters in C#
var lowMargin = (await db.Sales.ToListAsync())
    .Where(s => MarginCalculator.Margin(s) < 0.15m)
    .ToList();

The plan for this looks deceptively clean — a plain scan with no predicate — because the SQL is just SELECT * FROM Sales. The giveaway is in the logs: no WHERE clause, enormous row counts transferred. The fix is expressing the logic in translatable operators:

// After: the margin math translates to SQL, the filter runs in the database
var lowMargin = await db.Sales
    .Where(s => s.Revenue > 0 && s.Revenue - s.Cost < s.Revenue * 0.15m)
    .Select(s => new LowMarginSaleDto(s.Id, s.Sku, s.Revenue, s.Cost))
    .ToListAsync();

The division-free predicate is deliberate: SQL does not guarantee short-circuit evaluation of AND, so a guarded division by Revenue could still hit a divide-by-zero at runtime — the rearranged form cannot.

If the computation genuinely cannot be expressed in LINQ, that is one of the triggers for a SQL view (below) — not a license to filter millions of rows in the app server.

2. The scan that should be a seek: a missing index

var overdue = await db.Invoices
    .Where(i => i.Status == InvoiceStatus.Unpaid && i.DueDate < today)
    .Select(i => new { i.Id, i.CustomerId, i.TotalAmount, i.DueDate })
    .ToListAsync();

The LINQ is fine. The SQL is fine. The plan still shows a Clustered Index Scan reading the entire table to return a few thousand rows, because no index leads with these columns — the predicate is evaluated against every row. The fix is not C#:

CREATE NONCLUSTERED INDEX IX_Invoices_Status_DueDate
    ON dbo.Invoices (Status, DueDate)
    INCLUDE (CustomerId, TotalAmount);

Rerun the same query and the plan flips to an Index Seek with no key lookups — the INCLUDE columns make it covering for exactly this projection. Equality column first, range column second: that ordering lets the seek use both predicates.

3. Query shapes EF generates badly: the cartesian explosion

// Before: one query, JOIN-multiplied rows
var order = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .Include(o => o.StatusHistory)
    .FirstOrDefaultAsync(o => o.Id == id);

Three collection Include calls on one entity produce a single statement whose result set is lines times payments times history entries per order — an order with 40 lines, 3 payments, and 12 status events comes back as 1,440 rows, each duplicating every order column. In the plan you’ll see joins with inflated actual row counts and, on wide rows, sort spills.

// After: one query per collection, no row multiplication
var order = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .Include(o => o.StatusHistory)
    .AsSplitQuery()
    .FirstOrDefaultAsync(o => o.Id == id);

AsSplitQuery() trades one bloated statement for a few precise ones. It is not a blanket default — split queries mean extra round trips and no single-snapshot consistency — but for multi-collection loads it routinely wins. The EF Core performance docs cover the trade-offs well.

When to promote a query to a SQL view

Some queries stop being an ORM problem. My rule: when a query aggregates across several tables, the generated SQL has become unreadable nested subqueries, and the same shape feeds multiple consumers, I move it into a view and let EF Core query the view.

This is not hypothetical for me. On a cost-management SaaS I built for coffee roasters — real-time COGS and profit analytics on ASP.NET Core Web API and SQL Server — the dashboard queries were exactly this shape: multi-table aggregation on hot analytics paths. Optimizing them at the SQL level with tuned queries and views put the performance-critical SQL somewhere a human can read and tune it directly.

Mapping a view in EF Core is a keyless entity:

public sealed class ProductProfitability
{
    public int ProductId { get; init; }
    public string Sku { get; init; } = default!;
    public decimal Revenue { get; init; }
    public decimal Cogs { get; init; }
    public decimal GrossMarginPct { get; init; }
}

// In OnModelCreating:
modelBuilder.Entity<ProductProfitability>(e =>
{
    e.HasNoKey();
    e.ToView("vw_ProductProfitability");
});

And it stays composable — Where, OrderBy, and Take over the view still translate to SQL:

var thinMargins = await db.Set<ProductProfitability>()
    .Where(p => p.GrossMarginPct < 20m)
    .OrderBy(p => p.GrossMarginPct)
    .Take(25)
    .ToListAsync();

Criteria I hold myself to before promoting: the plan shows the ORM-generated shape is genuinely worse than hand-written SQL, not just unfamiliar; at least two consumers need the same shape; and the view earns a name a human can reason about. A view is an interface — don’t mint one per screen.

Projection and tracking hygiene

Two habits prevent a whole class of slow queries from ever existing.

Project read paths with Select. Fetching full entities means every column travels; projecting to a DTO means the SQL asks for exactly what the screen needs — often turning a seek-plus-key-lookup plan into a covered seek without touching a single index.

Mark read-only entity queries AsNoTracking(). Tracking cost lives on the materialization side, not the SQL side — the plan won’t change, but per-request latency and memory will, especially at dashboard row counts. Note that projecting into a DTO is already non-tracked; AsNoTracking() matters when you materialize entities you will never modify.

When the fix is an index, not code

A pattern worth internalizing: if the captured SQL reads like what you would write by hand and the plan still scans, the query is not the problem — the schema is. Selective predicate, reasonable SQL, scan in the plan: that is an index conversation. The green missing-index hint in SSMS is a decent lead but a naive one — it ignores your existing indexes and orders columns mechanically, so treat it as a starting point and weigh the write-path cost of every index you add.

The reverse also holds: an index cannot save an untranslatable filter or a cartesian explosion. That is why the order of this workflow is fixed — SQL first, plan second, classification third. Jumping straight to “add an index” without reading the plan is how tables end up with fourteen indexes and slow writes.

This diagnostic loop matters most on read-heavy surfaces: analytics dashboards, admin grids, anything a user stares at while it loads. If that dashboard is a Blazor app, the hosting model changes how latency is perceived — I’ve written a decision guide on Blazor Server vs WebAssembly vs Hybrid that pairs well with this one. And once the queries are fast, pushing changes instead of polling is the next win — the same thinking behind my SignalR real-time leaderboard write-up.

And if you have inherited an EF Core codebase where every page is slow and nobody knows why, this exact workflow is a large part of the modernization and rescue work I take on.