Migrate entity with updated DatabaseGeneratedOption in Entity-Framework

Question

I have created code-first app according to this article - Code First to a New Database. Now I am going to change DatabaseGeneratedOption for Blog.BlogId. I changed my code in next way:

public class Blog
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int BlogId
    {
        get;set;
    }
    ...
}

And created migration for this code update:

public override void Up()
{
    DropForeignKey("dbo.Posts", "BlogId", "dbo.Blogs");
    DropPrimaryKey("dbo.Blogs");
    AlterColumn("dbo.Blogs", "BlogId", c => c.Int(nullable: false, identity: false));
    AddPrimaryKey("dbo.Blogs", "BlogId");
    AddForeignKey("dbo.Posts", "BlogId", "dbo.Blogs", "BlogId", cascadeDelete: true);
}

According to this I changed code that created blog entity in Main function (added BlogId there.)

var blog = new Blog
{
    Name = name,
    BlogId = 110//it could be any other value that isn't represented in column
};

Now when I am trying to run my code I am getting next exception: DbUpdateException with next message - Cannot insert explicit value for identity column in table 'Blogs' when IDENTITY_INSERT is set to OFF.

From other hand when I delete all migrations and create initial migration from updated entity and creating db without identity flag (don't trying to update exist db), my code that create entity with my BlogId works.

My issue that in real project I have created table and I don't wont to recreate entire table just wont to update key column. How to do it with entity framework migration?

Answer 1

You're trying to drop an IDENTITY property from a column, and unfortunately, that's usually not trivial (and for SQL Server at least, I don't think it's possible).

For some explanation, see:

• Entering keys manually with Entity Framework

  EF puts this in the migration:

  AlterColumn("dbo.Events", "EventID", c => c.Int(nullable: false, identity: false))
  

  And the sql generated is this:

  ALTER TABLE [dbo].[Events] ALTER COLUMN [EventID] [int] NOT NULL`
  

  Which actually does diddly squat.

• https://dba.stackexchange.com/questions/87420/how-to-remove-identity-specification-for-multiple-tables

• Remove Identity from a column in a table
• http://romiller.com/2013/04/30/ef6-switching-identity-onoff-with-a-custom-migration-operation/

---

The last few links also provide some ideas for how you can customize your migration to remove the IDENTITY column and still keep your data. For example, from the last link:

The steps for changing the identity setting on a column in SQL Server are:

1. Drop all foreign key constraints that point to the primary key we are changing
2. Drop the primary key constraint
3. Rename the existing column (so that we can re-create the foreign key relationships later)
4. Add the new primary key column with the new identity setting
5. Update existing data so that previous foreign key relationships remain
6. If the new column is an identity column, we need to update all foreign key columns with the new values
7. If the new column doesn’t have identity on, we can copy the old values from the previous identity column
8. Drop old primary key column
9. Add primary key constraint
10. Add back foreign key constraints

http://romiller.com/2013/04/30/ef6-switching-identity-onoff-with-a-custom-migration-operation/