Migrations - Under the Hood

Play Migrations - Under the Hood

The Discussion

  • User profile image
    Adam Pond


    Thanks so much Rowan. Awesome video.
    I found situations ourselves in a situation where migrations had been deleted and effectively orphaned the production DB. We were effectively on a see-saw where, when we fixed migration scripts for the production DB, it left us unable to create a new local environment, and vice versa.

    Since it took some effort to recover and little help online, I thought I'd write our the steps and share with everyone else, in case they get in a similar situation.

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

    Description: If you're like us when your team is new to EF, you'll end up in a state where you either can't create a new local database or you can't apply updates to your production database. You want to get back to a clean EF environment and then stick to basics, but you can't. If you get it working for production, you can't create a local db, and if you get it working for local, your production server gets out of sync. And finally, you don't want to delete any production server data.

    Symptom: Can't run "Update-Database" because it's trying to run the creation script and the database already has tables with the same name.
    Error Message: System.Data.SqlClient.SqlException (0x80131904): There is already an object named '<YOURTABLE>' in the database.

    Problem Background: EF understands where the current database is at compared to where the code is at based on a table in the database called dbo.__MigrationHistory. When it looks at the Migration Scripts, it tries to reconsile where it was last at with the scripts. If it can't, it just tries to apply them in order. This means, it goes back to the initial creation script and if you look at the very first part in the UP command, it'll be the CreeateTable for the table that the error was occurring on.

    To understand this in more detail, I'd recommend watching both videos referenced here:

    Solution: What we need to do is to trick EF into thinking that the current database is up to date while not applying these CreateTable commands. At the same time, we still want those commands to exist so we can create new local databases.

    Step 1: Production DB clean
    First, make a backup of your production db. In SSMS, Right-Click on the database, Select "Tasks > Export Data-tier application..." and follow the prompts.
    Open your production database and delete/drop the dbo.__MigrationHistory table.

    Step 2: Local environment clean
    Open your migrations folder and delete it. I'm assuming you can get this all back from git if necessary.

    Step 3: Recreate Initial
    In the Package Manager, run "Enable-Migrations" (EF will prompt you to use -ContextTypeName if you have multiple contexts).
    Run "Add-Migration Initial -verbose". This will Create the initial script to create the database from scratch based on the current code.
    If you had any seed operations in the previous Configuration.cs, then copy that across.

    Step 4: Trick EF
    At this point, if we ran "Update-Database", we'd be getting the original error. So, we need to trick EF into thinking that it's up to date, without running these commands. So, go into the Up method in the Initial migration you just created and comment it all out.

    Step 5: Update-Database
    With no code to execute on the Up process, EF will create the dbo.__MigrationHistory table with the correct entry to say that it ran this script correctly. Go and check it out if you like.
    Now, uncomment that code and save.
    You can run "Update-Database" again if you want to check that EF thinks its up to date. It won't run the Up step with all of the CreateTable commands because it thinks it's already done this.

    Step 6: Confirm EF is ACTUALLY up to date
    If you had code that hadn't yet had migrations applied to it, this is what I did...

    Run "Add-Migration MissingMigrations"
    This will create practically an empty script. Because the code was there already, there was actually the correct commands to create these tables in the initial migration script, so I just cut the CreateTable and equivalent drop commands into the Up and Down methods.

    Now, run "Update-Database" again and watch it execute your new migration script, creating the appropriate tables in the database.

    Step 7: Re-confirm and commit.
    Build, test, run. Ensure that everything is running then commit the changes.

    Step 8: Let the rest of your team know how to proceed.
    When the next person updates, EF won't know what hit it given that the scripts it had run before don't exist. But, assuming that local databases can be blown away and re-created, this is all good. They will need to drop their local database and add create it from EF again. If they had local changes and pending migrations, I'd recommend they create their DB again on master, switch to their feature branch and re-create those migration scripts from scratch.

Add Your 2 Cents