It is documented and known that EF core migration scripts don't support dropping a column. So I'm trying to do it by hand.
My model classes are:
class Master
{
public int Id { get; set; }
public string ToBeDeleted { get; set; }
}
class Detail
{
public int Id { get; set; }
public Master Master { get; set; }
}
My context:
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);
}
}
I create a migration script then run the following program to create the db file and add a couple of rows:
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();
}
}
}
}
I delete ToBeDeleted
property of Master
class and generated a second migration script, which generates the very simple code that doesn't work because it'll only be supported in the future:
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);
}
So it's time for me to write my own stuff, and this is what I've tried:
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");
}
However this causes the context.Database.Migrate()
to throw an exception:
An unhandled exception of type 'Microsoft.Data.Sqlite.SqliteException' occurred in Microsoft.EntityFrameworkCore.Relational.dll
Additional information: SQLite Error 19: 'FOREIGN KEY constraint failed'.
Finally, the question: how do I drop a column by hand in the migration script?
UPDATE
Following advice I got on the discussion, I used Script-Migration
to generate the sql from the migration scripts and got this:
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');
And the script works fine. The exception is some foreign key check performed by EF somewhere.
The EF core developers pointed out that PRAGMA foreign_keys=0
doesn't work within a transaction in SQLite, and it was suggested to use the migrationBuilder.Sql
method to suppress the use of the transaction.
So I came up with:
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);
}
And it does the trick.
For the lazier what I did was manually delete the migrations in the solution and the sqlite db then added a migration. When that pesky error comes up that it cant drop, comment out the the body of the up method like so:in the generated script.Then update the db. worked fine for me when It generated a fk when I added the reference object type SocialMediaLinks to my users. This is not a suitable solution for all as it may cause data loss.ALSO DELETE THE CLASS ENTITY that was causing the fk dependencey.
//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);
}