EF Core Migrations — Managing Schema Changes

1. Change domain model / configuration
2. dotnet ef migrations add 
   → creates Migrations/_.cs
3. Review generated migration — ALWAYS check before applying
4. dotnet ef database update
   → applies migration to database
5. Commit the .cs migration file to source control

# Add a migration
dotnet ef migrations add AddPatientMrnUniqueIndex \
  --project Infrastructure \
  --startup-project Api \
  --context ApplicationDbContext \
  --output-dir Persistence/Migrations

# List pending migrations
dotnet ef migrations list

# Remove the last migration (only if not applied to any DB)
dotnet ef migrations remove

# Generate SQL script (review before running)
dotnet ef migrations script --idempotent --output migrations.sql

// Program.cs — auto-migrate on startup (dev/test environments)
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    await db.Database.MigrateAsync();
}

// Production: DO NOT auto-migrate at startup.
// Use migration bundles or idempotent SQL scripts run by CI/CD instead.
// Auto-migrate in production means every restart risks a schema change mid-rollout.

# Build a self-contained migration binary
dotnet ef migrations bundle \
  --project Infrastructure \
  --startup-project Api \
  --output efbundle \
  --self-contained

# Run the bundle in CI/CD pipeline (no dotnet SDK required)
./efbundle --connection "Server=prod-db;..."

# Idempotent SQL script alternative
dotnet ef migrations script --idempotent --output ./migrations/apply.sql
# Run apply.sql via sqlcmd or ADO.NET in the pipeline

// Configuration-based seeding (applied with migrations)
public sealed class RoleConfiguration : IEntityTypeConfiguration<AppRole>
{
    public void Configure(EntityTypeBuilder<AppRole> builder)
    {
        builder.HasData(
            new AppRole { Id = Guid.Parse("..."), Name = "Clinician",     NormalizedName = "CLINICIAN" },
            new AppRole { Id = Guid.Parse("..."), Name = "Pharmacist",    NormalizedName = "PHARMACIST" },
            new AppRole { Id = Guid.Parse("..."), Name = "Administrator", NormalizedName = "ADMINISTRATOR" }
        );
    }
}

// HasData uses fixed IDs — required for idempotent seeding.
// Avoid auto-generated IDs in HasData — each migration regeneration changes them.

// Add data migration inside a schema migration
public partial class AddPatientMrnColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name:      "mrn",
            table:     "patients",
            maxLength: 20,
            nullable:  true);   // nullable first — then populate — then make required

        // Back-fill existing rows
        migrationBuilder.Sql(@"
            UPDATE patients
            SET mrn = CONCAT('MRN-', CAST(id AS NVARCHAR(36)))
            WHERE mrn IS NULL");

        // Now make the column required
        migrationBuilder.AlterColumn<string>(
            name:      "mrn",
            table:     "patients",
            maxLength: 20,
            nullable:  false,
            oldClrType: typeof(string),
            oldMaxLength: 20,
            oldNullable: true);

        migrationBuilder.CreateIndex(
            name:    "ix_patients_mrn",
            table:   "patients",
            column:  "mrn",
            unique:  true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(name: "ix_patients_mrn", table: "patients");
        migrationBuilder.DropColumn(name: "mrn", table: "patients");
    }
}

EF Core has no built-in rollback — Down() is a manual operation.

Option A: Down() migration (revert to previous state)
  dotnet ef database update 
  Risk: destructive if Down() drops columns with data

Option B: Forward rollback (preferred for production)
  Add a new migration that reverts the unwanted change.
  Never destroy data — add nullable columns, rename safely.

Option C: Point-in-time restore (database backup)
  For catastrophic failures — restore the DB from backup.
  Requires taking a backup before applying migrations in production.

Production rule: migrations should always be backward-compatible
  with the previous version of the application code.

// Apply migrations only in specific environments
if (app.Environment.IsDevelopment() || app.Environment.IsStaging())
{
    using var scope = app.Services.CreateScope();
    var db = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    await db.Database.MigrateAsync();
}

// Production: CI/CD pipeline runs the migration bundle before deploying
// This ensures migration completes before new code is active.