Master EF Core migrations

Database migrations are essential in modern software development, enabling teams to manage and apply changes to database schemas efficiently. With Entity Framework (EF) Core Migrations, developers can streamline database versioning and schema modifications across environments, ensuring consistency and reducing errors. In this blog we will learn about every critical aspect of using EF Core Migrations, from creating migrations to handling advanced customizations.

Creating Migrations in EF Core

Creating a migration is the first step in applying schema changes to your database. EF Core provides two main methods for generating migrations: through the Command Line Interface (CLI) and PowerShell.

Using the CLI:

The .NET CLI allows you to create migrations with a command:

dotnet ef migrations add <MigrationName>

Replace <MigrationName> with a descriptive name, like AddCustomerTable, indicating the purpose of the migration. This command will generate three files:

• MigrationName.cs: The main migration file with Up and Down methods for applying and reverting changes.
• MigrationName.Designer.cs: A metadata file used by EF.
• MyContextModelSnapshot.cs: A snapshot of your current model, which helps EF determine what changes have been made since the last migration.

Using PowerShell:

In PowerShell, use the command:

Add-Migration <MigrationName>

Applying Migrations to a Database

After creating migrations, applying them to the database is the next step. EF Core offers several methods for this, each suitable for different environments.

• Local Development:
  Use the following command to apply all pending migrations in a local development environment:

dotnet ef database update

• Production Environments:
  In production, direct application of migrations can be risky. It’s recommended to generate a SQL script (covered in the next section) to review and execute manually, ensuring a safe application of migrations.

Consistency across environments (development, staging, production) is key, so always verify migrations in a staging environment before deploying them to production.

Managing Migrations with SQL Scripts

For production environments, SQL scripts provide a safe and reliable way to apply migrations. With EF Core, you can generate these scripts using the Script-Migration command, which outputs SQL statements based on your migration files.

• Basic Command:
  Run the command to generate a script from the current database schema to the latest migration:

dotnet ef migrations script -o filename

• Customizing SQL Scripts:
  You can generate SQL scripts for specific migrations by specifying a start and end migration. This is useful for creating scripts that update only certain parts of your schema:

dotnet ef migrations script <FromMigration> <ToMigration>

• Idempotent Scripts:
  To ensure that migrations only apply if they haven’t been executed yet, use the –idempotent flag. This is especially useful in distributed environments where databases may be at different migration levels.

dotnet ef migrations script --idempotent

Using SQL scripts helps avoid errors by allowing a DBA to review and modify scripts before applying them to production.

Advanced Migration Operations

EF Core Migrations offers flexibility for handling more complex schema changes and custom operations that may not be supported by the EF Core API alone.

• Renaming Columns and Tables:
  For column or table renaming, EF Core generates a Drop and Add sequence by default, which can result in data loss. To avoid this, modify the migration file to use RenameColumn or RenameTable:

migrationBuilder.RenameColumn(

name: "OldColumnName",

table: "TableName",

newName: "NewColumnName"

);

• Using Raw SQL:
  For custom SQL commands or database-specific configurations, MigrationBuilder.Sql() allows raw SQL to be included in migrations. This is helpful for adding stored procedures, triggers, or performing data transformations.

migrationBuilder.Sql("CREATE INDEX IX_ColumnName ON TableName(ColumnName)");

Advanced operations in migrations give you greater control, but it’s crucial to review and test them to prevent unexpected behavior.

Bundling Migrations for CI/CD

In a CI/CD pipeline, migration bundles can simplify deployment and ensure smooth migration application without requiring full EF Core tools on production servers.

• Creating a Migration Bundle:
  Use the following command to create a migration bundle:

dotnet ef migrations bundle

This command generates an executable file containing all necessary migrations, which can be applied to the database without requiring .NET SDK or source code on the production server.

• Self-Contained Bundles:
  For environments that don’t have .NET installed, use the –self-contained flag to create a self-contained migration bundle executable:

dotnet ef migrations bundle --self-contained -r linux-x64

Bundling migrations enhances deployment reliability and simplifies management by encapsulating all migration logic in a standalone file, which can be integrated directly into deployment workflows.

Removing and Listing Migrations

Managing migrations in a project involves not only creating and applying them but also sometimes removing or listing migrations to keep the database schema clean.

• Removing a Migration:
  If you need to delete the last migration due to an error or change in requirements, use:

dotnet ef migrations remove

This command will remove the last migration that hasn’t yet been applied to the database. However, avoid using it for migrations that have already been applied in production, as it can disrupt the migration history.

• Listing Migrations:
  To view all migrations in your project, use:

dotnet ef migrations list

This list provides a quick overview of the migrations applied and helps track changes in your schema over time. It’s especially useful in larger projects where tracking schema evolution is critical.

Applying Migrations Programmatically

In some scenarios, particularly in development or testing environments, you may want to apply migrations directly within your codebase.

• Using context.Database.Migrate():
  EF Core allows you to apply migrations programmatically using the Migrate method. This approach is beneficial when automating schema updates during application startup, especially in integration tests or local development setups.

  var app = WebApplication.CreateBuilder().Build();
    using (var scope = app.Services.CreateScope())
    {
        var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
        dbContext.Database.Migrate();
    }
    app.Run();

• Production Caution:
  Avoid programmatically applying migrations in production as it can cause issues with concurrent access and elevated permissions. Manual review and execution of SQL scripts are generally safer for production deployments.

Applying migrations programmatically is convenient for local development but should be used carefully to avoid unintended changes in live environments.

Handling Migrations in Complex Scenarios

In complex projects, migrations can become difficult to manage. Resetting or squashing migrations can simplify schema management while maintaining data integrity.

• Resetting Migrations:
  If you need to reset all migrations, delete the Migrations folder and use dotnet ef migrations add InitialMigration to create a new initial migration. This will reset your migration history.
  • Warning: This approach is only safe if all previous migrations have been applied across all environments, as it effectively “resets” the migration history.
• Squashing Migrations:
  Squashing involves consolidating multiple migrations into a single, clean migration. To do this, delete the old migration files, apply all migrations to a local database, then generate a new migration based on the final schema. This reduces complexity, especially in projects with many small changes.

Best Practices for EF Core Migrations

Following best practices helps avoid common pitfalls and makes EF Core Migrations more manageable over the long term.

• Use Descriptive Names:
  Name migrations based on the change being made, like AddCustomerAddress or UpdateProductSchema. This naming convention makes it easier to trace changes and maintain a clear migration history.
• Keep Migrations Small and Focused:
  Avoid bundling multiple unrelated changes into a single migration. Small, focused migrations are easier to troubleshoot and maintain, reducing the risk of conflicts and errors during deployment.
• Test Migrations Before Production:
  Testing migrations in a staging environment that mirrors production helps identify any issues before they impact live data. Applying migrations in local development is not always a sufficient test for production environments.

Implementing these practices improves the reliability of database migrations and reduces the likelihood of errors or data loss in live systems.

Common Pitfalls and Troubleshooting

While EF Core Migrations are powerful, certain common pitfalls can lead to issues if not handled carefully.

• Avoiding Data Loss:
  Some operations, like dropping columns, result in data loss. Review autogenerated migrations to avoid unintentional deletions, and make sure to back up data before applying destructive changes.
• Managing Conflicts in Source Control:
  Migration conflicts can occur when multiple developers create migrations simultaneously. To avoid conflicts, ensure team members pull the latest changes before adding new migrations.
• Debugging Migration Errors:
  Errors during migration are often due to schema inconsistencies or misconfigured commands. Check the migration history table (__EFMigrationsHistory) and validate schema changes against the model snapshot to identify inconsistencies.

Tools & Extensions

You can checkout following link for tools and extension available for EF Core that provides addition functionality and features.

Top EF core tools and extensions

Conclusion

Entity Framework Core Migrations simplify database schema management by allowing developers to define schema changes directly in code, supporting continuous integration, and reducing the risk of errors in deployment. By following best practices and leveraging advanced operations, you can make migrations an efficient and reliable part of your development workflow. Whether for local development or production deployment, EF Core Migrations provide the tools necessary for maintaining database consistency across all environments.