A Simple Analogy

Optimizing EF queries is like tuning an engine. Small adjustments yield big speed improvements without changing overall design.

---

N+1 Problem

// Bad: N+1 queries (1 for orders + N for each customer)
var orders = await context.Orders.ToListAsync();
foreach (var order in orders)
{
    var customer = await context.Customers.FindAsync(order.CustomerId);  // N queries!
    Console.WriteLine($"{order.Id}: {customer.Name}");
}

// Good: Single query with join
var orders = await context.Orders
    .Include(o => o.Customer)  // Eager load
    .ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");  // No extra query
}

// Or: Projection
var result = await context.Orders
    .Select(o => new
    {
        o.Id,
        CustomerName = o.Customer.Name
    })
    .ToListAsync();  // One query!

---

Query Projection

// Bad: Loads entire entity into memory
var orders = await context.Orders
    .Where(o => o.Total > 100)
    .ToListAsync();  // Huge dataset

var result = orders
    .Select(o => new { o.Id, o.Total })
    .ToList();

// Good: Project at database level
var result = await context.Orders
    .Where(o => o.Total > 100)
    .Select(o => new { o.Id, o.Total })
    .ToListAsync();  // Much smaller dataset

---## Indexing Strategy

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Single column index (frequently filtered)
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.CustomerId)
        .HasName("idx_order_customer");
    
    // Composite index (WHERE CustomerId AND Status)
    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.Status })
        .HasName("idx_order_customer_status");
    
    // Unique index
    modelBuilder.Entity<User>()
        .HasIndex(u => u.Email)
        .IsUnique()
        .HasName("idx_user_email_unique");
    
    // Filtered index (only active orders)
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.CustomerId)
        .HasFilter("[Status] = 'Active'")
        .HasName("idx_active_orders");
}

---

Split Queries

// Bad: Cartesian explosion
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .ToListAsync();
// If customer has 5 items, result multiplies unnecessarily

// Good: Split into multiple queries
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .AsSplitQuery()  // Multiple queries instead of joins
    .ToListAsync();

---

Compiled Queries

// Reusable, pre-compiled queries (faster)
private static readonly Func<AppDbContext, int, Task<Order>> GetOrderById =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) =>
        ctx.Orders
            .Include(o => o.Items)
            .FirstOrDefault(o => o.Id == id));

// Usage
var order = await GetOrderById(context, orderId);

---

Batch Operations

// Bad: Individual saves (100 queries)
foreach (var order in orders)
{
    context.Orders.Add(order);
    await context.SaveChangesAsync();  // Slow!
}

// Good: Batch insert
context.Orders.AddRange(orders);
await context.SaveChangesAsync();  // Single batch operation

// Even better: Bulk operations (EF Core Power Tools)
await context.BulkInsertAsync(orders);
await context.BulkUpdateAsync(orders);
await context.BulkDeleteAsync(orders);

---

Asynchronous Loading

// Bad: Synchronous
var order = context.Orders.FirstOrDefault(o => o.Id == id);
var items = order.Items;  // Blocks thread

// Good: Asynchronous
var order = await context.Orders.FirstOrDefaultAsync(o => o.Id == id);
var items = order.Items;  // Already loaded (eager loaded)

// Async enumeration
await foreach (var order in context.Orders.AsAsyncEnumerable())
{
    ProcessOrder(order);
}

---

Best Practices

1. Use projections: Don't load unnecessary columns
2. Include relationships: Avoid N+1
3. Index properly: Add indexes for frequent queries
4. Monitor queries: Use profiler to find slow queries
5. Batch operations: Insert/update in batches

---

Related Concepts

• SQL query analysis
• Database statistics
• Query hints
• Execution plans

---

Summary

Optimize EF Core by using projections, including relationships, creating indexes, and batching operations. Monitor generated SQL to identify bottlenecks.