Performance and Troubleshooting

"EF is slow" is almost never the diagnosis — it's the symptom. The skill that separates a working developer from a struggling one is being able to see the SQL, name the anti-pattern, and pick the right fix from a short list.

Step one: read the generated SQL

Never guess. One line in your context options dumps every statement:

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

Or lean on the built-in ILogger integration — EF's SQL flows into whatever logging you already configured (enable EnableSensitiveDataLogging() in dev to see parameter values). For production, SQL Server-side tools — Extended Events or Query Store — show what actually ran and how long it took. Once you can see the SQL, most mysteries solve themselves: you'll spot the 200 identical queries (N+1), the SELECT of 40 columns feeding a 5-column grid, or the query that never filtered at all.

The usual suspects

A short list covers most real incidents:

• Materialize-then-filter — .ToList() before .Where(); the database sent everything.
• N+1 loops — lazy loading inside a loop or a Razor view; fix with Include or projection.
• Missing index — the query is fine, SQL Server scans anyway. Check the execution plan; foreign-key columns and common filter columns need indexes. This fix belongs to the schema (a migration or the DBA), not the C#.
• Non-sargable predicates — Where(p => p.Number.ToString().Contains(x)) or functions wrapped around columns defeat indexes; compare against typed values instead.
• Giant Contains lists — a 5,000-element ids.Contains(p.Id) becomes a monstrous IN clause; chunk it or stage the IDs in a temp table.
• Change-tracker bloat — importing 50,000 rows through one tracked context; use AsNoTracking for reads and batch/recreate contexts for bulk writes. EF Core batches SaveChanges statements automatically, but genuinely bulk loads may still deserve SqlBulkCopy.

When to drop to raw SQL or stored procedures

EF earns its keep on the 95% of queries that are ordinary CRUD. For the rest — heavy reports, set-based updates over millions of rows, queries needing hints or CTEs — drop down deliberately: FromSql/SqlQuery (or ExecuteUpdate/ExecuteDelete for set-based writes), or a stored procedure (often mandatory in agencies where DBAs review production SQL). Keep parameters parameterized — never string-concatenate user input — and keep the raw SQL in the data layer behind the same repository or service interface as everything else, so callers can't tell the difference.

The honest hierarchy of fixes: fix the query shape → fix the indexes → then consider raw SQL. Rewriting a bad LINQ query as a bad stored procedure just moves the slowness somewhere with less type safety.