EF Core Querying — LINQ to SQL, Projections, and Filtering

EF Core translates LINQ to SQL at the database, not in memory.
Query is built as an expression tree → sent to the database as SQL.

Methods that translate:
  .Where()          → WHERE clause
  .Select()         → SELECT column list
  .OrderBy()        → ORDER BY
  .GroupBy()        → GROUP BY (limited — see note below)
  .Include()        → JOIN
  .Take() / Skip()  → TOP / OFFSET FETCH
  .Any()            → EXISTS
  .Count()          → COUNT(*)
  .Sum/Min/Max/Avg  → SQL aggregate functions

Methods that do NOT translate (run in memory):
  Custom C# methods inside Where/Select
  String.Format, Regex, arbitrary delegates
  Client-side evaluation throws InvalidOperationException in EF Core 3+

// Filter and project — SQL runs on DB server
var activePrescriptions = await _db.Prescriptions
    .Where(p => p.PatientId == patientId && p.IsActive)
    .Select(p => new PrescriptionDto(
        p.Id.Value,
        p.MedicationName,
        p.DosageValue.Amount,
        p.DosageValue.Unit,
        p.PrescribedAt))
    .OrderByDescending(p => p.PrescribedAt)
    .ToListAsync(ct);

// Single or default
var patient = await _db.Patients
    .Where(p => p.Mrn.Value == mrn)
    .Select(p => new PatientSummaryDto(p.Id.Value, p.FirstName, p.LastName))
    .FirstOrDefaultAsync(ct);

// Count with filter
var activeCount = await _db.Patients
    .CountAsync(p => !p.IsDeleted && p.WardId == wardId, ct);

// Defined in OnModelCreating — applied automatically to every query
modelBuilder.Entity<Patient>()
    .HasQueryFilter(p => !p.IsDeleted);

modelBuilder.Entity<Prescription>()
    .HasQueryFilter(p => !p.IsDeleted && p.TenantId == _currentTenant.TenantId);

// All queries against Patient automatically include WHERE is_deleted = 0
// To bypass the filter:
var deletedPatient = await _db.Patients
    .IgnoreQueryFilters()
    .FirstOrDefaultAsync(p => p.Id == id, ct);

// NEVER load entities when you only need a subset of columns for reading
// Bad: loads all columns, materializes Patient entity, no reason to
var patient = await _db.Patients.FindAsync(patientId);
return new PatientDto(patient.Id.Value, patient.Mrn.Value, patient.FirstName);

// Good: SELECT only what you need
var dto = await _db.Patients
    .Where(p => p.Id == patientId)
    .Select(p => new PatientDto(p.Id.Value, p.Mrn.Value, p.FirstName))
    .FirstOrDefaultAsync(ct);

// Projection with navigation properties — still a single query, no tracking
var summary = await _db.Patients
    .Where(p => p.Id == patientId)
    .Select(p => new PatientWithPrescriptionsDto(
        p.Id.Value,
        p.Mrn.Value,
        p.Prescriptions
            .Where(pr => pr.IsActive)
            .Select(pr => new PrescriptionSummary(pr.MedicationName, pr.DosageValue.Amount))
            .ToList()))
    .FirstOrDefaultAsync(ct);

// Read-only query — do not track the entities in the change tracker
var patients = await _db.Patients
    .AsNoTracking()
    .Where(p => p.WardId == wardId)
    .ToListAsync(ct);

// AsNoTrackingWithIdentityResolution — same as above but
// returns the same object reference for repeated rows (useful with Includes)
var patients = await _db.Patients
    .AsNoTrackingWithIdentityResolution()
    .Include(p => p.Prescriptions)
    .ToListAsync(ct);

// Rule: use AsNoTracking for all read queries.
// Tracking is only needed when you will update/delete the entity in the same DbContext scope.

// Problem: including multiple collections generates a Cartesian product
// Patient has 10 prescriptions and 20 lab results → 200 row result set (10 × 20)
var patient = await _db.Patients
    .Include(p => p.Prescriptions)
    .Include(p => p.LabResults)
    .FirstOrDefaultAsync(p => p.Id == patientId, ct);
// SQL: JOIN on both → 10 × 20 = 200 rows returned, then deduplicated in memory

// Fix: AsSplitQuery — runs separate queries, joins in memory
var patient = await _db.Patients
    .Include(p => p.Prescriptions)
    .Include(p => p.LabResults)
    .AsSplitQuery()
    .FirstOrDefaultAsync(p => p.Id == patientId, ct);
// SQL: 3 separate queries, no Cartesian product

// Set as default globally
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString,
        o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)));

// Tag queries so you can find them in SQL Profiler / Application Insights
var patients = await _db.Patients
    .TagWith("GetWardPatients - WardController")
    .Where(p => p.WardId == wardId)
    .ToListAsync(ct);

// Generated SQL includes a comment at the top:
// -- GetWardPatients - WardController
// SELECT ...

// Useful for diagnosing slow queries in production — the tag appears in query plans

// Offset pagination: SKIP(n) gets slower as n grows (scans to the offset)
var page3 = await _db.Patients
    .OrderBy(p => p.Mrn)
    .Skip(200)
    .Take(100)
    .ToListAsync(ct);

// Keyset pagination: WHERE after last seen key — O(1) regardless of page number
var nextPage = await _db.Patients
    .Where(p => string.Compare(p.Mrn.Value, lastSeenMrn) > 0)
    .OrderBy(p => p.Mrn)
    .Take(100)
    .ToListAsync(ct);

// Keyset pagination requires: consistent ORDER BY, unique sort key, index on sort column