LINQ Filtering and Projection — Where, Select, and Efficient Queries

// Simple predicate
var activeDoctors = db.Users
    .Where(u => u.IsActive && u.Role == "Doctor")
    .ToList();
// SQL: WHERE IsActive = 1 AND Role = 'Doctor'

// String filtering
var smiths = db.Patients
    .Where(p => p.LastName.StartsWith("Smith"))
    .ToList();
// SQL: WHERE LastName LIKE 'Smith%'

// Date range
var recentPrescriptions = db.Prescriptions
    .Where(p => p.IssuedAt >= DateTime.UtcNow.AddDays(-30))
    .ToList();
// SQL: WHERE IssuedAt >= DATEADD(day, -30, GETUTCDATE())

// AND — all conditions must be true
var criticalOrders = db.DrugOrders
    .Where(o => o.Priority == "Critical" &&
                o.Status != "Completed" &&
                o.RequestedAt < DateTime.UtcNow.AddHours(-2))
    .ToList();

// OR — any condition
var urgentOrOverdue = db.DrugOrders
    .Where(o => o.Priority == "Urgent" || o.IsOverdue)
    .ToList();

// NOT
var unprocessed = db.DrugOrders
    .Where(o => !o.IsProcessed)
    .ToList();

// Build the WHERE clause conditionally — only one SQL query
public async Task<List<Patient>> SearchAsync(PatientSearchFilter f, CancellationToken ct)
{
    var query = db.Patients.AsQueryable();

    if (!string.IsNullOrWhiteSpace(f.Name))
        query = query.Where(p => p.FullName.Contains(f.Name));

    if (!string.IsNullOrWhiteSpace(f.MRN))
        query = query.Where(p => p.MRN == f.MRN);

    if (f.Department.HasValue)
        query = query.Where(p => p.DepartmentId == f.Department.Value);

    if (f.OnlyActive)
        query = query.Where(p => p.IsActive);

    return await query.ToListAsync(ct);
}

// Load entire entity — includes all columns
var patients = db.Patients.Where(p => p.IsActive).ToList();

// Project to DTO — only loads the columns in the DTO
var dtos = db.Patients
    .Where(p => p.IsActive)
    .Select(p => new PatientSummaryDto(p.Id, p.FullName, p.MRN, p.Department))
    .ToList();
// SQL: SELECT Id, FullName, MRN, Department FROM Patients WHERE IsActive = 1
// No BlobData, FullAuditHistory, NavigationProperties columns loaded

// Efficient: project across relationships in one query
var prescriptionDtos = await db.Prescriptions
    .Where(p => p.PatientId == patientId)
    .Select(p => new PrescriptionDto(
        p.Id,
        p.Drug.Name,         // joined to Drug table automatically
        p.Drug.Dosage,
        p.IssuedAt,
        p.IssuedBy.FullName  // joined to Users table
    ))
    .ToListAsync(ct);

// EF Core generates: SELECT p.Id, d.Name, d.Dosage, p.IssuedAt, u.FullName
//   FROM Prescriptions p
//   JOIN Drugs d ON p.DrugId = d.Id
//   JOIN Users u ON p.IssuedById = u.Id
//   WHERE p.PatientId = @patientId

// Each patient has many prescriptions — flatten to one sequence
var allActiveDrugs = db.Patients
    .Where(p => p.IsActive)
    .SelectMany(p => p.Prescriptions)
    .Select(rx => rx.DrugName)
    .Distinct()
    .ToList();

// In-memory equivalent (when collection is already loaded)
var patientList = new List<Patient> { /* ... */ };
var allPrescriptions = patientList
    .SelectMany(p => p.Prescriptions)
    .ToList();

// Handle nullable reference types correctly
var patientsWithPhone = db.Patients
    .Where(p => p.PhoneNumber != null && p.PhoneNumber.Length > 0)
    .ToList();

// Or with null-conditional (EF Core handles this)
var withAllergy = db.Patients
    .Where(p => p.AllergyNotes != null)
    .ToList();

// In-memory null-safe
var notes = patients
    .Select(p => p.AllergyNotes?.ToUpperInvariant() ?? "NONE")
    .ToList();

// Project to anonymous type for one-off use (no reuse needed)
var summary = await db.Patients
    .Where(p => p.IsActive)
    .Select(p => new
    {
        p.Id,
        p.FullName,
        PrescriptionCount = p.Prescriptions.Count()
    })
    .OrderByDescending(x => x.PrescriptionCount)
    .Take(10)
    .ToListAsync();

// Filter a collection of base types to a specific derived type
public abstract class BaseOrder { }
public class DrugOrder : BaseOrder { public string DrugName { get; set; } = ""; }
public class LabOrder  : BaseOrder { public string TestName { get; set; } = ""; }

var orders = new List<BaseOrder> { /* mixed */ };
var drugOrders = orders.OfType<DrugOrder>().ToList();
// Only DrugOrder instances — no cast needed