I started getting this message when trying to add a new column to a database table. I did not try to make any change to the key column in the DB.

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

So working through various potential solutions online, I have been trying to drop the table and replace it etc. without any luck. When I run Update-Database in PMC I get the same error so nothing can be dropped, nothing can be added to the DB. Its completely blocked and I have no idea why because I did not change anything fundamental in the DB, I just added a normal int column to the table and tried to migrate an update.

So then, I saw info online saying that I should delete the table and update the database, and EF core would replace the table to get the DB back in sync. I right-clicked the table in VS SQL Server Object Explorer and deleted it. But when I add the migration and run Update-Database, I again get the error:

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

This is not yet in production, so I don't care about the data loss from deleting my table, but its completely blocking me. How can I get my Database-Update to run again so I can get my table back and not see this error?

I have no idea what caused this issue. At this point I'm thinking I should create a brand new database in Azure for my solution and recreate tables one by one, but that will take days/weeks and I don't know if it will get rid of this issue.

Has anyone come across this issue and do you know if recreating my DB from scratch will solve the problem or does it usually originate somewhere that a complete DB recreation wont help?

CodePudding user response：

I fixed it! Sometimes you just need to write it down to come back with a fresh approach!

By using ''Update-Database -Migration [migration name]'', I was able to try every migration one by one, working back from the most recent. By doing this I identified that the problem was with a specific migration 4 or 5 migrations back. Thankfully I always keep migrations small. I then used Remove-Migration to remove them one by one, back to the last good one. This allowed me to follow the steps I found online and was trying yesterday, to create a new table. But I had another error about duplicate column names, so I needed to create my new table with no columns, and delete some columns from the model of the old table before I could drop the offending table (Still dont know why it stopped working). Then I added the old table model to the new one to get my columns back and renamed the new table to match the old one. Working fine now. There was also a bit of commenting and uncommenting needed to get the build working as I switched the tables.