GitHub Actions snippet

xtFactory<ApplicationDbContext> { public ApplicationDbContext CreateDbContext(string[] args) { var configuration = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.Development.json") .Build();

    var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
    optionsBuilder.UseSqlServer(configuration.GetConnectionString("DefaultConnection"));

    return new ApplicationDbContext(optionsBuilder.Options);
}

}

Rationale: This prevents design-time commands from failing due to missing runtime secrets, ensures migration scaffolding uses consistent provider configurations, and enables CI/CD agents to generate migrations without spinning up full application hosts.

### Step 2: Transactional Migration Execution
EF Core wraps migrations in a transaction by default, but this behavior changes when using certain providers or when migrations contain raw SQL that cannot be rolled back. Explicitly configure transaction behavior and isolate large data transformations.

```csharp
public static IHost MigrateDatabase(this IHost host)
{
    using var scope = host.Services.CreateScope();
    var context = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    var logger = scope.ServiceProvider.GetRequiredService<ILogger<Program>>();

    try
    {
        context.Database.Migrate(); // Executes pending migrations transactionally
        logger.LogInformation("Database migration completed successfully.");
    }
    catch (Exception ex)
    {
        logger.LogError(ex, "Database migration failed. Rolling back.");
        throw;
    }

    return host;
}

Rationale: Application startup migration execution blocks the first request and ties deployment success to app health. In production, decouple migration execution from app startup using a dedicated CLI command or CI/CD step. Use context.Database.Migrate() only in controlled environments or as a fallback with explicit timeout and retry policies.

Step 3: Expand/Contract Pattern for Zero-Downtime Schema Changes

Direct column additions or renames on active tables cause lock escalation and query failures. The expand/contract pattern splits changes into backward-compatible phases.

Phase 1 (Expand): Add new column, keep old column, sync data via triggers or background jobs. Phase 2 (Contract): Deprecate old column, remove references from code, drop old column.

// Phase 1: Add new column without dropping old
migrationBuilder.AddColumn<string>(
    name: "CustomerEmailV2",
    table: "Customers",
    type: "nvarchar(256)",
    nullable: true);

// Data sync executed separately via background worker or raw SQL migration
migrationBuilder.Sql(@"
    UPDATE Customers 
    SET CustomerEmailV2 = CustomerEmail 
    WHERE CustomerEmailV2 IS NULL AND CustomerEmail IS NOT NULL;
");

Rationale: This pattern ensures zero-downtime deployments by maintaining compatibility across application versions. Old code continues reading CustomerEmail, new code reads CustomerEmailV2. Data synchronization occurs asynchronously, avoiding long-running transactions that block writes.

Step 4: CI/CD Integration & Idempotent Execution

Migrations must be idempotent and environment-aware. Use dotnet ef database update in pipelines with explicit connection resolution and dry-run validation.

# GitHub Actions snippet
- name: Validate migrations
  run: dotnet ef migrations script --idempotent --output migrations.sql

- name: Apply migrations
  run: dotnet ef database update --connection "${{ secrets.DB_CONNECTION_STRING }}"
  env:
    ASPNETCORE_ENVIRONMENT: Production

Rationale: Generating an idempotent SQL script allows DBA review, enables execution against read-only replicas for validation, and provides an audit trail. Pipeline execution ensures migrations run before application deployment, preventing version mismatches. Environment variables isolate connection strings, and --idempotent guarantees safe re-execution.

Architecture Decisions & Rationale

• Separate schema and data migrations: Schema changes require immediate execution; data changes can be batched, throttled, or run during maintenance windows.
• Avoid startup-time migrations in production: Ties deployment success to app health, blocks requests, and complicates rollback.
• Use explicit transaction boundaries: EF Core’s default transaction behavior varies by provider. Explicit control prevents partial schema states.
• Version migration artifacts: Treat .sql scripts and EF Core migration files as immutable deployment artifacts. Store them alongside application binaries.
• Implement migration health checks: Expose /health/migrations endpoint that verifies __EFMigrationsHistory matches expected version before routing traffic.

Pitfall Guide

1. Running migrations inside application startup in production Startup execution blocks the first HTTP request, ties deployment success to app health, and complicates rollback. If migration fails, the app fails to start, triggering cascading health check failures. Best practice: Execute migrations in CI/CD pipelines or dedicated deployment containers. Use startup execution only for local development or staging environments with explicit feature flags.

2. Ignoring data migrations alongside schema changes Adding a column without migrating existing data creates null constraint violations, application errors, or silent data loss. EF Core’s Up() method handles schema but leaves data transformation to developers. Best practice: Pair every schema migration with a corresponding data migration strategy. Use background workers, batched updates, or database triggers for large tables. Never assume ALTER TABLE is sufficient.

3. Non-transactional migrations causing partial failures Some providers (e.g., SQL Server) cannot roll back certain DDL operations like CREATE INDEX or ALTER TABLE with large data shifts. EF Core wraps migrations in transactions, but provider limitations override this behavior. Best practice: Review provider-specific DDL transaction support. Split large migrations into smaller, idempotent steps. Use Sql() commands with explicit BEGIN TRANSACTION/COMMIT/ROLLBACK blocks when needed.

4. Skipping environment parity testing Migrations that succeed locally often fail in staging due to different collations, index fragmentation, or constraint states. EF Core generates provider-agnostic SQL, but execution plans vary by environment. Best practice: Run migrations against environment-cloned databases before production deployment. Use Docker Compose or testcontainers to replicate production schema states. Validate migration scripts with dotnet ef migrations script against target providers.

5. Hardcoding connection strings in migration contexts Design-time contexts that read from appsettings.json without environment overrides fail in CI/CD or containerized deployments. Best practice: Use IDesignTimeDbContextFactory with environment-aware configuration. Inject connection strings via pipeline secrets or Kubernetes ConfigMaps. Never commit production connection strings to source control.

6. Lock escalation on large table alterations ALTER TABLE on tables with millions of rows acquires schema modification locks, blocking inserts/updates and causing timeout exceptions. EF Core does not mitigate lock escalation. Best practice: Use expand/contract pattern. Schedule large migrations during maintenance windows. Consider online index rebuilds, partition switching, or table recreation strategies for extreme scale.

7. Treating migrations as disposable rather than versioned artifacts Deleting or modifying existing migration files breaks deployment history, causes __EFMigrationsHistory mismatches, and prevents rollbacks. Best practice: Never edit generated migration files after commit. Create new migrations for corrections. Archive failed migrations with explicit rollback scripts. Maintain a migration ledger in your deployment pipeline.

Production Bundle

Action Checklist

• Implement IDesignTimeDbContextFactory: Decouple design-time context creation from runtime configuration to ensure consistent migration scaffolding across environments.
• Separate schema and data migrations: Execute schema changes immediately, defer large data transformations to background workers or maintenance windows to avoid transactional blocking.
• Enforce idempotent migration execution: Generate and validate SQL scripts with --idempotent flag before pipeline execution to guarantee safe re-runs and auditability.
• Adopt expand/contract pattern: Split column additions, renames, and type changes into backward-compatible phases to maintain zero-downtime deployments across application versions.
• Decouple migrations from app startup: Execute migrations in CI/CD pipelines or dedicated deployment containers to prevent startup blocking and simplify rollback procedures.
• Validate environment parity: Test migrations against cloned production databases using testcontainers or staging replicas to catch collation, constraint, and index fragmentation issues early.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Single-developer project with <100k rows	Default EF Core CLI + startup migration	Low complexity, fast iteration, acceptable risk profile	Minimal infrastructure overhead
Multi-team microservices with active tables	Expand/Contract Pattern + CI/CD pipeline	Prevents lock escalation, maintains backward compatibility, enables parallel deployments	Higher engineering overhead, lower outage cost
Compliance-driven environment (SOC2, HIPAA)	Manual SQL + DBA review + idempotent scripts	Full audit trail, explicit transaction control, regulatory alignment	Increased deployment time, reduced compliance risk
High-traffic SaaS with rolling deployments	Migration Orchestrator (DbUp/FluentMigrator) + health checks	Deterministic execution, versioned artifacts, automated rollback verification	Moderate tooling cost, high operational resilience

Configuration Template

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=AppDb;Trusted_Connection=True;TrustServerCertificate=True;"
  },
  "MigrationSettings": {
    "EnableStartupMigration": false,
    "IdempotentScriptPath": "./scripts/migrations.sql",
    "TimeoutSeconds": 300
  }
}

// Program.cs / HostBuilder configuration
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"),
        sql => sql.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName)));

// Optional: Disable startup migration in production
if (!builder.Environment.IsProduction())
{
    using var scope = builder.Services.BuildServiceProvider().CreateScope();
    scope.ServiceProvider.GetRequiredService<ApplicationDbContext>().Database.Migrate();
}

# .github/workflows/migrate.yml
name: Database Migration Pipeline
on:
  push:
    branches: [main]
    paths: ['src/Infrastructure/Migrations/**']

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Setup .NET
        uses: actions/setup-dotnet@v4
        with:
          dotnet-version: '8.0.x'
      - name: Generate idempotent script
        run: dotnet ef migrations script --idempotent --output migrations.sql --project ./src/Infrastructure
      - name: Apply to staging
        run: dotnet ef database update --connection "${{ secrets.STAGING_DB_CONNECTION }}" --project ./src/Infrastructure
        env:
          ASPNETCORE_ENVIRONMENT: Staging

Quick Start Guide

1. Scaffold design-time factory: Create DesignTimeDbContextFactory.cs implementing IDesignTimeDbContextFactory<TContext> with environment-aware configuration to enable reliable dotnet ef commands.
2. Generate first migration: Run dotnet ef migrations add InitialCreate --project ./src/Infrastructure to scaffold schema artifacts and verify provider compatibility.
3. Validate idempotent execution: Execute dotnet ef migrations script --idempotent --output migrations.sql to generate a reusable, reviewable script that safely handles re-runs.
4. Integrate into pipeline: Add CI/CD steps that generate, review, and apply migrations before application deployment, ensuring schema state matches application version at runtime.
5. Verify health endpoint: Implement /health/migrations that queries __EFMigrationsHistory to confirm expected version before routing production traffic.