Entity Framework Core: Advanced Query Patterns and Performance

Entity Framework Core: Advanced Query Patterns and Performance

Introduction

[Explain how advanced EF Core usage impacts latency, memory, and database load for enterprise APIs.]

Prerequisites

• .NET 8 SDK
• Database provider configured (SQL Server / PostgreSQL)
• Sample domain model

Core Patterns Overview

Step-by-Step Guide

Step 1: Efficient Projection

var dto = await context.Orders
  .Where(o => o.Status == Status.Pending)
  .Select(o => new OrderSummary { o.Id, o.Total, o.Customer.Name })
  .ToListAsync();

Step 2: Compiled Query

static readonly Func<AppDbContext, Status, Task<List<Order>>> PendingOrders =
  EF.CompileAsyncQuery((AppDbContext ctx, Status s) => ctx.Orders.Where(o => o.Status == s));
var list = await PendingOrders(context, Status.Pending);

Step 3: Performance Diagnostics

var tags = new Dictionary<string, object?> { ["QueryName"] = "PendingOrders" };
context.Database.LogTo(msg => logger.LogInformation(msg), tags: tags);

Step 4: Batching & Transactions

await using var tx = await context.Database.BeginTransactionAsync();
foreach(var item in items){ context.Add(item); }
await context.SaveChangesAsync();
await tx.CommitAsync();

Best Practices

• Avoid N+1: Prefer explicit Include or projection
• Use AsNoTracking for read-only queries
• Profile with Query Store / Execution Plans

Common Issues & Troubleshooting

Issue: High memory usage
Solution: Use pagination & projection, avoid loading full graphs

Issue: Slow query translation
Solution: Employ compiled queries for hot paths

Key Takeaways

• Projection + compiled queries drive EF Core performance.
• Diagnostics logging enables targeted optimization.
• Batching reduces round trips and improves throughput.

Next Steps

• Introduce caching layer for hot read models
• Evaluate bulk extension library for large imports

Additional Resources

• EF Core Docs
• Performance Tips
• Compiled Queries

What EF Core optimization gave you the biggest performance gain?