Entity Framework Core in Production: Advanced Performance Tuning, Query Splitting, and Avoiding Memory Leaks

Introduction: The 2GB Memory Leak That Taught Us About Change Tracking

Early in my career, I deployed a "simple" reporting endpoint that exported 50,000 order records to CSV. In staging, it worked fine. In production, the application's memory usage spiked to 2GB within minutes, triggering an OutOfMemory exception that took down the entire web farm. The culprit? I had forgotten to call .AsNoTracking(). EF Core was not just materializing 50,000 entities; it was creating a full snapshot of every single property for every single entity so it could track changes. The change tracker consumed more memory than the actual data.

Entity Framework Core is an incredible productivity tool, but it abstracts away the database. If you don't understand what SQL it generates and how it manages memory, it will silently destroy your application's performance. This guide covers the advanced, production-tested patterns we use to keep EF Core fast, scalable, and memory-efficient.

The Golden Rule: Disable Tracking for Read-Only Queries

When you query data with tracking enabled, EF Core does two expensive things: it stores the entity in the Change Tracker, and it takes a snapshot of the original values to detect changes later. If you are only reading data to display it, this is pure waste.

              // BAD: Tracks 50,000 entities and their snapshots
var orders = await _context.Orders.ToListAsync();

// GOOD: Skips change tracking entirely. Much faster and uses less memory.
var orders = await _context.Orders
    .AsNoTracking()
    .ToListAsync();

// PRO TIP: If you need to load a graph of entities (e.g., Order with Items)
// without tracking, but want EF to fix up navigation properties correctly:
var ordersWithItems = await _context.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Items)
    .ToListAsync();

            

Projection: Fetch Only What You Need

Fetching entire entities when you only need three columns wastes network bandwidth, CPU, and memory. Always project directly into DTOs (Data Transfer Objects) or anonymous types.

              // BAD: Fetches all 50 columns of the Product table
var products = await _context.Products.ToListAsync();
var dtos = products.Select(p => new ProductDto { Id = p.Id, Name = p.Name });

// GOOD: Translates to SELECT Id, Name FROM Products.
// The database does the filtering, not your application.
var dtos = await _context.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        // You can even do client-side evaluation safely here if it translates to SQL
        FormattedPrice = "$" + p.Price
    })
    .ToListAsync();

            

Warning: Never call .ToList() before .Select(). Doing so forces EF Core to materialize the full entities into memory first, completely defeating the purpose of projection.

Solving the N+1 Problem and Cartesian Explosions

The N+1 problem happens when you load a parent entity, and then lazily load its children in a loop, resulting in 1 query for the parent and N queries for the children. EF Core 3.0+ throws an exception for lazy loading by default, but N+1 can still happen if you aren't careful with Include().

However, using Include() for multiple collection navigations causes a "Cartesian Explosion." If an Order has 10 Items and 5 Payments, a single query with both Includes returns 50 rows, duplicating data heavily.

              // BAD: Cartesian Explosion. Returns Order * Items * Payments rows.
var orders = await _context.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .ToListAsync();

// GOOD: AsSplitQuery() generates separate SQL queries for each collection.
// Query 1: SELECT * FROM Orders
// Query 2: SELECT * FROM Items WHERE OrderId IN (...)
// Query 3: SELECT * FROM Payments WHERE OrderId IN (...)
var orders = await _context.Orders
    .AsSplitQuery()
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .ToListAsync();

            

Advanced Pagination: Beyond Skip and Take

Standard offset pagination (Skip/Take) is fine for the first few pages. But Skip(100000).Take(20) forces the database to scan and discard 100,000 rows before returning 20. This is incredibly slow on large tables.

For large datasets, use Keyset Pagination (also known as the Seek Method). It uses a WHERE clause on the last seen ID to jump directly to the correct position.

              // BAD: Offset Pagination (Slow for deep pages)
var page1000 = await _context.Products
    .OrderBy(p => p.Id)
    .Skip(100000)
    .Take(20)
    .ToListAsync();

// GOOD: Keyset Pagination (Consistently fast)
// Assuming 'lastId' is the Id of the last item on the previous page
var lastId = 100000;
var nextPage = await _context.Products
    .Where(p => p.Id > lastId) // Jump directly to the start point
    .OrderBy(p => p.Id)
    .Take(20)
    .ToListAsync();

            

Bulk Operations: ExecuteUpdate and ExecuteDelete

Prior to EF7, updating or deleting multiple records required loading them into memory, modifying them, and calling SaveChanges(). This was terribly inefficient. EF7 introduced ExecuteUpdateAsync and ExecuteDeleteAsync, which translate directly to SQL UPDATE and DELETE statements.

              // BAD: Loads 10,000 entities into memory, tracks them, updates them, saves them.
var products = await _context.Products.Where(p => p.Stock == 0).ToListAsync();
foreach(var p in products) p.IsDiscontinued = true;
await _context.SaveChangesAsync();

// GOOD: Translates to: UPDATE Products SET IsDiscontinued = 1 WHERE Stock = 0
// Executes entirely in the database. Zero memory overhead.
await _context.Products
    .Where(p => p.Stock == 0)
    .ExecuteUpdateAsync(setters =>
        setters.SetProperty(p => p.IsDiscontinued, true));

// GOOD: Translates to: DELETE FROM Products WHERE Stock = 0
await _context.Products
    .Where(p => p.Stock == 0)
    .ExecuteDeleteAsync();

            

Indexing Strategies for EF Core

No amount of EF Core optimization can fix a missing database index. If you are filtering or sorting by a column, it almost certainly needs an index. Configure these using the Fluent API in OnModelCreating.

              protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 1. Standard Index on a frequently filtered column
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.CategoryId);

    // 2. Composite Index for multi-column filtering
    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.OrderDate });

    // 3. Filtered Index (SQL Server specific, but highly valuable)
    // Only indexes active products, saving space and improving query speed
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.Sku)
        .HasFilter("[IsDeleted] = 0");
}

            

Monitoring and Catching Slow Queries

You cannot optimize what you do not measure. EF Core provides powerful logging and interception capabilities. Use SimpleCommandInterceptor to log queries that take longer than a specific threshold.

              public class SlowQueryInterceptor : DbCommandInterceptor
{
    private readonly ILogger _logger;
    private readonly TimeSpan _threshold = TimeSpan.FromMilliseconds(500);

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        // Note: In production, use the async version and measure elapsed time
        // using a Stopwatch around the base call.
        return base.ReaderExecuting(command, eventData, result);
    }
}

// Register in Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.AddInterceptors(new SlowQueryInterceptor(logger));
});

            

Common Production Mistakes

• ●

  ❌ Client-Side Evaluation: Calling .ToList() or .AsEnumerable() before filtering. This pulls the entire table into memory and filters it using C# instead of SQL.

• ●

  ❌ Ignoring Async: Using .ToList() instead of .ToListAsync() in ASP.NET Core controllers. This blocks the thread pool and destroys your app's ability to handle concurrent requests.

• ●

  ❌ Overusing Include(): Loading entire object graphs when you only need the parent. Use projection instead.

• ●

  ❌ Long-Lived DbContexts: Keeping a DbContext alive for the duration of a user session. DbContexts are not thread-safe and should be scoped to a single request/operation.

• ●

  ❌ String Concatenation in LINQ: Using methods that EF Core cannot translate to SQL, forcing client-side evaluation.

"EF Core is a tool for generating SQL, not a replacement for understanding SQL. Always check the generated query."

Frequently Asked Questions

Conclusion

Want to dive deeper into .NET data access? Check out our guides on [Advanced Dapper Patterns for High-Performance Reads], [Designing Scalable Database Indexes], and [Optimizing SQL Server for Cloud Workloads].