Mastering Entity Framework Core: A Guide to Efficient Data Access and Common Pitfalls

Mastering Entity Framework Core: A Guide to Efficient Data Access and Common Pitfalls

A few months ago, I found myself staring at a query that was way slower than it should’ve been. What started as a simple request for a list of blog posts ballooned into hundreds of queries hitting the database, one for each related author, category, and comment. My CPU was fine. My database was fine. The problem? I wasn’t.

Like many developers, I had fallen into some classic Entity Framework Core traps without realizing it—lazy loading, improper projections, and unoptimized query patterns. After a long night of debugging and tweaking, I finally got things running smoothly. And I figured: if I hit these issues, then others are likely to as well.

So I decided to write the guide I wish I had when I was pulling my hair out.

Entity Framework Core (EF Core) is Microsoft’s go-to object-relational mapper (ORM) for .NET developers, and for good reason. It makes data access clean, testable, and powerful. But with that power comes a whole set of performance landmines that can cripple your application if you’re not careful.

In this post, I’ll walk you through the most common EF Core performance pitfalls—and show you the wrong and right ways to handle them, with code examples and practical tips you can use immediately.

Understanding EF Core's Lazy Loading Trap

One of the most notorious performance killers in EF Core applications is the N+1 query problem, often caused by lazy loading. This occurs when you retrieve a list of entities and then access related data for each entity individually.

The Wrong Way: Triggering N+1 Queries

// ❌ BAD: This will execute N+1 queries
public async Task<List<BlogPostViewModel>> GetBlogPostsAsync()
{
    var posts = await _context.BlogPosts.ToListAsync();
    
    var result = new List<BlogPostViewModel>();
    foreach (var post in posts)
    {
        result.Add(new BlogPostViewModel
        {
            Title = post.Title,
            Content = post.Content,
            AuthorName = post.Author.Name, // Lazy loading triggers a query for each post
            CategoryName = post.Category.Name, // Another query for each post
            CommentCount = post.Comments.Count() // Yet another query for each post
        });
    }
    
    return result;
}

If you have 100 blog posts, this code will execute 301 queries: 1 to get the posts, 100 to get authors, 100 to get categories, and 100 to get comment counts.

The Right Way: Using Eager Loading with Include

// ✅ GOOD: Using Include to load related data in a single query
public async Task<List<BlogPostViewModel>> GetBlogPostsAsync()
{
    var posts = await _context.BlogPosts
        .Include(p => p.Author)
        .Include(p => p.Category)
        .Include(p => p.Comments)
        .ToListAsync();
    
    return posts.Select(post => new BlogPostViewModel
    {
        Title = post.Title,
        Content = post.Content,
        AuthorName = post.Author.Name,
        CategoryName = post.Category.Name,
        CommentCount = post.Comments.Count
    }).ToList();
}

Even Better: Using Projections to Select Only What You Need

// ✅ BETTER: Using projections to select only required data
public async Task<List<BlogPostViewModel>> GetBlogPostsAsync()
{
    return await _context.BlogPosts
        .Select(p => new BlogPostViewModel
        {
            Title = p.Title,
            Content = p.Content,
            AuthorName = p.Author.Name,
            CategoryName = p.Category.Name,
            CommentCount = p.Comments.Count()
        })
        .ToListAsync();
}

This approach loads only the data you actually need and executes a single, optimized query.

Avoiding the "Select N+1" Anti-Pattern with Batch Loading

When you need to load related data conditionally or in complex scenarios, consider using batch loading techniques.

The Wrong Way: Individual Queries in Loops

// ❌ BAD: Loading related data individually
public async Task<List<OrderSummary>> GetOrderSummariesAsync(List<int> orderIds)
{
    var summaries = new List<OrderSummary>();
    
    foreach (var orderId in orderIds)
    {
        var order = await _context.Orders
            .Include(o => o.OrderItems)
            .ThenInclude(oi => oi.Product)
            .FirstOrDefaultAsync(o => o.Id == orderId);
            
        if (order != null)
        {
            summaries.Add(new OrderSummary
            {
                OrderId = order.Id,
                TotalAmount = order.OrderItems.Sum(oi => oi.Quantity * oi.Product.Price),
                ItemCount = order.OrderItems.Count
            });
        }
    }
    
    return summaries;
}

The Right Way: Batch Loading

// ✅ GOOD: Loading all data in a single query
public async Task<List<OrderSummary>> GetOrderSummariesAsync(List<int> orderIds)
{
    return await _context.Orders
        .Where(o => orderIds.Contains(o.Id))
        .Select(o => new OrderSummary
        {
            OrderId = o.Id,
            TotalAmount = o.OrderItems.Sum(oi => oi.Quantity * oi.Product.Price),
            ItemCount = o.OrderItems.Count()
        })
        .ToListAsync();
}

Managing DbContext Lifecycle Properly

One critical aspect of EF Core that developers often get wrong is DbContext lifecycle management. The DbContext is designed to be short-lived and should not be shared across multiple operations.

The Wrong Way: Long-lived DbContext

// ❌ BAD: Keeping DbContext alive for too long
public class BadUserService
{
    private readonly ApplicationDbContext _context;
    
    public BadUserService(ApplicationDbContext context)
    {
        _context = context; // This context might be shared across requests
    }
    
    public async Task<User> GetUserAsync(int id)
    {
        return await _context.Users.FindAsync(id);
    }
    
    public async Task UpdateUserAsync(User user)
    {
        _context.Users.Update(user);
        await _context.SaveChangesAsync();
    }
}

The Right Way: Proper DbContext Lifecycle

// ✅ GOOD: Using scoped DbContext or explicit disposal
public class GoodUserService
{
    private readonly IDbContextFactory<ApplicationDbContext> _contextFactory;
    
    public GoodUserService(IDbContextFactory<ApplicationDbContext> contextFactory)
    {
        _contextFactory = contextFactory;
    }
    
    public async Task<User> GetUserAsync(int id)
    {
        using var context = _contextFactory.CreateDbContext();
        return await context.Users.FindAsync(id);
    }
    
    public async Task UpdateUserAsync(User user)
    {
        using var context = _contextFactory.CreateDbContext();
        context.Users.Update(user);
        await context.SaveChangesAsync();
    }
}

For dependency injection in ASP.NET Core, use the scoped lifetime:

// In Program.cs or Startup.cs
services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString), ServiceLifetime.Scoped);

Optimizing Queries with Proper Indexing and Query Structure

EF Core generates SQL queries based on your LINQ expressions, but not all LINQ expressions are created equal in terms of performance.

The Wrong Way: Inefficient Query Patterns

// ❌ BAD: Using client-side evaluation
public async Task<List<Product>> GetExpensiveProductsAsync()
{
    var products = await _context.Products.ToListAsync();
    
    // This calculation happens in memory, not in the database
    return products
        .Where(p => CalculateDiscountedPrice(p.Price, p.DiscountPercentage) > 100)
        .ToList();
}

private decimal CalculateDiscountedPrice(decimal price, decimal discountPercentage)
{
    return price * (1 - discountPercentage / 100);
}

The Right Way: Database-side Evaluation

// ✅ GOOD: Using database expressions
public async Task<List<Product>> GetExpensiveProductsAsync()
{
    return await _context.Products
        .Where(p => p.Price * (1 - p.DiscountPercentage / 100) > 100)
        .ToListAsync();
}

Using Raw SQL for Complex Queries

Sometimes, complex business logic is better expressed in raw SQL:

// ✅ GOOD: Using raw SQL for complex aggregations
public async Task<List<SalesReport>> GetMonthlySalesReportAsync(int year)
{
    return await _context.SalesReports
        .FromSqlRaw(@"
            SELECT 
                MONTH(o.OrderDate) as Month,
                COUNT(*) as OrderCount,
                SUM(oi.Quantity * oi.UnitPrice) as TotalRevenue
            FROM Orders o
            INNER JOIN OrderItems oi ON o.Id = oi.OrderId
            WHERE YEAR(o.OrderDate) = {0}
            GROUP BY MONTH(o.OrderDate)
            ORDER BY MONTH(o.OrderDate)", year)
        .ToListAsync();
}

Implementing Effective Change Tracking Strategies

EF Core's change tracking can become a performance bottleneck when working with large datasets. Understanding when to disable it can significantly improve performance.

The Wrong Way: Always Using Change Tracking

// ❌ BAD: Using change tracking for read-only operations
public async Task<List<ProductReport>> GenerateProductReportAsync()
{
    var products = await _context.Products
        .Include(p => p.Category)
        .Include(p => p.OrderItems)
        .ToListAsync(); // Change tracking is enabled by default
    
    return products.Select(p => new ProductReport
    {
        ProductName = p.Name,
        CategoryName = p.Category.Name,
        TotalSold = p.OrderItems.Sum(oi => oi.Quantity),
        Revenue = p.OrderItems.Sum(oi => oi.Quantity * oi.UnitPrice)
    }).ToList();
}

The Right Way: Selective Change Tracking

// ✅ GOOD: Disabling change tracking for read-only operations
public async Task<List<ProductReport>> GenerateProductReportAsync()
{
    return await _context.Products
        .AsNoTracking() // Disable change tracking
        .Select(p => new ProductReport
        {
            ProductName = p.Name,
            CategoryName = p.Category.Name,
            TotalSold = p.OrderItems.Sum(oi => oi.Quantity),
            Revenue = p.OrderItems.Sum(oi => oi.Quantity * oi.UnitPrice)
        })
        .ToListAsync();
}

// For updates, use change tracking selectively
public async Task UpdateProductPricesAsync(List<ProductPriceUpdate> updates)
{
    var productIds = updates.Select(u => u.ProductId).ToList();
    var products = await _context.Products
        .Where(p => productIds.Contains(p.Id))
        .ToListAsync(); // Change tracking enabled for updates
    
    foreach (var update in updates)
    {
        var product = products.First(p => p.Id == update.ProductId);
        product.Price = update.NewPrice;
        product.LastUpdated = DateTime.UtcNow;
    }
    
    await _context.SaveChangesAsync();
}

Implementing Efficient Bulk Operations

When dealing with large datasets, individual entity operations can be extremely slow.

The Wrong Way: Individual Operations

// ❌ BAD: Inserting records one by one
public async Task ImportUsersAsync(List<UserImportModel> users)
{
    foreach (var userModel in users)
    {
        var user = new User
        {
            Name = userModel.Name,
            Email = userModel.Email,
            CreatedDate = DateTime.UtcNow
        };
        
        _context.Users.Add(user);
        await _context.SaveChangesAsync(); // Saves after each insert
    }
}

The Right Way: Bulk Operations

// ✅ GOOD: Bulk insert with batching
public async Task ImportUsersAsync(List<UserImportModel> users)
{
    const int batchSize = 1000;
    
    for (int i = 0; i < users.Count; i += batchSize)
    {
        var batch = users.Skip(i).Take(batchSize);
        var userEntities = batch.Select(u => new User
        {
            Name = u.Name,
            Email = u.Email,
            CreatedDate = DateTime.UtcNow
        }).ToList();
        
        _context.Users.AddRange(userEntities);
        await _context.SaveChangesAsync();
        
        // Clear tracked entities to free memory
        _context.ChangeTracker.Clear();
    }
}

Best Practices for Configuration and Performance

Configure Your DbContext for Performance

// ✅ GOOD: Optimized DbContext configuration
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString, options =>
        {
            options.CommandTimeout(30); // Set appropriate timeout
            options.EnableRetryOnFailure(); // Handle transient failures
        })
        .EnableSensitiveDataLogging(false) // Disable in production
        .EnableDetailedErrors(false) // Disable in production
        .ConfigureWarnings(warnings =>
            warnings.Throw(RelationalEventId.QueryPossibleUnintendedUseOfEqualsWarning));
}

Implement Connection Resiliency

// ✅ GOOD: Connection resiliency configuration
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString, sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null);
    });
});

Monitoring and Debugging Query Performance

Always monitor your EF Core queries in production:

// ✅ GOOD: Logging slow queries
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging() // Only in development
        .ConfigureWarnings(warnings =>
            warnings.Log(CoreEventId.FirstWithoutOrderByAndFilterWarning,
                        CoreEventId.RowLimitingOperationWithoutOrderByWarning));
}

Conclusion

Efficient use of Entity Framework Core requires understanding its internals and being mindful of the SQL queries it generates. The key principles to remember are:

  1. Always think about the SQL being generated - Use tools like EF Core's logging to see what queries are executed
  2. Avoid N+1 problems - Use Include, projections, or batch loading appropriately
  3. Manage DbContext lifecycle properly - Keep contexts short-lived and dispose them correctly
  4. Use AsNoTracking() for read-only operations - Don't track entities you won't modify
  5. Batch operations when dealing with large datasets - Avoid individual saves in loops
  6. Configure connection resiliency - Handle transient failures gracefully
  7. Monitor and profile your queries - Use logging and profiling tools to identify bottlenecks

By following these practices and avoiding the common pitfalls outlined in this guide, you'll be able to build high-performance applications that scale effectively with Entity Framework Core. Remember that premature optimization can be counterproductive, so profile your application to identify actual bottlenecks before implementing complex optimizations.

The examples provided here represent real-world scenarios that many developers encounter. By understanding both the wrong and right approaches, you'll be better equipped to write efficient, maintainable code that performs well under load.

Comments ()