Execute custom SQL script as part of Entity Framework migration

database-migration ef-migrations entity-framework-core sql

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.

1
3
10/9/2017 3:23:03 AM

Accepted Answer

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.

12
10/9/2017 2:24:49 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow