Entity Framework code-first: migration fails with update-database, forces unneccessary(?) add-migration

I have a funny effect using migration (EF 5.0) and code-first:

I created some models with GUID primary keys. (BTW: It is important for me, that SQL Server uses NEWSEQUENTIALID(), which seems to be the default value in the current version)

At some point I activated migrations. I added some code to the initial migration, this is mostly .Index() as needed.

When I delete the database and call update-database, I get the following error:

> 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.

I tried AutomaticMigrationsEnabled = true, which worked without changing or adding anything!

But since I don't want AutomaticMigrationsEnabled, I also tried deleting the database again, called update-database and then add-migration. I ended up with an additional migration that seems not to change anything (see below). I also tried adding these lines to the bottom of the initial migration - but this does not change anything.

One of the models:

[Table(Speaker.TABLENAME)]
public class Speaker : BaseModel
{
	public const String TABLENAME = "Speaker";

	[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

	[Required]
	[MaxLength(50, ErrorMessage = "Name must be 50 characters or less")]
	public string Name { get; set; }
}

The initial migration code:

public partial class InitialCreate : DbMigration
{
	public override void Up()
	{
		// [...]
		CreateTable(
			"dbo.Speaker",
			c => new
				{
					Id = c.Guid(nullable: false, identity: true),
					Name = c.String(nullable: false, maxLength: 50),
				})
			.PrimaryKey(t => t.Id)
			.Index(t => t.Name, true, false);	// added manually: unique Name
		// [...]
	}
}

internal sealed class Configuration : DbMigrationsConfiguration<MyProject.Repositories.DBContext>
{
	public Configuration()
	{
		AutomaticMigrationsEnabled = false;
	}

	protected override void Seed(MyProject.Repositories.DBContext context)
	{
		// ...
	}
}

Below is the code created by add-migration: It does not seem to do anything new - maybe I am missing something?

public partial class UnneccessaryMigration : DbMigration
{
	public override void Up()
	{
		// isn't this the exact same code from InitialMigrations?
		AlterColumn("dbo.Speaker", "Id", c => c.Guid(nullable: false, identity: true));
		// ...
	}
	
	public override void Down()
	{
		//...
		AlterColumn("dbo.Speaker", "Id", c => c.Guid(nullable: false));
	}
}

So I am curious: What did I do to disorientate migrations? And what can I do to get it working with just one initial migration?

Solution: The following workaround did it for me:

1. I deleted the database and all migrations as decribed here: https://stackoverflow.com/a/11679386/3168401
2. Executed Enable-Migrations + Add-Migration Initial
3. Merged my handmade .Index() changes into the file. Now Update-Database works again - also repeatedly, when deleting the database.

> I also tried deleting the database again, called update-database and > then add-migration. I ended up with an additional migration that seems > not to change anything (see below)

Based on above details, I think you have done last thing first. If you run Update database before Add-migration, it won't update the database with your migration schemas. First you need to add the migration and then run update command.

Try them in this order using package manager console.

PM> Enable-migrations //You don't need this as you have already done it
PM> Add-migration Give_it_a_name
PM> Update-database

Entity Framework does have some issues around identity fields.

You can't add GUID identity on existing table

Migrations: does not detect changes to DatabaseGeneratedOption

Reverse engineering does not mark GUID keys with default NEWSEQUENTIALID() as store generated identities

None of these describes your issue exactly and the Down() method in your extra migration is interesting because it appears to be attempting to remove IDENTITY from the column when your CREATE TABLE in the initial migration appears to set it!

Furthermore, if you use Update-Database -Script or Update-Database -Verbose to view the sql that is run from these AlterColumn methods you will see that the sql is identical in Up and Down, and actually does nothing. IDENTITY remains unchanged (for the current version - EF 6.0.2 and below) - as described in the first 2 issues I linked to.

I think you should delete the redundant code in your extra migration and live with an empty migration for now. And you could subscribe to/vote for the issues to be addressed.

References:

Change IDENTITY option does diddly squat

Switch Identity On/Off With A Custom Migration Operation

Try this:

PM> Enable-migrations -force
PM> Add-migration MigrationName
PM> Update-database -force

for me i solved it like the following In Visual Studio 2015 : From View menu click Other Windows then click Package Manager Console then run the following commands :

PM> enable-migrations

Migrations have already been enabled in project 'mvcproject'. To overwrite the existing migrations configuration, use the -Force parameter.

PM> enable-migrations -Force

Checking if the context targets an existing database... Code First Migrations enabled for project mvcproject.

then add the migration name under the migration folder it will add the class you need in Solution Explorer by run the following command

PM>Add-migration AddColumnUser

Finally update the database

PM> update-database 

When using VS2019, MVC5 - look under Migrations folder for file Configuration.cs Edit : AutomaticMigrationsEnabled = true

In answer to your general question of

> So I am curious: What did I do to disorientate migrations? And what > can I do to get it working with just one initial migration?

I've just had the same error message as you after I merged several branches and the migrations got confused about the current state of the database. Worst of all, this was only happening on the client's server, not on our development systems.

In trying to work out what was happening there, I came across this superb Microsoft guide:

Microsoft's guide to Code First Migrations in Team Environments

Whilst that guide was written to explain migrations in teams, it also gives the best explanation I've found of how the migrations work internally, which may well lead to an explanation for the behaviour your seeing. It's very worth putting an hour aside to read all of that for anyone who works with EF6 or below.

For anyone brought to this question by that error message after merging migrations, the trick of generating a blank migration with the current state of the database solved things for me, but do be very sure to have read the whole guide to know if that solution is appropriate in your case.

I had this problem and the suggestions above didn't help. What I found is that the add-migration reads the current state and creates a signature of the current model. You must modify your model before modifying. So the sequence is.

1. Modify model
2. run add-migration

I did the opposite and added the migration before modifying my model (which was empty, so I added the new columns) and then ran my code.

Hope this helps.

if you set your context model as code first based on exist database so you have to for set migration:

Add-Migration InitialCreate –IgnoreChanges
Update-database -force

and then change your context model and set:

Add-migration RemoveIspositive
Update-database -force

I understand this is a very old thread. However, wanted to share how I encountered the message in my scenario and in case it might help others

1. I created an Add-Migration <Migration_name> on my local machine. Didn't run the update-database yet.
2. Meanwhile, there were series of commits in parent branch that I must down merge. The merge also had a migration to it and when I fixed conflicts, I ended up having 2 migrations that are added to my project but are not executed via update-database.
3. Now I don't use enable-migrations -force in my application. Rather my preferred way is execute the update-database -script command to control the target migrations I need.
4. So, when I attempted the above command, I get the error in question.

My solution was to run update-database -Script -TargetMigration <migration_name_from_merge> and then my update-database -Script -TargetMigration <migration_name> which generated 2 scripts that I was able to run manually on my local db.

Needless to say above experience is on my local machine.