How to alter column to identity using entity frame

Question

I reverse engineered a small database using Entity Framework 5.0 and the power tools. Then I started developing a website. I hit a problem because I had forgotten to make the ID column on one of my tables an IDENTITY column.

So I added a code first migration and ran it using the update-database command in the package manager console. Here is the "Up" method I wrote:

    public override void Up()
    {
        Sql("DELETE FROM dbo.Sections");
        Sql("DELETE FROM dbo.QuestionInstances");

        DropForeignKey("dbo.SectionInstances", "SectionID", "dbo.Sections");
        DropForeignKey("dbo.QuestionInstances", "SectionID", "dbo.Sections");

        DropTable("dbo.Sections");

        CreateTable(
            "dbo.Sections",
            c => new
            {
                ID = c.Int(nullable: false, identity: true),
                UniqueDescription = c.String(nullable: false, maxLength: 50),
                Heading = c.String(maxLength: 500),
            })
            .PrimaryKey(t => t.ID);

        AddForeignKey("dbo.SectionInstances", "SectionID", "dbo.Sections");
        AddForeignKey("dbo.QuestionInstances", "SectionID", "dbo.Sections");
    }

I check the database and it has been altered successfully.

I was expecting the entity framework to update its model and stop setting the ID explicitly, but it would appear that I am wrong because when I try to Save I now get this error: "Cannot insert explicit value for identity column in table 'Sections' when IDENTITY_INSERT is set to OFF"

How do I get the entity framework to recognise that the column is now an identity column?

Answer 1

Since you reverse-engineered the database when the Id column was not set as IDENTITY yet, the code first model has been generated with DatabaseGeneratedOption.None set for the Id property on the entity.

That causes EF to create an insert statement with the Id set, which no longer works after changing the column to IDENTITY.

You have to manually fix that configuration, by either setting it to DatabaseGeneratedOption.Identity or just removing it altogether, since that is the default value for integer fields.