LINQ Performance — Writing Queries That Scale

Performance hierarchy (best to worst):
  1. SQL aggregate  → COUNT(*) returns 1 row
  2. SQL projection → SELECT Id, Name returns 2 columns
  3. SQL filter     → WHERE IsActive = 1 returns a subset
  4. Full entity    → SELECT * returns all columns
  5. In-memory filter after ToList() → loads everything, filters in .NET
  6. N+1 queries    → 1 + N round trips to the database

// With tracking (default): EF Core tracks every entity in memory
// Use for: create, update, delete operations
var patient = await db.Patients.FindAsync(id);
patient.Name = "Updated";
await db.SaveChangesAsync();

// Without tracking: EF Core skips change tracking overhead
// Use for: read-only queries, lists, projections
var patients = await db.Patients
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

// QueryTrackingBehavior: set globally for read-heavy contexts
builder.Services.AddDbContext<ReadOnlyContext>(options =>
    options.UseSqlServer(connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

// Always combine Skip/Take with a deterministic OrderBy
// Without OrderBy, SQL Server can return rows in any order between pages
var page = await db.Patients
    .AsNoTracking()
    .OrderBy(p => p.Id)  // deterministic order
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .Select(p => new PatientSummaryDto(p.Id, p.FullName, p.MRN))
    .ToListAsync();

// Keyset pagination (better performance for deep pages)
// "Give me 20 patients after ID = lastSeenId"
var keysetPage = await db.Patients
    .AsNoTracking()
    .Where(p => p.Id > lastSeenId)
    .OrderBy(p => p.Id)
    .Take(pageSize)
    .ToListAsync();

// N+1: 1 query for patients, then 1 per patient for prescriptions
var patients = await db.Patients.ToListAsync();
foreach (var p in patients)
{
    var rxCount = p.Prescriptions.Count;  // lazy load — 1 query per patient
    Console.WriteLine($"{p.Name}: {rxCount} prescriptions");
}
// 1 + N queries total

// Fix 1: eager loading with Include
var patients = await db.Patients
    .Include(p => p.Prescriptions)  // JOIN — 1 query total
    .ToListAsync();

// Fix 2: projection (load only what you need — no Include overhead)
var summary = await db.Patients
    .Select(p => new
    {
        p.FullName,
        RxCount = p.Prescriptions.Count()  // SQL COUNT() subquery
    })
    .ToListAsync();
// 1 query, returns only Name + Count — no Prescription data loaded

// EF Core re-parses the expression tree on every call
// Compiled queries cache the parsed plan
private static readonly Func<AppDbContext, string, Task<Patient?>> FindByMRN =
    EF.CompileAsyncQuery(
        (AppDbContext db, string mrn) =>
            db.Patients
                .AsNoTracking()
                .FirstOrDefault(p => p.MRN == mrn && p.IsActive));

// Usage — zero expression compilation overhead
var patient = await FindByMRN(db, "MRN-001");

// Expensive: loads all 40+ columns including large text fields
var patients = await db.Patients.ToListAsync();

// Efficient: loads only 4 columns
var summaries = await db.Patients
    .Select(p => new PatientSummaryDto(p.Id, p.FullName, p.MRN, p.Department))
    .ToListAsync();

// Rule of thumb:
// If you only read properties (no write-back), project to DTO
// If you need to track and save, load the entity

// PLINQ: parallel processing for CPU-intensive in-memory operations
// DO NOT use PLINQ with EF Core — DbContext is not thread-safe

// Appropriate use: CPU-bound processing on already-loaded data
var drugInteractions = prescriptions
    .AsParallel()
    .WithDegreeOfParallelism(4)
    .Where(rx => CheckInteraction(rx, allDrugs))  // CPU-intensive check
    .ToList();

// DO NOT:
// db.Patients.AsParallel().Where(...)  // wrong — DbContext not thread-safe

// Processing 100,000 records in one pass risks timeouts and memory pressure
// Process in chunks
var offset = 0;
const int batchSize = 500;

while (true)
{
    var batch = await db.DrugOrders
        .AsNoTracking()
        .Where(o => !o.IsProcessed)
        .OrderBy(o => o.Id)
        .Skip(offset)
        .Take(batchSize)
        .ToListAsync();

    if (batch.Count == 0) break;

    foreach (var order in batch)
        await ProcessOrderAsync(order);

    offset += batch.Count;
}

// Or use ExecuteDeleteAsync / ExecuteUpdateAsync for bulk operations
await db.DrugOrders
    .Where(o => o.IsCompleted && o.CompletedAt < DateTime.UtcNow.AddYears(-2))
    .ExecuteDeleteAsync();  // single SQL DELETE — no entity loading

// Development: log slow queries
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(
               output: Console.WriteLine,
               minimumLevel: LogLevel.Information,
               options: DbContextLoggerOptions.SingleLine)
           .EnableDetailedErrors()
           .EnableSensitiveDataLogging());  // dev only

// Custom slow query logging
options.ConfigureWarnings(w =>
    w.Log((RelationalEventId.CommandExecuted, LogLevel.Warning)));