# Efcore Query Performance

> Optimize EF Core (Npgsql/PostgreSQL) queries in the .NET API — fix N+1, choose tracking modes, use compiled queries, split queries, and avoid translation traps. Use when a query is slow, emits excessive SQL, or causes high DB load. Complements the efcore-patterns scaffolding skill.

- Skill: `atherio-danp/efcore-query-performance` (Agent Skill)
- Install (CLI): `npx skillmds@latest add atherio-danp/efcore-query-performance`
- Raw SKILL.md: https://api.skillmd.com/api/skills/atherio-danp/efcore-query-performance/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: atherio-danp (https://skillmd.com/u/atherio-danp)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/atherio-danp/efcore-query-performance

---


> **Serena MCP is mandatory for C# code.** First call `mcp__serena__initial_instructions` to load the Serena tool manual, then use the Serena tools for ALL `.cs` reading / searching / navigation / creation / editing — prefer symbol navigation (`get_symbols_overview` / `find_symbol` / `find_referencing_symbols`) over whole-file reads. Native `Edit`/`Write` on `.cs` is hook-blocked (the TS/React frontend uses the native tools).

# EF Core query performance — on Npgsql/PostgreSQL

For our stack: **EF Core with the Npgsql provider** (NOT SQL Server). C# (`.cs`) edits via **Serena**. Source of
truth: `.claude/rules/backend/persistence.md` + `docs/projectStandards/backend-architecture.md` §7. This is the
*performance* companion to the `efcore-patterns` scaffolding skill.

## 1. See the SQL first
`optionsBuilder.UseNpgsql(cs).LogTo(Console.WriteLine, LogLevel.Information).EnableSensitiveDataLogging().EnableDetailedErrors();`
(dev only) — or set `Microsoft.EntityFrameworkCore.Database.Command: Information` in `appsettings.Development.json`.

## 2. Fix N+1 (the #1 killer)
- **Eager load:** `.Include(o => o.Items)` instead of lazy-loading in a loop.
- **Split query** when multiple Includes or large child collections would cartesian-explode:
  `.Include(...).AsSplitQuery()`. (Single query when 1 Include or you need read-consistency in one round-trip.)
  On Postgres there's no MARS, so split results are buffered — measure before defaulting to split.
- **Best — project to a DTO:** `.Select(o => new ProjectSummary { ... })` fetches only the columns you need.
  Query/read side should bypass repositories and project to DTOs (per our CQRS read-side rule).

## 3. `AsNoTracking()` on every read-only query
Per-query, or globally `optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)`. Use
`AsNoTrackingWithIdentityResolution()` when the graph references the same row multiple times (de-dupes).

## 4. Compiled queries for hot paths
`private static readonly Func<{{ProjectName}}DbContext, Guid, Task<Project?>> GetProjectById =
EF.CompileAsyncQuery((db, id) => db.Projects.Include(s => s.Items).FirstOrDefault(s => s.Id == id));`

## 5. Translation traps (table)
| Trap | Fix |
|---|---|
| `.ToList()` then `.Where()` | filter in the DB first |
| `.Count() > 0` for existence | `.Any()` |
| `.Select()` after `.Include()` (Include ignored) | use `Select` only, project what you need |
| `string.Contains()` may client-eval | `EF.Functions.Like(...)`, or **`EF.Functions.ILike(...)`** for case-insensitive (Postgres-native — a Npgsql win over SQL Server) |
| `.ToList()` inside a `.Select()` | restructure to avoid nested per-row queries |

## 6. Raw / bulk
- Complex queries LINQ can't express: `FromSqlInterpolated($"... {param}")` (parameterized — **never**
  `FromSqlRaw` with string interpolation: injection). Chain `.AsNoTracking()`.
- Bulk writes: EF Core **`ExecuteUpdateAsync`/`ExecuteDeleteAsync`** (set-based, no change tracking).
- Idempotent inserts: prefer Postgres `INSERT … ON CONFLICT` over load-check-save (per the persistence rule).

## Pitfalls / ours
- Remove `Microsoft.EntityFrameworkCore.Proxies` (lazy loading) — it breeds N+1.
- Tenancy: our **named query filters** (`TenantFilter` + `SoftDeleteFilter`) apply automatically;
  `IgnoreQueryFilters()` is privileged/audited — never a casual perf shortcut.
- DbContext is scoped/per-request; never share across parallel tasks.
- Postgres folds unquoted identifiers to lowercase — EF quotes them; keep our snake_case convention.
- **No new NuGet package** for "performance" (e.g. a bulk-ext library) without Dan's approval.