1

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?

Colin
  • 22,328
  • 17
  • 103
  • 197
  • Do you actually have a code first model, or are you using a EDMX file? – Peter Hansen Mar 28 '13 at 10:26
  • I'm using code-first. No EDMX. – Colin Mar 28 '13 at 10:30
  • Ok - I would guess that you have the Id property configured with `DatabaseGeneratedOption.None` somewhere. Powertools probably did this when reverse engineering the database the first time, so you have to remove that manually since you have it as an identity column now. – Peter Hansen Mar 28 '13 at 10:34
  • @PeterHansen - of course that's it! I have a SectionMap.cs class that tells the framework how to map to the database....I've changed it to this: " this.Property(t => t.ID) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);" (it did have DatabaseGeneratedOption.None). Please put an answer up so i can accept it! – Colin Mar 28 '13 at 10:44

1 Answers1

4

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.

Peter Hansen
  • 8,807
  • 1
  • 36
  • 44
  • 2
    BTW. When I changed the Map class to match the database migration I manually created, the framework picked that up as a change to the model. So I ran add-migration. It created an Up() method with this code: "AlterColumn("dbo.Sections", "ID", c => c.Int(nullable: false, identity: true));" update-database -verbose reveals that this generates the sql "ALTER TABLE [dbo].[Sections] ALTER COLUMN [ID] [int] NOT NULL" - which does not actually change anything. The correct method is 1. change the mapping file 2. call add-migration 3. change the generated migration code to drop and recreate the table – Colin Mar 28 '13 at 16:17
  • 2
    Yes, certainly. The usual workflow is to change the code first model, scaffold a migration with the changes by calling `add-migration` and then update the database by calling `update-database`. The migrations feature wont know that you manually changed the db, so it will create a migration that does it as well. You could call `Add-Migration name –IgnoreChanges`. That would create a empty migration that ignores changes to the model. – Peter Hansen Mar 28 '13 at 16:40