Diagnosing and Fixing EF Core Lazy‑Loading N+1 Query Problems in ASP.NET Core

Recognizable Condition

When an ASP.NET Core endpoint accesses a navigation property (e.g., order.Customer) inside a loop, response times rise and the database shows many identical SELECT statements for the same related entity.

Cause / Diagnostic Overview

Symptom	Likely Cause
Repeated SELECTs for navigation property	Lazy loading enabled but missing Include/ThenInclude
Entity class sealed or navigation not virtual	Proxy cannot be created → lazy loading falls back to explicit load or throws

Ordered Checks

1. Enable EF Core logging at Information level in appsettings.Development.json or via DbContextOptionsBuilder.LogTo.
2. Reproduce the request that triggers the loop and examine the log for repeated SELECT statements targeting the same table.
3. Verify that the entity type is not sealed and that each navigation property is marked virtual (required for proxy generation).
4. Confirm that UseLazyLoadingProxies is present in the DbContextOptions registration.
5. Check the LINQ query that loads the root entities for missing Include or ThenInclude calls.

Fixes Tied to Findings

• Add missing eager loading: Modify the query to include the navigation, e.g.

  var orders = ctx.Orders.Include(o => o.Customer).ToList();

• Disable lazy loading if not needed: Remove UseLazyLoadingProxies and use explicit loading or projection. Example:

  var orders = ctx.Orders.Select(o => new { o.Id, CustomerName = o.Customer.Name }).ToList();

• Enable proxy generation: Ensure the entity class is public and not sealed, and navigation properties are virtual. If you cannot change the class, consider using EF Core 5+ UseLazyLoadingProxies with ILazyLoader injection instead of proxies.

Escalation Criteria

If after adding the appropriate Include the log still shows extra SELECTs:

• Consider rewriting the query to use projection (Select) to fetch only needed columns.
• Enable AsNoTracking for read‑only scenarios to reduce overhead.
• Evaluate batch loading or splitting the query into multiple queries (EF Core 6+ SplitQuery) when the relationship is one‑to‑many and would cause a Cartesian product.
• Profile memory usage; excessive Include can inflate result sets.

Verification Steps

1. Run the application with EF Core logging set to LogLevel.Information and confirm the number of SQL statements matches the expected count (e.g., one query for root entities plus one per Include).
2. Use a benchmarking tool such as BenchmarkDotNet to measure average response time before and after the change; look for fewer round‑trips and lower latency.
3. Inspect the generated SQL via a SQL profiler or by enabling DbContext.Database.Log to ensure no stray SELECT statements for the navigation property remain.

Limitations and Practical Check

Adding Include may increase the size of the result set; verify that the additional columns do not cause excessive memory consumption by monitoring GC allocations or using a profiler. If the payload grows beyond acceptable limits, switch to a projection that selects only required fields.