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

Question

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.

Answer 1

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 

Answer 2

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