optimizing-ef-core-queries

Fix N+1 queries by eager loading related data with Include, using AsSplitQuery for multiple collections to avoid cartesian explosion, or projecting only needed columns with Select. Enable EF Core query logging first to confirm how many SQL statements are generated.

Enable EF Core logging by calling LogTo with LogLevel.Information on the DbContext options, or set the Microsoft.EntityFrameworkCore.Database.Command category to Information in appsettings.json. EnableSensitiveDataLogging shows parameter values but should only be used in development.

Use AsNoTracking for any read-only query where you will not update the returned entities, 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 you have multiple Includes or large child collections that risk cartesian explosion. Single queries are preferable when you need transaction consistency across the loaded data.

This happens when ToList is called before Where, or when a LINQ expression cannot be translated to SQL and falls back to client evaluation. Filter with Where before materializing, and use EF.Functions.Like instead of string.Contains for pattern matching.

Use FromSqlInterpolated when LINQ cannot express the query efficiently, such as complex aggregations with HAVING clauses. Always use FromSqlInterpolated rather than FromSqlRaw with string interpolation to avoid SQL injection.