sqlentity-framework-coredatabase-migrationentity-framework-migrations

Execute custom SQL script as part of Entity Framework migration


I have been using the standard add-migration approach to updating my aspnet core database on Entity Framework. I now have to move two "image" columns into a new table (and their image data), remove the columns from the original table, and set up a foreign key relationship between the old and new tables. I have a working SQL script to do all this.

How can I execute this sql script as part of a normal EF migration, AND make sure subsequent add-migration changes will reflect the changes that my sql script will do (adding new table/columns, removing image columns from original table)?

I've seen a few references to SqlFile and deriving from DbMigration, but nothing that fits my scenario nicely. I am using EF Core, aspnet core 2.0.


Solution

  • Moving a populated, non-nullable column

    Get Entity Framework to create the base migration and then enhance the output.

    Some example code that moves an EmailAddress field from OldTable to NewTable (MS SQL Server):

    migrationBuilder.AddColumn<string>(
        name: "EmailAddress",
        table: "NewTable",
        defaultValue: "");
    
    migrationBuilder.Sql("UPDATE NewTable SET NewTable.EmailAddress = OldTable.EmailAddress FROM NewTable JOIN OldTable ON NewTable.Id = OldTable.NewTableId");
    
    migrationBuilder.AlterColumn<string>(
        name: "EmailAddress",
        table: "NewTable",
        nullable: false,
        defaultValue: "");
    
    migrationBuilder.DropColumn(
        name: "EmailAddress",
        table: "OldTable");
    

    Remember, this needs to happen for Up() and Down(), except Down() undoes the operation.