Entity Framework Core - Query Optimization Strategy

Optimization in EF Core is not just about writing faster LINQ; it is about understanding how EF Core translates our code into SQL and managing memory effectively.

The following guide covers the most critical optimization strategies, ranging from "low-hanging fruit" to solving complex industry-standard problems like the N+1 problem and Cartesian Explosion.

---

1. The "Must-Dos" (Low-Hanging Fruit)

These are optimizations we should apply to almost every query by default.

A. Use AsNoTracking for Read-Only Queries

By default, EF Core tracks changes to every entity it retrieves (so it can save changes later). This involves significant overhead (snapshotting, identity resolution). If we are just displaying data, disable it.

• Impact: Reduces memory usage and CPU time by ~2-5x.

• Code:

    // BAD: Tracks entities unnecessarily
    var products = context.Products.ToList();
  
    // GOOD: Skips tracking overhead
    var products = context.Products.AsNoTracking().ToList();
  

B. Project Only What We Need (Select)

The most common mistake is fetching a generic SELECT * (entire entity) when we only need 2-3 columns. This wastes network bandwidth and database IO.

• Impact: Massive reduction in data payload.

• Code:

    // BAD: Selects all 50 columns including heavy 'Description' and 'ImageBlob'
    var users = context.Users.ToList();
  
    // GOOD: Selects only needed columns
    var users = context.Users
        .Select(u => new { u.Id, u.Name }) // Anonymous type or DTO
        .ToList();
  

---

2. Solving Industry "Killer" Issues

These two issues are responsible for the majority of database performance bottlenecks in enterprise applications.

A. The N+1 Problem

This occurs when we load a parent entity and then iterate over it to access children, causing EF Core to fire one query for the parent and then N separate queries for N children.

• Scenario: We have 1,000 orders. We loop through them to print the customer name.

• Result: 1 query for Orders + 1,000 queries for Customers = 1,001 Database Roundtrips.

• Fix: Use Eager Loading (Include).

// BAD: Triggers N+1 queries if lazy loading is on, or returns nulls if off
var orders = context.Orders.ToList();
foreach(var order in orders) {
    Console.WriteLine(order.Customer.Name); // May trigger a DB call per loop
}

// GOOD: Fetches everything in 1 query using JOIN
var orders = context.Orders
    .Include(o => o.Customer)
    .ToList();

B. Cartesian Explosion (and how to fix it with AsSplitQuery)

While Include fixes N+1, it introduces a new problem. If we Include multiple collections (e.g., An Order has many Items, and Items have many Tags), SQL Server produces a result set that is the product of all rows (Cartesian Product).

• Scenario: 1 Order has 10 Items, each Item has 5 Tags.

• Result: The database returns 1 10 5 = 50 rows for just ONE order. If we fetch 100 orders, we get 5,000 rows of duplicate data.

• Fix: Use Query Splitting. EF Core 5+ allows we to split this into separate SQL queries (one for Orders, one for Items, one for Tags) and stitch them in memory.

// GOOD: Fixes Cartesian Explosion
var orders = context.Orders
    .Include(o => o.Items)
    .ThenInclude(i => i.Tags)
    .AsSplitQuery() // <--- THE MAGIC KEYWORD
    .ToList();

Industry Rule of Thumb: Use AsSplitQuery whenever we Include more than one collection navigation.

---

3. Advanced High-Performance Techniques

A. Bulk Updates & Deletes (EF Core 7+)

Historically, to update 1,000 records, we had to fetch 1,000 records into memory, modify them, and call SaveChanges(). This is incredibly slow.

EF Core 7 introduced ExecuteUpdate and ExecuteDelete to run raw SQL-like logic without memory overhead.

// BAD: Fetches all data, modifies in memory, sends 1000 UPDATE statements
var inactiveUsers = context.Users.Where(u => u.LastLogin < DateTime.Now.AddYears(-1));
foreach(var user in inactiveUsers) user.IsDeleted = true;
context.SaveChanges();

// GOOD: Sends single SQL UPDATE statement (Zero memory overhead)
context.Users
    .Where(u => u.LastLogin < DateTime.Now.AddYears(-1))
    .ExecuteUpdate(s => s.SetProperty(u => u.IsDeleted, true));

B. Compiled Queries

Parsing LINQ to SQL takes time. For "Hot Paths" (queries executed thousands of times per minute), we can compile the query once and reuse the execution plan.

// Global static compiled query
private static readonly Func<MyDbContext, int, IEnumerable<Order>> _getOrdersByUser =
    EF.CompileQuery((MyDbContext ctx, int userId) =>
        ctx.Orders.Where(o => o.UserId == userId));

// Usage (skips LINQ translation overhead)
var orders = _getOrdersByUser(context, 123);

C. DbContext Pooling

Creating a DbContext instance is lightweight but not free. In high-throughput APIs (e.g., 10k requests/sec), the Garbage Collector (GC) gets overwhelmed destroying Context objects.

• Fix: Enable pooling in Program.cs. EF Core will reset and reuse Context objects.

// In Program.cs
builder.Services.AddDbContextPool<MyDbContext>(options => ...);

---

4. Database-Level Optimization (SARGability)

Even the best LINQ is useless if the underlying SQL cannot use Indexes. We must write SARGable (Search ARGument ABLE) queries. This means avoiding functions on the database column in the WHERE clause.

• Scenario: Find users created in 2023.

// BAD: Non-Sargable. Database must scan every single row to calculate Year().
// Index on CreatedDate is IGNORED.
var users = context.Users.Where(u => u.CreatedDate.Year == 2023).ToList();

// GOOD: Sargable. Uses ranges. Index on CreatedDate is USED.
var start = new DateTime(2023, 1, 1);
var end = new DateTime(2024, 1, 1);
var users = context.Users
    .Where(u => u.CreatedDate >= start && u.CreatedDate < end)
    .ToList();

---

5. Summary Checklist