EF Core Best Practices: A Senior Developer's Guide to DbContext, LINQ, Performance and Production Gotchas

EF Core Best Practices: A Senior Developer’s Guide to DbContext, LINQ, Performance and Production Gotchas

Entity Framework Core makes .NET development productive, but it is not magic. It sits between your C# code and the database, translating queries, tracking objects and generating SQL. That convenience is powerful; it is also why EF Core mistakes can quietly become slow APIs, memory pressure, unexpected SQL and production incidents.

I’m Faz Ahmed, and this is the mindset I encourage: do not ask only, “Does this LINQ query work?” Ask, “What SQL will it produce, how much data will it load, and what happens when real users and real data arrive?”

EF Core is an ORM, not a replacement for database thinking

EF Core is an Object-Relational Mapper. It maps the C# object world to the relational database world.

C# entities and LINQ
        ↓
EF Core mapping, tracking and query translation
        ↓
Tables, rows, joins, indexes and SQL execution plans

It does not remove the need to understand SQL; it makes that understanding more important when performance matters.

DbContext: unit of work, identity map and change tracker

DbContext is more than a class containing DbSets. Think of it as a database-session abstraction, unit of work, identity map and change tracker.

public class AppDbContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
}

When you load an entity, EF Core can track it. When you modify it, EF Core detects the change. SaveChangesAsync() then generates the required SQL.

var customer = await db.Customers.FindAsync(1);
customer!.Name = "Afzal Ahmed";
await db.SaveChangesAsync();

Tracking is useful, but it is not free. Tracking thousands of objects costs memory and CPU. For read-only API endpoints, reports, dashboards and lookups, make the intent explicit:

var customers = await db.Customers
    .AsNoTracking()
    .ToListAsync();

My rule: track entities when you intend to modify them; use no-tracking queries when you are simply reading.

LINQ is not SQL until materialisation

This query does not immediately hit SQL Server:

var query = db.Customers
    .Where(customer => customer.IsActive)
    .OrderBy(customer => customer.Name);

EF Core builds an expression tree. The query executes only when you materialise it.

var customers = await query.ToListAsync();
var firstCustomer = await query.FirstOrDefaultAsync();
var count = await query.CountAsync();

LINQ query → expression tree → SQL translation → database execution
→ returned rows → C# entities or DTOs

IQueryable versus IEnumerable: where is the work happening?

IQueryable means EF Core can still translate work to SQL. Once you materialise into a list, further work happens in memory.

// Bad: load every order, then filter in application memory.
var allOrders = await db.Orders.ToListAsync();
var pendingOrders = allOrders.Where(order => order.Status == OrderStatus.Pending);

// Better: SQL Server filters before data travels over the network.
var pendingOrders = await db.Orders
    .Where(order => order.Status == OrderStatus.Pending)
    .ToListAsync();

Keep filtering, sorting, grouping and pagination in IQueryable until the database has done the work. Materialise only when you need the results.

Projection is the strongest default for API reads

If a screen needs an order summary, do not load a large entity graph and map it afterwards.

var orders = await db.Orders
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.OrderDate)
    .Select(order => new OrderSummaryDto
    {
        Id = order.Id,
        OrderDate = order.OrderDate,
        Status = order.Status,
        TotalAmount = order.TotalAmount,
        CustomerName = order.Customer.Name
    })
    .AsNoTracking()
    .ToListAsync();

This selects only the rows and columns that the API needs. A senior rule worth remembering: do not load an object graph when the screen only needs a view model.

Include is useful—but easy to abuse

Include is appropriate when you genuinely need related entity graphs.

var customer = await db.Customers
    .Include(customer => customer.Orders)
    .FirstOrDefaultAsync(customer => customer.Id == customerId);

The trap is including several collections at once. Joins can multiply rows, creating cartesian explosion and expensive object fix-up in memory. AsSplitQuery() can reduce one giant join into multiple queries, but it also creates more database round trips. Measure the trade-off.

Use Include for entity graphs. Use Select projection for screen and API data.

Avoid the N+1 query problem

N+1 happens when you load a collection, then make a further query for every item.

var customers = await db.Customers.ToListAsync();

foreach (var customer in customers)
{
    customer.Orders = await db.Orders
        .Where(order => order.CustomerId == customer.Id)
        .ToListAsync();
}

That becomes one customer query plus one order query per customer. It may look harmless locally and become painful in production. A projected read model is usually better:

var customers = await db.Customers
    .Select(customer => new CustomerDto
    {
        Id = customer.Id,
        Name = customer.Name,
        OrderCount = customer.Orders.Count
    })
    .AsNoTracking()
    .ToListAsync();

SaveChangesAsync, transactions and concurrency

SaveChangesAsync() reads tracked entity states—Added, Modified, Deleted, Unchanged and Detached—then produces SQL. Do not call it repeatedly inside a loop unless separate transactions are genuinely required.

// Better: one unit of work and fewer database round trips.
db.Orders.AddRange(items);
await db.SaveChangesAsync();

EF Core normally uses a transaction for changes that must succeed together. Use explicit transactions only when one business operation spans several saves, keep them short, and never hold one open while calling a slow external service.

For concurrent editing, use optimistic concurrency with a row version.

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public byte[] RowVersion { get; set; } = Array.Empty<byte>();
}

modelBuilder.Entity<Customer>()
    .Property(customer => customer.RowVersion)
    .IsRowVersion();

When another user changes the row first, EF Core can throw DbUpdateConcurrencyException. The technical exception is only the beginning: deciding whether to reload, merge, retry or warn the user is a business decision.

Migrations and production discipline

Migrations are helpful locally; in production they are change management. Before deployment ask whether a migration locks a large table, is backward compatible, needs data backfilling or can be rolled back.

For zero-downtime changes, use an expand-contract approach:

1. Add a nullable field.
2. Deploy code that writes old and new values.
3. Backfill existing data.
4. Move reads to the new field.
5. Remove the old field later.

Investigate EF Core performance with evidence

Inspect generated SQL before guessing.

var query = db.Orders
    .Where(order => order.Status == OrderStatus.Pending)
    .Select(order => new { order.Id, order.OrderDate, order.TotalAmount });

var sql = query.ToQueryString();

Then use structured logging, Application Insights, SQL Query Store and actual execution plans. Ask: are we loading too many rows or columns? Are read-only queries tracked? Are Includes too broad? Is the result paginated? Do indexes support the generated query? Are we saving in loops?

A production query lab: trace the real work

The fastest way to improve at EF Core is to follow one request from a browser action to the database evidence. Suppose a dashboard needs the latest payable orders for one organisation.

var orders = await db.Orders
    .Where(o => o.OrganisationId == organisationId)
    .Where(o => o.Status == OrderStatus.Payable)
    .OrderByDescending(o => o.CreatedUtc)
    .Select(o => new PayableOrderDto(
        o.Id,
        o.Customer.Name,
        o.TotalAmount,
        o.CreatedUtc))
    .AsNoTracking()
    .Take(50)
    .ToListAsync(cancellationToken);

The code has useful clues: tenant scope is explicit, filtering happens before projection, only the required columns are selected, reads are not tracked, ordering is deterministic, and output is bounded. It is still only a hypothesis until you inspect the generated SQL and its plan against representative data.

What to inspect

Start with the exact SQL and parameters, then ask questions in this order:

1. Is the organisation predicate present in the database query, not added after materialisation?
2. Does the ordering match the expected user experience, including tie-breakers?
3. Does the query return only fields the screen uses?
4. Is the plan seeking a useful index or scanning far more rows than expected?
5. Are estimated rows close to actual rows for common and unusual parameter values?
6. Is a join multiplying results or forcing a costly sort/hash operation?
7. Does the query remain bounded when an organisation has millions of rows?

Junior developer: The endpoint is only 80ms on my machine. Why look at a plan?

>

Senior mentor: Your machine usually has a small, warm database and no competing traffic. The plan tells us the shape of work the server chose. It helps us see a scan, bad join or expensive sort before 80ms becomes a queue of slow requests.

An index is not a magic answer. It consumes storage, changes write cost and may be ignored if the predicate/order does not match it. Propose an index only after naming the query and measuring its workload. For the example, an index beginning with OrganisationId, Status and the requested sort key may help, but the right design depends on selectivity and the actual SQL Server plan.

Pagination is a product decision as well as a query decision

Never let a public endpoint return an unbounded table because the first customer has 20 rows. Cap page size and document defaults. Offset pagination is easy to understand:

var page = await query
    .OrderByDescending(x => x.CreatedUtc).ThenByDescending(x => x.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(cancellationToken);

It can be suitable for modest, browseable pages. Deep offsets require the database to find and discard earlier rows, and concurrently inserted rows can make results shift. For large ordered feeds, use a cursor/keyset based on the last returned stable ordering values:

var next = await db.Orders
    .Where(x => x.OrganisationId == organisationId)
    .Where(x => x.CreatedUtc < cursor.CreatedUtc ||
        (x.CreatedUtc == cursor.CreatedUtc && x.Id < cursor.Id))
    .OrderByDescending(x => x.CreatedUtc).ThenByDescending(x => x.Id)
    .Take(pageSize)
    .Select(x => new OrderSummary(x.Id, x.CreatedUtc, x.TotalAmount))
    .AsNoTracking()
    .ToListAsync(cancellationToken);

Encode and validate cursors; do not trust a client-provided expression or raw SQL fragment. Review which navigation users actually need before selecting a strategy.

Model the domain and database deliberately

EF Core maps what you tell it to map. Conventions are productive, but important constraints should be explicit and enforced by the database as well as the application.

Keys, constraints and relationships protect invariants

Use a primary key and make natural uniqueness rules explicit. If an organisation cannot have two customers with the same external reference, a C# pre-check alone is not safe under concurrency.

modelBuilder.Entity<Customer>(entity =>
{
    entity.HasKey(x => x.Id);
    entity.HasIndex(x => new { x.OrganisationId, x.ExternalReference })
        .IsUnique();
    entity.Property(x => x.DisplayName).HasMaxLength(200).IsRequired();
    entity.HasOne(x => x.Organisation)
        .WithMany()
        .HasForeignKey(x => x.OrganisationId)
        .OnDelete(DeleteBehavior.Restrict);
});

The unique index tells every writer, including future jobs and data fixes, what must remain true. A maximum length aligns validation with storage and prevents silent truncation assumptions. A deliberate delete behaviour prevents a relationship from becoming an accidental cascade across business data.

Junior developer: Should every entity expose all its navigation properties?

>

Senior mentor: Only model relationships that the domain and application genuinely need. Navigation properties are convenient, but they can encourage large Include graphs and hide load behaviour. A foreign-key ID is often enough for a write model; a read projection can shape the display data directly.

Value objects, owned data and conversions

Small concepts such as money, an address or a strongly typed identifier can make invalid states harder to represent. EF Core mappings should preserve their storage and comparison rules. Be cautious with conversions that make a database column hard to query or index; for example, serialising a frequently filtered value into JSON may make a simple predicate expensive.

Store time consistently—usually UTC instants for events—and make the conversion to a customer's local time a deliberate presentation concern. Review currency, precision and rounding with the business rule in mind. A decimal amount needs defined precision and a rounding policy; a generic default is not a financial decision.

DbContext lifetime, tracking and change detection

In an ASP.NET Core application, a scoped DbContext per request is a common fit. It is not thread-safe and should not be shared across concurrent work. Background processing should create a fresh scope/context per work item or unit of work rather than retaining a request context.

public sealed class InvoiceWorker(IServiceScopeFactory scopes)
{
    public async Task ProcessAsync(Guid invoiceId, CancellationToken cancellationToken)
    {
        await using var scope = scopes.CreateAsyncScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        var invoice = await db.Invoices.FindAsync([invoiceId], cancellationToken);
        // Apply one durable work item's changes, then save once.
        await db.SaveChangesAsync(cancellationToken);
    }
}

Do not cache tracked entities beyond their intended unit of work. Their values can become stale, their graph retains memory, and attaching them later can create surprising state changes. If an API receives a DTO, load the current entity with the right authorization scope, apply only allowed fields, then save. Avoid blindly calling Update on client-provided graphs: it can mark fields as modified that the client was never permitted to change.

For genuinely read-heavy queries, AsNoTracking() is a good default. AsNoTrackingWithIdentityResolution() can reduce duplicate instances in some relationship-shaped reads, but it still has a cost and should be measured. Prefer projecting the result the caller needs.

Write operations, transactions and external effects

SaveChangesAsync batches tracked changes where the provider supports it and usually wraps a single save in a transaction. That does not make an email, payment, message broker or HTTP call part of the same atomic unit. Never hold a database transaction open while awaiting a slow external service.

For a reliable cross-boundary operation, record intent and an outbox message in the database transaction, then let a worker publish/retry the message. Give the operation an idempotency key and record outcomes so duplicate delivery is safe. The exact pattern varies, but the review question remains constant: after any failure point, what durable state tells us what happened and what to do next?

Concurrency needs a user-facing policy

Row versions detect a conflict; they do not resolve it. A profile editor may reload and ask the user to reconcile changes. A stock reservation may reject the request. A background aggregate update may retry after reloading. Choose based on the invariant and side effect, not on what is easiest to catch.

try
{
    await db.SaveChangesAsync(cancellationToken);
}
catch (DbUpdateConcurrencyException)
{
    return Results.Conflict(new ProblemDetails
    {
        Title = "The record changed while you were editing it.",
        Status = StatusCodes.Status409Conflict
    });
}

Log enough safe context to investigate a conflict, but do not leak another user's data in the response. Test the conflict with two independent contexts against a real relational provider rather than an in-memory substitute that does not mirror database behaviour.

Core advice from Faz Ahmed

Diagnose before you optimise

Performance work starts with a symptom that can be observed: a slow route, connection-pool pressure, database CPU, timeouts, a growing queue or a memory spike. “EF Core is slow” is not a diagnosis. Capture the route, correlation ID, query duration, rows returned, parameter shape and concurrent load before changing code.

A disciplined investigation loop

1. Reproduce the behaviour with a safe, representative dataset.
2. Identify the request and query through application and database telemetry.
3. Inspect generated SQL, actual plan, duration, reads and waits.
4. Form one hypothesis: over-fetching, N+1, missing index, lock contention, parameter skew or excessive tracking.
5. Make the smallest change that tests the hypothesis.
6. Re-measure both the target query and the write/operational cost.
7. Keep the evidence with the change so future maintainers know why it exists.

Sensitive data must not be casually enabled in production logs. Use parameter logging only in tightly controlled development or incident conditions, and sanitize identifiers/secrets. A correlation ID plus query timing and shape is usually more useful than copying customer data into a log.

Common symptoms and likely questions

Do not introduce AsSplitQuery, compiled queries, raw SQL or a cache merely because a blog listed them as tricks. Each has trade-offs. A cache introduces freshness and invalidation concerns. Raw SQL introduces mapping and security responsibilities. A compiled query may help a hot path but does not repair a query that returns too much data.

Testing EF Core behaviour honestly

Unit tests are excellent for domain rules that do not need a database. They are not enough to prove relational behaviour such as translation, unique constraints, transactions, concurrency or provider-specific SQL. Use a small number of integration tests against the same database engine or a close, disposable environment for the behaviours that matter.

Test the important contract

For a tenant-scoped query, test that a record in another organisation cannot be returned. For a unique external reference, run two independent contexts and confirm that the constraint protects the invariant. For a migration, apply it to a copy or production-shaped schema and verify timing, compatibility and backfill. For a concurrency token, reproduce an update from two contexts and verify the chosen customer response.

await using var first = CreateDbContext();
await using var second = CreateDbContext();

var a = await first.Customers.SingleAsync(x => x.Id == customerId);
var b = await second.Customers.SingleAsync(x => x.Id == customerId);

a.Name = "First change";
await first.SaveChangesAsync();

b.Name = "Second change";
await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
    () => second.SaveChangesAsync());

Keep tests independent: create known data, act through the intended boundary, assert observable outcome and clean up through an isolated database strategy. Tests that share mutable state become intermittent and erode trust.

Junior developer: Can I just use an EF Core in-memory provider for all repository tests?

>

Senior mentor: It is useful for some fast tests, but it is not a relational database and may accept/query behaviours differently from SQL Server. Use it only when that difference does not matter. For the query and constraint rules we depend on in production, include relational integration tests.

A migration and release playbook

Treat a migration as a sequence of deployable, observable states. Before merging, write the answers: What existing application versions will coexist? Does the migration lock or scan a large table? How is data backfilled? What is the stop condition? What happens if deployment is paused halfway through?

For a large backfill, avoid one enormous transaction. Use controlled batches, a resumable marker, metrics for remaining work and a rate that respects live traffic. Test rerunning the job; deployment automation and operators will eventually retry something.

Release 1: add nullable column/index, ship readers tolerant of old data.
Release 2: deploy writers for both representations.
Backfill: process batches; record progress and failures.
Release 3: make new representation authoritative after verification.
Release 4: remove old reader/writer and, after a window, old column.

Database rollback is often forward repair, not Down() migration magic. Once data has been transformed or a new version has written it, reversing schema can lose information or break running instances. Plan to disable the feature, restore compatible behaviour, reconcile data and take a deliberate next migration.

A senior review checklist for EF Core changes

• Is the request/tenant/authorization boundary applied in the query itself?
• Does the query project only what the caller needs and use no tracking for reads?
• Are filters, ordering and paging database-side and bounded?
• Have generated SQL and a representative plan been inspected for meaningful paths?
• Are constraints, precision, nullability and delete rules explicit where they express invariants?
• Is the context lifetime safe for requests, background work and concurrency?
• Does a write avoid over-posting, long transactions and uncoordinated external calls?
• Is conflict handling a defined business experience rather than an unhandled exception?
• Are migrations compatible, rehearsed and observable at production scale?
• Do tests use a relational provider for relational claims?

A short mentoring exercise

Choose one endpoint in your application that returns a list. Write down its expected maximum result size, its ordering, the fields actually rendered, tenant/permission boundary and the index you expect it to use. Then inspect the SQL and plan. If the evidence differs from the prediction, you have found the best possible learning opportunity: a concrete gap between elegant C# and real database work.

Use the related guides on SQL Server relational design, clean architecture with CQRS and EF Core, code review and async C# to practise the surrounding decisions. EF Core is strongest when it is treated as part of a complete system, not a layer that hides the database.

Read and write models do different jobs

The same entity type is not automatically the right shape for every operation. A command needs to protect an invariant and record a meaningful state change. A screen needs a small, stable representation that is convenient to render. Trying to satisfy both with one large graph commonly produces too many Include calls, accidental tracking and APIs that expose fields they should own internally.

A command path

Consider an order cancellation. Load only the aggregate/data needed to decide whether cancellation is allowed, apply the domain rule, add an audit or outbox record, and save once.

var order = await db.Orders
    .SingleOrDefaultAsync(x => x.Id == command.OrderId &&
        x.OrganisationId == actor.OrganisationId, cancellationToken);

if (order is null) return Results.NotFound();
if (!order.CanBeCancelled(actor)) return Results.Forbid();

order.Cancel(command.Reason, clock.UtcNow);
db.OutboxMessages.Add(OutboxMessage.For(new OrderCancelled(order.Id)));
await db.SaveChangesAsync(cancellationToken);
return Results.Accepted();

The command does not accept an Order graph from the browser. That would make it too easy to over-post price, organisation, status or audit fields. It loads the authoritative record under the right scope and applies a narrow input model. The database constraint and transaction protect facts the application cannot safely protect alone.

A query path

The order-history screen does not need the methods and relationships of an aggregate. It needs a readable projection with explicit paging and a contract that can evolve without making the persistence entity public.

var history = await db.Orders
    .Where(x => x.OrganisationId == organisationId)
    .OrderByDescending(x => x.CreatedUtc).ThenByDescending(x => x.Id)
    .Select(x => new OrderHistoryItem(
        x.Id, x.Reference, x.Status, x.TotalAmount, x.CreatedUtc,
        x.Customer.DisplayName))
    .AsNoTracking()
    .Take(50)
    .ToListAsync(cancellationToken);

This separation is not ceremony for its own sake. It makes authorization, fetching cost, data ownership and API compatibility visible. A simple application can use the principle without creating a dozen abstractions.

Handling bulk work without exhausting the database

Bulk updates and imports are where a pleasant local EF Core loop can become an incident. First decide whether every row needs domain behaviour in memory. If not, a set-based database update may be clearer and far more efficient.

var changed = await db.Orders
    .Where(x => x.OrganisationId == organisationId)
    .Where(x => x.Status == OrderStatus.Expired)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(x => x.Status, OrderStatus.Cancelled)
        .SetProperty(x => x.CancelledUtc, clock.UtcNow), cancellationToken);

This avoids materialising every order. It also bypasses the normal change tracker and entity callbacks, so review it carefully: does the command need per-record audit entries, events, concurrency policy or business validation? If yes, process in bounded batches or use a purpose-built database operation that preserves the required invariant.

For imports, validate rows before writing, define duplicate behaviour, bound batch size, record progress and make reruns safe. Do not start hundreds of contexts/commands in parallel because the machine has many cores. The bottleneck is often connection capacity, log throughput, locking or an external dependency. Measure, set a deliberate concurrency limit, and provide pause/cancel/recovery behaviour.

Junior developer: Would SaveChangesAsync in a loop be easier to recover if one row fails?

>

Senior mentor: It may be, but it also creates many round trips and partial state. Choose a batch and error policy deliberately: validate all then commit, commit small resumable chunks, or route failures to a review queue. “Easier” should refer to the required recovery story, not only the first implementation.

Security and data governance in persistence code

EF Core parameterises normal LINQ values, which helps avoid injection. Do not defeat that protection by concatenating user input into raw SQL, dynamic order clauses or identifiers. If raw SQL is justified, keep the SQL static where possible, parameterise values, whitelist any dynamic identifier, and give the code a focused test and reviewer scrutiny.

Tenant filtering deserves the same attention as authentication. A repository that takes only id and returns an entity may be safe in one internal job and dangerous in an API. Make the organisation/ownership boundary explicit at the entry point and test cross-tenant attempts. Global query filters can help for pervasive rules, but review administrator/reporting/background paths carefully so a hidden filter neither leaks data nor silently excludes required records.

Classify sensitive data before making it convenient to query or log. Passwords, access tokens and secrets do not belong in normal application logs or broad read models. Personal data has retention, access and deletion implications. Encryption, hashing, column permissions, audit and backup behaviour should be based on the actual risk and regulatory needs, not a one-line ORM setting.

A realistic incident walkthrough

An order dashboard reports occasional 20-second timeouts after a new “include customer details” change. The unhelpful response is to add retries. The useful investigation is:

1. Find the affected route, correlation IDs and database duration in telemetry.
2. Capture generated SQL for the slow request shape, with sensitive values removed.
3. Compare actual rows/read counts and plan to a normal request.
4. Notice a collection Include joined orders, lines, discounts and payments into a multiplied result.
5. Replace the graph load with a narrow dashboard projection, or intentionally split/batch the required reads.
6. Check result count, allocations, SQL duration and UI behaviour under representative data.
7. Add an integration/performance regression check or review note for the workload.

The lesson is not “never use Include.” The lesson is to make the result shape match the customer need and prove it under a credible workload. Retries would have increased pressure on an already expensive query.

Your next practical exercise

Pick one tracked read in your codebase. Explain why it needs tracking. If it does not, convert it to a projection or no-tracking query, inspect the generated SQL and run the relevant tests. Then pick one write endpoint and list the fields a malicious client could try to over-post. These two small exercises build more durable judgement than memorising every EF Core API.

Establish team defaults that make safe work easy

Good EF Core usage should not depend on every developer remembering every warning. Agree a few defaults, document their exceptions, and automate the routine checks.

Useful defaults

• Use a scoped context per web request and a fresh scope per background work item.
• Make read-only endpoint queries no-tracking and project their response model.
• Require stable ordering and a maximum page size for list endpoints.
• Treat tenant/ownership filtering as part of every externally reachable query.
• Use a database constraint for an invariant that concurrent writers must not break.
• Pass cancellation tokens through database calls and stop work safely on shutdown.
• Review generated SQL and a plan for changed or expensive high-volume paths.
• Rehearse significant migrations and use compatibility windows for live systems.

Each default has exceptions. An edit screen needs tracking; an administrator's cross-tenant report has a different access model; a small internal table may not require cursor pagination. The point is to make exceptions explicit and reviewable instead of accidental.

A compact pull request description

For a meaningful persistence change, authors can make review faster with this template:

Outcome: show the latest payable orders for an organisation.
Data boundary: organisation predicate applied in query; policy tested at API boundary.
Query shape: projection/no tracking, ordered by CreatedUtc + Id, capped at 50.
Database evidence: SQL and representative plan attached; index considered.
Write/migration: no schema change / compatible migration and rollback plan linked.
Failure behaviour: empty page, invalid cursor and database timeout are handled.
Tests: cross-tenant, ordering/cursor and relational integration tests added.

This is an engineering map, not paperwork. It lets a reviewer focus on the important unanswered question rather than reverse-engineering intent from a diff.

Debugging surprises without creating a second incident

When a production issue involves the database, urgency can lead to unsafe fixes. Start by preserving evidence and reducing harm. If a query is saturating the database, temporarily disable a new feature or lower a cohort before increasing connection pools, retries or logging volume. A retry storm can turn a slow query into an outage.

Use read-only diagnostics first: application traces, query timings, database Query Store/history, execution plans and waits. Verify the tenant and time window before running any data inspection. If a data repair is necessary, use a reviewed, scoped script with a backup/restore or compensating plan, a count of expected rows and an audit of what changed. Never paste an unbounded update into production because the model “looks obvious.”

A safe diagnostic question set

What changed, and when did the symptom start?
Which customers/tenants are affected?
Which route, SQL command and plan are responsible?
Is the database CPU, I/O, lock, log or connection capacity constrained?
Can we reduce entry safely while we investigate?
What evidence proves a proposed fix improves the target without harming writes?
How will we confirm recovery and prevent recurrence?

This approach applies whether the cause is EF Core translation, a migration, an index regression or a workload that outgrew its earlier assumptions.

Questions a junior engineer should ask early

Junior developer: How do I know whether a repository should return IQueryable?

>

Senior mentor: Be cautious. Returning IQueryable leaks query composition and database behaviour across a boundary. It can be useful inside a tightly controlled query layer, but a public service contract is usually clearer when it accepts explicit filters and returns an explicit result. That makes authorization, paging and cost easier to reason about.

Junior developer: Is a generic repository always a clean architecture requirement?

>

Senior mentor: No. DbSet and DbContext already provide repository and unit-of-work-like capabilities. Add an abstraction when it expresses a useful domain/query boundary or makes testing/design clearer—not merely to wrap every EF Core method in another name.

Junior developer: Should I call SaveChangesAsync in every service method?

>

Senior mentor: Decide where the unit of work ends. One business command often coordinates several changes and should save atomically once. A lower-level helper that saves unexpectedly makes transactions and tests harder to understand.

Junior developer: Is raw SQL a failure?

>

Senior mentor: No. It can be the clearest choice for a specialised query or operation. Treat it as a deliberate database contract: parameterise it, test it with the real provider, document its performance assumptions and keep its result mapping narrow.

Closing practice plan

In your first week, inspect generated SQL for one list endpoint and one edit path. In week two, add a relational integration test for an invariant that matters. In week three, walk through a migration as if it were a production release: compatibility, backfill, pause and recovery. In week four, pair with a teammate to review a query plan and explain the result shape in plain English.

That progression turns EF Core from a convenient API into a tool you can reason about under load, during failure and across years of change.

Reference architecture walkthrough

Use this small workflow to connect the ideas in the guide. A user opens an order list, chooses one order and requests cancellation.

List request → authorised, tenant-scoped projection → paged response
Cancel command → authoritative entity + policy → state change + outbox → SaveChanges
Worker → durable message → idempotent external notification → audit/telemetry
UI → accepted/pending state → refresh/status → understandable final outcome

At each arrow, ask what is trusted, what can fail and what is observable. The list query needs bounded projection and a plan. The command needs authorization and a concurrency policy. The transaction needs a durable record of the follow-up action. The worker needs retry behaviour that does not repeat a side effect. The UI needs to represent progress honestly rather than claiming completion when only acceptance occurred.

This is why database code cannot be reviewed in isolation. A perfect LINQ projection does not repair an unsafe command; a row version does not repair an email sent twice; a successful migration does not explain an operation that support cannot trace.

From requirement to evidence

Suppose the requirement says: “An administrator can cancel an unpaid order.” Translate it into engineering statements:

By the time code is written, the test plan and review plan are largely visible. This is the senior habit: turn an appealing feature sentence into observable claims before production turns ambiguity into a support case.

Mentoring checkpoint

Before you consider an EF Core feature finished, be able to answer these in your own words:

1. What SQL is generated, and which fields/rows are returned?
2. What makes the query safe at the largest expected tenant and page depth?
3. What constraint protects the important invariant under concurrent writers?
4. Which fields can the client change, and which are loaded authoritatively?
5. How does a conflict, timeout or duplicate operation appear to the caller?
6. What migration state can coexist with old and new application versions?
7. Which relational test proves the risky assumption?
8. Which trace, metric or audit record helps an authorised operator recover?

If any answer is unclear, that is not a reason to guess. It is the next focused question for design, testing or measurement.

The habits that prevent most EF Core incidents

Most costly mistakes are not exotic bugs. They are ordinary decisions made without checking the database consequence: returning an unbounded list, tracking a report, filtering after ToListAsync, adding several collection includes, saving inside a loop, applying a destructive migration beside a code change, or trusting the browser to define ownership.

Build a pause into each of those moments. Before materialising, ask how many rows and columns this could return. Before adding an Include, ask whether the consumer needs entities or a view. Before creating an index, ask which measured query and sort it serves. Before a migration, ask which deployed versions must coexist. Before handling a client DTO, ask which values must be read from the authoritative database record.

A final review conversation

Junior developer: I have followed the patterns in this guide. How can I be sure the feature is ready?

>

Senior mentor: You cannot prove every future condition. You can make the important assumptions visible and testable. Show the query shape, test the boundary and invariant with a relational database, rehearse the migration, explain failure recovery, and watch the release. That is more valuable than claiming a pattern makes a feature automatically safe.

The best evidence is proportional. A small lookup may need a clear projection and an integration test. A high-volume financial write may need a compatibility plan, concurrency decision, plan review, audit, idempotency and a rollout dashboard. Mature engineering adjusts effort to consequence rather than applying the same ritual to every line of code.

Keep learning from the running system

After release, compare the actual query duration, rows and error rate with your prediction. Read a sample of slow-query traces and migration logs. When a support question reveals missing audit context, improve the operation rather than relying on an operator to remember a database query. These feedback loops turn production from a place that teaches through pain into a source of measured engineering knowledge.

Query patterns worth recognising

Some LINQ is expressive but hides a very different database operation than the author intended. Learn to notice these patterns during design and code review.

Existence, counts and terminal operations

Use the smallest query that answers the question. If the requirement is “does this customer have an unpaid order?”, AnyAsync communicates that intent better than loading rows or counting every match.

var hasUnpaid = await db.Orders.AnyAsync(x =>
    x.CustomerId == customerId && x.Status == OrderStatus.Unpaid,
    cancellationToken);

Similarly, SingleAsync is a useful assertion only when the data model guarantees exactly one result. If that guarantee matters, back it with a unique database constraint. FirstOrDefaultAsync without a stable order leaves the chosen row to database implementation details; add an order when “first” has business meaning.

Aggregates belong close to the data

Calculate sums, counts and averages in SQL when the database can do it. Project only the aggregate needed and consider null/empty-set semantics deliberately.

var summary = await db.Orders
    .Where(x => x.OrganisationId == organisationId)
    .GroupBy(x => x.Status)
    .Select(g => new { Status = g.Key, Count = g.Count(), Total = g.Sum(x => x.TotalAmount) })
    .AsNoTracking()
    .ToListAsync(cancellationToken);

Check that the resulting query and index strategy are appropriate. A dashboard with several independent aggregates may be clearer as several well-indexed queries, a specialised reporting model or precomputed data; one giant LINQ expression is not automatically the fastest solution.

Dates, strings and translated functions

Avoid assumptions that every .NET method translates identically to every provider. A date conversion or case-insensitive comparison can change whether an index is useful. Keep business time boundaries explicit, use parameter values rather than string-building predicates, and inspect SQL when a function is central to a high-volume query. Collation and time-zone rules are database/product decisions, not cosmetic details.

Context pooling and connection pooling are different

Connection pooling is normally provided by the database driver and reuses physical database connections. DbContext pooling can also reuse context instances. It can reduce allocation overhead for suitable high-throughput applications, but it adds a responsibility: any per-request state must be reset correctly, and a context still cannot be shared concurrently.

Do not turn pooling on because it sounds like a performance upgrade. Establish the workload, measure allocation/throughput, understand tenant or interceptor state, and test disposal/error paths. A simple scoped context is often the clearest and safest default. Optimise a measured bottleneck, then document why the non-default lifetime is safe.

Interceptors and cross-cutting behaviour

Interceptors can be useful for audit stamping, diagnostics or controlled policy enforcement. They can also make writes surprising when hidden logic changes data or calls external services. Keep an interceptor deterministic, fast and testable; never let it silently create a network dependency inside SaveChanges. Make its ordering, failure behaviour and impact on background work visible to reviewers.

EF Core is a powerful .NET ORM, but elegant C# can hide expensive SQL. The senior mindset is simple:

Understand the SQL EF Core generates.
Fetch only what the user needs.
Track only what you intend to change.
Keep database work in the database.
Use projection for read models.
Use Include deliberately.
Avoid N+1 queries.
Paginate large results.
Respect concurrency.
Treat migrations as production engineering.
Measure before optimising.

The difference between someone who uses EF Core and someone who understands EF Core is the ability to predict what the database will do before production has to teach the lesson.

---

EF Core Production Practices for Senior Engineers

1. Defining the DbContext boundary

Defining the DbContext boundary matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Begin with one representative user or system scenario. Architecture becomes useful when it explains real decisions rather than presenting a catalogue of patterns.

Junior developer asks: “When is the DbContext boundary ready for production?”

Practical exercise

Select one existing feature and create a one-page review for defining the DbContext boundary. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

2. Configuring entity mappings explicitly

Configuring entity mappings explicitly matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Make ownership explicit before selecting a library. Many production defects arise because two components both assume the other owns state, validation or recovery.

Junior developer asks: “When is configuring entity mappings explicitly ready for production?”

Practical exercise

Select one existing feature and create a one-page review for configuring entity mappings explicitly. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

3. Choosing tracking behaviour

Choosing tracking behaviour matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Prefer an observable thin slice over speculative infrastructure. Real behaviour gives the team evidence with which to refine the design.

Junior developer asks: “When is choosing tracking behaviour ready for production?”

Practical exercise

Select one existing feature and create a one-page review for choosing tracking behaviour. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

4. Projecting only required data

Projecting only required data matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Optimise for change and diagnosis. A solution is durable when another engineer can locate the rule, verify it and recover safely from failure.

Junior developer asks: “When is projecting only required data ready for production?”

Practical exercise

Select one existing feature and create a one-page review for projecting only required data. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

5. Avoiding N+1 queries

Avoiding N+1 queries matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Begin with one representative user or system scenario. Architecture becomes useful when it explains real decisions rather than presenting a catalogue of patterns.

Junior developer asks: “When is avoiding n+1 queries ready for production?”

Practical exercise

Select one existing feature and create a one-page review for avoiding n+1 queries. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

6. Understanding query translation

Understanding query translation matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Make ownership explicit before selecting a library. Many production defects arise because two components both assume the other owns state, validation or recovery.

Junior developer asks: “When is understanding query translation ready for production?”

Practical exercise

Select one existing feature and create a one-page review for understanding query translation. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

7. Using compiled queries selectively

Using compiled queries selectively matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Prefer an observable thin slice over speculative infrastructure. Real behaviour gives the team evidence with which to refine the design.

Junior developer asks: “When is using compiled queries selectively ready for production?”

Practical exercise

Select one existing feature and create a one-page review for using compiled queries selectively. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

8. Applying pagination correctly

Applying pagination correctly matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Optimise for change and diagnosis. A solution is durable when another engineer can locate the rule, verify it and recover safely from failure.

Junior developer asks: “When is applying pagination correctly ready for production?”

Practical exercise

Select one existing feature and create a one-page review for applying pagination correctly. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

9. Managing transactions

Managing transactions matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Begin with one representative user or system scenario. Architecture becomes useful when it explains real decisions rather than presenting a catalogue of patterns.

Junior developer asks: “When is managing transactions ready for production?”

Practical exercise

Select one existing feature and create a one-page review for managing transactions. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

10. Handling optimistic concurrency

Handling optimistic concurrency matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Make ownership explicit before selecting a library. Many production defects arise because two components both assume the other owns state, validation or recovery.

Junior developer asks: “When is handling optimistic concurrency ready for production?”

Practical exercise

Select one existing feature and create a one-page review for handling optimistic concurrency. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

11. Designing idempotent writes

Designing idempotent writes matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Prefer an observable thin slice over speculative infrastructure. Real behaviour gives the team evidence with which to refine the design.

Junior developer asks: “When is designing idempotent writes ready for production?”

Practical exercise

Select one existing feature and create a one-page review for designing idempotent writes. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

12. Choosing loading strategies

Choosing loading strategies matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Optimise for change and diagnosis. A solution is durable when another engineer can locate the rule, verify it and recover safely from failure.

Junior developer asks: “When is choosing loading strategies ready for production?”

Practical exercise

Select one existing feature and create a one-page review for choosing loading strategies. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

13. Using value converters carefully

Using value converters carefully matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Begin with one representative user or system scenario. Architecture becomes useful when it explains real decisions rather than presenting a catalogue of patterns.

Junior developer asks: “When is using value converters carefully ready for production?”

Practical exercise

Select one existing feature and create a one-page review for using value converters carefully. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

14. Managing interceptors

Managing interceptors matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Make ownership explicit before selecting a library. Many production defects arise because two components both assume the other owns state, validation or recovery.

Junior developer asks: “When is managing interceptors ready for production?”

Practical exercise

Select one existing feature and create a one-page review for managing interceptors. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

15. Creating safe migrations

Creating safe migrations matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Prefer an observable thin slice over speculative infrastructure. Real behaviour gives the team evidence with which to refine the design.

Junior developer asks: “When is creating safe migrations ready for production?”

Practical exercise

Select one existing feature and create a one-page review for creating safe migrations. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

16. Deploying schema changes gradually

Deploying schema changes gradually matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Optimise for change and diagnosis. A solution is durable when another engineer can locate the rule, verify it and recover safely from failure.

Junior developer asks: “When is deploying schema changes gradually ready for production?”

Practical exercise

Select one existing feature and create a one-page review for deploying schema changes gradually. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

17. Testing against a real provider

Testing against a real provider matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Begin with one representative user or system scenario. Architecture becomes useful when it explains real decisions rather than presenting a catalogue of patterns.

Junior developer asks: “When is testing against a real provider ready for production?”

Practical exercise

Select one existing feature and create a one-page review for testing against a real provider. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

18. Observing database performance

Observing database performance matters in an ASP.NET Core system using EF Core to protect transactional business data under realistic concurrency and load. The objective is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely. Make ownership explicit before selecting a library. Many production defects arise because two components both assume the other owns state, validation or recovery.

Junior developer asks: “When is observing database performance ready for production?”

Practical exercise

Select one existing feature and create a one-page review for observing database performance. Include the scenario, invariant, dependency diagram, failure matrix, tests, performance evidence, security boundary, telemetry and rollback. Ask a teammate unfamiliar with the implementation to trace the behaviour. Confusion in that review is valuable evidence that the model or documentation needs improvement.

Final Perspective

The practices in this guide form a feedback loop: understand the outcome, model ownership and boundaries, deliver a narrow path, test important failure, observe the real system and refine the design. Apply the relevant chapters according to risk. The goal throughout is correct persistence behaviour with efficient queries, explicit transactions and database changes that can be deployed safely.