How to recover from Entity Framework nightmare - database already has tables with the same name

database-migration ef-migrations entity-framework entity-framework-6

Question

What should you do when Update-database produces the following message? How can you bring EF back in Sync with code without erasing any data?

Error Message: System.Data.SqlClient.SqlException (0x80131904): There is already an object named '' in the database.

1
0
9/7/2017 3:29:25 AM

Popular Answer

Since I and a couple of my coworkers had been struggling with the issue for a while, I had initially written this as a self-answering question. Unfortunately, my answer was accidentally destroyed, and I am unable to retrieve it.

I thought I'd chronicle the scenario with step-by-step instructions as I imagine it may occur more than once when individuals attempt to "clean up" previous migrations.

We were unable to build a new local database due to an unfinished init script, and we were unable to make changes to the production database since the migration scripts created tables that previously existed. Furthermore, we did not want to remove production data.

Zzz-18-Zzz: Zzz-23-Zzz is unable to be executed because it is attempting to run the creation script when the database already contains tables with the same name.

Error Message: System.Data.SqlClient.SqlException (0x80131904): There is already an object named '' in the database.

Problem History: I suggest viewing both of the videos linked below to have a more thorough understanding of this: https://msdn.microsoft.com/en-us/library/dn481501(v=vs.113).aspx

In conclusion, EF uses a table in the database called dbo. MigrationHistory to determine where the current database is in relation to where the code is. It attempts to remember where it was with the Migration Scripts when it looks at them. It merely attempts to apply them in sequence if it can't if it can. This implies that it returns to the original creation script, and the CreeateTable for the table where the issue was happening may be found in the very first section of the UP command.

Solution: In order to "not" execute these CreateTable commands since the production database already exists, we must mislead EF into believing that the existing database is up to date. We still need to be able to build up local databases once the production database is established.

Cleaning up the production database is the first step. Create a backup of your production database first. Right-click the database in SSMS, choose "Tasks > Export Data-tier application," and then follow the on-screen instructions. The dbo. MigrationHistory table should be deleted or dropped in your production database.

Second step: a clean local environment Delete it by opening your migrations folder. I'm assuming you can get anything from git if needed.

3. Recreate the initial Run "Enable-Migrations" in the package manager (EF will prompt you to use -ContextTypeName if you have multiple contexts). "Add-Migration Initial -verbose" should be run. This will generate the first script necessary to build the database from the ground up using the present code. Copy whatever seed operations you had in the prior Configuration.cs file.

Step 4: EF Trick At this point, the original mistake would appear if we run Update-Database. Therefore, we must deceive EF into believing that it is current without really performing these instructions. So, open the Initial migration you just created's Up method and comment everything out.

Update-Database (Step 5) Since there is no code to run on the Up process, EF will construct the proper item in the dbo. MigrationHistory table to indicate that the script was successfully executed. If you like, go over and have a look. Uncomment that code now, then save. If you wish to confirm that EF believes it to be up to date, you may run Update-Database once again. Because it believes that it has already completed this, it won't execute the Up step with all of the CreateTable instructions.

Step 6: Verify that EF is indeed current This is what I did if you had code that hadn't yet had migrations applied to it.

Activate "Add-Migration MissingMigrations" It will almost certainly result in an empty script. Since the code to generate these tables was already included in the original migration script, I simply incorporated the CreateTable command and its corresponding Drop command into the Up and Down procedures.

Run Update-Database once again and see as it executes your updated migration script, building the necessary database tables.

Reaffirm and commit in step 7. Build, check, and run. Once everything is up and running, commit the modifications.

Step 8: Explain the next steps to the rest of your team. Given that the scripts it has previously ran don't exist, EF won't know what hit it when the next person changes. However, this is all okay provided that local databases can be destroyed and recreated. They will have to remove their current local database and install a new one made using EF. I would advise them to recreate their database on master, move to their feature branch, and start again with those migration scripts if they have local modifications and outstanding migrations.

0
9/10/2017 1:56:46 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow