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

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.

Solution 1:^[1]

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

Step 1

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

Step 2

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

Solution 2:^[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:

Remove all the migrations until the creation of the target model with Remove-Migration -Project <target_project> in the Package Manager Console
Delete the actual database
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!).
create a new migration with add-migration <migration_name> in the Package Manager Console
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.

Solution 3:^[3]

This error occurs when you try to alter or modify a table that already exists when you want to change schema or the table that already exists, which EF core Doesn't support it yet it needs manual action. here is what you can do about this:

Comment related code in migration file to avoid this error.
or Remove migration files and create a fresh one.
Delete upstream migration and let migration generate new code.

Solution 4:^[4]

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.

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; }
    ...
}

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 });
}
```

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");
...
}

Then do the database update with $ dotnet ef database update (these commands may vary, use whatever syntax you already used before).
(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.
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.

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.
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.

Solution 5:^[5]

I had to:

Comment the table totally from code
Run a migration that clears it from DB
Uncomment table again with corrected mapping
Run migration again

Done

Solution 6:^[6]

In my opinion running the EF Migrations against anything but your development database is asking for trouble as you are naturally limited by the fact that EF migrations will sometimes flatly refuse to work when altering the structure of you objects (changing primary keys and changing foreign keys being the most often encountered).

For many years I have used tools to ensure DB schema is included in version control (complementary to EF migrations). Do your developments to change your dev database (where the data is not important), create multiple migrations but then use the tools to roll these up into a DB deployment script.

Here’s a summary of what I would do in this case: -

Remove (comment out) all references to the old class BankAccount
Create Migration and apply to dev database
Re-Add the BankAccount class with it’s corrected definition
Create Migration and apply to dev database
Use a DB comparison tool (my preference is APEX SQL Diff, but there are other in the marketplace) to create a deployment script that rolls up both migrations.
Test this script on your staging environment (where you should have some data)
If test is good apply to Production

The reality is if you have production data that you want to radically change the structure of with a code first approach it will probably end badly for you unless you understand and address the data migration from one structure to the other.

Solution 7:^[7]

In my case, table SharedBalances is renamed to Balances and it's identity column SharedBalancesId is renamed BalanceId. SQL commands are executed on SQL Server. You can also try migrationBuilder.Sql(my_sql_command_here)

I created the migration and got the same error.

Rename the column and the table using TSQL command:

EXEC sp_RENAME 'SharedBalances.SharedBalanceId', 'BalanceId', 'COLUMN';

EXEC sp_RENAME 'SharedBalances', 'Balances';

-- Caution: Changing any part of an object name could break scripts and stored procedures.

Comment the RenameTable command in your migration:

/*
migrationBuilder.RenameTable(
    name: "SharedBalances",
    newName: "Balances");
*/

Comment the AddPrimaryKey command in your migration:

/*
migrationBuilder.DropPrimaryKey(
    name: "PK_SharedBalances",
    table: "Balances");
migrationBuilder.AddPrimaryKey(
    name: "PK_Balances",
    table: "Balances",
    column: "BalanceId");
*/

Update occurences of the table name DropForeignKey commands in your migration:

From this....

        migrationBuilder.DropForeignKey(
            name: "FK_SharedBalances_Users_OwnerUserId",
            table: "SharedBalances");

        migrationBuilder.DropPrimaryKey(
            name: "PK_SharedBalances",
            table: "SharedBalances");

To this:

        migrationBuilder.DropForeignKey(
            name: "FK_SharedBalances_Users_OwnerUserId",
            table: "Balances");

        migrationBuilder.DropPrimaryKey(
            name: "PK_SharedBalances",
            table: "Balances");

Now your migration will work. This is how it happened:

Solution 8:^[8]

I ran into the same problem ( In my case I didn't have any data in the tables ), and I solved it in this way ( It's not the proper way, but it worked for me ):

I've deleted manually migrations from the EFCore project. I removed those lines which have been added from the file _ContextModelSnapshot as well. ( I had one migration which has been applied and one which has been created but it wasn't applied, as I was getting an error - Change the IDENTITY property of a column, the column needs to be dropped and recreated )
I've deleted manually the tables which have been created in the database ( by the first migration)
I've deleted the row in the table _EFMigrationHistory, that one which related to the Migration I wanted to remove.
Re-run VS
Add-Migration NewOneCleanMigration
Update-Database

Solution 9:^[9]

For those who are lazy like me: You want to change the datatype of a primary key column "Id" from int to Guid in a table called "Translations" as my case was.

Your generated migration in that case is

migrationBuilder.AlterColumn<Guid>(
     name: "Id",
     table: "Translations",
     type: "uniqueidentifier",
     nullable: false,
     oldClrType: typeof(int),
     oldType: "int")
     OldAnnotation("SqlServer:Identity", "1, 1");

You can delete or comment that out

From the update-database error System.InvalidOperationException: To change the IDENTITY property of a column, the column needs to be dropped and recreated.
We also know that we cannot drop the column without dropping the primary key constraint first. Our new migration becomes

migrationBuilder.DropPrimaryKey(
    name: "PK_Translations",
    table: "Translations");

  migrationBuilder.DropColumn(
    name: "Id",
    table: "Translations");

  migrationBuilder.AddColumn<Guid>(
    name: "Id",
    table: "Translations",
    type: "uniqueidentifier",
    nullable: false);

Remember to do the opposite in the Down override method in case you may want to reverse the migration

Solution 10:^[10]

Table has no important data

Rename the table entity.ToTable("BankAccount2")
Add Run migration Add-Migration BankAccountTempChanges
Update the database Update-Database
Rename back the table entity.ToTable("BankAccount")
Add Run migration Add-Migration BankAccountOk
Update the database again Update-Database

Table has data not to be lost

Apply solution from @Hani answer

Solution 11:^[11]

please follow this step:

1-please do all change of identity column in sql server(not in your code first entity framework)

2-comment identity column changes in the migration (.cs file)

3-update-database

enjoy that

Solution 12:^[12]

I had a similar problem where I was changing the relational navigation component of a table's configuration from WithMany to WithRequiredDependent. Entity framework wanted to drop the index and recreate the column, even though nothing in the database should have changed.

To fix this, I rescaffolded the latest migration which allowed entity to absorb the change without any new migration being created. You can rescaffold the latest migration by reverting the migration from the target database, and re-running the Add-Migration script for the latest migration with the exact same migration name.

Solution 13:^[13]

(Note: I'm using .Net 6 with EF 6.0.5)

I received this error, but the situation isn't an exact match. I have 2 models with a 1 to many relationship. On the model that had the many relationship, I realized I had marked the foreign key property as the key so it was both the PK and FK and identity. I didn't want this.

I changed the [Key] annotation to the field I wanted and created a migration. When I ran the migration, I got this error. I resolved this by commenting out the AlterColumn statement in the new migration and adding the Identity annotation to the PK Field.

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropPrimaryKey(
            name: "PK_EmailListAddresses",
            table: "EmailListAddresses");

        //migrationBuilder.AlterColumn<int>(
        //    name: "AddressId",             //Property I wanted to make the new PK
        //    table: "EmailListAddresses",   //Class/Entity Name
        //    type: "int",
        //    nullable: false,
        //    oldClrType: typeof(int),
        //    oldType: "int")
        //    .Annotation("SqlServer:Identity", "1, 1");

        //Added annotation here
        migrationBuilder.AddPrimaryKey(
            name: "PK_EmailListAddresses",
            table: "EmailListAddresses",
            column: "AddressId").Annotation("SqlServer:Identity", "1, 1");

        migrationBuilder.CreateIndex(
            name: "IX_EmailListAddresses_EmailListId",
            table: "EmailListAddresses",
            column: "EmailListId");
    }

From here I ran the update-database command and things worked as intended. AddressId became the PK and EmailListId remained a FK.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Katelyn Kim
Solution 2	Tito Leiva
Solution 3	Payam Khaninejad
Solution 4	unJordi
Solution 5	Bassel
Solution 6
Solution 7	profimedica
Solution 8	K.Nikolic
Solution 9	Brainykat Dev
Solution 10	Bellash
Solution 11	Ali Rasouli
Solution 12	nightwuffle
Solution 13	jason

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

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

The question has a 2 part answer:

First: Let the EF do the relations

Second: Force the EF to drop the Identity property

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]