atherio-danp Serena MCP is mandatory for C# code. First call
mcp__serena__initial_instructionsto load the Serena tool manual, then use the Serena tools for ALL.csreading / searching / navigation / creation / editing — prefer symbol navigation (get_symbols_overview/find_symbol/find_referencing_symbols) over whole-file reads. NativeEdit/Writeon.csis 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 — neverFromSqlRawwith string interpolation: injection). Chain.AsNoTracking(). - Bulk writes: EF Core
ExecuteUpdateAsync/ExecuteDeleteAsync(set-based, no change tracking). - Idempotent inserts: prefer Postgres
INSERT … ON CONFLICTover 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.