TL;DR:

  • The N+1 query problem creates excessive database calls. Fix it with Include() or projections.
  • Early calls to ToList() defeat EF Core’s query optimization. Build your full query before materializing results.
  • Client-side filtering pulls all data into memory. Keep your filtering in SQL with proper LINQ expressions.
  • Use Select() projections to fetch only the columns you need, especially for large text or blob fields.
  • Add indexes for columns used in WHERE, JOIN, and ORDER BY clauses to prevent table scans.
  • Disable change tracking with AsNoTracking() for read-only operations to improve memory usage and performance.
  • Avoid lazy loading in production; explicitly Include() related data you know you’ll need.
  • Scope your DbContext properly: don’t create one per operation or keep one alive too long.
  • Use compiled queries for frequently executed identical queries to reduce CPU usage.
  • Batch operations instead of saving changes after each entity addition or modification.

Every developer has been there. Your application works perfectly in development with a handful of test records, but once it hits production with real data, everything crawls to a halt. Users complain about slow page loads, your cloud costs skyrocket, and suddenly you’re debugging why a simple product listing page takes 8 seconds to render.

Most of the time, the real problem isn’t your business logic or UI framework, it’s simply how you’re talking to your database with Entity Framework Core. Bad EF Core queries will slow down your app, drive up your hosting bills, and frustrate your users enough to make them leave.

I’m going to show you the most common EF Core performance mistakes I’ve found in real production code, and exactly how to fix them. You’ll see practical examples, actual performance improvements, and debugging techniques you can use right away to catch these issues before they hit production.

1. The N+1 Query Problem: Death by a Thousand Queries

The N+1 problem is the #1 performance killer in EF Core. It works like this: you load a list of items with one query, then your code accidentally triggers a separate database query for each item when you access related data.

N+1 Problem Summary:
Each navigation property not loaded explicitly = extra database query. Multiply that by N.

Here’s a typical example that looks innocent but destroys performance:

// BAD: This generates 1 + N queries
public async Task<List<OrderDto>> GetOrdersAsync()
{
    var orders = await _context.Orders.ToListAsync(); // Query 1
    
    return orders.Select(order => new OrderDto
    {
        Id = order.Id,
        CustomerName = order.Customer.Name, // Query 2, 3, 4... N+1
        ProductCount = order.OrderItems.Count // More queries!
    }).ToList();
}

With 100 orders, this generates 101 database queries. With 1,000 orders, you’re looking at 1,001 queries. I’ve seen production systems where a single page load triggered over 2,000 database round trips.

💡 Quick Math:
1,000 orders = 1,001 database round trips. At 10ms per query, that’s 10+ seconds just for database calls!

// GOOD: Single query with joins
public async Task<List<OrderDto>> GetOrdersAsync()
{
    var orders = await _context.Orders
        .Include(o => o.Customer)           // Join customer data
        .Include(o => o.OrderItems)         // Join order items
        .ToListAsync();
    
    return orders.Select(order => new OrderDto
    {
        Id = order.Id,
        CustomerName = order.Customer.Name,
        ProductCount = order.OrderItems.Count
    }).ToList();
}

Performance Tip:
Include() creates SQL JOINs. One query with JOINs beats hundreds of individual queries every time.

Fix #2: Use Projection for Better Performance

Even better, use projection to fetch only the data you need:

// BEST: Single query, minimal data transfer
public async Task<List<OrderDto>> GetOrdersAsync()
{
    return await _context.Orders
        .Select(order => new OrderDto
        {
            Id = order.Id,
            CustomerName = order.Customer.Name,    // EF Core handles the join
            ProductCount = order.OrderItems.Count  // Converted to COUNT() in SQL
        })
        .ToListAsync();
}

Best Practice:
Projection with Select() is often faster than Include() because it fetches only the columns you need, not entire entities.

This generates a single SQL query with proper joins and only transfers the data you actually need.

Fix #2: Use Projection for Better Performance

Even better, use projection to fetch only the data you need:

// BEST: Single query, minimal data transfer
public async Task<List<OrderDto>> GetOrdersAsync()
{
    return await _context.Orders
        .Select(order => new OrderDto
        {
            Id = order.Id,
            CustomerName = order.Customer.Name,    // EF Core handles the join
            ProductCount = order.OrderItems.Count  // Converted to COUNT() in SQL
        })
        .ToListAsync();
}

This generates a single SQL query with proper joins and only transfers the data you actually need.


2. Premature Query Execution: The ToList() Trap

Many developers call ToList() too early, forcing query execution before applying additional filters or operations. This leads to unnecessary data transfer and memory usage.

ToList() Trap Warning:
Calling ToList() or ToArray() executes the query immediately. Build your complete query first, then materialize the results.

// BAD: Executes query immediately, then filters in memory
public async Task<List<Product>> GetExpensiveProductsAsync(decimal minPrice)
{
    var allProducts = await _context.Products.ToListAsync(); // Fetches ALL products
    return allProducts.Where(p => p.Price > minPrice).ToList(); // Filters in memory
}

// GOOD: Builds query expression, executes once
public async Task<List<Product>> GetExpensiveProductsAsync(decimal minPrice)
{
    return await _context.Products
        .Where(p => p.Price > minPrice)  // Converted to SQL WHERE clause
        .ToListAsync();
}

The first version fetches every product from the database, then filters in memory. The second version adds a WHERE clause to the SQL query and only fetches matching records.

💡 Performance Impact:
The first version downloads 100,000 products to filter out 500. The second version only downloads the 500 you actually need.

The first version fetches every product from the database, then filters in memory. The second version adds a WHERE clause to the SQL query and only fetches matching records.

Best Practice:
Think of LINQ queries as SQL builders, not collections. Chain your operations (Where, OrderBy, Take) before calling ToList() or ToArray().


3. Where’s Your Code Running? Database or Memory

EF Core has to decide: should this filtering happen in SQL (fast) or in your C# code after pulling all the data (painfully slow)? The technical difference is between Expression<Func<T, bool>> (becomes SQL) and Func<T, bool> (runs in .NET).

Critical Distinction:
Expression<Func<T, bool>> becomes SQL WHERE clauses. Func<T, bool> forces all data into memory first.

// BAD: Client-side evaluation
public async Task<List<Order>> GetRecentOrdersAsync()
{
    Func<Order, bool> recentFilter = o => o.CreatedAt > DateTime.Now.AddDays(-30);
    
    // This fetches ALL orders, then filters in memory
    return await _context.Orders.Where(recentFilter).ToListAsync();
}

// GOOD: Server-side evaluation
public async Task<List<Order>> GetRecentOrdersAsync()
{
    Expression<Func<Order, bool>> recentFilter = o => o.CreatedAt > DateTime.Now.AddDays(-30);
    
    // This adds WHERE clause to SQL query
    return await _context.Orders.Where(recentFilter).ToListAsync();
}

Even simpler, skip the variable entirely:

// BEST: Direct expression
public async Task<List<Order>> GetRecentOrdersAsync()
{
    return await _context.Orders
        .Where(o => o.CreatedAt > DateTime.Now.AddDays(-30))
        .ToListAsync();
}

💡 Pro Tip:
When in doubt, use direct LINQ expressions like .Where(x => x.Property == value). EF Core handles the SQL translation automatically.

The Hidden AsEnumerable/ToList Trap

A particularly sneaky issue happens when you use C# functions that aren’t translatable to SQL:

// BAD: Forces client evaluation with AsEnumerable()
public async Task<List<Product>> GetDiscountedProductsAsync()
{
    return await _context.Products
        .AsEnumerable()  // Pulls ALL products into memory!
        .Where(p => CalculateDiscountedPrice(p) < 50.00m)
        .ToListAsync();
}

// GOOD: Keep as much as possible in SQL
public async Task<List<Product>> GetDiscountedProductsAsync()
{
    return await _context.Products
        .Where(p => p.Price * (1 - p.DiscountRate) < 50.00m)  // SQL-translatable
        .ToListAsync();
}

Memory Killer:
AsEnumerable() is a silent performance destroyer. It forces EF Core to download ALL data before applying any subsequent filtering.

In our retail app, removing an AsEnumerable() call reduced memory usage from 1.2GB to 50MB when filtering 200,000 products.

Best Practice:
Replace custom C# methods with SQL-equivalent operations. Use database functions instead of pulling data into memory for processing.


4. Stop Fetching Data You Don’t Need

Why download a product’s entire 2MB description when you just need its name and price? Pulling complete entities when you only need a few fields wastes bandwidth and memory, especially with text blobs or binary data.

Data Transfer Rule:
Only fetch the columns you actually use. Large text fields and binary data can turn a 1KB response into a 2MB response.

// BAD: Fetches all columns including large Description field
public async Task<List<ProductSummary>> GetProductSummariesAsync()
{
    var products = await _context.Products.ToListAsync();
    
    return products.Select(p => new ProductSummary
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price
        // Description field (potentially large) is fetched but unused
    }).ToList();
}

// GOOD: Projects to exactly what's needed
public async Task<List<ProductSummary>> GetProductSummariesAsync()
{
    return await _context.Products
        .Select(p => new ProductSummary
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price
        })
        .ToListAsync();
}

💡 SQL Translation:
The second version generates SELECT Id, Name, Price instead of SELECT *, reducing network traffic and memory usage.

Performance Impact:
Projection can reduce data transfer by 80-90% when entities have large text or binary fields.

Projection Benefits:
Projection with Select() fetches only the columns you need, dramatically reducing bandwidth and memory usage for entities with large text or binary fields.

Hidden Cost:
That innocent-looking product description field might be 2MB per record. Multiply by 1,000 products and you’re downloading 2GB of data you never use!


5. Your Database Needs a Map: Add Proper Indexes

EF Core writes SQL, but your database needs the right indexes to run those queries quickly. Without proper indexes, a query that should take 20ms might take 5 seconds. It’s like trying to find a book in a library with no organization system.

Index Performance Rule:
Missing indexes turn lightning-fast queries into table scans. A 20ms query becomes a 5-second nightmare without proper indexing.

Common Indexing Mistakes

  1. Missing filter indexes:
// Without index: Full table scan (slow with millions of orders)
// With index: Direct lookup (fast)
var pendingOrders = await _context.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .OrderBy(o => o.CreatedAt)
    .Take(50)
    .ToListAsync();

Performance Impact:
Without an index on Status, this query scans every row in a million-record table. With an index, it finds only the pending orders instantly.

  1. Missing foreign key indexes:
// Will be slow without index on CustomerId
var customerOrders = await _context.Orders
    .Where(o => o.CustomerId == customerId)
    .ToListAsync();

Foreign Key Warning:
EF Core doesn’t automatically create indexes on foreign keys. Always add indexes for columns used in WHERE clauses and JOINs.

  1. Missing covering indexes for projections:
// Better with index including both Status and Total columns
var orderSummaries = await _context.Orders
    .Where(o => o.Status == OrderStatus.Completed)
    .Select(o => new { o.Id, o.Total })
    .ToListAsync();

Covering Index Tip:
A covering index includes all columns needed by the query, eliminating the need to access the actual table data. This can make queries 5-10x faster.

Create the necessary indexes in your migrations:

protected override void Up(MigrationBuilder migrationBuilder)
{
    // For the pending orders query
    migrationBuilder.CreateIndex(
        name: "IX_Orders_Status_CreatedAt",
        table: "Orders",
        columns: new[] { "Status", "CreatedAt" });
        
    // For customer's orders
    migrationBuilder.CreateIndex(
        name: "IX_Orders_CustomerId",
        table: "Orders",
        column: "CustomerId");
}

💡 Index Strategy:

  • Create indexes for every column used in WHERE clauses
  • Add composite indexes for multi-column filters
  • Include frequently selected columns in covering indexes
  • Monitor query execution plans to identify missing indexes

Index Trade-offs:
Indexes speed up reads but slow down writes. For read-heavy applications, this trade-off is almost always worth it.


6. Turn Off Change Tracking When Reading Data

EF Core watches for changes to entities by default, which eats memory and CPU time. When you’re just reading data, tell EF Core to stop tracking with AsNoTracking().

Memory Saver:
Change tracking can use 30-50% more memory. For read-only operations, AsNoTracking() eliminates this overhead entirely.

// BAD: Tracks entities that will never be updated
public async Task<List<OrderDto>> GetOrderHistoryAsync(int customerId)
{
    var orders = await _context.Orders
        .Where(o => o.CustomerId == customerId)
        .Include(o => o.OrderItems)
        .ToListAsync();
    
    return orders.Select(o => new OrderDto { /* mapping */ }).ToList();
}

// GOOD: Disables change tracking for read-only operations
public async Task<List<OrderDto>> GetOrderHistoryAsync(int customerId)
{
    var orders = await _context.Orders
        .AsNoTracking()  // Improves performance and reduces memory usage
        .Where(o => o.CustomerId == customerId)
        .Include(o => o.OrderItems)
        .ToListAsync();
    
    return orders.Select(o => new OrderDto { /* mapping */ }).ToList();
}

💡 When to Use AsNoTracking():

  • Read-only queries for DTOs or ViewModels
  • Data export operations
  • Reporting queries
  • Any scenario where you won’t update the entities

7. Lazy Loading: Convenience That Kills Performance

Lazy loading seems helpful, just access a property and EF Core magically loads the data. But this magic comes at a cost: surprise database queries that you didn’t plan for, often leading to the N+1 problem we talked about earlier.

Lazy Loading Warning:
Every navigation property access can trigger a database round trip. In loops, this creates the N+1 query problem all over again.

// BAD: Lazy loading triggers queries inside the loop
public async Task<decimal> CalculateOrderTotalAsync(int orderId)
{
    var order = await _context.Orders.FindAsync(orderId);
    
    decimal total = 0;
    foreach (var item in order.OrderItems) // Triggers query here
    {
        total += item.Price * item.Quantity;
    }
    
    return total;
}

// GOOD: Explicitly load related data
public async Task<decimal> CalculateOrderTotalAsync(int orderId)
{
    var order = await _context.Orders
        .Include(o => o.OrderItems)
        .FirstOrDefaultAsync(o => o.Id == orderId);
    
    return order.OrderItems.Sum(item => item.Price * item.Quantity);
}

Disable Lazy Loading:
Consider disabling lazy loading globally and using explicit Include() or projection for better performance predictability:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLazyLoadingProxies(false);
}

8. Be Careful with Navigation Properties

Even with lazy loading turned off, how you access related objects can make or break your performance:

// BAD: Accessing navigation properties in inefficient ways
public async Task<List<ProductViewModel>> GetCatalogItemsAsync(int categoryId)
{
    var products = await _context.Products
        .Where(p => p.CategoryId == categoryId)
        .ToListAsync();
        
    return products.Select(p => new ProductViewModel
    {
        Id = p.Id,
        Name = p.Name,
        CategoryName = p.Category.Name,  // If Category isn't loaded, this is null
        Price = p.Price
    }).ToList();
}

// GOOD: Explicit joins with projection
public async Task<List<ProductViewModel>> GetCatalogItemsAsync(int categoryId)
{
    return await _context.Products
        .Where(p => p.CategoryId == categoryId)
        .Select(p => new ProductViewModel
        {
            Id = p.Id,
            Name = p.Name,
            CategoryName = p.Category.Name,  // Properly joined in SQL
            Price = p.Price
        })
        .ToListAsync();
}

In a real e-commerce application, the first approach caused NullReferenceExceptions in production when the Category navigation property was null. The second approach guarantees the data is loaded via SQL JOIN.


9. Get Your DbContext Lifetime Right

Creating a new DbContext for every query is bad. Keeping one alive forever is worse. You need to match your DbContext’s lifespan to your actual work.

DbContext Lifecycle Rule:
One DbContext per logical operation (web request, business transaction). Not per query, not per application lifetime.

// BAD: Creates new context for every operation
public class OrderService
{
    public async Task<Order> GetOrderAsync(int id)
    {
        using var context = new AppDbContext();
        return await context.Orders.FindAsync(id);
    }
    
    public async Task<List<Order>> GetOrdersAsync()
    {
        using var context = new AppDbContext();
        return await context.Orders.ToListAsync();
    }
}

// GOOD: Uses injected, properly scoped context
public class OrderService
{
    private readonly AppDbContext _context;
    
    public OrderService(AppDbContext context)
    {
        _context = context;
    }
    
    public async Task<Order> GetOrderAsync(int id)
    {
        return await _context.Orders.FindAsync(id);
    }
    
    public async Task<List<Order>> GetOrdersAsync()
    {
        return await _context.Orders.ToListAsync();
    }
}

Register the DbContext with appropriate lifetime in your DI container:

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString), ServiceLifetime.Scoped);

💡 Connection Pool Impact:
Creating new contexts exhausts connection pools. Scoped lifetime ensures one context per web request, optimizing both performance and resource usage.


10. Speed Up Repeated Queries with Compilation

When you run the same query hundreds of times, EF Core rebuilds the query plan each time by default. This is like Google Maps recalculating your route every time you drive to work. Compiled queries fix this.

When to Use Compiled Queries:
Only for identical queries executed frequently (100+ times per request). The compilation overhead isn’t worth it for queries run just a few times.

// BAD: Query recompiled on each call
public async Task<Customer> GetCustomerByIdAsync(int customerId)
{
    return await _context.Customers.FirstOrDefaultAsync(c => c.Id == customerId);
}

// GOOD: Compiled once, reused many times
private static readonly Func<AppDbContext, int, Task<Customer>> _getCustomerById =
    EF.CompileAsyncQuery((AppDbContext context, int id) => 
        context.Customers.FirstOrDefault(c => c.Id == id));

public async Task<Customer> GetCustomerByIdAsync(int customerId)
{
    return await _getCustomerById(_context, customerId);
}

Compilation Benefits:
For high-frequency queries, compilation eliminates query plan overhead. Best for lookup operations called hundreds of times per request.

For a product catalog with 50 database lookups per page load, this reduced CPU usage by 15% and improved response time from 220ms to 180ms in our e-commerce app.


11. Performance Monitoring and Debugging

Debug Like a Pro:
You can’t optimize what you can’t measure. Enable query logging to see exactly what SQL EF Core generates for your LINQ queries.

Enable Query Logging

Configure EF Core to log generated SQL queries:

// appsettings.Development.json
{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

Production Warning:
Only enable detailed query logging in development. In production, it can impact performance and expose sensitive data in logs.

Use ToQueryString() for Debugging

Inspect generated SQL without executing the query:

var query = _context.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .Include(o => o.Customer);

var sql = query.ToQueryString();
Console.WriteLine(sql);

SQL Inspection Tip:
Use ToQueryString() to verify EF Core generates the SQL you expect. Essential for catching inefficient queries before they hit production.

Measure Query Performance

Add timing to identify slow queries:

public async Task<List<Order>> GetOrdersAsync()
{
    var stopwatch = Stopwatch.StartNew();
    
    var orders = await _context.Orders
        .AsNoTracking()
        .Include(o => o.Customer)
        .ToListAsync();
    
    stopwatch.Stop();
    _logger.LogInformation("GetOrdersAsync took {ElapsedMs}ms", stopwatch.ElapsedMilliseconds);
    
    return orders;
}

Performance Baseline:
Set clear performance targets (e.g., <100ms for lists, <50ms for lookups). Anything slower needs investigation and optimization.


Common Mistakes Summary

MistakeImpactFix
N+1 Queries100x more database callsUse Include() or projection
Premature ToList()Full table scansBuild expressions before executing
Client-side evaluationAll data transferred to appUse Expression<Func<T, bool>>
AsEnumerable() trapForces client evaluationKeep operations SQL-translatable
Over-fetchingUnnecessary bandwidth/memoryUse Select() projection
Missing indexesSlow query executionAdd indexes for common queries
Unnecessary trackingMemory and CPU overheadUse AsNoTracking() for read-only
Lazy loading N+1Hidden performance problemsExplicitly load related data
Poor context lifetimeConnection pool exhaustionUse scoped lifetime in DI
Non-compiled queriesCPU overhead on repeated queriesUse EF.CompileQuery for hotpaths
Individual operationsExcessive round tripsBatch operations, use ExecuteUpdateAsync

Performance Checklist

Before releasing any feature that uses EF Core, run through this checklist with specific metrics:

Performance Goals:

  • ≤3 database calls per screen/operation
  • ≤100ms response time for typical queries
  • Zero N+1 query patterns in production
  1. Query Count: How many database calls does this feature make? Target: <=3 per screen

    • Use SQL logging to verify actual query count
    • Look for N+1 patterns in logs (repeated similar queries)
  2. Data Transfer: Are you fetching only required columns?

    • Check .ToQueryString() output for SELECT * vs targeted columns
    • For entities >10 columns, always use projection unless you need all fields
  3. Execution Location: Is filtering happening in SQL or in memory?

    • Verify WHERE clauses appear in generated SQL, not just LINQ
    • Watch for client evaluation warnings in logs
  4. Indexes: Will queries use existing indexes or need new ones?

    • Add .Include(SQL_PLAN) comments to verify index usage
    • Create indexes for columns in WHERE, JOIN, and ORDER BY clauses
  5. Change Tracking: Is tracking disabled for read-only operations?

    • Use AsNoTracking() for all read-only queries
    • Measure memory usage with and without tracking
  6. Bulk Operations: Are you using the most efficient batch operations?

    • Never use individual SaveChanges in loops
    • Consider specialized bulk libraries for >1000 records
  7. Response Time: Have you tested with realistic data volumes?

    • Test with 10x your expected initial data
    • Set clear performance SLAs (e.g., <200ms for list operations)

Don’t Process Records One by One

Processing records individually is like checking out groceries one item at a time. Batch operations exist for a reason.

// BAD: Individual inserts - causes N round trips
public async Task ImportProductsAsync(List<ProductDto> products)
{
    foreach (var product in products)
    {
        var entity = new Product
        {
            Name = product.Name,
            Price = product.Price,
            CategoryId = product.CategoryId
        };
        
        _context.Products.Add(entity);
        await _context.SaveChangesAsync(); // Database round-trip for EACH product!
    }
}

// GOOD: Batch all inserts - single round trip
public async Task ImportProductsAsync(List<ProductDto> products)
{
    foreach (var product in products)
    {
        var entity = new Product
        {
            Name = product.Name,
            Price = product.Price,
            CategoryId = product.CategoryId
        };
        
        _context.Products.Add(entity);
    }
    
    await _context.SaveChangesAsync(); // Single database round-trip
}

// BETTER: Use EF Core ExecuteUpdateAsync for bulk updates (EF Core 7+)
public async Task ApplyDiscountAsync(int categoryId, decimal discountPercentage)
{
    await _context.Products
        .Where(p => p.CategoryId == categoryId)
        .ExecuteUpdateAsync(s => 
            s.SetProperty(p => p.Price, p => p.Price * (1 - discountPercentage / 100)));
}

In a recent catalog import job, switching from individual inserts to batched operations reduced import time from 45 minutes to under 2 minutes for 10,000 products.


The Grocery Store Model

Here’s how I think about database access: it’s like grocery shopping. Before you go to the store (database), make a specific list of what you need. Don’t just grab random things and figure it out later at home, that’s wasteful and expensive.

Make one efficient trip with a clear list (use projection), take the fastest route through the store (use indexes), and avoid running back and forth for items you forgot (eliminate N+1 queries).

Getting good at EF Core isn’t about memorizing tricks, it’s about respecting what database operations actually cost. When you understand the real price of each query, you’ll naturally write better code. Get these basics right, and your apps will stay fast even as they grow.

Related Posts