EF Core Performance

N+1 Query Problem

The N+1 problem occurs when you load a collection, then load related data for each item.

// ❌ Bad - 1 query for products + N queries for categories
var products = await _context.Products.ToListAsync(); // 1 query
foreach (var product in products)
{
    var category = product.Category.Name; // N queries!
}

Eager Loading with Include

// ✅ Good - 1 query with JOIN
var products = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

Multiple Includes

var products = await _context.Products
    .Include(p => p.Category)
    .Include(p => p.Supplier)
    .ToListAsync();

ThenInclude for Nested

var orders = await _context.Orders
    .Include(o => o.OrderItems)
        .ThenInclude(i => i.Product)
    .ToListAsync();

Select Projections

Only select the data you need.

// ❌ Bad - loads entire entity
var products = await _context.Products.ToListAsync();

// ✅ Good - only loads needed columns
var products = await _context.Products
    .Select(p => new 
    {
        p.Id,
        p.Name,
        p.Price
    })
    .ToListAsync();

AsNoTracking

Disable change tracking for read-only queries.

// ✅ Good for read-only
var products = await _context.Products
    .AsNoTracking()
    .ToListAsync();

Compiled Queries

Pre-compile frequently used queries.

private static readonly Func<InvenTrackDbContext, int, Task<Product>> _getProductById =
    EF.CompileAsyncQuery((InvenTrackDbContext context, int id) =>
        context.Products.FirstOrDefault(p => p.Id == id));

public async Task<Product> GetProductAsync(int id)
{
    return await _getProductById(_context, id);
}

InvenTrack Example

public class ProductsController : Controller
{
    // ✅ Good - AsNoTracking for read-only
    [HttpGet]
    public async Task<IActionResult> Index()
    {
        var products = await _context.Products
            .AsNoTracking()
            .Include(p => p.Category)
            .ToListAsync();
        
        return View(products);
    }

    // ✅ Good - Projection for list view
    [HttpGet("api/products")]
    public async Task<IActionResult> GetProducts()
    {
        var products = await _context.Products
            .Select(p => new
            {
                p.Id,
                p.Name,
                p.Price,
                CategoryName = p.Category.Name
            })
            .ToListAsync();
        
        return Ok(products);
    }

    // ✅ Good - Include for details
    [HttpGet("{id}")]
    public async Task<IActionResult> Details(int id)
    {
        var product = await _context.Products
            .Include(p => p.Category)
            .Include(p => p.Supplier)
            .FirstOrDefaultAsync(p => p.Id == id);
        
        if (product == null)
            return NotFound();
        
        return View(product);
    }
}

Key Takeaways

• N+1 problem: Avoid lazy loading in loops
• Include(): Eager load related data
• ThenInclude(): Load nested relationships
• Select(): Project only needed columns
• AsNoTracking(): Disable tracking for read-only
• Compiled queries: Pre-compile frequent queries