## The N+1 Query Death Trap This innocent-looking code is a performance killer: ```csharp // 😱 WRONG - Generates N+1 queries var orders = await context.Orders.Take(100).ToListAsync(); foreach (var order in orders) { Console.WriteLine($"Customer: {order.Customer.Name}"); // Lazy loading triggers here } // Result: 1 query for orders + 100 queries for customers = 101 database hits ``` **The Fix**: Explicit loading with `Include`: ```csharp // ✅ CORRECT - Single optimized query var orders = await context.Orders .Include(o => o.Customer) .Take(100) .ToListAsync(); // Result: 1 query with JOIN ``` **Pro tip**: Use `AsSingleQuery()` vs `AsSplitQuery()` strategically: ```csharp // For narrow results - single query with JOINs var orders = await context.Orders .Include(o => o.Customer) .Include(o => o.OrderItems) .AsSingleQuery() // One complex query .ToListAsync(); // For wide results - split into multiple queries var orders = await context.Orders .Include(o => o.Customer) .Include(o => o.OrderItems) .ThenInclude(oi => oi.Product) .AsSplitQuery() // Separate optimized queries .ToListAsync(); ``` ## AsNoTracking: Not Always Your Friend Everyone knows `AsNoTracking()` improves performance, but timing matters: ```csharp // 😱 WRONG - AsNoTracking applied too early var expensiveProducts = await context.Products .AsNoTracking() // Applied here .Where(p => p.Price > 1000) .Select(p => new ProductDto { Name = p.Name, Price = p.Price }) .ToListAsync(); // AsNoTracking is pointless here since Select creates new objects anyway ``` ```csharp // ✅ CORRECT - AsNoTracking where it matters var products = await context.Products .Where(p => p.Price > 1000) .AsNoTracking() // Only when returning entities .ToListAsync(); // ✅ EVEN BETTER - Skip AsNoTracking with projections var productDtos = await context.Products .Where(p => p.Price > 1000) .Select(p => new ProductDto { Name = p.Name, Price = p.Price }) .ToListAsync(); // No AsNoTracking needed ``` ## The Projection Performance Multiplier Always project to exactly what you need: ```csharp // 😱 WRONG - Loading entire entities var customers = await context.Customers .Include(c => c.Orders) .ThenInclude(o => o.OrderItems) .Where(c => c.IsActive) .ToListAsync(); // Loads massive amounts of unnecessary data // ✅ CORRECT - Project to specific needs var customerSummaries = await context.Customers .Where(c => c.IsActive) .Select(c => new CustomerSummaryDto { Name = c.Name, Email = c.Email, OrderCount = c.Orders.Count(), TotalSpent = c.Orders.Sum(o => o.Total) }) .ToListAsync(); // Generates efficient GROUP BY query ``` ## The Contains() Performance Trap Large `Contains()` calls can break SQL parameter limits: ```csharp // 😱 PROBLEMATIC - Too many parameters var productIds = GetLargeListOfIds(); // 5000+ IDs var products = await context.Products .Where(p => productIds.Contains(p.Id)) .ToListAsync(); // Can hit SQL Server's 2100 parameter limit ``` **The Fix**: Batch your queries or use temp tables: ```csharp // ✅ SOLUTION 1 - Batch processing const int batchSize = 1000; var allProducts = new List(); for (int i = 0; i < productIds.Count; i += batchSize) { var batch = productIds.Skip(i).Take(batchSize).ToList(); var batchProducts = await context.Products .Where(p => batch.Contains(p.Id)) .ToListAsync(); allProducts.AddRange(batchProducts); } // ✅ SOLUTION 2 - FromSqlRaw with table-valued parameter (SQL Server) var products = await context.Products .FromSqlRaw("SELECT p.* FROM Products p INNER JOIN @ids i ON p.Id = i.Value", new SqlParameter("@ids", CreateTableValuedParameter(productIds))) .ToListAsync(); ``` ## Async/Await: The Memory Leak Trap Wrong async usage can exhaust your connection pool: ```csharp // 😱 WRONG - Blocking async calls public List GetCustomers() { return context.Customers.ToListAsync().Result; // Deadlock risk } // 😱 WRONG - Not disposing context properly public async Task> GetCustomersWrong() { var context = new AppDbContext(); return await context.Customers.ToListAsync(); // Context never disposed } ``` ```csharp // ✅ CORRECT - Proper async pattern public async Task> GetCustomersAsync() { return await context.Customers.ToListAsync(); } // ✅ CORRECT - Using statements for explicit disposal public async Task> GetCustomersWithDisposalAsync() { using var context = new AppDbContext(); return await context.Customers.ToListAsync(); } ``` ## The Query Compilation Cache Trick EF compiles LINQ expressions into SQL. Help it cache efficiently: ```csharp // 😱 BAD - Prevents query caching public async Task> SearchProductsWrong(string searchTerm, bool includeInactive) { var query = context.Products.AsQueryable(); if (!string.IsNullOrEmpty(searchTerm)) query = query.Where(p => p.Name.Contains(searchTerm)); if (!includeInactive) query = query.Where(p => p.IsActive); return await query.ToListAsync(); // Different query shapes hurt caching } // ✅ GOOD - Cache-friendly approach public async Task> SearchProducts(string searchTerm, bool includeInactive) { return await context.Products .Where(p => (string.IsNullOrEmpty(searchTerm) || p.Name.Contains(searchTerm)) && (includeInactive || p.IsActive)) .ToListAsync(); // Same query shape, parameters vary } ``` ## Performance Monitoring Pro Tip > **Tip**: Always log your SQL to catch performance issues: ```csharp // In your DbContext protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder .LogTo(Console.WriteLine, LogLevel.Information) .EnableSensitiveDataLogging() // Only in development .EnableDetailedErrors(); } ``` ## Key Takeaways 1. **Always use `Include` for related data** - Don't rely on lazy loading in loops 2. **Project early, project often** - Only load the data you actually need 3. **`AsNoTracking()` only helps when returning entities** - Projections make it redundant 4. **Batch large `Contains()` operations** - Avoid parameter limit issues 5. **Keep query shapes consistent** - Help EF's query compilation cache 6. **Monitor your SQL** - The generated queries tell the real performance story ## Conclusion Remember: EF Core is powerful, but it's not magic. Understanding the SQL it generates is the key to writing high-performance applications. --- _Want to dive deeper? Check out the [Efficient Querying by Microsoft](https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying)._

Latest Articles

No articles found

Stay Updated