Can you remove Identity from a primary key with Entity Framework 6?

Question

I created a table via entity framework code-first with a primary key set to auto increment, but now I want to remove that auto-incrementing from the column. I've tried doing that with both fluent API:

    public class ProductTypeMap: EntityTypeConfiguration<ProductType>
    {
        public ProductTypeMap()
        {
            // This is an enum effectively, so we need fixed IDs
            Property(x => x.ProductTypeId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        }

    }

And an annotation:

    public class ProductType
    {
        [Required, Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int ProductTypeId { get; set; }
        public string ProductTypeName { get; set; }

    }

And in both cases they produce the same migration code:

    public partial class removeproducttypeidentity : DbMigration
    {
        public override void Up()
        {
            DropPrimaryKey("dbo.ProductTypes");
            AlterColumn("dbo.ProductTypes", "ProductTypeId", c => c.Int(nullable: false));
            AddPrimaryKey("dbo.ProductTypes", "ProductTypeId");
        }
        
        public override void Down()
        {
            DropPrimaryKey("dbo.ProductTypes");
            AlterColumn("dbo.ProductTypes", "ProductTypeId", c => c.Int(nullable: false, identity: true));
            AddPrimaryKey("dbo.ProductTypes", "ProductTypeId");
        }
    }

However, when I run that migration on the database, the Identity Specification is not removed from the SQL Server 2008 database table?

I also tried explicitly turning off the Identity in the migration as follows, but that didn't do it either:

AlterColumn("dbo.ProductTypes", "ProductTypeId", c => c.Int(nullable: false, identity: false));

Is there another way to tell SQL to remove the Identity?

Answer 1

You can not use ALTER COLUMN to set whether a column is an identity column (How to alter column to identity(1,1)).

Instead, you have to:

• (backup DB)
• CREATE TMP_table with columns of original table, but ID column set to identity: false
• SET IDENTITY_INSERT [TMP_Table] ON
• copy data from original to TMP table
• SET IDENTITY_INSERT [TMP_Table] OFF
• DROP original table
• Rename TMP_table to original table name (EXECUTE sp_rename)

Tip: change the column in SQL Management Studio and inspect the emitted script (SQL SERVER – Add or Remove Identity Property on Column).