// MigrationsProject/DesignTimeDbContextFactory.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    public AppDbContext CreateDbContext(string[] args)
    {
        var configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

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

        return new AppDbContext(optionsBuilder.Options);
    }
}

# GitHub Actions Example
name: Generate Migration Scripts
on:
  push:
    branches: [ main ]

jobs:
  build-and-script:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - name: Se

tup .NET uses: actions/setup-dotnet@v4 with: dotnet-version: '8.0.x'

  - name: Restore dependencies
    run: dotnet restore

  - name: Generate SQL Script
    run: |
      dotnet ef migrations script --idempotent --output ./scripts/migrations.sql \
        --project ./src/MigrationsProject/MigrationsProject.csproj \
        --startup-project ./src/ApiProject/ApiProject.csproj
      
  - name: Upload Script Artifact
    uses: actions/upload-artifact@v4
    with:
      name: db-scripts
      path: ./scripts/migrations.sql

The `--idempotent` flag is critical. It generates scripts that check for the existence of objects before creating them, allowing safe re-runs in case of pipeline failures.

### 3. Expand/Contract Implementation

For breaking changes (e.g., renaming a column, changing a type), use the expand/contract pattern across multiple releases.

**Phase 1: Expand**
Add the new structure without removing the old. The application writes to both.

```csharp
// Migration 20240520_AddNewEmailColumn.cs
public partial class AddNewEmailColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "NewEmail",
            table: "Users",
            type: "nvarchar(256)",
            nullable: true);

        // Data migration: Backfill new column
        migrationBuilder.Sql("UPDATE Users SET NewEmail = Email WHERE NewEmail IS NULL;");
    }
}

Phase 2: Contract Once the application reads from the new column and the old column is deprecated, remove the legacy structure.

// Migration 20240615_RemoveOldEmailColumn.cs
public partial class RemoveOldEmailColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropColumn(
        name: "Email",
        table: "Users");
    
    migrationBuilder.RenameColumn(
        name: "NewEmail",
        table: "Users",
        newName: "Email");
}

This approach ensures zero downtime. The database remains consistent, and the application can roll back to the previous version without schema errors during the transition.

4. Handling Complex SQL

EF Core's fluent API has limitations. Use migrationBuilder.Sql() for operations requiring raw SQL, such as triggers, computed columns, or complex constraints.

migrationBuilder.Sql(@"
    CREATE TRIGGER trg_UpdateTimestamp 
    ON Users 
    AFTER UPDATE 
    AS 
    BEGIN
        UPDATE Users SET UpdatedAt = GETUTCDATE() 
        WHERE Id IN (SELECT Id FROM inserted);
    END;
");

Always wrap raw SQL in conditional checks if idempotency is required, or manage script execution order carefully.

Pitfall Guide

1. Runtime Migration in Production: Calling context.Database.Migrate() in Program.cs for production environments. This couples schema changes to application startup, causes connection pool exhaustion under load, and prevents rollback without redeploying the application.
2. Manual Migration Editing: Modifying generated migration code without understanding the underlying SQL. This often leads to mismatches between the model snapshot and the database state, causing dotnet ef migrations add to generate empty or duplicate migrations.
3. Ignoring Data Migrations: Focusing solely on schema changes and neglecting data transformations. Adding a NOT NULL column without a default value or data backfill will break existing rows. Always include data migration steps in the Up method.
4. Long-Running Transactions: Generating migrations that lock large tables for extended periods. In SQL Server, this blocks all concurrent access. Use batch updates for large data migrations and consider ONLINE=ON options for index rebuilds where supported.
5. Missing Idempotency: Deploying scripts that fail on re-run. In automated pipelines, failures may trigger retries. Scripts must handle cases where objects already exist to avoid DROP errors or duplicate creation failures.
6. Migration Collisions: Multiple developers adding migrations simultaneously without rebasing. This results in conflicting migration IDs or snapshot mismatches. Enforce a policy where migrations are added on short-lived branches and merged sequentially.
7. Seed Data Management: Using HasData() for large seed datasets or environment-specific configuration. This bloats the migration assembly and can cause primary key conflicts. Use separate seed scripts or application logic for non-static data.

Production Bundle

Action Checklist

• Isolate Migrations: Move migrations to a dedicated class library project.
• Implement Factory: Add IDesignTimeDbContextFactory to support tooling in CI.
• CI Script Generation: Configure pipeline to generate idempotent SQL scripts as artifacts.
• Expand/Contract Strategy: Plan breaking changes across multiple releases using additive phases.
• Idempotent Scripts: Ensure all generated scripts use IF NOT EXISTS or equivalent checks.
• Backup Verification: Validate that backup and restore procedures are tested alongside migration scripts.
• Review Raw SQL: Audit migrationBuilder.Sql() calls for performance and security implications.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Startup / MVP	Runtime Apply (MigrateAsync)	Speed of development; low operational overhead.	Low infrastructure cost; high risk as scale increases.
Mid-Size App / Internal Tools	Pre-generated SQL Scripts	Balance of safety and simplicity; DBA review possible.	Moderate CI/CD setup; reduces incident response costs.
Enterprise / High Availability	Expand/Contract + CI Scripts	Zero downtime; deterministic rollback; audit compliance.	Higher development velocity cost; significant risk mitigation.
Regulated Industry	Scripted + Manual Approval	Compliance requirements; audit trails; change control.	High process overhead; essential for certification.

Configuration Template

MigrationsProject.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <OutputType>Library</OutputType>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.*" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.*" PrivateAssets="all" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="8.0.*" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="..\Domain\Domain.csproj" />
  </ItemGroup>
  <ItemGroup>
    <None Update="appsettings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>
</Project>

appsettings.json (for DesignTime)

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=AppDb;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

Quick Start Guide

1. Install Tools:

   dotnet tool install --global dotnet-ef
   dotnet tool update --global dotnet-ef
   

2. Create Context and Factory: Define AppDbContext in your domain project. Create DesignTimeDbContextFactory in the migrations project as shown in the Core Solution.

3. Generate Initial Migration:

   dotnet ef migrations add InitialCreate \
     --project ./MigrationsProject \
     --startup-project ./ApiProject \
     --output-dir Migrations
   

4. Apply to Local Database:

   dotnet ef database update \
     --project ./MigrationsProject \
     --startup-project ./ApiProject
   

5. Generate SQL Script:

   dotnet ef migrations script --idempotent --output migration.sql
   

This workflow establishes a foundation for production-ready migrations. Integrate the script generation step into your CI pipeline and adopt the expand/contract pattern for all schema changes affecting live data.