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

As others have said, EF can't do this natively, although it will create a migration script that makes it look like it has. I've experimented with lots of ways of doing this and I find the easiest way is to:

1. Back up the database...
2. Change your class so it no longer has an identity in code (by removing the attribute or mapping)
3. Generate the migration with the Package Manager Console (add-migration [your migration name] )
4. Comment out the code in Up method in the newly generated migration
5. Add a new line of code ready to receive the SQL you'll generate below: Sql (@" ");
6. Go into SSMS and make sure it's set to generate scripts when you make a table change
7. Remove the identity in the table designer in SMSS
8. Save the table change in SSMS and copy the SQL generated. (That SQL makes a copy of the table with the data, drops the original table, then recreates the original table without the identity set on the column, then copies all the data back and adds all the foreign keys and constraints back on again)
9. Paste the SQL you just copied between the speech marks in the code you added above.
10. Run the migration

That should then give you a migration that removes the identity and can be run on other copies of the database successfully.

NB:The Down method that was generated won't work either because it will be adding the identity back on, which EF also can't do. If you need the Down method to work create a copy of the SQL you added and adjust it to create the table with the identity again.

The approach above is a quick way of what @Georg Patscheider describes so well in his answer.