Entity Framework optimization

await context.SaveChangesAsync();

// Optimized: Server-side execution await context.Orders .Where(o => o.Status == OrderStatus.Cancelled && o.CreatedAt < cutoff) .ExecuteDeleteAsync();

**Rationale:** Batch operations generate a single `DELETE` or `UPDATE` statement, eliminating the round-trip overhead and memory allocation associated with materialization.

#### 5. Split Queries for Collection Navigation

When loading multiple collection navigations, EF generates a Cartesian product, duplicating data in the result set. Use `SplitQuery` to fetch collections in separate queries.

```csharp
var orders = await context.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .AsSplitQuery() // Prevents Cartesian explosion
    .ToListAsync();

Rationale: SplitQuery executes multiple SQL queries and stitches results in memory. This prevents data duplication, reduces network transfer size, and avoids memory spikes caused by large result sets.

Pitfall Guide

Production optimization fails when common anti-patterns persist. The following pitfalls are frequently observed in code reviews and performance audits.

1. Cartesian Explosion via Multiple Includes: Including multiple collections in a single query causes a cross join, multiplying the row count. If an order has 10 items and 5 shipments, the result set contains 50 rows with duplicated order data.

   • Best Practice: Always use AsSplitQuery() when including multiple collections, or restructure the query to fetch collections separately.

2. Silent Client-Side Evaluation: If a LINQ expression cannot be translated to SQL, EF may evaluate it on the client side, pulling excessive data from the database. While EF Core warns about this, it can still occur in complex expressions.

   • Best Practice: Configure the context to throw on client evaluation: options.ConfigureWarnings(w => w.Throw(RelationalEventId.QueryClientEvaluationWarning)).

3. Overuse of Lazy Loading: Lazy loading triggers queries implicitly when navigation properties are accessed. This leads to N+1 problems that are difficult to detect during development.

   • Best Practice: Disable lazy loading proxies in production. Use explicit loading (Load) or eager loading (Include) with projections to maintain control over query execution.

4. Ignoring Index Coverage: EF generates queries based on the model. If the database lacks indexes on filtered or sorted columns, performance degrades regardless of ORM optimization.

   • Best Practice: Analyze query execution plans regularly. Ensure indexes exist for all WHERE, JOIN, and ORDER BY columns used in frequent queries. Use HasIndex in the model to manage indexes via migrations.

5. Large Object Graphs in Memory: Loading entire entity graphs for simple updates keeps large objects in the change tracker, increasing memory pressure and SaveChanges duration.

   • Best Practice: For updates, fetch only the entity key and modified properties. Use Attach and Property setters to mark specific fields as modified, or use ExecuteUpdate for simple changes.

6. Misconfigured Connection Pooling: Default connection pooling is usually sufficient, but high-concurrency apps may exhaust the pool if connections are held too long or not returned promptly.

   • Best Practice: Ensure DbContext is scoped correctly (per request). Avoid capturing DbContext in singletons. Tune Max Pool Size and Connect Timeout based on load testing metrics.

7. Skipping AsNoTracking on List Views: List endpoints often return collections of data without modifying entities. Failing to disable tracking here is a guaranteed memory leak in long-running processes.

   • Best Practice: Adopt a policy where all read operations use AsNoTracking by default. Enable tracking only within explicit unit-of-work boundaries for writes.

Production Bundle

Action Checklist

• Audit Hot Paths: Identify top 10 queries by frequency and latency using SQL logging or APM tools.
• Apply AsNoTracking: Ensure all read-only queries use AsNoTracking or configure global no-tracking behavior.
• Implement Projections: Replace entity materialization with DTO projections in all list and detail views.
• Enable Split Queries: Add AsSplitQuery() to all queries including multiple collection navigations.
• Compile Frequent Queries: Convert high-traffic LINQ queries to compiled queries using EF.CompileQuery.
• Migrate Bulk Ops: Replace foreach loops with ExecuteUpdate and ExecuteDelete for batch operations.
• Verify Indexes: Review execution plans for full table scans and add missing indexes via migrations.
• Configure Warnings: Set ThrowOnClientEvaluation to prevent silent performance degradation.

Decision Matrix

Configuration Template

Use this template to configure an optimized DbContext for production workloads.

public static class EfCoreOptimizationExtensions
{
    public static IServiceCollection AddOptimizedDbContext<TContext>(
        this IServiceCollection services, 
        string connectionString,
        Action<SqlServerDbContextOptionsBuilder>? configureSqlServer = null)
        where TContext : DbContext
    {
        services.AddDbContext<TContext>(options =>
        {
            options.UseSqlServer(connectionString, sqlOptions =>
            {
                sqlOptions.EnableRetryOnFailure(
                    maxRetryCount: 5,
                    maxRetryDelay: TimeSpan.FromSeconds(30),
                    errorNumbersToAdd: null);
                
                // Connection pooling is enabled by default; 
                // tune pool size if necessary
                sqlOptions.CommandTimeout(30);
                
                configureSqlServer?.Invoke(sqlOptions);
            });

            // Global optimization settings
            options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
            
            // Fail fast on client evaluation
            options.ConfigureWarnings(w => 
                w.Throw(RelationalEventId.QueryClientEvaluationWarning));
            
            // Enable sensitive data logging only in development
            #if DEBUG
            options.EnableSensitiveDataLogging();
            #endif
        });

        // Register DbContext pool for high-throughput scenarios
        // Note: Only use pooling if DbContext is stateless per request
        services.AddDbContextPool<TContext>(options =>
        {
            options.UseSqlServer(connectionString, sqlOptions =>
            {
                sqlOptions.EnableRetryOnFailure();
            });
            options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
        }, poolSize: 128);

        return services;
    }
}

Quick Start Guide

1. Configure Context: Replace standard AddDbContext with the optimized configuration template. Ensure UseQueryTrackingBehavior is set to NoTracking and ThrowOnClientEvaluation is enabled.
2. Refactor Read Queries: Update existing repository methods to use AsNoTracking() and Select() projections. Remove Include calls where possible; use explicit projections for required fields.
3. Implement Batch Ops: Identify loops performing updates or deletes. Replace them with ExecuteUpdateAsync or ExecuteDeleteAsync using the same Where clause.
4. Profile and Validate: Enable SQL logging in a staging environment. Verify that generated SQL uses efficient plans, no client-side evaluation warnings appear, and memory allocation decreases under load.
5. Deploy Compiled Queries: For endpoints identified as bottlenecks, extract LINQ queries into compiled query definitions. Measure latency improvement and deploy.

Entity Framework optimization is a continuous discipline. By enforcing no-tracking defaults, leveraging projections, utilizing compiled queries, and adopting batch operations, teams can extract database-grade performance from the ORM while maintaining developer productivity. Regular auditing of query patterns and execution plans ensures the data layer scales efficiently with application growth.