Entity Framework optimization

// Read-optimized context
public class ReadDbContext : DbContext
{
    public ReadDbContext(DbContextOptions<ReadDbContext> options) : base(options) { }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
    }
}

// Write-optimized context
public class WriteDbContext : DbContext
{
    public WriteDbContext(DbContextOptions<WriteDbContext> options) : base(options) { }
}

// Unoptimized
var orders = await _context.Orders
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .ToListAsync();

// Optimized
var orderDtos = await _context.Orders
    .Where(o => o.Status == OrderStatus.Active)
    .Select(o => new OrderSummaryDto
    {
        Id = o.Id,
        Total = o.Items.Sum(i => i.Price * i.Quantity),
        CreatedAt = o.CreatedAt,
        Items = o.Items.Select(i => new ItemDto
        {
            Name = i.Product.Name,
            Quantity = i.Quantity
        }).ToList()
    })
    .AsNoTracking()
    .ToListAsync();

rialization overhead. AsNoTracking() is redundant here but explicit for safety.

Step 3: Compile Hot-Path Queries

LINQ queries are translated to SQL at runtime. Repeated execution of the same query structure incurs translation overhead. EF Core's compiled query cache eliminates this cost.

public static class OrderQueries
{
    public static Func<AppDbContext, int, int, Task<List<OrderSummaryDto>>> GetActiveOrders =
        EF.CompileAsyncQuery((AppDbContext ctx, int skip, int take) =>
            ctx.Orders
               .Where(o => o.Status == OrderStatus.Active)
               .OrderBy(o => o.CreatedAt)
               .Skip(skip)
               .Take(take)
               .Select(o => new OrderSummaryDto
               {
                   Id = o.Id,
                   Total = o.Items.Sum(i => i.Price * i.Quantity),
                   CreatedAt = o.CreatedAt
               })
               .AsNoTracking()
               .ToList());
}

// Usage
var results = await OrderQueries.GetActiveOrders(_context, skip, take);

Architecture Rationale: Compiled queries are cached and reused across requests. Translation overhead drops to near-zero. This is critical for high-throughput endpoints where LINQ expression trees would otherwise be rebuilt per request.

Step 4: Control Change Tracking Explicitly

When mutations are required, disable tracking during reads and attach only modified entities. This prevents the context from monitoring unchanged rows.

public async Task UpdateOrderStatusAsync(int orderId, OrderStatus newStatus)
{
    var order = await _context.Orders
        .AsNoTracking()
        .FirstOrDefaultAsync(o => o.Id == orderId);

    if (order == null) return;

    order.Status = newStatus;
    
    _context.Orders.Attach(order);
    _context.Entry(order).Property(o => o.Status).IsModified = true;
    
    await _context.SaveChangesAsync();
}

Architecture Rationale: Attach() + IsModified limits change tracking to specific properties. SQL generated uses targeted UPDATE statements instead of full row comparisons. Memory footprint remains stable regardless of query result size.

Step 5: Align Database Indexes with Query Patterns

EF Core optimizations fail if the database lacks supporting indexes. Use HasIndex() in migrations and verify execution plans.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.Status, o.CreatedAt })
        .HasFilter("Status = 1"); // Partial index for active orders
}

Architecture Rationale: Composite indexes match WHERE + ORDER BY patterns. Partial indexes reduce index size and maintenance overhead. EF Core migrations ensure index drift is caught during deployment, not production incidents.

Pitfall Guide

1. Blind Include() Chains Without Filtering

Including entire navigation collections forces SQL JOIN multiplication. A single Include() with three child collections can generate Cartesian products that explode row counts. Always filter includes or use split queries.

2. Client Evaluation Traps

Methods like .AsEnumerable(), .ToList(), or unsupported LINQ operators force client-side execution. Data transfers from the database before filtering occurs, negating index usage. Always verify generated SQL with Microsoft.EntityFrameworkCore.Diagnostics.

3. Over-Reliance on Lazy Loading Proxies

Lazy loading triggers implicit queries when navigation properties are accessed. In loops or serialized responses, this creates N+1 patterns that are difficult to trace. Disable proxies in production and use explicit Include() or projection.

4. DbContext Lifetime Mismanagement

Registering DbContext as singleton causes thread-safety violations and memory leaks. Scoped is correct for web requests, but long-running background services require explicit factory patterns or context pooling.

5. Change Tracker Memory Growth in Batch Processes

Processing thousands of entities in a single context causes the change tracker to accumulate references. Use context.ChangeTracker.Clear() periodically or create new contexts per batch.

6. Ignoring Connection Pool Exhaustion

Unoptimized queries hold connections longer than necessary. Combined with high concurrency, this exhausts the pool, causing TimeoutException cascades. Always wrap EF Core calls in proper async/await patterns and avoid .Result or .Wait().

7. Treating Migrations as Afterthoughts

Schema drift between code and database forces EF Core to fall back to client evaluation or inefficient queries. Run migrations in CI/CD pipelines and validate execution plans after deployment.

Best Practices from Production:

• Always project to DTOs for read operations
• Default to AsNoTracking() unless mutation is required
• Profile with EnableSensitiveDataLogging() in staging, never production
• Use SplitQuery() for complex graphs to avoid Cartesian explosion
• Monitor Microsoft.EntityFrameworkCore.Database.Command logs for execution time spikes

Production Bundle

Action Checklist

• Replace entity returns with DTO projection on all read endpoints
• Enable AsNoTracking() as default behavior for read contexts
• Compile LINQ queries used in high-traffic paths using EF.CompileAsyncQuery
• Audit Include() chains and replace with split queries or explicit joins
• Register DbContext with scoped lifetime and enable context pooling in web apps
• Add composite indexes matching WHERE/ORDER BY patterns in migrations
• Implement ChangeTracker.Clear() in batch processing loops
• Configure query logging to staging for execution plan validation

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Read-heavy dashboard with 10k+ rows	AsNoTracking + DTO Projection + Compiled Query	Eliminates tracking overhead and reduces memory allocation by 80%+	Reduces compute costs by 30-50%
High-concurrency API with complex graphs	SplitQuery + Indexed foreign keys + Connection pooling	Prevents Cartesian explosion and maintains stable roundtrip count	Lowers database tier requirements
Batch data import/update	Scoped DbContext per batch + ChangeTracker.Clear() + Bulk extensions	Prevents memory leaks and maintains transaction boundaries	Avoids infrastructure scaling during maintenance windows
Legacy app with lazy loading enabled	Disable proxies + Replace with explicit Include/Select	Eliminates N+1 patterns and predictable query counts	Reduces latency spikes under load

Configuration Template

// Program.cs / Startup.cs
builder.Services.AddPooledDbContextFactory<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default"))
           .EnableSensitiveDataLogging() // Remove in production
           .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
           .EnableThreadSafetyChecks(false); // Required for pooled contexts
});

builder.Services.AddScoped<IReadRepository, ReadRepository>();
builder.Services.AddScoped<IWriteRepository, WriteRepository>();

// Repository base pattern
public abstract class BaseRepository
{
    protected readonly IDbContextFactory<AppDbContext> _contextFactory;
    
    protected BaseRepository(IDbContextFactory<AppDbContext> contextFactory)
    {
        _contextFactory = contextFactory;
    }
    
    protected async Task<T> ExecuteReadAsync<T>(Func<AppDbContext, Task<T>> query)
    {
        await using var ctx = _contextFactory.CreateDbContext();
        ctx.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
        return await query(ctx);
    }
}

Quick Start Guide

1. Install Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer (or your provider) via NuGet
2. Replace direct DbContext injection with IDbContextFactory<T> and configure pooling in Program.cs
3. Convert all read endpoints to use AsNoTracking() and project results to DTOs using .Select()
4. Add QuerySplittingBehavior.SplitQuery to DbContext options to prevent Cartesian product explosion
5. Run dotnet ef migrations add InitialOptimization and verify generated SQL matches expected execution plans

Apply these steps to baseline your application. Measure execution time, memory allocation, and database roundtrips before and after. The delta will dictate which advanced optimizations (compiled queries, bulk extensions, read/write splitting) require implementation. EF Core performance is not a configuration toggle; it is a disciplined alignment of code patterns with relational engine capabilities.