EF Core Performance — Query Optimization and Benchmarking

EF Core overhead vs raw ADO.NET:
  Simple SELECT → EF Core adds ~0.1ms overhead (negligible)
  Complex projection → EF Core is comparable to hand-written SQL
  Entity tracking → Change tracker overhead for large result sets
  
The N+1 problem and missing indexes matter more than EF Core overhead.
Profile first — optimize where the data shows it is needed.

// Default: EF Core tracks every loaded entity in the ChangeTracker
// Change tracker stores original values for comparison on SaveChanges
// For read-only queries, tracking is wasted memory and CPU

// Tracked (default) — use when you will update/delete the entity
var prescription = await _db.Prescriptions.FindAsync(id);
prescription.MarkDispensed();
await _db.SaveChangesAsync(ct);

// No tracking — use for all read queries
var summaries = await _db.Prescriptions
    .AsNoTracking()
    .Where(p => p.PatientId == patientId)
    .ToListAsync(ct);

// Global: disable tracking by default, enable when needed
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString)
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

// Then explicitly enable for write scenarios:
var entity = await _db.Patients
    .AsTracking()
    .FirstOrDefaultAsync(p => p.Id == id, ct);

// EF Core compiles a query expression tree to a SQL query on first execution.
// For hot paths called thousands of times per second, pre-compile the query.

// Define compiled query as a static field
private static readonly Func<ApplicationDbContext, Guid, Task<Patient?>> GetPatientById =
    EF.CompileAsyncQuery((ApplicationDbContext db, Guid id) =>
        db.Patients
            .AsNoTracking()
            .FirstOrDefault(p => p.Id == new PatientId(id)));

// Use the compiled query — skips expression tree compilation
var patient = await GetPatientById(_db, patientId.Value);

// Benchmark: compiled queries are ~30% faster on hot paths
// (eliminates repeated query plan compilation overhead)

// Problem: loading entities to update them is wasteful
// BAD: loads 1,000 prescriptions into memory just to mark them expired
var expired = await _db.Prescriptions
    .Where(p => p.ExpiryDate < DateTime.UtcNow && p.IsActive)
    .ToListAsync(ct);

foreach (var p in expired)
    p.MarkExpired();

await _db.SaveChangesAsync(ct);

// GOOD: ExecuteUpdateAsync — UPDATE in one SQL statement, no entity loading
await _db.Prescriptions
    .Where(p => p.ExpiryDate < DateTime.UtcNow && p.IsActive)
    .ExecuteUpdateAsync(
        setters => setters.SetProperty(p => p.IsActive, false),
        ct);

// GOOD: ExecuteDeleteAsync — DELETE in one SQL statement
await _db.AuditLogs
    .Where(a => a.CreatedAt < retentionCutoff)
    .ExecuteDeleteAsync(ct);

// ExecuteUpdate/Delete bypass the change tracker and interceptors.
// Do not use for entities that need audit trail or soft delete via interceptors.

// For processing large datasets without loading all into memory
// Use Chunk() (.NET 6+) to process in batches

await foreach (var batch in _db.Patients
    .Where(p => p.WardId == wardId)
    .AsNoTracking()
    .AsAsyncEnumerable()
    .Chunk(100))
{
    foreach (var patient in batch)
        await ProcessPatientAsync(patient, ct);

    // Can release memory between batches
}

// Or use chunked paging with keyset pagination for predictable memory use
var lastId = Guid.Empty;
while (true)
{
    var batch = await _db.Patients
        .Where(p => p.Id.Value > lastId)
        .OrderBy(p => p.Id)
        .Take(100)
        .AsNoTracking()
        .ToListAsync(ct);

    if (batch.Count == 0) break;

    foreach (var patient in batch)
        await ProcessAsync(patient, ct);

    lastId = batch.Last().Id.Value;
}

// SQL Server connection pool is managed by SqlClient — EF Core uses it automatically.
// Default: min=0, max=100 connections per connection string.
// Configure via connection string:
"Server=sql-server;Database=clinical_db;Min Pool Size=5;Max Pool Size=200;Connection Timeout=30"

// DbContext lifetime: Scoped (one per HTTP request) is the default and correct.
// Never use Singleton DbContext — it causes thread safety issues.
// Never use Transient DbContext — too many pool checkouts.

// Monitor pool exhaustion:
// "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool."
// This means Max Pool Size is too low, or connections are being held too long.

// EF Core caches query SQL per query expression shape.
// Parameterized queries share the same plan — good.
// Queries with different constant values get different cache entries — bad if many variations.

// BAD: each wardId value creates a new cache entry (if passed as a constant)
// (EF Core normally parameterizes correctly — this is an edge case with Contains)
var ids = new List<Guid> { id1, id2, id3 };  // list length matters for caching
var patients = await _db.Patients
    .Where(p => ids.Contains(p.Id.Value))  // EF Core parameterizes each element separately
    .ToListAsync(ct);

// For highly variable lists: consider temp table patterns or raw SQL with table-valued parameters

MiniProfiler (development):
  NuGet: MiniProfiler.AspNetCore.Mvc + MiniProfiler.EntityFrameworkCore
  Visit /profiler/results-index to see query counts, durations per request

EF Core logging:
  options.LogTo(Console.WriteLine, LogLevel.Information)
  options.EnableSensitiveDataLogging()   // shows parameter values

Application Insights:
  Dependency tracking shows individual SQL calls with durations
  Use QueryTagWith() to tag queries with feature context

SQL Server Profiler / Extended Events:
  Shows actual SQL reaching the database
  Identifies missing index suggestions
  Query Execution Plan: look for Table Scan → should be Index Seek