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?

If you try to do this with SSMS, it creates a new table and copies all the data into the new table. — Moby Disk, May 26 '15 at 17:32

score 6 · Accepted Answer · edited May 23 '17 at 11:46

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:

Drop all foreign key constraints that point to the primary key we are changing

Drop the primary key constraint

Rename the existing column (so that we can re-create the foreign key relationships later)

Add the new primary key column with the new identity setting

Update existing data so that previous foreign key relationships remain

If the new column is an identity column, we need to update all foreign key columns with the new values

If the new column doesn’t have identity on, we can copy the old values from the previous identity column

Drop old primary key column

Add primary key constraint

Add back foreign key constraints

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

Thanks it works. I took off AlterColumn(..) and replaced it with AddColumn(tempColumn), run SQL(update), DropColumn(oldColumn) and finally RenameColumn(tempColumnToOld) — RredCat, May 27 '15 at 10:08

Migrate entity with updated DatabaseGeneratedOption in Entity-Framework

1 Answers1

Linked