Execute custom SQL script as part of Entity Framework migration

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.


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.