Execute custom SQL script as part of Entity Framework migration

Question

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.

Answer 1

You may edit created migration class (Up and Down methods) and include any SQL you want in correct place:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropColumn(
        name: "MyExtraColumn",
        table: "MySuperTable");

    migrationBuilder.Sql("DROP DATABASE [master]"); // <<< Anything you want :)

    migrationBuilder.DropColumn(
        name: "MyExtraColumn2",
        table: "MySuperTable");
}

It's your responsibility do not "break" the database schema (in EntityFramework point of view) and to provide reliable Down script to be able to migrate up/down/up/down multiple times.

Also, don't forget to re-add SQLs again if you will remove migration and re-add it.

Answer 2

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.