A Simple Analogy

ORM optimization is like traffic management. Fewer trips, smarter routes, batching orders together.

---

Why Optimize?

• Performance: Fewer database calls
• Scalability: Handle more users
• Cost: Reduce database usage
• UX: Faster response times
• Reliability: Less database strain

---

Eager vs Lazy Loading

// Lazy Loading (N+1 problem)
var users = _db.Users.ToList();
foreach (var user in users)
{
    var orders = user.Orders;  // One query per user!
}

// Eager Loading (better)
var users = _db.Users
    .Include(u => u.Orders)
    .ToList();  // Only 2 queries

// Explicit Loading
_db.Entry(user)
    .Collection(u => u.Orders)
    .Load();

---

Projection

// Don't do this - loads all columns
var users = _db.Users.ToList();

// Better - select only what you need
var userDtos = _db.Users
    .Select(u => new UserDto
    {
        Id = u.Id,
        Name = u.Name,
        Email = u.Email
    })
    .ToList();

---

Batch Operations

// Bad - N queries
foreach (var product in products)
{
    _db.Products.Update(product);
    _db.SaveChanges();
}

// Good - 1 query
foreach (var product in products)
{
    _db.Products.Update(product);
}
_db.SaveChanges();

// Better - Use batch operations
_db.Products.UpdateRange(products);
_db.SaveChanges();

---

Filtering Early

// Bad - loads all then filters
var activeUsers = _db.Users
    .ToList()
    .Where(u => u.IsActive)
    .ToList();

// Good - filters in database
var activeUsers = _db.Users
    .Where(u => u.IsActive)
    .ToList();

---

Best Practices

1. Projections: Select only needed fields
2. Eager loading: Load related data intentionally
3. Filtering: Push filters to database
4. Indexing: Create indexes for common queries
5. Profiling: Measure and optimize actual queries

---

Related Concepts

• Query execution plans
• Database indexing
• Caching strategies
• Denormalization

---

Summary

Optimize ORM by using eager loading, projections, batch operations, and pushing filters to the database.