Adding new column using Update-Database in Entity Framework Core

Question

May be i'm missing something very obvious. But i haven't been able to figure out a way to add a new column to an existing table/model in EF Core.

This is the documentation that I'm following: https://learn.microsoft.com/en-us/ef/core/miscellaneous/cli/powershell

And this is what i have done so far:

1. Created migration using this command: "Add-Migration -Name CodingSoldierDbContextMigration -OutputDir Migrations -Context CodingSoldierDbContext"
2. Updated database using the command: "Update-Database -Migration CodingSoldierDbContextMigration -Context CodingSoldierDbContext". Tables got created in the Database.
3. Now i need to add a new column to an existing table. I add that column to the model in the .cs file. And i remove the existing migration: "Remove-Migration -Force -Context CodingSoldierDbContext"
4. Now i re-run the commands in steps 1 and 2. Add-Migration works and migration gets created. But Update-Database fails with the error: "There is already an object named 'AspNetRoles' in the database." which means the table is already present in the database which makes sense.

So how do i update an already existing table table ? 2 ways i can think of are:

1. Drop the database. Create migration and update database. But all data will be gone.
2. Add column in the model. Manually update the Table using a SQL script to add the new column.

But i feel there should be a better way to do this.

Answer 1

This is how i resolved the issue. Not sure if it is the perfect way.

Key is NOT to delete the initial migration, before you create the new migration.

Adding the steps here:

1. Creating initial migration: "Add-Migration -Name CodingSoldierDbContextMigration -OutputDir Migrations -Context CodingSoldierDbContext"
2. Updated database using the command: "Update-Database -Migration CodingSoldierDbContextMigration -Context CodingSoldierDbContext". Tables gets created in the Database.
3. Added new field in one of the models.
4. Creating updated migration: "Add-Migration -Name CodingSoldierDbContextMigrationUpdated -OutputDir Migrations -Context CodingSoldierDbContext". This migration will have code only for updating the existing table.
5. Updating DB with the updated migration: "Update-Database -Migration CodingSoldierDbContextMigrationUpdated". Ideally this should have resolved it. But for me, it gave error because(as from Verbose logs) it was trying to update with initial migration: "CodingSoldierDbContextMigration", i don't know why.
6. So i generate scripts using Script-Migration: "Script-Migration -From CodingSoldierDbContextMigration -Idempotent -Output C:\MigrationScript.sql -Context CodingSoldierDbContext". It generated the script which had changes only from the updated migration. Running that script in DB created the column and added the migration entry in "__EFMigrationsHistory" table.

Answer 2

This is a way which helped me to add a new column to the existed database without losing data:

1. I've written this command into Package Manager Console to the Project which contains my Model

   add-migration MyFirstMigration
   

2. The above command created a class with a lot of code:

   public partial class MyFirstMigration : Migration
   {
       protected override void Up(MigrationBuilder migrationBuilder)
       {
             ...
             // The code is omitted for the brevity
             ...
        }
   }
   

3. I've deleted all generated code and added the following code snippet into the above method Up(MigrationBuilder migrationBuilder):

   protected override void Up(MigrationBuilder migrationBuilder)
   {
       migrationBuilder.AddColumn<string>("Password", "Users", "varchar(255)", 
           unicode:false, maxLength: 255, nullable: true);
   }
   

4. Then it is necessary to write the following command into Package Manager Console to add a column into your database:

   Update-Database
   

Answer 3

I kinda did a mixture of what @Boney and @StepUp suggested and it ran without hitches. Here's what I did. Assume a have a table Period that I decided to add an additional column PeriodTypeId.

1. Add another migration. e.g.

Add-Migration -Name OVCDbMigrationsUpdated -Context DbContext

This created a new migration file with name: OVCDbMigrationsUpdated.

2. Run Update-Database targeting the new migration file. e.g.

Update-Database -verbose -Migration OVCDbMigrationsUpdated -Context DbContext

My DB was updated successfully with the new column.

Answer 4

I was running into a similar issue with EF Core and an existing database with some of the model fleshed out, but I was adding a new model (that already had a table) and was getting the error

There is already an object named 'tableN' in the database.

The way I got this to work on an existing database was:

1. Download Repo
2. Run all migrations up to date (if you have a database with migrations) If you don't, then just create an initial "dummy" migration and apply that
3. Create a baseline migration prior to making any changes in your model/code
4. Update your code with any changes to your database
5. Create another migration
6. Delete the initial baseline migration
7. update-database