Entity Framework Core Code First 依據不同的 DB 類型產生 Migration 的作法

此作法是某些 SQL Server 不支援特定語法時的解套方法

譬如 SQLite 不支援 rename column 這件事


Migration 物件本身有一個 ActiveProvider 的屬性，該屬性是紀錄目前作用中的 database provider 名稱

M$ SQL Server 的名稱為 

"Microsoft.EntityFrameworkCore.SqlServer"

Sqlite 為

"Microsoft.EntityFrameworkCore.Sqlite"

在 SQL Server 上 rename column 只要下

migrationBuilder.RenameColumn("Old_Col_Name", "MyTable", "New_Col_Name");

就可以了

這邊要注意一下 Add-Migration 針對調整 Domain Model 屬性名稱的預設作法為

migrationBuilder.DropColumn(
    name: "Old_Col_Name",
    table: "MyTable");

migrationBuilder.AddColumn(
    name: "New_Col_Name",
    table: "MyTable",
    nullable: false,
    defaultValue: 0);

如果是在已經上線的系統這樣子 DropColumn 完之後資料就不見了

但 Sqlite 無法用 rename 的方式！

不過還好 Sqlite 還能 rename table

所以作法可以改為如下

1. rename MyTable to MyTable_old
2. create new table MyTable
3. select MyTable_Old into MyTable
4. drop MyTable_old

以下為完整的一個 Migration

    public partial class Rename_Column_A_2_B_At_MyTable : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            switch (ActiveProvider)
            {
                case "Microsoft.EntityFrameworkCore.Sqlite":
                    migrationBuilder.RenameTable("MyTable", newName: "MyTable_old");

                    migrationBuilder.CreateTable(
                    name: "MyTable",
                    columns: table => new
                    {
                       //some columns
                    },
                    constraints: table =>
                    {
                        table.PrimaryKey("PK_MyTable", x => x.PK_COL);
                    });

                    migrationBuilder.Sql(@"
                        INSERT INTO MyTable 
                            (/*some columns*/) 
                        SELECT /* some columns with old column name*/
                        FROM MyTable_old
                    ");

                    migrationBuilder.DropTable("MyTable_old");

                    break;
                case "Microsoft.EntityFrameworkCore.SqlServer":
                    migrationBuilder.RenameColumn("A", "MyTable", "B");
                    break;
                default:
                    System.Diagnostics.Debugger.Launch();
                    break;
            }
        }


        protected override void Down(MigrationBuilder migrationBuilder)
        {
            switch (ActiveProvider)
            {
                case "Microsoft.EntityFrameworkCore.Sqlite":
                    migrationBuilder.RenameTable("MyTable", newName: "MyTable_new");

                    migrationBuilder.CreateTable(
                    name: "MyTable",
                    columns: table => new
                    {
                        //some columns
                    },
                    constraints: table =>
                    {
                        table.PrimaryKey("PK_MyTable", x => x.PK_COL);
                    });

                    migrationBuilder.Sql(@"
                        INSERT INTO MyTable 
                            (/*some columns with old column name*/) 
                        SELECT /*some columns*/
                        FROM MyTable_new
                    ");

                    migrationBuilder.DropTable("MyTable_old");

                    break;
                case "Microsoft.EntityFrameworkCore.SqlServer":
                    migrationBuilder.RenameColumn("B", "MyTable", "A");
                    break;
                default:
                    System.Diagnostics.Debugger.Launch();
                    break;
            }

        }
    }

其中後面 default 那個 Launch 是可以在走到這行時提示要不要啟用工具進行偵錯，可以選擇當前專案的 vs 執行個體來替 migration 進行逐步偵錯

不然用 nuget package console 跑 update-database 咻咻的就過去然後噴掉了也不知道是中了哪種類型的 Provider