Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

Resetting Entity Framework Migrations to a Clean Slate


:P
On this page:

Clean Slate

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:

DeleteMigrationsHistory

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.

MigrationCodeFiles

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:

PackageManagerEnable[6]

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:

InitialMigration

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

Posted in Entity Framework  ADO.NET  Sql Server  

The Voices of Reason


 

rich
January 13, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

EF must offer some phenomenal benefits to be worth all this futzing around !

Rick Strahl
January 13, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

@rich - While that may be true overall for EF, in this case this isn't what I would call a lot of 'futzing around'. Migrations are not trivial on any platform if they get out of sync so this is not a problem unique to EF. You can do without migrations and update the database manually - if that's what you're doing now there's some serious futzing around you can get into with that :-)

Richard L
January 14, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

You can replace the Add-Migration + remove generated code step by using "Add-Migration Initial -IgnoreChanges", which will scaffold an empty migration, just adding the MigrationHistory row.

Tim
January 14, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

I follow a similar process after every production release where I delete all the migrations, empty the migration history table, re-create the first Create migration and then capture the SQL for the insert into the migration history table to repopulate it. The purpose is to reset back to a single Create migration once the individual migration steps are no longer needed. And I use a utility I wrote (gratuitous plug: http://theothertimduncan.com/archive/Entity-Framework-Migrations-Real-World/) to make this easier.

But at the risk of hijacking this discussion (for which I'll apologize up front), I have a slightly related question. In recent discussions with a senior developer at my job, he raised the question of who actually uses EF migrations in the real world. Are there any statistics regarding how many businesses use EF migrations for applications currently in production, either enterprise or otherwise?

Denys Chamberland
January 14, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

Hi Rick,
I've tried pretty similar EF migration scenarios (sort of offline testing as DBAs would have probably killed me... ;) though I never was 100% sure it was the best thing to do, anyway glad to see I wasn't the only one to give it a shot... Also while enable-migration approach may well work with EF6 versions, dealing with EF7 is a slightly different ball game ;)

Rob
May 05, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

Hi Rick,

I've appreciate your blog. I've been in this situation a couple of times as well. It's why I don't like code first for large applications. Sure they work wonderfully on little demo type applications. But something seems to go wrong eventually. So I've used both methods to resolve my "bonked" migrations. Good to know I'm not alone.

I do what Tim does when I get to a point where I don't need the migrations anymore. Create a SQL script, initialize a database with that SQL script, and call it a day. So why code first?

Harold Chattaway
October 21, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

I've had to use Ef on a project at my company and it's amazing that the PMC is needed to manage this stuff.. it's like working back in a DOS window again.. It still needs better tools and a refined interface.

Rick Strahl
October 21, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

@Harold - welcome to the retro world. Where everything old is new again. CLI based tools are where everything is headed even with Visual Studio unfortunately.

But to be fair - how would you see a UI be better for this than doing it manually with a CLI interface? Other than not having to remember 3 commands I don't think there would be any real benefit or even easier use.

Mark
November 25, 2016

# re: Resetting Entity Framework Migrations to a clean Slate

Rick, I've often appreciated your work over the years, and had a good experience with your west wind controls a few years ago.

This is not the first time you've bailed me out or set me in a good direction. I tried some other suggestions that didn't work, this one did the trick.

Gracias.


Luis
July 26, 2017

# re: Resetting Entity Framework Migrations to a clean Slate

Hello,

Is the last part totally needed?: Update database with Scripts.

It happens that I can't update my database thru the console, I use the CMD and I can't get my hands on the script but right now any migration I make is blank, I'm guessing it is because I need this last step, altough in my database the Initial migration is recorded in the EFMigrationsHistory table.


Jason
October 06, 2017

# re: Resetting Entity Framework Migrations to a clean Slate

Hi Rick,

My situation is different from yours, but I think your method is what I need. Basically, I'm in school again, following a programming course. We're focusing on MVC and EF now, and make projects in class, that I put in Git or TFS and continue with at home.

In school I use their SQL DB, and at home, I have my own one. Up till now, I've just ignored the TFS/Git projects and recreated them to get some practice (changing the ConnectionString as needed), but if I'd just use the existing ones, could I use the method you describe here to recreate the DB at home?

To me it looks like it's exactly what I need; you're basically recreating a database with all models already created?

Thanks!


Chris
October 18, 2018

# re: Resetting Entity Framework Migrations to a clean Slate

How about this?

  1. Remove the _MigrationHistory table from the Database
  2. Build project solution - to be safe
  3. Update-Database -Script
  4. Remove everything in script except for the create _MigrationHistory table and entires
  5. Apply the the modified script to IndviewQS1 database

Tariq Hameed
September 19, 2020

# re: Resetting Entity Framework Migrations to a clean Slate

Hi Rick,

A great help. appreciate you. in my case i had role-authentications and lot of migrations. I commented both up scripts from CreateIdentitySchema and migration-Initial and than update-database. works fine now with only one migration. thanks alot.


Dez
June 12, 2021

# re: Resetting Entity Framework Migrations to a clean Slate

What should I do if there are multiple contexts sharing the same database. In particular, may I use the usual routine commands when enabling, adding migration and updating databse?


Randall Carter
March 23, 2022

# re: Resetting Entity Framework Migrations to a clean Slate

This was an excellent post I came across two years ago and have used it half-dozen times since. It has saved my bacon from a lot of hassles, especially when refactoring a large project with a lot of schema changes.


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024