Entity Framework Core performance can make or break your application. Let's explore advanced optimization techniques that can dramatically improve your application's speed and scalability.
Query Optimization Strategies
Projection and Select Specific Fields
Avoid loading unnecessary data by projecting only what you need:
csharp1// ❌ Bad: Loads entire entity 2var users = await context.Users 3 .Where(u => u.IsActive) 4 .ToListAsync(); 5 6// ✅ Good: Project only needed fields 7var userSummaries = await context.Users 8 .Where(u => u.IsActive) 9 .Select(u => new UserSummaryDto 10 { 11 Id = u.Id, 12 Name = u.Name, 13 Email = u.Email 14 }) 15 .ToListAsync();
Split Queries for Multiple Includes
Prevent Cartesian explosion with split queries:
csharp1// ❌ Bad: Creates Cartesian product 2var orders = await context.Orders 3 .Include(o => o.OrderItems) 4 .Include(o => o.Customer) 5 .ToListAsync(); 6 7// ✅ Good: Split into multiple queries 8var orders = await context.Orders 9 .AsSplitQuery() 10 .Include(o => o.OrderItems) 11 .Include(o => o.Customer) 12 .ToListAsync();
Raw SQL for Complex Queries
Use raw SQL for complex scenarios where LINQ is inefficient:
csharp1public class ProductRepository 2{ 3 private readonly ApplicationDbContext _context; 4 5 public async Task<IEnumerable<ProductSalesReport>> GetTopSellingProductsAsync( 6 DateTime startDate, DateTime endDate, int count) 7 { 8 return await _context.Database 9 .SqlQueryRaw<ProductSalesReport>(@" 10 SELECT TOP (@count) 11 p.Id, 12 p.Name, 13 SUM(oi.Quantity) as TotalSold, 14 SUM(oi.Quantity * oi.UnitPrice) as TotalRevenue 15 FROM Products p 16 INNER JOIN OrderItems oi ON p.Id = oi.ProductId 17 INNER JOIN Orders o ON oi.OrderId = o.Id 18 WHERE o.OrderDate BETWEEN @startDate AND @endDate 19 GROUP BY p.Id, p.Name 20 ORDER BY TotalRevenue DESC", 21 new SqlParameter("count", count), 22 new SqlParameter("startDate", startDate), 23 new SqlParameter("endDate", endDate)) 24 .ToListAsync(); 25 } 26}
Change Tracking Optimization
No-Tracking Queries for Read-Only Operations
Disable change tracking for read-only scenarios:
csharp1// ❌ With change tracking (slower) 2var products = await context.Products 3 .Where(p => p.CategoryId == categoryId) 4 .ToListAsync(); 5 6// ✅ Without change tracking (faster) 7var products = await context.Products 8 .AsNoTracking() 9 .Where(p => p.CategoryId == categoryId) 10 .ToListAsync(); 11 12// Global no-tracking for read-only contexts 13public class ReadOnlyContext : ApplicationDbContext 14{ 15 public ReadOnlyContext(DbContextOptions<ReadOnlyContext> options) 16 : base(options) 17 { 18 ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; 19 } 20}
Identity Resolution Optimization
Control identity resolution for better performance:
csharp1// ❌ Default: Identity resolution enabled 2var orders = await context.Orders 3 .AsNoTracking() 4 .Include(o => o.Customer) 5 .ToListAsync(); 6 7// ✅ Disable identity resolution for better performance 8var orders = await context.Orders 9 .AsNoTracking() 10 .AsNoTrackingWithIdentityResolution() 11 .Include(o => o.Customer) 12 .ToListAsync();
Compiled Queries
Pre-compile frequently used queries for better performance:
csharp1public static class CompiledQueries 2{ 3 public static readonly Func<ApplicationDbContext, int, IAsyncEnumerable<Product>> 4 GetProductsByCategory = EF.CompileAsyncQuery( 5 (ApplicationDbContext context, int categoryId) => 6 context.Products 7 .AsNoTracking() 8 .Where(p => p.CategoryId == categoryId) 9 .OrderBy(p => p.Name)); 10 11 public static readonly Func<ApplicationDbContext, string, Task<User?>> 12 GetUserByEmail = EF.CompileAsyncQuery( 13 (ApplicationDbContext context, string email) => 14 context.Users 15 .AsNoTracking() 16 .FirstOrDefault(u => u.Email == email)); 17} 18 19// Usage 20public class ProductService 21{ 22 private readonly ApplicationDbContext _context; 23 24 public async Task<IEnumerable<Product>> GetProductsByCategoryAsync(int categoryId) 25 { 26 var products = new List<Product>(); 27 await foreach (var product in CompiledQueries.GetProductsByCategory(_context, categoryId)) 28 { 29 products.Add(product); 30 } 31 return products; 32 } 33}
Connection and DbContext Management
Connection Pooling
Configure connection pooling for better resource utilization:
csharp1// Startup.cs or Program.cs 2services.AddDbContextPool<ApplicationDbContext>(options => 3{ 4 options.UseSqlServer(connectionString, sqlOptions => 5 { 6 sqlOptions.CommandTimeout(30); 7 sqlOptions.EnableRetryOnFailure( 8 maxRetryCount: 3, 9 maxRetryDelay: TimeSpan.FromSeconds(30), 10 errorNumbersToAdd: null); 11 }); 12}, poolSize: 128); // Configure pool size based on your needs
DbContext Factory Pattern
Use DbContext factory for better lifecycle management:
csharp1public interface IDbContextFactory<TContext> where TContext : DbContext 2{ 3 TContext CreateDbContext(); 4} 5 6public class ApplicationDbContextFactory : IDbContextFactory<ApplicationDbContext> 7{ 8 private readonly DbContextOptions<ApplicationDbContext> _options; 9 10 public ApplicationDbContextFactory(DbContextOptions<ApplicationDbContext> options) 11 { 12 _options = options; 13 } 14 15 public ApplicationDbContext CreateDbContext() 16 { 17 return new ApplicationDbContext(_options); 18 } 19} 20 21// Usage in service 22public class OrderService 23{ 24 private readonly IDbContextFactory<ApplicationDbContext> _contextFactory; 25 26 public async Task<Order> ProcessOrderAsync(CreateOrderRequest request) 27 { 28 using var context = _contextFactory.CreateDbContext(); 29 30 var order = new Order 31 { 32 CustomerId = request.CustomerId, 33 OrderDate = DateTime.UtcNow, 34 OrderItems = request.Items.Select(i => new OrderItem 35 { 36 ProductId = i.ProductId, 37 Quantity = i.Quantity, 38 UnitPrice = i.UnitPrice 39 }).ToList() 40 }; 41 42 context.Orders.Add(order); 43 await context.SaveChangesAsync(); 44 return order; 45 } 46}
Bulk Operations
Bulk Insert with EF Core Extensions
Handle large datasets efficiently:
csharp1public class BulkDataService 2{ 3 private readonly ApplicationDbContext _context; 4 5 public async Task BulkInsertProductsAsync(IEnumerable<Product> products) 6 { 7 // Using EFCore.BulkExtensions 8 await _context.BulkInsertAsync(products); 9 } 10 11 public async Task BulkUpdatePricesAsync(IEnumerable<ProductPriceUpdate> updates) 12 { 13 var products = await _context.Products 14 .Where(p => updates.Select(u => u.ProductId).Contains(p.Id)) 15 .ToListAsync(); 16 17 foreach (var product in products) 18 { 19 var update = updates.First(u => u.ProductId == product.Id); 20 product.Price = update.NewPrice; 21 product.LastModified = DateTime.UtcNow; 22 } 23 24 await _context.BulkUpdateAsync(products); 25 } 26}
Caching Strategies
Second-Level Caching
Implement caching for frequently accessed data:
csharp1public class CachedProductService 2{ 3 private readonly ApplicationDbContext _context; 4 private readonly IMemoryCache _cache; 5 private readonly ILogger<CachedProductService> _logger; 6 7 public async Task<Product?> GetProductByIdAsync(int id) 8 { 9 var cacheKey = $"product_{id}"; 10 11 if (_cache.TryGetValue(cacheKey, out Product? cachedProduct)) 12 { 13 _logger.LogInformation("Product {ProductId} retrieved from cache", id); 14 return cachedProduct; 15 } 16 17 var product = await _context.Products 18 .AsNoTracking() 19 .Include(p => p.Category) 20 .FirstOrDefaultAsync(p => p.Id == id); 21 22 if (product != null) 23 { 24 _cache.Set(cacheKey, product, TimeSpan.FromMinutes(30)); 25 _logger.LogInformation("Product {ProductId} cached for 30 minutes", id); 26 } 27 28 return product; 29 } 30 31 public async Task InvalidateProductCacheAsync(int productId) 32 { 33 var cacheKey = $"product_{productId}"; 34 _cache.Remove(cacheKey); 35 _logger.LogInformation("Cache invalidated for product {ProductId}", productId); 36 } 37}
Performance Monitoring
Query Logging and Analysis
Monitor and analyze query performance:
csharp1public class QueryPerformanceInterceptor : DbCommandInterceptor 2{ 3 private readonly ILogger<QueryPerformanceInterceptor> _logger; 4 5 public QueryPerformanceInterceptor(ILogger<QueryPerformanceInterceptor> logger) 6 { 7 _logger = logger; 8 } 9 10 public override ValueTask<DbDataReader> ReaderExecutedAsync( 11 DbCommand command, 12 CommandExecutedEventData eventData, 13 DbDataReader result, 14 CancellationToken cancellationToken = default) 15 { 16 var duration = eventData.Duration.TotalMilliseconds; 17 18 if (duration > 1000) // Log queries taking more than 1 second 19 { 20 _logger.LogWarning( 21 "Slow query detected: {Duration}ms - {CommandText}", 22 duration, 23 command.CommandText); 24 } 25 26 return base.ReaderExecutedAsync(command, eventData, result, cancellationToken); 27 } 28} 29 30// Register interceptor 31services.AddDbContext<ApplicationDbContext>(options => 32{ 33 options.UseSqlServer(connectionString) 34 .AddInterceptors(serviceProvider.GetRequiredService<QueryPerformanceInterceptor>()); 35});
Conclusion
EF Core performance optimization requires a multi-faceted approach covering query optimization, change tracking management, connection pooling, and caching strategies. By implementing these advanced techniques, you can build high-performance applications that scale effectively.
Remember to always measure performance before and after optimizations, and use tools like Application Insights or custom logging to monitor your application's database performance in production.
