optimizing-ef-core-queries

Fix N+1 queries in EF Core by eager loading related data with Include, using AsSplitQuery for multiple collections to avoid cartesian explosion, or projecting only needed columns with Select. Enable SQL logging first to confirm the N+1 pattern exists.

View EF Core generated SQL by calling LogTo on the DbContext options with LogLevel.Information, or by setting the Microsoft.EntityFrameworkCore.Database.Command log category to Information in appsettings.json. EnableSensitiveDataLogging shows parameter values during development.

Use AsNoTracking for read-only queries where entities will not be updated, since change tracking adds significant overhead. Use AsNoTrackingWithIdentityResolution when queries return duplicate entities to avoid duplicated objects in memory.

Use a single query for one level of Include, and AsSplitQuery when including multiple collections or large child collections to avoid cartesian explosion. Single queries are preferable when transaction consistency across the loaded graph matters.

This happens when ToList is called before Where, causing filtering to occur in memory instead of SQL. Always apply Where and other filters before materializing results, and watch for client-evaluation warnings in the logs.

Use FromSqlInterpolated when LINQ cannot express a query efficiently, such as complex aggregations or vendor-specific SQL. Always use the interpolated variant rather than FromSqlRaw with string interpolation to prevent SQL injection.