Drop Column from SQLite Database
SQLite is relatively limited in its schema manipulation capabilities as compared to other databases. For example, dropping a column from an existing table requires that the entire table be dropped and re-created.
In EF Core 5.0, migrations now support the automatic rebuilding of the table for schema changes that require it.
Let's suppose we have a simple model that contains a
Book entity as shown below.public class Book
{
public int Id { get; set; }
public string Title { get; set; }
public string Category { get; set; }
}
public class EntityContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder opBuilder)
{
opBuilder.UseSqlite("Data Source=D:\\Blogging.db");
}
public DbSet<Book> Books { get; set; }
}
To create the database, let's run the following migration command in Package Manager Console.
PM> Add-Migration Init
You will see that migration creates the following script.
public partial class Init : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Books",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("Sqlite:Autoincrement", true),
Title = table.Column<string>(nullable: true),
Category = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Books", x => x.Id);
});
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Books");
To apply the above script to the database, run the following command to update the database.
PM> Update-Database
You will see that the database is created that contains a
Books table.Now we want to remove the
Category property so let's remove that property from the Book class as shown below.public class Book
{
public int Id { get; set; }
public string Title { get; set; }
}
Add a new migration using the following migration command.
PM> Add-Migration RemoveCategory
It will create the following script to update the database.
public partial class RemoveCategory : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(
name: "Category",
table: "Books");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.AddColumn<string>(
name: "Category",
table: "Books",
type: "TEXT",
nullable: true);
}
}
To update the database with the above script, run the following.
PM> Update-Database
Before EF Core 5.0, this update will fail, because the column cannot be dropped.
In EF Core 5.0, you will see that migrations will instead rebuild the table successfully.
- A temporary table is created with the desired schema for the new table.
- Data is copied from the current table into the temporary table.
- Foreign key enforcement is switched off.
- The current table is dropped
- The temporary table is renamed to be the new table.
Last modified 1yr ago