EF Core Performance Tuning: Boost Your .NET App Speed

Entity Framework Core (EF Core) is a powerful ORM that simplifies data access in .NET applications. However, with great power comes great responsibility—and potential performance pitfalls. A poorly tuned EF Core query can bring your application to a crawl, especially under high load. In this advanced tutorial, we'll dive deep into performance tuning techniques that every senior .NET developer should know. You'll learn how to identify bottlenecks, optimize queries, and avoid common anti-patterns. We'll cover eager loading vs lazy loading, compiled queries, batch operations, and more. By the end, you'll be able to tune EF Core like a pro, ensuring your apps scale gracefully. Let's start with a real-world incident that taught us the hard way.

Understanding EF Core Query Lifecycle

Before tuning, you must understand how EF Core translates LINQ queries to SQL. The process involves: (1) LINQ expression tree compilation, (2) query plan caching, (3) SQL generation, (4) database execution, and (5) materialization. Each step can be optimized. For example, compiled queries skip step 1 on subsequent calls. Also, EF Core caches the query model (mapping) but not the SQL plan by default. We'll explore these internals with examples.

using (var context = new AppDbContext())
{
    // First call: compiles LINQ, generates SQL, executes
    var products = context.Products.Where(p => p.Price > 100).ToList();
    
    // Second call: uses cached model, but still generates SQL again
    var products2 = context.Products.Where(p => p.Price > 100).ToList();
}

Eager Loading vs Lazy Loading: The N+1 Problem

Lazy loading is convenient but dangerous. It defers loading of navigation properties until accessed, which can cause N+1 queries. Eager loading loads related data in a single query using Include() and ThenInclude(). Always prefer eager loading for collections that will be accessed. If you must use lazy loading, ensure it's disabled in production via configuration or by not installing the proxy package.

// Lazy loading (bad)
var products = context.Products.ToList();
foreach (var p in products)
{
    Console.WriteLine(p.Category.Name); // triggers separate query per product
}

// Eager loading (good)
var products = context.Products.Include(p => p.Category).ToList();
foreach (var p in products)
{
    Console.WriteLine(p.Category.Name); // no extra query
}

AsNoTracking for Read-Only Queries

By default, EF Core tracks entities for change detection. This adds overhead. For read-only queries, use AsNoTracking() to disable tracking. This reduces memory usage and speeds up materialization. However, if you later need to update the entity, you must attach it to the context. Use AsNoTrackingWithIdentityResolution() if you need identity resolution without tracking.

// Without AsNoTracking (tracks entities)
var products = context.Products.Where(p => p.IsActive).ToList();

// With AsNoTracking (no tracking)
var products = context.Products.AsNoTracking().Where(p => p.IsActive).ToList();

Compiled Queries for Hot Paths

Compiled queries cache the LINQ expression tree translation, avoiding recompilation on every call. Use EF.CompileQuery or EF.CompileAsyncQuery for frequently executed queries. This is especially beneficial for parameterized queries in high-throughput scenarios. Note that compiled queries cannot be composed further (e.g., with additional Where clauses).

private static readonly Func<AppDbContext, decimal, IQueryable<Product>> GetExpensiveProducts =
    EF.CompileQuery((AppDbContext context, decimal minPrice) =>
        context.Products.AsNoTracking().Where(p => p.Price > minPrice));

using (var context = new AppDbContext())
{
    var products = GetExpensiveProducts(context, 100).ToList();
}

Batch Operations with ExecuteUpdate and ExecuteDelete

Traditional approach to update/delete multiple entities loads them into memory, modifies, and saves. This is inefficient. EF Core 7+ introduced ExecuteUpdate and ExecuteDelete that execute a single SQL statement without loading entities. Use them for bulk operations. They are also more efficient than raw SQL because they integrate with the query pipeline.

// Old way (loads all entities)
var products = context.Products.Where(p => p.Price < 10).ToList();
foreach (var p in products)
{
    p.Price *= 1.1;
}
context.SaveChanges();

// New way (single SQL update)
context.Products
    .Where(p => p.Price < 10)
    .ExecuteUpdate(setters => setters.SetProperty(p => p.Price, p => p.Price * 1.1));

Indexing and Query Plan Analysis

Even with perfect EF Core code, missing database indexes can kill performance. Use SQL Server's execution plan or EF Core's logging to identify table scans. Add indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY. Also consider covering indexes to avoid key lookups. Use the Database.ExecuteSqlRaw to create indexes via migrations.

// Add index via migration
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateIndex(
        name: "IX_Products_Price",
        table: "Products",
        column: "Price");
}

// Or raw SQL
context.Database.ExecuteSqlRaw("CREATE INDEX IX_Products_Price ON Products (Price)");

Connection Pooling and Context Management

EF Core uses ADO.NET connection pooling by default. However, creating and disposing DbContext instances frequently can still be expensive. Use DbContext pooling (AddDbContextPool) to reuse context instances. Also, avoid long-lived contexts; use short-lived contexts per unit of work. For web apps, use a new context per request.

// In Program.cs
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// Usage in controller
public class ProductsController : ControllerBase
{
    private readonly AppDbContext _context;
    public ProductsController(AppDbContext context) => _context = context;
    
    [HttpGet]
    public IActionResult Get() => Ok(_context.Products.AsNoTracking().ToList());
}