I use EntityFrameworkCore.Sqlite
and I want use Migration everything works fine but when I want remove column, I am unable to update database. I know Sqlite
has limitation.
this is my way for deal with this issue:
1.Create new Table
2.Insert oldTable data to new table
3.Drop oldTable
4.Rename new table to oldtable
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(name: "XSettings",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("Sqlite:Autoincrement", true),
Name = table.Column<string>(nullable: true),
LName = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Settings", x => x.Id);
});
migrationBuilder.Sql("ALTER TABLE Settings DROP COLUMN HName;");
migrationBuilder.Sql("INSERT INTO XSettings * FROM Settings;");
migrationBuilder.DropTable(name: "Settings");
migrationBuilder.Sql("ALTER TABLE XSettings RENAME TO Settings;");
}
but when i run my app HName column still exist, so how can i delete a column?
To execute a drop column operation you must provide the row SQL code since the SQLite Limitations. Microsoft Docs: SQLite Limitations
Download the SQLiteStudio from the SQLiteStudio site
Load the local database file
migrationBuilder.Sql("...");
statement or you can split it in multiple statementsJust note while copying into C#