Not sure if this is a common occurrence, but I've had a number of occasions where Entity Framework migrations have left the state of migrations in an unusable state. Usually, this happens after a large number of migrations have been applied, and I get stuck where I can't update a database with new migrations or roll back. It simply won't go.

There are a number of hacks you can try to fix bonked migrations, but to be honest, more often than not, those simply don't work. So what do you do?

I've found, in most cases, it's simply easier to blow away the migrations and start with a clean slate from the current schema. To be clear, this works only if all of your databases are up to date in the first place or at least in some known consistent state. Usually, this isn't a problem as databases tend to be vital for anything to work, so they are very likely to be up to date. But if not, you'll have to find that consistent state so that your EF schema and the database are in sync. That might mean rolling back to the last known good migration.

As you might expect, resetting migrations is not as obvious as it could be – it's not a use case that Entity Framework expects you to work with. There's no built-in way to do this, so you have to perform a number of manual steps, and that's what this post is about.

A Word of Warning

If you go the route of resetting your migrations,  make sure you back up your code and make known good backups of your database, just in case the schema reversion doesn't do what you expect. While the EF code generator is pretty good at matching the EF schema and what's in your database, in some cases it doesn't work. And you don't want to be stuck in that place without a backup. This is especially true if you have custom code in your migrations that perform additional tasks to update the database. You may have to add these additional manual steps to the initial migration that gets created…

All that said I've had to do this sort of reset on a large project with a couple of hundred tables and it worked without a problem. But your mileage may vary, so whatever you do be safe about the data and code you already have and do the backup.

Removing and Resetting Migrations

The idea of this process is basically this: The database and the EF schema are up to date and just the way you want it, so we are going to remove the existing migrations and create a new initial migration.

In summary, the steps to do this are:

• Remove the _MigrationHistory table from the Database
• Remove the individual migration files in your project's Migrations folder
• Enable-Migrations in Package Manager Console
• Add-migration Initial in PMC
• Comment out the code inside of the Up method in the Initial Migration
• Update-database in PMC (does nothing but creates Migration Entry)
• Remove comments in the Initial method

You've now essentially reset the schema to the latest version.

Again if you had custom code in your old migrations that added custom constraints or modified data alongside of the generated Migration code you may have to add this code back in the initial migration generated.

Simple Example

I recently ran into this problem with a simple example database that I use for various applications. The migrations got corrupted because the database is shared amongst multiple applications and the migration history was hopelessly bonked.

Removing the Migrations Table

The first step is to remove the migrations table:

Go ahead and delete the _MigrationHistory table which tells EF what migrations have been applied. If this table exists EF checks to see whether the latest migration has been applied and if it hasn't fails and throws an error to the effect that the database and EF schema are out of sync. Note however, that if you remove the table and run your application it will run, as EF simply won't check if the schema matches.

Delete your Migrations in your Project

Your project that contains the DbContext for your EF application contains a Migrations folder. This folder contains code files for each schema modification that was made with Up() and Down() methods that add and remove a given migration.

You can leave the Configuration.cs file, as it may contain initial data adding code. If you leave it you might have to check if you need to update any initial data loading code to reflect the potentially updated schema. If you don't care about the initial code you can delete the file or the entire Migrations folder.

If you now recompile and run your application again you're likely going to find that your application will run just fine. Because there's no migrations table in the database and there are no migrations in your project EF just pretends that everything is in sync and runs. If there are any schema errors you will encounter them at runtime…

Recreating the Migrations

The next steps involve using the Nuget Package Manager Console to re-enable migrations and create an initial migration.

Open the Package Manager Console in Visual Studio and select the project that contains your DbContext (!important) and type Enable-Migrations:

Next create an initial migration by typing Add-Migration Initial.

This creates an initial migration source file with the Up() and Down() methods that define the schema for your database as EF sees it based on your DbContext class. If your database is large this may take a while and produce a massive source file.

For my minimal sample app I'm using to demonstrate this it looks like this:

namespace AlbumViewerBusiness.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Albums",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Title = c.String(),
                        Description = c.String(),
                        Year = c.Int(nullable: false),
                        ImageUrl = c.String(),
                        AmazonUrl = c.String(),
                        SpotifyUrl = c.String(),
                        ArtistId = c.Int(),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Artists", t => t.ArtistId)
                .Index(t => t.ArtistId);
            
            CreateTable(
                "dbo.Artists",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        ArtistName = c.String(maxLength: 128),
                        Description = c.String(),
                        ImageUrl = c.String(maxLength: 256),
                        AmazonUrl = c.String(maxLength: 256),
                    })
                .PrimaryKey(t => t.Id);
            
            CreateTable(
                "dbo.Tracks",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        AlbumId = c.Int(),
                        SongName = c.String(maxLength: 128),
                        Length = c.String(maxLength: 10),
                        Bytes = c.Int(nullable: false),
                        UnitPrice = c.Decimal(nullable: false, precision: 18, scale: 2),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Albums", t => t.AlbumId)
                .Index(t => t.AlbumId);
            
        }
        
        public override void Down()
        {
            DropForeignKey("dbo.Tracks", "AlbumId", "dbo.Albums");
            DropForeignKey("dbo.Albums", "ArtistId", "dbo.Artists");
            DropIndex("dbo.Tracks", new[] { "AlbumId" });
            DropIndex("dbo.Albums", new[] { "ArtistId" });
            DropTable("dbo.Tracks");
            DropTable("dbo.Artists");
            DropTable("dbo.Albums");
        }
    }
}

There are the expected create table commands and foreign key associations and any special restraints required based on your dbContext and Model classes. EF walks the dbContext, finds each of the model classes, figures out the relationships and foreign keys and applies any of the attribute settings defined in the model and expresses them as code. After you're done you now see this in the Solution Explorer:

Updating the Database

Finally we need to update the database with the updated Migration information, data by using the Update-Database command. But there's a twist – we want to write the migration record, but we actually don't want to update the database because it's already in the desired state. If you try to run the migration as is, it fails because the tables already exist.

To work around this we can fake out the Migration by commenting out the code in the Up() method. I like to just put a return at the top of the code like this:

public override void Up()
{
    return;        

    CreateTable(
        "dbo.Albums",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                Title = c.String(),
                Description = c.String(),
                Year = c.Int(nullable: false),
                ImageUrl = c.String(),
                AmazonUrl = c.String(),
                SpotifyUrl = c.String(),
                ArtistId = c.Int(),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Artists", t => t.ArtistId)
        .Index(t => t.ArtistId);
   … more code omitted for brevity            
}

Now you can run Update-Database and the Up() operation does nothing, yet still writes the migration record into the database.

When you're done, remove the return;  statement from the Up() method and – voila! – your code is now back in sync.

Update-Database with Scripts

The last step is arguably pretty clunky and you have to proceed with this same procedure for each database local and remote that you're updating. You have to remember to comment the code, and uncomment when your done which is a pain.

So perhaps the better approach is to generate the database scripts, edit the script and remove all the actual model update code and leave in just the database creation code. You can share that script with other developers or check that into source control for others to use to get their development databases into sync.

If you run Update-DataBase –script you can capture the full database update operations as a SQL script that you can edit. The script is the same that runs when you do the interactive update, but you can choose to run it yourself. You can also edit the script.

So maybe you can save both the full script to create the database completely as well as just the update script

You can grab the final INSERT statement from this script to write the Migration entry without the rest of the schema creation :

INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'201601140110091_Initial',         N'AlbumViewerBusiness.Migrations.Configuration',          0x1F8B0800000000000400ED5ACD6EE33610BE17E83B083AB545D6B2B368B ,         N'6.1.3-40302')

which is arguably a little bit easier to work with and can be more easily shared with others that might have to update their database as well. You'll still want to delete the __MigrationHistory table first.

Personally, I prefer to update the database with scripts like this because you can more easily see what operations are perforned and – if something goes wrong – you are likely to get better error information from Sql Server Management studio or command line execution than from the package manager update.

Sync up all Databases

It's important that when you wipe the slate clean as described above, all databases in use should get updated to a known consistent state before you perform these steps. Once the updates are applied your migration starting points are either no database at all, or the database in the fully updated base state. If you have databases that were a few iterations behind in migrations before you started the clean slate operations, there will be no easy way to get those in sync.

If you find that's happened, you may have to use the SQL Server Schema Comparison tools in Visual Studio or a tool like Red Gate's awesome Sql Compare.

Summary

Clearly this process is more difficult than it should be, but I also suspect that this is not something that the EF team would recommend. Yet, I've seen a number of occasions in my own apps and many more in client applicatinos where migrations have simply gone too far out of whack to fix, and this is the only solution I've found to get back a stable environment. In some cases when I have massive amounts of migration scripts I also find it more sensible to 'clean up' and consolidate the schema changes into an initial startup script and these steps fit the bill.

It would be nice if there was a command that could basically 'reset' migrations to a starting point with a single command. These steps here are repetitive and I find that when I go through this process I typically have to do it more than once because I forgot something along the way. Still to me it beats wasting hours or days on trying to troubleshoot migrations that have gone off the rails. Your mileage may vary…

I'd be curious to hear whether you dear reader have also run into EF migration problems and if you have whether you've used the same approach or something else. Chime in, in the comments.

Resources

• Entity Framework Migrations Command Reference