Customize Code-First EF Core Migrations

Depending on the use case and your current database implementation, it is sometimes necessary to customize your automatic generated migration code to handle database triggers or constraints or any other special case, which alters your schema or database structure. Usually, those generated migrations run into an error, which breaks your app startup or even worse, damages your database.

Alter Column Example

In this example, we need to alter an existing column to be computed by the SQL server and replace the original column, which was a default nullable float column. The auto-generated migration would look like the following statement. Imagine we have some constraints on this column or any view, which uses this column, the migration will fail.

migrationBuilder.AlterColumn<double>(
                name: "TotalWeight",
                table: "DemoTable",
                type: "float",
                nullable: true,
                computedColumnSql: "isNull([Weight1], 0) + isNull([Weight2], 0)",
                stored: true,
                oldClrType: typeof(double),
                oldType: "float",
                oldNullable: true);

To resolve this issue, you have to add custom statements to the migrationBuilder pipeline. In our case, we need to drop the view before the migration is executed and then rebuild it. This applies to any other constraints or if you want to skip an SQL Trigger.

The final solution will look like the following code snippet.

migrationBuilder.Sql("DROP VIEW [dbo].[DemoView]"); // Before Migration

migrationBuilder.AlterColumn<double>(
                name: "TotalWeight",
                table: "DemoTable",
                type: "float",
                nullable: true,
                computedColumnSql: "isNull([Weight1], 0) + isNull([Weight2], 0)",
                stored: true,
                oldClrType: typeof(double),
                oldType: "float",
                oldNullable: true);
                
 migrationBuilder.Sql("CREATE VIEW [dbo].[DemoView] ...view create script...") // After Migration

Conclusion

EF Core provides a powerful tool to automatically generate migrations and execute them on startup, however, at times and depending on the requirement, it is necessary to amend the generated code to fit in your environment and to be successful.

Pro Tip: Make sure you have a development database to test your migrations before applying them to a staging or production environment. This will make your life much easier and avoid any stressful deployments.