EF Core N+1 Problem — Detection and Resolution

The N+1 problem: 1 query to get a list, then N queries to get
  related data for each item — one per row.

Example:
  Query 1: SELECT * FROM patients WHERE ward_id = @wardId  → 50 patients
  Query 2: SELECT * FROM prescriptions WHERE patient_id = @id1
  Query 3: SELECT * FROM prescriptions WHERE patient_id = @id2
  ...
  Query 51: SELECT * FROM prescriptions WHERE patient_id = @id50

Total: 51 queries to display one ward dashboard.
At 3ms per query: 153ms wasted on unnecessary round trips.
At 500 patients: 501 queries, 1.5 seconds of query overhead alone.

// Scenario 1: Accessing navigation property without Include
var patients = await _db.Patients
    .Where(p => p.WardId == wardId)
    .ToListAsync(ct);

foreach (var patient in patients)
{
    // Each access to Prescriptions fires a new query if lazy loading is enabled
    // With lazy loading disabled (default), this throws NullReferenceException or returns empty
    var count = patient.Prescriptions.Count;  // N+1 if lazy loading is on
}

// Scenario 2: Lazy loading enabled via proxies
// services.AddDbContext<AppDbContext>(o => o.UseLazyLoadingProxies());
// Navigation property access silently fires queries — invisible unless you look at logs

// Enable detailed logging to see every SQL query
builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.LogTo(Console.WriteLine, LogLevel.Information);  // development only
    options.EnableSensitiveDataLogging();  // shows parameter values
});

// Or use structured logging
options.LogTo(
    (eventId, level) => level == LogLevel.Information,
    (data) => logger.LogInformation(data.ToString()));

// Watch for repeated queries with incrementing IDs — that is N+1.
// "SELECT * FROM prescriptions WHERE patient_id = '...' " appearing 50 times is the signal.

// NuGet: MiniProfiler.AspNetCore.Mvc + MiniProfiler.EntityFrameworkCore
builder.Services.AddMiniProfiler(options =>
    options.RouteBasePath = "/profiler")
    .AddEntityFramework();

// In development: visit /profiler/results-index to see query counts per request.
// Any request with >10 queries is suspect.
// Application Insights: dependency tracking shows individual SQL calls with durations.

// BAD: loads patients, then N queries for prescriptions
var patients = await _db.Patients
    .Where(p => p.WardId == wardId)
    .ToListAsync(ct);

// GOOD: single query (or split queries) with Include
var patients = await _db.Patients
    .Include(p => p.Prescriptions.Where(pr => pr.IsActive))
    .Where(p => p.WardId == wardId)
    .AsNoTracking()
    .ToListAsync(ct);
// SQL: JOIN prescriptions ON prescriptions.patient_id = patients.id
//      AND prescriptions.is_active = 1
// One query, all data returned together.

// Best for read queries — load only what you need, no N+1 possible
var summaries = await _db.Patients
    .Where(p => p.WardId == wardId)
    .Select(p => new PatientWardSummaryDto(
        p.Id.Value,
        p.Mrn.Value,
        p.FirstName + " " + p.LastName,
        p.Prescriptions.Count(pr => pr.IsActive),      // subquery, not N+1
        p.LabResults
            .OrderByDescending(r => r.RecordedAt)
            .Select(r => r.InrValue)
            .FirstOrDefault()))
    .AsNoTracking()
    .ToListAsync(ct);

// EF Core translates the nested Prescriptions.Count() and
// LabResults.OrderByDescending().FirstOrDefault() into correlated subqueries in SQL.
// Still ONE query — no N+1.

// When you can't use Include (e.g., loading related data conditionally)
// Load parent entities, then load all related entities in one query using Contains

var patients = await _db.Patients
    .Where(p => p.WardId == wardId)
    .AsNoTracking()
    .ToListAsync(ct);

var patientIds = patients.Select(p => p.Id).ToList();

// One query for all prescriptions for these patients
var prescriptions = await _db.Prescriptions
    .Where(pr => patientIds.Contains(pr.PatientId) && pr.IsActive)
    .AsNoTracking()
    .ToListAsync(ct);

// Group in memory
var prescriptionsByPatient = prescriptions
    .GroupBy(pr => pr.PatientId)
    .ToDictionary(g => g.Key, g => g.ToList());

// Total: 2 queries, not N+1

// Load specific navigation properties explicitly when needed
var patient = await _db.Patients.FindAsync(patientId);

// Load only if needed based on business condition
if (includePrescriptions)
{
    await _db.Entry(patient)
        .Collection(p => p.Prescriptions)
        .Query()
        .Where(pr => pr.IsActive)
        .LoadAsync(ct);
}
// One controlled query — no accidental N+1