1# Entity Framework Core Best Practices
2 
3Performance optimization and best practices for EF Core in production applications.
4 
5## Query Optimization
6 
7### 1. Use AsNoTracking for Read-Only Queries
8 
9```csharp
10// ✅ Good - No change tracking overhead
11var products = await _context.Products
12    .AsNoTracking()
13    .Where(p => p.CategoryId == categoryId)
14    .ToListAsync(ct);
15 
16// ❌ Bad - Unnecessary tracking for read-only data
17var products = await _context.Products
18    .Where(p => p.CategoryId == categoryId)
19    .ToListAsync(ct);
20```
21 
22### 2. Select Only Needed Columns
23 
24```csharp
25// ✅ Good - Project to DTO
26var products = await _context.Products
27    .AsNoTracking()
28    .Where(p => p.CategoryId == categoryId)
29    .Select(p => new ProductDto
30    {
31        Id = p.Id,
32        Name = p.Name,
33        Price = p.Price
34    })
35    .ToListAsync(ct);
36 
37// ❌ Bad - Fetching all columns
38var products = await _context.Products
39    .Where(p => p.CategoryId == categoryId)
40    .ToListAsync(ct);
41```
42 
43### 3. Avoid N+1 Queries with Eager Loading
44 
45```csharp
46// ✅ Good - Single query with Include
47var orders = await _context.Orders
48    .AsNoTracking()
49    .Include(o => o.Items)
50        .ThenInclude(i => i.Product)
51    .Where(o => o.CustomerId == customerId)
52    .ToListAsync(ct);
53 
54// ❌ Bad - N+1 queries (lazy loading)
55var orders = await _context.Orders
56    .Where(o => o.CustomerId == customerId)
57    .ToListAsync(ct);
58 
59foreach (var order in orders)
60{
61    // Each iteration triggers a separate query!
62    var items = order.Items.ToList();
63}
64```
65 
66### 4. Use Split Queries for Large Includes
67 
68```csharp
69// ✅ Good - Prevents cartesian explosion
70var orders = await _context.Orders
71    .AsNoTracking()
72    .Include(o => o.Items)
73    .Include(o => o.Payments)
74    .Include(o => o.ShippingHistory)
75    .AsSplitQuery()  // Executes as multiple queries
76    .Where(o => o.CustomerId == customerId)
77    .ToListAsync(ct);
78```
79 
80### 5. Use Compiled Queries for Hot Paths
81 
82```csharp
83public class ProductRepository
84{
85    // Compile once, reuse many times
86    private static readonly Func<AppDbContext, string, Task<Product?>> GetByIdQuery =
87        EF.CompileAsyncQuery((AppDbContext ctx, string id) =>
88            ctx.Products.AsNoTracking().FirstOrDefault(p => p.Id == id));
89 
90    private static readonly Func<AppDbContext, int, IAsyncEnumerable<Product>> GetByCategoryQuery =
91        EF.CompileAsyncQuery((AppDbContext ctx, int categoryId) =>
92            ctx.Products.AsNoTracking().Where(p => p.CategoryId == categoryId));
93 
94    public Task<Product?> GetByIdAsync(string id, CancellationToken ct)
95        => GetByIdQuery(_context, id);
96 
97    public IAsyncEnumerable<Product> GetByCategoryAsync(int categoryId)
98        => GetByCategoryQuery(_context, categoryId);
99}
100```
101 
102## Batch Operations
103 
104### 6. Use ExecuteUpdate/ExecuteDelete (.NET 7+)
105 
106```csharp
107// ✅ Good - Single SQL UPDATE
108await _context.Products
109    .Where(p => p.CategoryId == oldCategoryId)
110    .ExecuteUpdateAsync(s => s
111        .SetProperty(p => p.CategoryId, newCategoryId)
112        .SetProperty(p => p.UpdatedAt, DateTime.UtcNow),
113        ct);
114 
115// ✅ Good - Single SQL DELETE
116await _context.Products
117    .Where(p => p.IsDeleted && p.UpdatedAt < cutoffDate)
118    .ExecuteDeleteAsync(ct);
119 
120// ❌ Bad - Loads all entities into memory
121var products = await _context.Products
122    .Where(p => p.CategoryId == oldCategoryId)
123    .ToListAsync(ct);
124 
125foreach (var product in products)
126{
127    product.CategoryId = newCategoryId;
128}
129await _context.SaveChangesAsync(ct);
130```
131 
132### 7. Bulk Insert with EFCore.BulkExtensions
133 
134```csharp
135// Using EFCore.BulkExtensions package
136var products = GenerateLargeProductList();
137 
138// ✅ Good - Bulk insert (much faster for large datasets)
139await _context.BulkInsertAsync(products, ct);
140 
141// ❌ Bad - Individual inserts
142foreach (var product in products)
143{
144    _context.Products.Add(product);
145}
146await _context.SaveChangesAsync(ct);
147```
148 
149## Connection Management
150 
151### 8. Configure Connection Pooling
152 
153```csharp
154services.AddDbContext<AppDbContext>(options =>
155{
156    options.UseSqlServer(connectionString, sqlOptions =>
157    {
158        sqlOptions.EnableRetryOnFailure(
159            maxRetryCount: 3,
160            maxRetryDelay: TimeSpan.FromSeconds(10),
161            errorNumbersToAdd: null);
162 
163        sqlOptions.CommandTimeout(30);
164    });
165 
166    // Performance settings
167    options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
168 
169    // Development only
170    if (env.IsDevelopment())
171    {
172        options.EnableSensitiveDataLogging();
173        options.EnableDetailedErrors();
174    }
175});
176```
177 
178### 9. Use DbContext Pooling
179 
180```csharp
181// ✅ Good - Context pooling (reduces allocation overhead)
182services.AddDbContextPool<AppDbContext>(options =>
183{
184    options.UseSqlServer(connectionString);
185}, poolSize: 128);
186 
187// Instead of AddDbContext
188```
189 
190## Concurrency and Transactions
191 
192### 10. Handle Concurrency with Row Versioning
193 
194```csharp
195public class Product
196{
197    public string Id { get; set; }
198    public string Name { get; set; }
199 
200    [Timestamp]
201    public byte[] RowVersion { get; set; }  // SQL Server rowversion
202}
203 
204// Or with Fluent API
205builder.Property(p => p.RowVersion)
206    .IsRowVersion();
207 
208// Handle concurrency conflicts
209try
210{
211    await _context.SaveChangesAsync(ct);
212}
213catch (DbUpdateConcurrencyException ex)
214{
215    var entry = ex.Entries.Single();
216    var databaseValues = await entry.GetDatabaseValuesAsync(ct);
217 
218    if (databaseValues == null)
219    {
220        // Entity was deleted
221        throw new NotFoundException("Product was deleted by another user");
222    }
223 
224    // Client wins - overwrite database values
225    entry.OriginalValues.SetValues(databaseValues);
226    await _context.SaveChangesAsync(ct);
227}
228```
229 
230### 11. Use Explicit Transactions When Needed
231 
232```csharp
233await using var transaction = await _context.Database.BeginTransactionAsync(ct);
234 
235try
236{
237    // Multiple operations
238    _context.Orders.Add(order);
239    await _context.SaveChangesAsync(ct);
240 
241    await _context.OrderItems.AddRangeAsync(items, ct);
242    await _context.SaveChangesAsync(ct);
243 
244    await _paymentService.ProcessAsync(order.Id, ct);
245 
246    await transaction.CommitAsync(ct);
247}
248catch
249{
250    await transaction.RollbackAsync(ct);
251    throw;
252}
253```
254 
255## Indexing Strategy
256 
257### 12. Create Indexes for Query Patterns
258 
259```csharp
260public class ProductConfiguration : IEntityTypeConfiguration<Product>
261{
262    public void Configure(EntityTypeBuilder<Product> builder)
263    {
264        // Unique index
265        builder.HasIndex(p => p.Sku)
266            .IsUnique();
267 
268        // Composite index for common query patterns
269        builder.HasIndex(p => new { p.CategoryId, p.Name });
270 
271        // Filtered index (SQL Server)
272        builder.HasIndex(p => p.Price)
273            .HasFilter("[IsDeleted] = 0");
274 
275        // Include columns for covering index
276        builder.HasIndex(p => p.CategoryId)
277            .IncludeProperties(p => new { p.Name, p.Price });
278    }
279}
280```
281 
282## Common Anti-Patterns to Avoid
283 
284### ❌ Calling ToList() Too Early
285 
286```csharp
287// ❌ Bad - Materializes all products then filters in memory
288var products = _context.Products.ToList()
289    .Where(p => p.Price > 100);
290 
291// ✅ Good - Filter in SQL
292var products = await _context.Products
293    .Where(p => p.Price > 100)
294    .ToListAsync(ct);
295```
296 
297### ❌ Using Contains with Large Collections
298 
299```csharp
300// ❌ Bad - Generates massive IN clause
301var ids = GetThousandsOfIds();
302var products = await _context.Products
303    .Where(p => ids.Contains(p.Id))
304    .ToListAsync(ct);
305 
306// ✅ Good - Use temp table or batch queries
307var products = new List<Product>();
308foreach (var batch in ids.Chunk(100))
309{
310    var batchResults = await _context.Products
311        .Where(p => batch.Contains(p.Id))
312        .ToListAsync(ct);
313    products.AddRange(batchResults);
314}
315```
316 
317### ❌ String Concatenation in Queries
318 
319```csharp
320// ❌ Bad - Can't use index
321var products = await _context.Products
322    .Where(p => (p.FirstName + " " + p.LastName).Contains(searchTerm))
323    .ToListAsync(ct);
324 
325// ✅ Good - Use computed column with index
326builder.Property(p => p.FullName)
327    .HasComputedColumnSql("[FirstName] + ' ' + [LastName]");
328builder.HasIndex(p => p.FullName);
329```
330 
331## Monitoring and Diagnostics
332 
333```csharp
334// Log slow queries
335services.AddDbContext<AppDbContext>(options =>
336{
337    options.UseSqlServer(connectionString);
338 
339    options.LogTo(
340        filter: (eventId, level) => eventId.Id == CoreEventId.QueryExecutionPlanned.Id,
341        logger: (eventData) =>
342        {
343            if (eventData is QueryExpressionEventData queryData)
344            {
345                var duration = queryData.Duration;
346                if (duration > TimeSpan.FromSeconds(1))
347                {
348                    _logger.LogWarning("Slow query detected: {Duration}ms - {Query}",
349                        duration.TotalMilliseconds,
350                        queryData.Expression);
351                }
352            }
353        });
354});
355```
356