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.