Skip to main content
SAPFIAI uses Entity Framework Core for database access and migrations. This guide covers the complete workflow for managing your database schema.

​
Overview

Migrations are stored in src/Infrastructure/Data/Migrations/ and track changes to your database schema over time. Each migration is a C# class that describes how to apply and revert schema changes.

​
Prerequisites

Ensure you have the EF Core tools installed: 
dotnet tool install --global dotnet-ef
Or update to the latest version: 
dotnet tool update --global dotnet-ef

​
Creating Migrations

After modifying entities in the Domain layer or configurations in Infrastructure, create a migration to capture the changes.

​
Basic Syntax

dotnet ef migrations add "MigrationName" \
  --project src/Infrastructure \
  --startup-project src/Web \
  --output-dir Data/Migrations

​
Parameters Explained

​
--project
required
The project containing your DbContext (Infrastructure)
​
--startup-project
required
The project with your connection string configuration (Web)
​
--output-dir
required
Where to generate migration files (Data/Migrations)

​
Migration Naming Conventions

Use descriptive names that indicate what changed: 
# Good examples
dotnet ef migrations add "AddUserEmailIndex" --project src/Infrastructure --startup-project src/Web --output-dir Data/Migrations
dotnet ef migrations add "UpdatePermissionModuleLength" --project src/Infrastructure --startup-project src/Web --output-dir Data/Migrations
dotnet ef migrations add "CreateAuditLogTable" --project src/Infrastructure --startup-project src/Web --output-dir Data/Migrations

# Avoid generic names
dotnet ef migrations add "Update1" --project src/Infrastructure --startup-project src/Web --output-dir Data/Migrations

​
Migration Files

Each migration creates two files in src/Infrastructure/Data/Migrations/:

​
Migration Class

20260215221946_InitialCreate.cs: 
using Microsoft.EntityFrameworkCore.Migrations;

namespace SAPFIAI.Infrastructure.Data.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Apply changes
            migrationBuilder.CreateTable(
                name: "Permissions",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(maxLength: 100, nullable: false),
                    Module = table.Column<string>(maxLength: 50, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Permissions", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // Revert changes
            migrationBuilder.DropTable(name: "Permissions");
        }
    }
}

​
Designer File

20260215221946_InitialCreate.Designer.cs contains metadata about the model state.
Never manually edit Designer files. Always use EF Core commands to modify migrations.

​
Applying Migrations

Apply pending migrations to update your database schema.

​
Apply All Pending Migrations

dotnet ef database update \
  --project src/Infrastructure \
  --startup-project src/Web
This applies all migrations that haven’t been applied yet.

​
Apply Specific Migration

Rollback or roll forward to a specific migration: 
# Rollback to specific migration
dotnet ef database update MigrationName \
  --project src/Infrastructure \
  --startup-project src/Web

# Rollback all migrations
dotnet ef database update 0 \
  --project src/Infrastructure \
  --startup-project src/Web

​
Development Environment

In development, migrations can be applied automatically on startup. The application checks the environment in Program.cs:56: 
if (app.Environment.IsDevelopment() && !skipDatabaseInitialization)
{
    app.UseDeveloperExceptionPage();
    await app.InitialiseDatabaseAsync();
}
Set the SKIP_DB_INIT environment variable to true to disable automatic migrations during development.

​
Generating SQL Scripts

For production deployments, generate SQL scripts instead of applying migrations directly.

​
Generate Complete Script

dotnet ef migrations script \
  --project src/Infrastructure \
  --startup-project src/Web \
  --output migration.sql
This creates a SQL script with all migrations.

​
Generate Incremental Script

Generate a script for migrations between two points: 
# From specific migration to latest
dotnet ef migrations script FromMigration \
  --project src/Infrastructure \
  --startup-project src/Web \
  --output incremental.sql

# Between two migrations
dotnet ef migrations script FromMigration ToMigration \
  --project src/Infrastructure \
  --startup-project src/Web \
  --output incremental.sql

​
Idempotent Scripts

Generate scripts that can be run multiple times safely: 
dotnet ef migrations script \
  --project src/Infrastructure \
  --startup-project src/Web \
  --output migration.sql \
  --idempotent
Idempotent scripts include checks to skip migrations that have already been applied.

​
Removing Migrations

Remove the last migration if it hasn’t been applied to any database yet. 
dotnet ef migrations remove \
  --project src/Infrastructure \
  --startup-project src/Web
Never remove a migration that has been:
  • Applied to any database (dev, staging, or production)
  • Committed to source control and shared with the team
  • Deployed to any environment
Instead, create a new migration to revert the changes.

​
Migration Workflow

Follow this workflow when making database changes:
1

Modify Domain Entities

Update entity classes in src/Domain/Entities/ or add new ones.
public class Permission
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    // Add new property
    public string Category { get; set; } = "General";
}
2

Update Entity Configuration (Optional)

If needed, add or modify entity configurations in src/Infrastructure/Data/Configurations/.
public class PermissionConfiguration : IEntityTypeConfiguration<Permission>
{
    public void Configure(EntityTypeBuilder<Permission> builder)
    {
        builder.Property(p => p.Category)
            .HasMaxLength(50)
            .IsRequired();
    }
}
3

Create Migration

dotnet ef migrations add "AddPermissionCategory" \
  --project src/Infrastructure \
  --startup-project src/Web \
  --output-dir Data/Migrations
4

Review Generated Migration

Open the migration file and verify the changes are correct. Make manual adjustments if needed.
5

Test Migration

# Apply migration
dotnet ef database update --project src/Infrastructure --startup-project src/Web

# Test your application
dotnet run --project src/Web

# If issues found, rollback and fix
dotnet ef database update PreviousMigration --project src/Infrastructure --startup-project src/Web
6

Commit Changes

git add src/Domain/ src/Infrastructure/
git commit -m "Add Category field to Permission entity"

​
Configuration

​
Connection Strings

Connection strings are configured in appsettings.json: 
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=SAPFIAIDb;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}
For production, override in appsettings.Production.json or use environment variables: 
export ConnectionStrings__DefaultConnection="Server=prod-server;Database=SAPFIAIDb;User Id=app_user;Password=***"

​
Multiple Databases

If using different databases per environment: 
# Development (SQL Server LocalDB)
dotnet ef database update --project src/Infrastructure --startup-project src/Web

# Production (specify connection at runtime)
dotnet ef database update \
  --project src/Infrastructure \
  --startup-project src/Web \
  --connection "Server=prod;Database=SAPFIAIDb;User Id=sa;Password=***"

​
Common Scenarios

  1. Create entity in src/Domain/Entities/NewEntity.cs
  2. Add DbSet<NewEntity> to ApplicationDbContext
  3. Create migration: dotnet ef migrations add "CreateNewEntityTable" --project src/Infrastructure --startup-project src/Web --output-dir Data/Migrations
  4. Apply: dotnet ef database update --project src/Infrastructure --startup-project src/Web
  1. Add property to entity class
  2. Create migration: dotnet ef migrations add "AddColumnToEntity" --project src/Infrastructure --startup-project src/Web --output-dir Data/Migrations
  3. Apply: dotnet ef database update --project src/Infrastructure --startup-project src/Web
  1. Rename property in entity
  2. Create migration: dotnet ef migrations add "RenameColumn" --project src/Infrastructure --startup-project src/Web --output-dir Data/Migrations
  3. Edit migration to use RenameColumn instead of DropColumn + AddColumn:
migrationBuilder.RenameColumn(
    name: "OldName",
    table: "TableName",
    newName: "NewName");
  1. Apply: dotnet ef database update --project src/Infrastructure --startup-project src/Web
Migrations can include data updates:
protected override void Up(MigrationBuilder migrationBuilder)
{
    // Schema changes
    migrationBuilder.AddColumn<string>(
        name: "Category",
        table: "Permissions",
        nullable: false,
        defaultValue: "General");
    
    // Data migration
    migrationBuilder.Sql(
        "UPDATE Permissions SET Category = 'Security' WHERE Module = 'Auth'");
}

​
Troubleshooting

​
”No migrations configuration type was found”

Ensure you’re specifying both --project and --startup-project: 
dotnet ef migrations add "Migration" \
  --project src/Infrastructure \
  --startup-project src/Web

​
“A connection was not found”

Verify your connection string in appsettings.json and ensure SQL Server is running: 
# Check connection string
cat src/Web/appsettings.json | grep ConnectionStrings

# Test connection (if using LocalDB)
sqllocaldb info

​
“The migration has already been applied”

Check applied migrations: 
dotnet ef migrations list --project src/Infrastructure --startup-project src/Web

​
Pending Model Changes

Check if there are pending model changes: 
dotnet ef migrations has-pending-model-changes \
  --project src/Infrastructure \
  --startup-project src/Web

​
Production Deployment

For production deployments:
1

Generate SQL Script

dotnet ef migrations script \
  --project src/Infrastructure \
  --startup-project src/Web \
  --output migration_script.sql \
  --idempotent
2

Review Script

Manually review the generated SQL for any potential issues or data loss.
3

Test on Staging

Apply the script to a staging environment first:
sqlcmd -S staging-server -d SAPFIAIDb -i migration_script.sql
4

Backup Production

Always backup the production database before applying migrations.
5

Apply to Production

sqlcmd -S prod-server -d SAPFIAIDb -i migration_script.sql
Never run dotnet ef database update directly against production. Always use SQL scripts for production deployments.

​
Next Steps

Creating Use Cases

Learn how to implement CQRS commands and queries

Testing

Test your database interactions

Build docs developers (and LLMs) love

Get started for freeTalk to us