optimizing-ef-core-queries

# Optimizing EF Core Queries ## When to Use - EF Core queries are slow or generating too many SQL statements - Database CPU/IO is high due to ORM inefficiency - N+1 query patterns are detected in logs - Large result sets cause memory pressure ## When Not to Use - The user is using Dapper or raw ADO.NET (not EF Core) - The performance issue is database-side (missing indexes, bad schema) - The user is building a new data access layer from scratch ## Inputs | Input | Required | Description | |-------|----------|-------------| | Slow EF Core queries | Yes | The LINQ queries or DbContext usage to optimize | | SQL output or logs | No | EF Core generated SQL or query execution logs | ## Workflow ### Step 1: Enable query logging to see the actual SQL ```csharp // In Program.cs or DbContext configuration: optionsBuilder .UseSqlServer(connectionString) .LogTo(Console.WriteLine, LogLevel.Information) .EnableSensitiveDataLogging() // shows parameter values (dev only!) .EnableDetailedErrors(); ``` Or use the `Microsoft.EntityFrameworkCore` log category: ```json { "Logging": { "LogLevel": { "Microsoft.EntityFrameworkCore.Database.Command": "Information" } } } ``` ### Step 2: Fix N+1 query patterns **The #1 EF Core performance killer.** Happens when loading related entities in a loop. **Before (N+1 — 1 query for orders + N queries for items):** ```csharp var orders = await db.Orders.ToListAsync(); foreach (var order in orders) { // Each a...