EF Core Performance Optimization: Fixing N+1 Queries, Slow SQL, and Memory Problems

1 Introduction to Enterprise EF Core Performance & Architecture

An order-history endpoint passes functional tests, then struggles when a large customer opens it. The database receives dozens of queries, application memory climbs, and adding API instances increases database contention. Nothing is obviously broken in the LINQ. The problem is how much work that LINQ asks the system to perform.

EF Core performance optimization starts with that work: commands executed, rows examined, bytes transferred, objects materialized, and time spent waiting. Optimizing those quantities usually matters more than replacing an ORM method with a supposedly faster equivalent.

The examples use .NET 10, ASP.NET Core 10, EF Core 10, and the Microsoft SQL Server provider against SQL Server or Azure SQL. This is a modern LTS baseline. Keep Microsoft EF Core packages on the same version and select extension packages explicitly compatible with that major version. Index syntax, batching behavior, and execution-plan advice below are SQL Server specific.

Assume an AppDbContext with Orders, OrderLines, and Products. Orders belong to a tenant, reference a customer, and contain lines and payments. Code fragments show the relevant application method or configuration rather than a complete repository.

1.1 The Hidden Cost of Object-Relational Mapping Abstraction in High-Scale Systems

LINQ describes a result, but several systems determine its cost. EF translates the expression, the driver sends commands, the database chooses a plan, and EF materializes the response. Tracking and relationship fixup may add further work before serialization begins.

Architectural boundaries should make these costs visible. Use short-lived contexts for units of work, explicit read models for API responses, and separate execution paths for interactive requests and exports. A repository that returns every aggregate with every navigation loaded makes endpoint-specific optimization difficult.

A DbContext is not thread-safe. Await its operations sequentially; use separate contexts for genuinely independent concurrent database work. Increasing concurrency without considering connection-pool capacity and database capacity simply moves the bottleneck.

1.2 Setting Up a Production-Grade Benchmarking Suite with BenchmarkDotNet

BenchmarkDotNet is useful for controlled comparisons of query shapes and allocations. Run it in Release mode, outside the debugger, against an isolated database containing representative data. Avoid sharing the database with unrelated benchmarks or developer activity.

This comparison deliberately keeps filters and result counts equivalent:

using BenchmarkDotNet.Attributes;
using Microsoft.EntityFrameworkCore;

[MemoryDiagnoser]
public class OrderReadBenchmarks
{
    private DbContextOptions<AppDbContext> _options = null!;

    [GlobalSetup]
    public void Setup()
    {
        var connection = Environment.GetEnvironmentVariable("BENCHMARK_DB")
            ?? throw new InvalidOperationException("BENCHMARK_DB is required.");

        _options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(connection)
            .Options;
    }

    [Benchmark(Baseline = true)]
    public async Task<int> Entities()
    {
        await using var db = new AppDbContext(_options);
        var rows = await db.Orders
            .Where(o => o.TenantId == 42)
            .OrderByDescending(o => o.Id)
            .Take(100)
            .ToListAsync();
        return rows.Count;
    }

    [Benchmark]
    public async Task<int> Projection()
    {
        await using var db = new AppDbContext(_options);
        var rows = await db.Orders
            .Where(o => o.TenantId == 42)
            .OrderByDescending(o => o.Id)
            .Select(o => new { o.Id, o.CreatedUtc, o.Total })
            .Take(100)
            .ToListAsync();
        return rows.Count;
    }
}

Seed and migrate before measurement. Include small and large tenants, realistic string widths, and skewed relationships. Warm measurements answer steady-state questions; measure startup separately in fresh processes. A loop against an already-warm local database does not represent cold deployment behavior.

1.3 Establishing Performance Baselines and Key Metrics (Latency, Throughput, GC Pressure)

Record endpoint latency percentiles, successful throughput, command count, database CPU, logical reads, allocation rate, and garbage-collection activity. Add connection-pool waits and timeouts when investigating saturation.

BenchmarkDotNet’s allocation results describe managed allocations in the measured process. They do not measure SQL Server memory or establish production peak working set. Use runtime counters, heap profiles, and request-level load tests alongside microbenchmarks.

Keep the load generator outside the application process and watch whether it can sustain the requested arrival rate. A closed-loop test that waits for each response before sending another can hide queuing under overload. Report attempted requests, successful completions, errors, and latency together. Otherwise, rejecting more work can misleadingly look like a throughput improvement.

Define a baseline per workload: an order list, order detail, export, or import. Preserve dataset size, database configuration, hardware, concurrency, and commit identifier. A faster average with a worse p99 can be a regression for an interactive API.

---

2 Anatomy of Common EF Core Anti-Patterns and Memory Leaks

Most incidents begin with excessive work rather than a defect in EF itself. Distinguish temporary allocation pressure from retained objects: a large response can exhaust memory without a true leak, while a long-lived context can retain tracked entities indefinitely.

2.1 The Classic N+1 Query Problem: Detection, Root Causes, and Business Impact

N+1 occurs when one query loads parent rows and subsequent operations issue one query per parent. Lazy-loading proxies make this easy to miss, but explicit loading inside a loop produces the same behavior.

With lazy loading enabled, this code can execute 51 queries for 50 orders:

var orders = await db.Orders
    .Where(o => o.TenantId == tenantId)
    .Take(50)
    .ToListAsync(ct);

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

Without lazy loading, accessing the navigation does not automatically query the database. Do not diagnose N+1 solely from the presence of navigation access; inspect executed commands.

Group repeated command shapes within one request trace. Increase fixture size and observe whether command count grows with parent count. Database latency makes the business impact worse: individually fast lookups become a slow page and occupy connections longer across concurrent requests.

Use a projection when the response needs customer fields, or Include when the application needs the entity graph. Batch explicit child queries when neither shape translates cleanly. Avoid returning entities directly to serializers that might trigger navigation loading.

2.2 Cartesian Explosion in Multi-Collection Includes and How to Avoid It

Loading sibling collections in one query can multiply rows:

var orders = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .Where(o => o.TenantId == tenantId)
    .ToListAsync(ct);

An order with 20 lines and 5 payments can produce 100 joined rows. EF reconstructs the graph, but repeated parent and child data still travels across the connection. Large text columns make duplication especially expensive.

Nested relationships through ThenInclude do not necessarily create the same sibling cross product, though they can still return substantial data. Inspect actual row counts rather than treating every include as equally dangerous.

Prefer focused projections, split queries, or separate bounded child reads. Filtered includes help when a screen needs only a subset, but tracking navigation fixup can reintroduce previously tracked related entities. Use a fresh context or no-tracking query when that distinction matters.

2.3 The Dangers of Default Tracking: Understanding AsNoTracking vs. Tracking Overhead

Tracking retains entity identity and state so SaveChanges can persist modifications. That is useful for an edit workflow and often unnecessary for a list endpoint.

AsNoTracking() avoids adding returned entities to the context’s change tracker. AsNoTrackingWithIdentityResolution() uses a temporary tracker to reuse an entity instance when its key appears repeatedly, without retaining it in the context afterward.

Neither option is universally fastest. Identity resolution costs memory and CPU but can avoid many duplicate objects in repetitive graphs. Tracking can also reuse instances already present in a context. Measure the actual shape.

A projection containing only scalars and DTOs does not materialize tracked entities. Conversely, Select(o => new { Order = o, ... }) still includes an entity and can track it. Set no-tracking defaults for a dedicated read context if useful; keep write workflows explicit and understandable.

2.4 Memory Bloat from Large Result Sets and Identity Resolution Caches

ToListAsync() buffers the complete result. Removing tracking does not remove that list, its objects, or the eventual serialized payload. Enforce page-size limits before materialization and avoid unbounded includes.

For sequential exports, AsAsyncEnumerable() permits application-level streaming. Keep the context alive until enumeration finishes and propagate cancellation with WithCancellation(ct). The connection may remain occupied while a slow consumer reads, so streaming transfers pressure rather than eliminating it.

Retrying execution strategies and some split-query configurations can buffer internally. Validate memory behavior with the actual provider settings. For very large exports, bounded keyset batches feeding background storage are often easier to operate than a long-running HTTP response.

Investigate retained roots when memory never recovers: singleton services capturing contexts, static collections, event subscriptions, and queues retaining entire entity graphs. ChangeTracker.Clear() can release tracked state in deliberate batch workflows, but it also discards pending tracked changes.

---

3 Advanced Query Shaping and Efficient Projections

The most effective query optimization often changes the result contract. Define what the caller needs before choosing loading strategies or lower-level execution techniques.

3.1 Transforming Heavy Entities into Lightweight Projections (Select vs. Include)

An order list needs summary fields, not editable aggregates. Project those fields and let the database calculate aggregates:

var pageSize = Math.Clamp(requestedPageSize, 1, 100);

var query = db.Orders
    .Where(o => o.TenantId == tenantId);

if (beforeId is long cursor)
    query = query.Where(o => o.Id < cursor);

var page = await query
    .OrderByDescending(o => o.Id)
    .Select(o => new
    {
        o.Id,
        o.CreatedUtc,
        CustomerName = o.Customer.Name,
        LineCount = o.Lines.Count(),
        o.Total
    })
    .Take(pageSize)
    .ToListAsync(ct);

EF can translate navigation references and counts without an Include. Check the generated SQL and its plan: translation does not guarantee an inexpensive plan, particularly for aggregates over poorly indexed child tables.

This keyset pagination avoids scanning increasingly large offsets. It assumes the required ordering is descending ID. For creation-time ordering, use a stable composite cursor such as (CreatedUtc, Id) with a matching predicate and index. Do not silently change product ordering merely to simplify pagination.

3.2 Navigating Trade-Offs: Single Query vs. Split Query (AsSplitQuery) in High-Concurrency APIs

For an entity graph with multiple collections, AsSplitQuery() avoids their joined cross product by issuing separate collection queries. It is an explicit loading strategy, not N+1: command count depends on the query’s collection shape rather than the number of parents.

For a bounded detail view, the loading decision can remain local:

var order = await db.Orders
    .AsNoTrackingWithIdentityResolution()
    .Where(o => o.TenantId == tenantId && o.Id == orderId)
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .SingleOrDefaultAsync(ct);

The trade-off is additional commands and round trips. Benchmark across realistic network latency and concurrency; fewer bytes may still come with higher response time. Multiple commands also consume more database scheduling work.

Separate statements can observe changes committed between them. If the response requires a consistent snapshot, evaluate snapshot or serializable transaction isolation and its database costs. A transaction at ordinary read-committed isolation does not automatically provide that guarantee.

Always use deterministic pagination ordering. EF versions before 10 have a documented split-query paging hazard with nonunique ordering; a unique tie-breaker remains sound design on EF 10. Prefer per-query decisions before enabling split behavior globally.

3.3 Leveraging Compiled Queries and Compiled Models for Ultra-Low Latency Hot Paths

EF already caches query compilation by expression shape. Explicit compiled queries bypass its usual cache lookup and can reduce application overhead on frequently executed, stable queries:

private static readonly Func<AppDbContext, int, IAsyncEnumerable<long>>
    RecentOrderIds = EF.CompileAsyncQuery(
        (AppDbContext db, int tenantId) => db.Orders
            .Where(o => o.TenantId == tenantId)
            .OrderByDescending(o => o.Id)
            .Select(o => o.Id)
            .Take(50));

// Inside an async application method:
await foreach (var id in RecentOrderIds(db, tenantId)
    .WithCancellation(ct))
{
    // Consume the bounded result.
}

Use simple scalar parameters and a single EF model per compiled delegate. The delegate can serve different context instances concurrently; an individual context still cannot execute concurrent operations.

Compiled models address model-initialization cost, especially with large models. Generate them with dotnet ef dbcontext optimize, regenerate after model changes, and confirm their supported features match your model. Compiled-model limitations also matter: global query filters and lazy-loading proxies are unsupported in the documented workflow. That can rule it out for a tenant-filtered model. Do not remove a security boundary to improve startup time.

Context pooling is another separate option. It reuses context instances rather than database connections, whose pooling belongs to the driver. With pooled contexts, explicitly initialize and reset request-specific state such as tenant identifiers; configuration is not rerun for every lease. Profile allocation savings before introducing a more complicated lifetime model.

Neither technique fixes expensive SQL. Measure cold start for compiled models and steady-state CPU for compiled queries separately.

3.4 Structuring Specification Patterns for Maintainable, Reusable Query Optimization

A specification should describe filtering, ordering, projection, pagination, and loading intent without hiding execution. Prefer named query objects such as RecentOrdersForCustomer over one generic repository method with a dozen optional includes.

Keep reusable predicates as expression trees so EF can translate them. Calling Compile() and filtering an already-enumerated sequence moves work into the application. Similarly, premature ToList() prevents later filters from reaching SQL.

Treat tenant constraints and authorization as mandatory inputs. Review specifications for bounded results and predictable command counts. If two endpoints need different shapes, give them different query definitions; shared code is not valuable when it forces both endpoints to overfetch.

---

4 Database-Level Optimization, Indexing, and Advanced SQL Integration

Once the query shape is sensible, investigate how the database executes it. EF cannot compensate for unsuitable indexes, misleading statistics, or a predicate that forces unnecessary work.

4.1 Translating LINQ to SQL: Reading and Analyzing Database Execution Plans

Use ToQueryString() during development to understand translation, then capture the actual executed command and parameter metadata for diagnosis. Terminal operations can modify SQL, so the pre-execution representation is not always the complete command.

Inspect an actual execution plan in a controlled environment. Compare estimated and actual row counts, rows read versus returned, join choices, sorts, memory grants, spills, and key lookups. Pair the plan with logical reads and elapsed time using SQL Server diagnostics.

A scan can be correct when most rows are needed. A seek can still be expensive when followed by thousands of lookups. Focus on unnecessary work and cardinality errors, rather than declaring individual operators inherently bad.

4.2 Identifying Missing Indexes, Missing Includes, and Implicit Cast Performance Penalties

For tenant-specific recent-order queries, a candidate index might be:

CREATE INDEX IX_Orders_TenantId_Id
ON dbo.Orders (TenantId, Id DESC)
INCLUDE (CreatedUtc, CustomerId, Total);

The leading tenant key supports equality filtering, while ID supports ordering and cursor navigation. Included columns can cover the order-table portion of the projection; they do not cover the customer join or line-count lookup. Index OrderLines.OrderId where needed and inspect the complete plan.

Here, “missing includes” means missing included index columns, not EF navigation loading. Add them selectively: wider indexes consume storage, cache space, and write capacity.

Align CLR mappings and parameter types with database columns. Unicode versus non-Unicode strings and incompatible numeric types can introduce conversions that damage index access or estimates. Inspect which side is converted. Configure maximum lengths, Unicode behavior, and decimal precision intentionally rather than relying on accidental schema defaults.

4.3 Leveraging Filtered Indexes, Columnstore Indexes, and Compiled Execution Plans

Filtered indexes suit selective subsets such as active work items:

CREATE INDEX IX_Orders_Open_TenantId_Id
ON dbo.Orders (TenantId, Id DESC)
INCLUDE (Total)
WHERE IsClosed = 0;

The optimizer must prove that the query predicate fits the filter. Parameterized status predicates can complicate that proof because a reusable plan must remain valid for other values. Verify actual use instead of assuming the index name guarantees selection.

Columnstore indexes target large scans and analytical aggregation. They can help reporting workloads, but require evaluation of write overhead, rowgroup quality, and operational query patterns. A reporting replica or separate read model may provide better isolation than adding analytics pressure to checkout tables.

Database execution plans are separate from EF compiled queries. SQL Server can reuse plans for parameterized commands; skewed tenant sizes can make one reusable plan unsuitable. Use Query Store to investigate plan changes and parameter sensitivity. Apply hints or plan forcing only with evidence and a review process.

4.4 Seamless Integration of Raw SQL via FromSql, ExecuteUpdate, and ExecuteDelete

Use raw SQL when a database feature or carefully tuned shape is awkward to express through LINQ. Interpolated FromSql parameterizes values:

var orders = await db.Orders
    .FromSql($"SELECT * FROM dbo.Orders WHERE TenantId = {tenantId}")
    .AsNoTracking()
    .ToListAsync(ct);

Entity queries must return the required mapped columns. Compose additional LINQ only over composable SQL. SQL identifiers cannot be parameterized like values; dynamic table names or ordering expressions require strict allowlists. Never concatenate untrusted input into FromSqlRaw.

ExecuteUpdate and ExecuteDelete are set-based LINQ APIs, not raw-SQL APIs:

var affected = await db.Orders
    .Where(o => o.TenantId == tenantId && o.CreatedUtc < cutoff)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.IsArchived, true), ct);

They execute immediately, bypass tracked state, and do not require SaveChanges. Separate calls do not share an automatic transaction. For an individual optimistic update, include the expected rowversion in the predicate and require exactly one affected row. An explicit transaction is also necessary if several set-based changes must succeed or fail together. When retrying execution strategies are enabled, execute that transaction through the strategy’s supported unit-of-work pattern.

Add explicit concurrency predicates and check affected rows when conflicts matter; refresh or avoid tracked entities whose values are now stale.

---

5 Diagnostics, Telemetry, and Deep Query Logging

Production diagnostics should explain expensive requests without becoming another source of latency or exposing sensitive data. Connect endpoint behavior to database work, and keep diagnostic overhead measurable.

5.1 Configuring EF Core DiagnosticSource and Interceptors for Granular Telemetry

Use EF’s standard logging categories for command events. DiagnosticSource supports observing EF events across contexts; interceptors integrate directly with configured contexts and can observe or modify execution.

For custom metrics, derive from DbCommandInterceptor and override the relevant reader, scalar, and nonquery callbacks, including asynchronous paths. Record failures and cancellation as well as successful completions. A reader callback alone misses updates and scalar commands.

Reader-command duration generally measures execution until a reader is returned, not complete enumeration and materialization. Measure the application operation separately. Keep interceptors stateless or thread-safe, avoid blocking exporters, and never perform synchronous network logging on every command.

5.2 Integrating MiniProfiler and OpenTelemetry for Real-Time Distributed Query Tracking

MiniProfiler is useful during development for spotting repeated commands in a request. Install its ASP.NET Core integration and EF Core integration, register AddMiniProfiler().AddEntityFramework(), and add UseMiniProfiler() to the middleware pipeline. Restrict access if exposing its UI outside development.

For distributed tracing, instrument ASP.NET Core requests and SQL calls, then export to an OpenTelemetry collector. With SQL Server, SqlClient instrumentation is a practical starting point. Pin instrumentation packages and verify compatibility with the driver and runtime you deploy.

Avoid collecting duplicate database spans from overlapping instrumentation. Correlate database spans with the request trace, and verify whether the chosen instrumentation measures execution, consumption, or both. Enable SQL-text capture only after reviewing its privacy and cardinality implications.

5.3 Detecting Slow Queries, Threshold Alerts, and Query Tagging (TagWith) in Production

Add stable operation tags:

var query = db.Orders
    .TagWith("Orders.List.v2")
    .Where(o => o.TenantId == tenantId);

Tags appear as SQL comments. Keep them constant; request IDs and tenant IDs belong in trace context, not dynamically generated tags. Variable SQL text can fragment aggregation and plan-cache entries.

Alert on command counts, sustained latency changes, timeout rates, and pool pressure. A request containing 80 quick queries may never breach a per-command slow threshold. Capture sampled slow traces and compare them with Query Store. Avoid unrestricted sensitive-data logging: parameters may contain customer information or credentials.

---

6 Caching Strategies and Second-Level Cache Architecture

Caching is appropriate when repeated reads tolerate a defined freshness window. First fix query shape and indexing; cache misses, deployments, and invalidation bursts still reach the database.

6.1 First-Level vs. Second-Level Caching: Architectural Trade-offs and Invalidation Challenges

EF’s context-level identity map is often called a first-level cache. It reuses tracked instances, but an ordinary LINQ query still executes SQL. Find can return an already-tracked entity without another database lookup.

A second-level cache reuses results across contexts or requests. EF Core does not provide a general built-in second-level query-result cache. Its query-compilation cache is a different mechanism.

Define ownership, tenant isolation, maximum staleness, and invalidation before implementation. Do not cache a stock-availability decision or payment authorization merely because the same lookup is frequent. A display estimate may tolerate staleness; the command that reserves stock still needs authoritative validation and concurrency control. Define those boundaries in the application contract.

Cache DTOs or query results rather than shared tracked entities. Authorization-sensitive results require keys that reflect the relevant scope, or a separate authorization check before cached data is returned.

6.2 Implementing Second-Level Caching Using Open-Source Libraries (EFCoreSecondLevelCacheInterceptor)

EFCoreSecondLevelCacheInterceptor provides opt-in query caching. Install the main package plus a compatible provider package, such as its MemoryCache provider:

using EFCoreSecondLevelCacheInterceptor;

builder.Services.AddEFSecondLevelCache(options =>
    options.UseMemoryCacheProvider()
        .UseCacheKeyPrefix("catalog:v1:"));

builder.Services.AddDbContext<AppDbContext>((sp, options) =>
    options.UseSqlServer(connectionString)
        .AddInterceptors(
            sp.GetRequiredService<SecondLevelCacheInterceptor>()));

Mark a bounded lookup explicitly:

var categories = await db.Categories
    .AsNoTracking()
    .OrderBy(c => c.Name)
    .Select(c => new { c.Id, c.Name })
    .Cacheable(CacheExpirationMode.Absolute, TimeSpan.FromMinutes(2))
    .ToListAsync(ct);

The library supports dependency-based invalidation for recognized writes. Do not assume it covers external writers, bulk tools, or every set-based operation. Test ExecuteUpdate, ExecuteDelete, raw SQL, rollback, and cross-instance behavior against the exact package version. Explicit invalidation may be required. In-memory providers also require an invalidation strategy across replicas.

6.3 Distributed Caching with Redis and Cache-Aside Patterns for Read-Heavy Microservices

Cache-aside gives the application explicit control: construct a versioned tenant-aware key, read Redis, query on a miss, then store a serialized DTO with an expiry. Register Redis through AddStackExchangeRedisCache when using IDistributedCache.

Invalidate after the database commit, not before it. Even then, a concurrent reader can repopulate an old value after invalidation. TTL bounds exposure; versioned keys, durable invalidation events, or stronger coordination address stricter requirements. An outbox helps deliver invalidation events reliably but does not make Redis and SQL one atomic system.

Prevent hot-key stampedes with request coalescing and expiry jitter. Bound cache payloads and test failure behavior: falling back to SQL during a cache outage can overwhelm the database. Monitor hit rate, eviction, stale-data incidents, and miss-path latency together.

---

7 High-Throughput Bulk Operations and Batching Techniques

Large imports and maintenance jobs have different requirements from interactive writes. Choose a write path based on volume, transactional boundaries, domain behavior, and restartability.

7.1 The Limitations of Traditional Change Tracking for Mass Inserts and Updates

AddRange followed by SaveChanges still creates tracked entries and generates entity-oriented writes. Provider batching reduces round trips, but it is not a bulk-copy protocol. Large graphs increase allocation, change-detection work, and transaction-log pressure.

For uniform updates, prefer ExecuteUpdate without reading entities first. For modest inserts, bounded SaveChanges batches may be sufficient. Dispose the context between batches or clear it only after successful persistence.

Disabling automatic change detection is a specialized optimization. It can miss modifications unless detection occurs explicitly at the correct time. Measure change detection before adding that complexity.

7.2 Accelerating Bulk Operations Using Open-Source Libraries (EFCore.BulkExtensions)

The requested EFCore.BulkExtensions library uses database-specific bulk mechanisms, including SQL Server bulk copy. Its current distribution uses conditional community/commercial licensing; source availability does not mean unrestricted enterprise use. Review licensing and provider-version compatibility before adoption.

For a compatible SQL Server package:

using EFCore.BulkExtensions;

await db.BulkInsertAsync(rows, new BulkConfig
{
    BatchSize = 5_000,
    SetOutputIdentity = false
}, cancellationToken: ct);

The batch size is a starting experiment, not a universal recommendation. Bound the input collection too; a bulk API does not prevent memory bloat if the application first builds a million-row list.

Confirm identity propagation, foreign-key ordering, computed columns, triggers, transaction behavior, and concurrency handling. Bulk operations do not automatically reproduce change-tracker behavior or application logic around SaveChanges. Validate business rules explicitly, and use staging tables when imports need deduplication, validation, and restartable promotion into production tables.

7.3 Minimizing Round-Trips with Database-Specific Batching Configurations

SQL Server’s EF provider normally batches up to 42 statements for SaveChanges. Treat that as a provider default, not an ideal workload size. MaxBatchSize changes statement batching; it does not convert inserts into bulk copy or combine separate ExecuteUpdate calls.

Test alternatives against representative row widths, generated values, locks, and transaction sizes. Larger batches can reduce round trips while increasing lock duration and log bursts. Keep transactions bounded where business semantics allow it, and make retries idempotent. A faster import is not operationally better if a transient failure duplicates half its records.

---

8 Production Readiness Checklist and Architectural Guardrails

Performance work lasts when teams preserve the behavior they improved. Guardrails should catch excess database work early while leaving room for legitimate query evolution.

8.1 Automated Performance Unit Tests with Testcontainers and EF Core Diagnostics

Provider-dependent checks are integration tests, even when they run beside unit tests. Use Testcontainers’ SQL Server module with a pinned image, apply migrations, and seed deterministic fixtures. EF’s InMemory provider cannot validate SQL translation, indexes, or execution plans.

Attach a test interceptor that counts executed commands within one operation. Cover asynchronous and synchronous paths as appropriate, exclude setup commands, and isolate counters from parallel tests. Disable result caching for miss-path assertions.

Test order lists with 1, 20, and 100 parents. A bounded query should not add one command per parent. Add a deliberate lazy-loading or explicit-loading regression to confirm the test detects it. Include a high-fan-out fixture: one order with many lines and payments exposes joined-row amplification that a hundred empty orders will miss. Exercise cancellation during enumeration and verify that contexts and connections are released. For update paths, add concurrent writers so an optimization cannot silently remove conflict detection.

Also verify returned values, tenant isolation, and page limits; low command count alone does not establish correctness.

8.2 CI/CD Pipeline Checks for Unintentional N+1 and Missing Index Regressions

Use deterministic checks in pull requests: command-count budgets, bounded result sizes, translation success, and migration review. Run larger load tests on controlled infrastructure separately. Tight millisecond assertions on shared CI runners create noise rather than confidence.

For important queries, preserve representative SQL and plan evidence for review. Do not require identical plan XML across database versions. Tiny fixtures rarely expose missing indexes, so use larger distributions and compare logical reads alongside latency.

Review generated index migrations against existing indexes and write workloads. Missing-index suggestions are candidates, not automatic schema changes. After deployment, compare tagged-operation latency, database resource consumption, and Query Store behavior; keep a rollback path for both code and schema changes.

8.3 Summary Architectural Best Practices for Enterprise Engineering Teams

Before releasing a database-heavy endpoint, verify:

• Its result contract is bounded and excludes unnecessary columns and navigations.
• Command count remains predictable as parent counts increase.
• Tracking, split-query consistency, and context lifetime are deliberate choices.
• Indexes match observed predicates and ordering without excessive write overhead.
• Telemetry distinguishes command execution from complete request processing.
• Cache keys, freshness limits, invalidation, and outage behavior are tested.
• Bulk workflows preserve required business rules and support safe retries.
• Integration checks and production baselines make regressions visible.

The practical order is straightforward: measure the operation, reduce unnecessary data access, inspect database execution, and then optimize application overhead. Keep the resulting query contract visible in code and tests. That is how an EF Core optimization becomes a maintained architectural property instead of a temporary improvement.