[SOLVED] Execute custom SQL script as part of Entity Framework migration

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.