EF Core - 刪除列解決方法(sqlite)

entity-framework-core sql sqlite3

記錄並知道EF核心遷移腳本不支持刪除列。所以我試圖手工完成。

我的模型類是:

class Master
{
    public int Id { get; set; }
    public string ToBeDeleted { get; set; }
}

class Detail
{
    public int Id { get; set; }

    public Master Master { get; set; }
}

我的背景:

class Context : DbContext
{
    public DbSet<Master> Masters { get; set; }
    public DbSet<Detail> Details { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Filename=local.db");
        base.OnConfiguring(optionsBuilder);
    }
}

我創建了一個遷移腳本,然後運行以下程序來創建db文件並添加幾行:

class Program
{
    static void Main(string[] args)
    {
        using (var context = new Context())
        {
            context.Database.Migrate();
            if(!context.Masters.Any())
            {
                var master = new Master {ToBeDeleted = "Some string"};
                context.Add(master);
                context.Add(new Detail {Master = master});
                context.SaveChanges();
            }
        }
    }
}

我刪除了Master類的ToBeDeleted屬性,並生成了第二個遷移腳本,該腳本生成非常簡單的代碼,但不能正常工作,因為它將來只會被支持:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropColumn(
        name: "ToBeDeleted",
        table: "Masters");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
   migrationBuilder.AddColumn<string>(
        name: "ToBeDeleted",
        table: "Masters",
        nullable: true);
}

所以現在是我寫自己的東西的時候了,這就是我嘗試過的:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("PRAGMA foreign_keys=OFF");
    migrationBuilder.CreateTable(
        name: "NEW_Masters",
        columns: table => new
        {
             Id = table.Column<int>(nullable: false)
                .Annotation("Sqlite:Autoincrement", true),
        },
         constraints: table =>
         {
             table.PrimaryKey("PK_Masters", x => x.Id);
         });
    migrationBuilder.Sql("INSERT INTO NEW_Masters SELECT Id FROM Masters;");
    migrationBuilder.DropTable("Masters");
    migrationBuilder.RenameTable("NEW_Masters", newName: "Masters");
    migrationBuilder.Sql("PRAGMA foreign_keys=OFF");
}

但是這會導致context.Database.Migrate()拋出異常:

Microsoft.EntityFrameworkCore.Relational.dll中發生未處理的“Microsoft.Data.Sqlite.SqliteException”類型異常

附加信息:SQLite錯誤19:'FOREIGN KEY約束失敗'。

最後,問題是:如何在遷移腳本中手動刪除列?

UPDATE

根據我的討論建議,我使用Script-Migration從遷移腳本生成sql並獲得:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
    "ProductVersion" TEXT NOT NULL
);

CREATE TABLE "Masters" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Masters" PRIMARY KEY AUTOINCREMENT,
    "ToBeDeleted" TEXT
);

CREATE TABLE "Details" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Details" PRIMARY KEY AUTOINCREMENT,
    "MasterId" INTEGER,
    CONSTRAINT "FK_Details_Masters_MasterId" FOREIGN KEY ("MasterId") REFERENCES "Masters" ("Id") ON DELETE RESTRICT
);

CREATE INDEX "IX_Details_MasterId" ON "Details" ("MasterId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20170127204056_Migration1', '1.1.0-rtm-22752');

INSERT INTO Masters (ToBeDeleted) VALUES ("ASDF"); --I've added this line manually for test only

INSERT INTO Details (MasterId) VALUES (1); --I've added this line manually for test only

PRAGMA foreign_keys="0";

CREATE TABLE "NEW_Masters" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Masters" PRIMARY KEY AUTOINCREMENT
);

INSERT INTO NEW_Masters SELECT Id FROM Masters;;

DROP TABLE "Masters";

ALTER TABLE "NEW_Masters" RENAME TO "Masters";

PRAGMA foreign_keys="1";

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20170127204851_Migration2', '1.1.0-rtm-22752');

腳本工作正常。例外是EF在某處執行的一些外鍵檢查。

一般承認的答案

EF核心開發人員指出PRAGMA foreign_keys=0在SQLite中的事務中不起作用,並且建議使用migrationBuilder.Sql方法來禁止使用事務。

所以我提出來:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "NEW_Masters",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Masters", x => x.Id);
            });
        migrationBuilder.Sql("INSERT INTO NEW_Masters SELECT Id FROM Masters;");
        migrationBuilder.Sql("PRAGMA foreign_keys=\"0\"", true);
        migrationBuilder.Sql("DROP TABLE Masters", true);
        migrationBuilder.Sql("ALTER TABLE NEW_Masters RENAME TO Masters", true);
        migrationBuilder.Sql("PRAGMA foreign_keys=\"1\"", true);
    }

它就是訣竅。


熱門答案

對於lazier,我所做的是手動刪除解決方案中的遷移,然後sqlite db添加了遷移。當那個討厭的錯誤出現而無法刪除時,請註釋掉up方法的主體,如下所示:在生成的腳本中。然後更新數據庫。當我向用戶添加參考對像類型SocialMediaLinks時,它生成了一個fk,對我來說很好。這對所有人來說都不是一個合適的解決方案,因為它可能會導致數據丟失.ALSO刪除導致fk依賴的類實體。

//generated migration:

 protected override void Up(MigrationBuilder migrationBuilder)
        {
             //comment out--
            //migrationBuilder.DropColumn(
            //    name: "SocialMediaLinks",
            //    table: "ProfileUserVM");

            //migrationBuilder.DropColumn(
            //    name: "SocialMediaLinks",
            //    table: "AspUsers");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "SocialMediaLinks",
                table: "ProfileUserVM",
                nullable: true);

            migrationBuilder.AddColumn<string>(
                name: "SocialMediaLinks",
                table: "AspUsers",
                nullable: true);
        }


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow