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 // 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: { "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): var orders = await db . Orders . ToListAsync ( ) ; foreach ( var order in orders ) { // Each access triggers a lazy-load query! var items = order . Items . Count ; } After (eager loading — 1 or 2 queries total): // Option 1: Include (JOIN) var orders = await db . Orders . Include ( o => o . Items ) . ToListAsync ( ) ; // Option 2: Split query (separate SQL, avoids cartesian explosion) var orders = await db . Orders . Include ( o => o . Items ) . AsSplitQuery ( ) . ToListAsync ( ) ; // Option 3: Explicit projection (best - only fetches needed columns) var orderSummaries = await db . Orders . Select ( o => new OrderSummary { OrderId = o . Id , Total = o . Items . Sum ( i => i . Price ) , ItemCount = o . Items . Count } ) . ToListAsync ( ) ; When to use Split vs Single query: Scenario Use 1 level of Include Single query (default) Multiple Includes (Cartesian risk) AsSplitQuery() Include with large child collections AsSplitQuery() Need transaction consistency Single query Step 3: Use NoTracking for read-only queries Change tracking overhead is significant. Disable it when you don't need to update entities: // Per-query var products = await db . Products . AsNoTracking ( ) . Where ( p => p . IsActive ) . ToListAsync ( ) ; // Global default for read-heavy apps services . AddDbContext < AppDbContext

( options => options . UseSqlServer ( connectionString ) . UseQueryTrackingBehavior ( QueryTrackingBehavior . NoTracking ) ) ; Use AsNoTrackingWithIdentityResolution() when the query returns duplicate entities to avoid duplicated objects in memory. Step 4: Use compiled queries for hot paths // Define once as static private static readonly Func < AppDbContext , int , Task < Order ?

GetOrderById

EF . CompileAsyncQuery ( ( AppDbContext db , int id ) => db . Orders . Include ( o => o . Items ) . FirstOrDefault ( o => o . Id == id ) ) ; // Use repeatedly — skips query compilation overhead var order = await GetOrderById ( db , orderId ) ; Step 5: Avoid common query traps Trap Problem Fix ToList() before Where() Loads entire table into memory Filter first: .Where().ToList() Count() to check existence Scans all rows Use .Any() instead .Select() after .Include() Include is ignored with projection Remove Include, use Select only string.Contains() in Where May not translate, falls to client eval Use EF.Functions.Like() for SQL LIKE Calling .ToList() inside Select() Causes nested queries Use projection with Select all the way Step 6: Use raw SQL or FromSql for complex queries When LINQ can't express it efficiently: var results = await db . Orders . FromSqlInterpolated ( $@" SELECT o.* FROM Orders o INNER JOIN ( SELECT OrderId, SUM(Price) as Total FROM OrderItems GROUP BY OrderId HAVING SUM(Price) > { minTotal } ) t ON o.Id = t.OrderId" ) . AsNoTracking ( ) . ToListAsync ( ) ; Validation SQL logging shows expected number of queries (no N+1) Read-only queries use AsNoTracking() Hot-path queries use compiled queries No client-side evaluation warnings in logs Include/split strategy matches data shape Common Pitfalls Pitfall Solution Lazy loading silently creating N+1 Remove Microsoft.EntityFrameworkCore.Proxies or disable lazy loading Global query filters forgotten in perf analysis Check HasQueryFilter in model config; use IgnoreQueryFilters() if needed DbContext kept alive too long DbContext should be scoped (per-request); don't cache it Batch updates fetching then saving EF Core 7+: use ExecuteUpdateAsync / ExecuteDeleteAsync for bulk operations String interpolation in FromSqlRaw SQL injection risk — use FromSqlInterpolated (parameterized)