Update-Database fails due to Pending Changes, but Add-Migration Creates a Duplicate Migration

Question

I'm working with Entity Framework 5.0 Code First Migrations, and am having a problem with running Update-Database. It says there are pending model changes; but it should be up-to-date, so I run

Add-Migration SomeMigrationName

and it creates a file... however, it creates a file which is essentially the same a duplicate of a prior Migration (if I try to Update-Database again on that file, it fails with issues related to trying to drop a non-existent constraint). Furthermore, I have been able to confirm that the 'original' migration has been run based on both the data model in the DB, and from the presence of a record in the __MigrationHistory table!

If I delete the whole database, and run all the migrations again, automatically or by hand, I have the same problem.

The 'original' migration file I had is as follows:

public partial class RenameLinkColumns : DbMigration
{
    public override void Up()
    {
        DropForeignKey("dbo.Listing", "OfferedByUserId", "dbo.User");
        DropIndex("dbo.Listing", new[] { "OfferedByUserId" });
        AddColumn("dbo.Listing", "ListedByUserId", c => c.Int(nullable: false));
        AddForeignKey("dbo.Listing", "ListedByUserId", "dbo.User", "UserId", cascadeDelete: true);
        CreateIndex("dbo.Listing", "ListedByUserId");
        DropColumn("dbo.Listing", "OfferedByUserId");
    }

    public override void Down()
    {
        AddColumn("dbo.Listing", "OfferedByUserId", c => c.Int(nullable: false));
        DropIndex("dbo.Listing", new[] { "ListedByUserId" });
        DropForeignKey("dbo.Listing", "ListedByUserId", "dbo.User");
        DropColumn("dbo.Listing", "ListedByUserId");
        CreateIndex("dbo.Listing", "OfferedByUserId");
        AddForeignKey("dbo.Listing", "OfferedByUserId", "dbo.User", "UserId", cascadeDelete: true);
    }
}

When I ran that Add-Migration again, the Up/Down methods in that file are exactly the same as these.

I'm quite impressed that migrations were correctly able to detect that I had renamed a ForeignKey column; but is that what is causing this to choke?

It seems there is a work-around: I have deleted the database, and all migration files, and created a new 'Initial' Migration, but I would prefer not to do this if possible.

Update: This was not the latest migration that caused this issue, but the problem started after a merge (I am working alone, but am simulating team work on branches to learn more about git too), and trying to get the database in step with the merge. Might this have come about from placing migrations in some particular order after a merge - though a noted, the migrations did work as expected in the order they ran when I gave them an empty DB.

Additionally, this original migration needed manually tweaking when the tables had data in, because data needed to be copied from the old to the new column. However, I tested that file with and without my manual edits in that file, and still encountered the behaviour noted.

Answer 1

As a general answer to this part of the question:

(I am working alone, but am simulating team work on branches to learn more about git too), and trying to get the database in step with the merge. Might this have come about from placing migrations in some particular order after a merge

Yes, that may well be the case, it's fairly easy for EF to get confused after a merge, but it is possible to resolve it. The key is understanding why it got confused in the first place:

What happens when migrations are merged from branches?

The reason EF gets confused is that EF stores the current shape of the database in the actual migration file, it's the 'Target' value in the resx file found under each migration, e.g.

Imagine you have two branches:

• Branch 1: You add 'URL' field to the Blog table. The Target field now has a description of the database with that extra field
• Branch 2: You add a new 'Links' table. Again, the db description in the Target field now has that extra table, but it doesn't have the URL field as that was added on a different branch

If you now merge both of those branches back onto the master branch and then try to run migrations you may well get the dreaded

Unable to update database to match the current model because there are pending changes and automatic migration is disabled...

That error message is really unhelpfully misleading, but the cause of the error is actually fairly simple to understand:

Why do multiple branches confuse EF?

When the two branches were merged back onto Master, whichever of them is now the last migration (as per the dates at the starts of the filename) is considered by EF to have the true current state of the database in that migration's Target field.

However, as we saw above, Branch 1 and Branch 2 both have different views of what the true state of the database is (one thinks there's a new URL field, the other thinks there's a new links field), and unhelpfully they are now both wrong because the database has both of those fields now.

The error message occurs because EF computes the expected state of the DB from the actual steps in the migrations and compares that to the Target and finds they are different.

How to fix it

The solution to all of this is to force EF to recompute the state of the database based on all the migrations now in the project and then update the Target value to one that includes the changes made on all the migrations.

The simplest way to do that is simply to add a 'blank' migration, using the command:

Add-Migration <pick_a_name> –IgnoreChanges

The alternative approach is to overwrite the Target value in the final migration.

Consult the manual..

All of the above is a brief overview of the superb guide to understanding migrations in general and also in a team environment that can be found in:

Microsoft's Code First Migrations in Team Environments

That document should be referred to in every EF error message as it makes sense of so many day to day EF issues.

Answer 2

This is not the solution, that would help always. I would recommend also all answers.

I had the same issue and the suggestion from Mike doesn't apply in my case. I found out why ...

The DLL with the migrations was part of a (sharepoint) deployment and also in the GAC (C:\Windows\Microsoft.NET\assembly\GAC_MSIL).

I removed the DLL from the GAC and restarted Visual Studio.

Now "update-database" took the correct DLL and migration worked fine.

Answer 3

This answer explains why it happens. To resolve it I call add-migration and name it MERGE and then remove any duplicate migration code that has already happened. This is just to update the model snapshot to reflect the merged model.

Example:

public partial class MERGE : DbMigration
{
    public override void Up()
    {
        // Intentionally left blank.

        // This may seem like a hack, but it is necessary when using source control.
        // When a migration is created via add-migration, EF creates 
        // an .edmx file from the current code first classes. It compares this .edmx to the .edmx stored in the last migration before this, 
        // which I'll call it's parent migration. The edmx snapshots are gzipped and stored in base64 in the resource files (.resx) if you 
        // want to see them. EF uses the difference between these two snapshots to determine what needs to be migrated.

        // When using source control it will happen that two users add entities to the model independently. The generated edmx snapshots will 
        // only have the changes that they have made. When they merge in source control, they will end up with this:

        // Migration                        |  Snapshot Contents
        // -------------------------------- | ----------------
        // 20150101_Parent Migration        |  A
        // 20150102_Developer 1's Migration |  A + Change 1
        // 20150103_Developer 2's Migration |  A + Change 2

        // So calling add-migration will create the current snapshot edmx from the Code First model and compare it to the 
        // the latest migration's snapshot, which is A + Change 2, and see that Change 1 is missing. That is why it 
        // creates a duplicate migration. We know that the migrations have already been applied, so the only thing that this 
        // migration will do is update the current snapshot .edmx so that later migrations work fine.
    }

    public override void Down()
    {

    }
}

Answer 4

I have just faced the same issue.

Once the migration created. I tried to update the database and received the following message:

Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration. You can use the Add-Migration command to write the pending model changes to a code-based migration.

Then, i generated again the migration but it was duplicated.

The issue solved when i Build the project after creating the migration. Then Update-Database script find the Migration Method and it works. At least for my case.

Answer 5

I see this all the time also. I don't know why, wish i did, but my solution is to do a add-migration which will make a duplicate. Now this duplicate opens in the editor and then i edit it, so that the Up and Down methods are empty. So the result is a migration file that does nothing! VS is happy and you can do the update-database without errors (until next time).

I hope this helps :)