Skip to content

Database Standard — EF Core & SQL

v1.0 — 2026-06-06 — SQL Server primary; PostgreSQL/Oracle/MongoDB via ExpertGroup.Core providers.

Migrations

  • Never apply migrations at app startup in production. Generate idempotent SQL scripts or migration bundles; apply in the deployment pipeline with a reviewed script.
  • Every migration is code-reviewed including the generated SQL. Never edit an applied migration — add a new one.
  • Keep migrations in a dedicated project for larger solutions (existing *.Dal pattern).

Querying & performance

  • AsNoTracking() for all read-only queries (already the codebase pattern — keep it).
  • Caveat — joins/includes: plain no-tracking skips identity resolution, so when the same parent appears in many rows (e.g. Orders.Include(o => o.Customer)), each row materializes its own copy of the parent. For such queries use AsNoTrackingWithIdentityResolution() — still no change tracking, but shared entities are materialized once and referenced. Plain AsNoTracking() stays the default for flat/projected queries.
  • Project only needed columns (Select); no lazy loading in web apps.
  • ExecuteUpdate/ExecuteDelete for bulk operations; AsSplitQuery() when joins explode cartesian results.
  • AddDbContextPool for high-throughput services; size pools deliberately.

Query efficiency tests (spot bad queries at development time)

Repository/service test projects include query-shape tests alongside functional tests:

  • Inspect generated SQL: assert on query.ToQueryString() for critical queries — catches client evaluation, missing WHERE pushdown, and select-star regressions in review diffs.
  • Count queries (N+1 detector): run the operation under test against real SQL (Testcontainers/SQLite) with a DbCommandInterceptor that records every command; assert executedCommands.Count <= expected. An operation that issues 1 query today and 50 after a refactor fails the build instead of failing in production.
  • Surface EF warnings as failures: configure the test DbContext with ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning, CoreEventId.FirstWithoutOrderByAndFilterWarning)) — cartesian explosions and non-deterministic First become test failures.
  • Time-box hot paths: for known hot queries, assert an upper bound on rows materialized (not wall-clock time — flaky on CI).

✅ DONE (2026-06): ExpertGroup.Core.Testing.Ef now ships QueryCountingInterceptor + EfQueryAssert + UseQueryCounting(). Canonical consumption guide: docs/testing-query-counting.md in the ExpertGroup repo (cross-linked from docs/packages.md) — includes a copyable [Test] using MaxQueriesAsync(counter, 1, ...) as the idiomatic N+1 guard, with real SQL via a SQLite shared in-memory connection. Use the library helper; do not re-implement the interceptor per repo.

Naming (EF Core defaults — don't fight them)

  • PascalCase, singular entity classes; table names per EF convention (consistent within a service).
  • No tbl_/Hungarian prefixes. Indexes/constraints: IX_Table_Column, PK_, FK_Parent_Child.

Data safety

  • Connection strings from configuration/Key Vault — never in code or appsettings committed to git.
  • Destructive migrations (column/table drops) require explicit sign-off and a backup/rollback note in the PR.
  • Steady State (item 9, accepted 2026-06-06): no table may grow without bound — any append-only table (audit, history, message/event logs) gets a retention/archival decision at design time, recorded in the PR or an ADR, not when the disk fills.

Sources: Applying migrations · Tracking · Efficient querying · DbContext config