EF Core Relationships — One-to-Many, Many-to-Many, and Navigation Properties

One-to-many:   Patient → Prescriptions (one patient, many prescriptions)
One-to-one:    Patient → PatientNotes (one record each)
Many-to-many:  Patient ↔ Clinician (patient has many clinicians, clinician has many patients)

EF Core conventions detect relationships via navigation properties.
Fluent API is required for: non-conventional naming, cascade behavior,
  shadow properties, join entity configuration, composite keys.

// Domain
public class Patient
{
    public PatientId Id { get; private set; }
    private readonly List<Prescription> _prescriptions = new();
    public IReadOnlyList<Prescription> Prescriptions => _prescriptions.AsReadOnly();
}

public class Prescription
{
    public PrescriptionId Id  { get; private set; }
    public PatientId  PatientId { get; private set; }
    public Patient    Patient   { get; private set; } = default!;
}

// Configuration
public sealed class PrescriptionConfiguration : IEntityTypeConfiguration<Prescription>
{
    public void Configure(EntityTypeBuilder<Prescription> builder)
    {
        builder.ToTable("prescriptions");

        builder.HasOne(p => p.Patient)
            .WithMany(pat => pat.Prescriptions)
            .HasForeignKey(p => p.PatientId)
            .OnDelete(DeleteBehavior.Restrict);  // never cascade-delete prescriptions
    }
}

DeleteBehavior.Cascade:     delete parent → delete all children automatically
DeleteBehavior.Restrict:    delete parent → throws if children exist
DeleteBehavior.SetNull:     delete parent → set FK to NULL in children
DeleteBehavior.NoAction:    EF does nothing — database constraint decides
DeleteBehavior.ClientSetNull: EF sets FK to null in tracked children only

Clinical rule:
  NEVER cascade-delete prescriptions, lab results, or audit records.
  Use Restrict — force the caller to explicitly delete or archive children first.
  Cascade delete in clinical systems is a data loss risk.

public class PatientAllergySummary
{
    public PatientId PatientId  { get; private set; }
    public Patient   Patient    { get; private set; } = default!;
    public string    Summary    { get; private set; } = default!;
    public DateTime  UpdatedAt  { get; private set; }
}

// Configuration
builder.HasOne(p => p.Patient)
    .WithOne(pat => pat.AllergySummary)
    .HasForeignKey<PatientAllergySummary>(a => a.PatientId)
    .OnDelete(DeleteBehavior.Cascade);  // summary is owned by patient — safe to cascade

// The FK is on PatientAllergySummary, not Patient.
// WithOne/WithOne requires specifying which side holds the FK.

// Explicit join entity — required when the join has its own properties
public class PatientClinician
{
    public PatientId   PatientId  { get; private set; }
    public ClinicianId ClinicianId { get; private set; }
    public DateTime    AssignedAt { get; private set; }
    public string      Role       { get; private set; } = default!;  // "primary", "consultant"

    public Patient    Patient    { get; private set; } = default!;
    public Clinician  Clinician  { get; private set; } = default!;
}

public sealed class PatientClinicianConfiguration : IEntityTypeConfiguration<PatientClinician>
{
    public void Configure(EntityTypeBuilder<PatientClinician> builder)
    {
        builder.ToTable("patient_clinicians");

        builder.HasKey(pc => new { pc.PatientId, pc.ClinicianId });

        builder.HasOne(pc => pc.Patient)
            .WithMany(p => p.PatientClinicians)
            .HasForeignKey(pc => pc.PatientId)
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasOne(pc => pc.Clinician)
            .WithMany(c => c.PatientClinicians)
            .HasForeignKey(pc => pc.ClinicianId)
            .OnDelete(DeleteBehavior.Cascade);

        builder.Property(pc => pc.AssignedAt)
            .HasColumnType("datetime2");

        builder.Property(pc => pc.Role)
            .IsRequired()
            .HasMaxLength(50);
    }
}

// Shadow property: stored in DB, not in the domain entity
// Useful for: audit columns, soft delete, created/modified timestamps

public sealed class PatientConfiguration : IEntityTypeConfiguration<Patient>
{
    public void Configure(EntityTypeBuilder<Patient> builder)
    {
        // Shadow property — EF manages it, domain doesn't expose it
        builder.Property<DateTime>("CreatedAt")
            .HasColumnName("created_at")
            .HasDefaultValueSql("GETUTCDATE()");

        builder.Property<DateTime>("UpdatedAt")
            .HasColumnName("updated_at");
    }
}

// Set shadow property in SaveChanges override
public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    foreach (var entry in ChangeTracker.Entries<Patient>())
    {
        if (entry.State == EntityState.Modified)
            entry.Property("UpdatedAt").CurrentValue = DateTime.UtcNow;
    }
    return await base.SaveChangesAsync(ct);
}

// Eager loading: JOIN in the query
var patient = await _db.Patients
    .Include(p => p.Prescriptions)
        .ThenInclude(pr => pr.Medication)
    .FirstOrDefaultAsync(p => p.Id == patientId, ct);

// Explicit loading: separate query when needed
var patient = await _db.Patients.FindAsync(patientId);
await _db.Entry(patient).Collection(p => p.Prescriptions).LoadAsync(ct);

// Lazy loading: requires proxies, avoid in ASP.NET Core
// Lazy loading fires N+1 queries without warning — do not use.

// Projection: only load what you need (best for read queries)
var dto = await _db.Patients
    .Where(p => p.Id == patientId)
    .Select(p => new PatientDto(
        p.Id.Value,
        p.Mrn.Value,
        p.Prescriptions.Count(pr => pr.IsActive)))
    .FirstOrDefaultAsync(ct);