Change the IDENTITY property of a column, the column needs to be dropped and recreated

Question

I am using EF Core 2.1

This was my initial model definition.

public class Customer //Parent
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Email { get; set; }

    public BankAccount BankAccount { get; set; }

}


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

    public string Branch { get; set; }

    public string AcntNumber { get; set; }

    public DateTime CreatedDate { get; set; }

    public int CustomerId { get; set; }

    public Customer Customer { get; set; }

}

But I realized having Id & CustomerId both is overhead as its One-to-One relation, I can update my BankAccount model definition as below.

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

    public string Branch { get; set; }

    public string AcntNumber { get; set; }

    public DateTime CreatedDate { get; set; }

    public Customer Customer { get; set; }

}

While in DbContext class defined the principal entity as below.

HasOne(b => b.Customer).WithOne(c => c.BankAccount).HasForeignKey<BankAccount>(f => f.Id);

While running the update-database I am getting the below error.

System.InvalidOperationException: To change the IDENTITY property of a column, the column needs to be dropped and recreated.

However, ideally I should not but just get rid of this error, I deleted the column, constraints and as well table and then the complete database as well. But still the same error.

Answer 1

I ran into the same problem, and I solved it by two steps and two migrations:

Step 1

1. Drop the identity column.
2. Comment the ID in BankAccount and add a new one (i.e., BankAccountId as
   identity, add migration and update - this drops id).
3. Add a new column as identity.

Step 2

1. Drop the newly added column and re-add the previous one. Comment BankAccountId and un-comment ID.
2. Add migration and update (this drops the BankAccountId and adds Id as identity).

Answer 2

I had this problem when I tried to change a model from public byte Id {get; set;} to public int Id {get; set;}. To face the issue, I did the following things:

1. Remove all the migrations until the creation of the target model with Remove-Migration -Project <target_project> in the Package Manager Console
2. Delete the actual database
3. If you have some migrations in the middle that you have not created, (for example they came from another branch), copy the migrations files and also the ModelSnapshot file and paste them in your branch (overwrite them carefully!).
4. create a new migration with add-migration <migration_name> in the Package Manager Console
5. update the database with update-database in the Package Manager Console

I can solve it in this way because my code was not in a production environment. Maybe you have to face another complex issues if the model is already in there.

Answer 3

The question has a 2 part answer:

TL;DR.: Let the EF do it for you.

First: Let the EF do the relations

In short, the identity property is what the DB uses to manage a dedicated ID column, so it does not depend on anything outside itself to identify each row, therefore the bankAccount class needs it's own Id field to have the identity property; now, if you try to tell the EF manually how to do the relations like you do with the line

    HasOne(b => b.Customer).WithOne(c => c.BankAccount).HasForeignKey<BankAccount>(f => f.Id);

what you do is to override the inside logic of the EF itself and in this case you are telling the EF that the bank account Id is the field that references the costumer, which is not the case, and so EF tries to drop the IDENTITY from the id field in the bank account model, but that is only because you told it that the bank accound Id should be the same as the Customer ID. I think I understand what you are trying to say with the one to one relation, but what happens when the customer tries to open another bank account? D: Instead, you should keep the int CustomerId field and delete the Customer Customer one, not only because it's cleaner but also because the EF will recognize the relation between the two as long as there is a class called Customer with a field Id.

   public int CustomerId { get; set; } //keep

   public Customer Customer { get; set; } //delete

So then, the customer gets to open as many accounts with the bank as they please, the table does not suffer, and the EF knows what to do.

It will automatically generate the appropriate foreign key and add it to the migration file like so:

    migrationBuilder.AddForeignKey(
            name: "FK_BankAccount_Customer_CustomerId",
            table: "BankAccount",
            column: "CustomerId",
            principalTable: "Customer",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

That will reflect a one to many relation, which is fine and dandy.

Now, to answer the question, if you still want to drop the property:

Second: Force the EF to drop the Identity property

(but it will not solve the foreign key problem of the original question).

First, just to recap: to change the identity property, the DB manager (mysql, sqlserver, etc.) will always ask you to drop and recreate the table because that field is the heart of the table. So you need to trick the EF to do a workaround for you.

1. Add a second Id element to the model class with an obvious name like duplicateId.

   public class BankAccount
   {
       public int Id { get; set; }
       public int duplicateId { get; set; }
       ...
   }
   

2. THIS IS THE TRICK ;)
   In the class there you implemented the DbContext interface, add the following method where you tell the ef which field you want the primary key to be:

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
       modelBuilder.Entity<BankAccount>().HasKey(x => new { x.duplicateId });
   }
   

3. Add a new Migration with $ dotnet ef migrations add ModelIdChange1, since this changes the primary key of the table and the migration Up method should look lile this:

   protected override void Up(MigrationBuilder migrationBuilder)
   {
       migrationBuilder.DropPrimaryKey(
           name: "PK_BankAccount",
           table: "BankAccountTableName");
   
       migrationBuilder.AddColumn<int>(
           name: "duplicateId",
           table: "BankAccountTableName",
           type: "int",
           nullable: false,
           defaultValue: 0)
           .Annotation("SqlServer:Identity", "1, 1");
   
       migrationBuilder.AddPrimaryKey(
           name: "PK_BankAccount",
           table: "BankAccountTableName",
           column: "duplicateId");
   ...
   }
   

4. Then do the database update with $ dotnet ef database update (these commands may vary, use whatever syntax you already used before).

5. (OPTIONAL) If you are need to preserve the original IDs, check that they got preserved or simply do a dirty update on the table to copy the data from the Id field into the duplicateId.

6. Now the original Id field is free to be deleted or updated, so go ahead and just delete de original field from the model:

   public class BankAccount
   {
       public int duplicateId { get; set; }
       ...
   }
   

If you are still trying to force the original command that links the BankAccount Id with the Customer's Id, it should work if you run the command at this step, but please don't.

7. And add a new Migration with $ dotnet ef migrations add ModelIdChange2, and then do the database update with $ dotnet ef database update, which deletes the original Id column and leaves duplicateId as the primary key.

8. Now, the model looks almost the Original one but with a new identity column, you can leave it like that or just rename the field back from duplicateId to Id in the BankAccount class like this:

   public class BankAccount
   {
       public int Id { get; set; }
       ...
   }
   

   and do $ dotnet ef migrations add ModelIdChange3, and then do the database update with $ dotnet ef database update.