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?

score 7 · Answer 1 · answered Nov 18 '21 at 19:25

7

If you want to remove identity, you need to

Step 1. And an annotation to the key column

[DatabaseGenerated(DatabaseGeneratedOption.None)]

public short Id { get; set; }

this will generate the migration script

AlterColumn("dbo.TableName", "Id", c => c.Long(nullable: false));

But this script won't remove identity from the key column.

Step 2. manually remove identity from the key column in SQL Management Studio. You don't need to back up the database.

If you get an error, you might need to go to "Tools"->"Options"->"Designers", and uncheck the "Prevent saving changes that require table re-creation"

answered Nov 18 '21 at 19:25

Tracy Zhou

714
1
7
11

This sounds like a great solution. But for me in MS Management Studio all column properties are dimmed and cannot be edited. So I cannot change (Is Identity). Is this a restriction of MS SQL Express (which I am using)? – Jpsy Jan 20 '22 at 15:27
This is very helpful. Thank you!! – FLICKER Jan 27 '22 at 00:36

tomRedox · Answer 2 · 2018-05-09T16:14:08.037

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:

Back up the database...
Change your class so it no longer has an identity in code (by removing the attribute or mapping)
Generate the migration with the Package Manager Console (add-migration [your migration name] )
Comment out the code in Up method in the newly generated migration
Add a new line of code ready to receive the SQL you'll generate below: Sql (@" ");
Go into SSMS and make sure it's set to generate scripts when you make a table change
Remove the identity in the table designer in SMSS
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)
Paste the SQL you just copied between the speech marks in the code you added above.
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.

And here is what it generates https://stackoverflow.com/a/43688658/2394945 — Moby Disk, Jul 29 '19 at 17:46

score 3 · Accepted Answer · answered Dec 04 '17 at 10:50

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).

score 1 · Answer 4 · answered Dec 04 '17 at 10:55

If you want to remove the Identity from a Primary Key, you’re going to need to Drop and Recreate the table and any associated Foreign Keys along with it.

Removing an identity specification from a column can only be done by re-creating the table. Look at the ALTER TABLE - ALTER COLUMN statement: there is no syntax to change (add or remove) an identity specification.

Refer flowing sites:

Cant remove identity attribute from PK

Entering keys manually with Entity Framework

EF6: Switching Identity On/Off with a Custom Migration Operation

Remove Identity from Primary Key with Drop and Recreate Table

score 0 · Answer 5 · answered Dec 04 '17 at 11:40

Thanks to both of the answers explaining about what's involved in dropping the identity from the column I came up with a new version of the migration that achieves the removal of the identity from the PK column.

This solution loses all of the data in the table. This is acceptable in my case because the table in question is one that represents an enum and so is populated manually by my seed method (I was removing the identity in the first place so I could dictate the values used for the PK Id fields). Do not use this approach for user entered data!

public partial class removeproducttypeidentity : DbMigration
{
    public override void Up()
    {
        DropForeignKey("dbo.Products", "ProductTypeId", "dbo.ProductTypes");
        DropPrimaryKey("dbo.ProductTypes");

        DropTable("dbo.ProductTypes");

        CreateTable(
                "dbo.ProductTypes",
                c => new
                {
                    ProductTypeId = c.Int(nullable: false),
                    ProductTypeName = c.String(),
                })
            .PrimaryKey(t => t.ProductTypeId);

        AddForeignKey("dbo.Products", "ProductTypeId", "dbo.ProductTypes", "ProductTypeId");
    }

    public override void Down()
    {
        DropForeignKey("dbo.Products", "ProductTypeId", "dbo.ProductTypes");
        DropPrimaryKey("dbo.ProductTypes");

        DropTable("dbo.ProductTypes");

        CreateTable(
                "dbo.ProductTypes",
                c => new
                {
                    ProductTypeId = c.Int(nullable: false, identity: true),
                    ProductTypeName = c.String(),
                })
            .PrimaryKey(t => t.ProductTypeId);

        AddForeignKey("dbo.Products", "ProductTypeId", "dbo.ProductTypes", "ProductTypeId");
    }
}

harvzor · Answer 6 · 2021-02-15T16:47:55.727

This solution works well for when:

you can't lose data
recreating the table would take too long (e.g. you have millions of rows and copying would take a long time)
you want to handle everything with EF rather than writing custom migrations (the approach from @Georg Patscheider and @tomRedox go around EF)
- which is a good idea because future migrations written by EF are less likely to have issues
  - (in my database, I have hundreds of migrations, some with custom SQL, which is causing me trouble years after those migrations were initially written because EF doesn't know about them!)

The solution

Create a new column and copy all the old IDs to it
Make the new column the primary key
Update all references to the old column to the new column

The setup

Example entities:

public class Foo
{
    [Key]
    public int Id { get; set; }
}

public class Bar
{
    // This is the foreign key:
    public int FooId { get; set; }
    public virtual Foo Foo { get; set; }
}

Add a new property which has identity set to false

Change Foo to:

public class Foo
{
    [Key]
    public int Id { get; set; }

    [Index(IsUnique = true)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int NewId { get; set; }
}

Generate a migration:

public partial class AddNewId : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Foos", "NewId", c => c.Int(nullable: false));

        // This is a custom line which copies the data from the old column to the new.
        Sql("UPDATE dbo.Foos SET NewId = Id");

        CreateIndex("dbo.Foos", "NewId", unique: true);
    }
        
    public override void Down()
    {
        DropIndex("dbo.Foos", new[] { "NewId" });
        DropColumn("dbo.Foos", "NewId");
    }
}

Change references from `Foo.Id` to `Foo.NewId`

Here's the tricky part.

Any references to the Foo entity are still using the old Id property rather than the new NewId property.

Using Visual Studio:

Temporarily comment out the old Foo.Id property
Manually change the name of Foo.NumericId to Foo.Id (without updating references)
Rename Foo.Id back to Foo.NumericId but have VS also change all references
Comment back in the old Foo.Id property that was commented out in step 1

Make `Foo.NewId` the primary key

public class Foo
{
    public int Id { get; set; }

    [Key]
    [Index(IsUnique = true)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int NewId { get; set; }
}

Update `Bar.FooId`

All entities now need to reference Foo.NewId instead of Foo.Id.

Change Bar:

public class Bar
{
    // This is the foreign key:
    public int FoodNewId { get; set; }
    public virtual Foo Foo { get; set; }
}

Create a new migration:

public partial class AddNewIdPart2 : DbMigration
{
    public override void Up()
    {
        DropForeignKey("dbo.Bars", "FooId", "dbo.Foos");
        RenameColumn(table: "dbo.Bars", name: "FooId", newName: "FooNewId");
        DropPrimaryKey("dbo.Foos");
        AddPrimaryKey("dbo.Foos", "NewId");
        AddForeignKey("dbo.Bars", "FooNewId", "dbo.Foos", "Newid");
    }
        
    public override void Down()
    {
        // ...
    }
}

Done!

That should be everything.

You can now remove the old Foo.Id column if you want.

But how does this get rid of the identity column, which generally needs table drop? — Ε Г И І И О, Sep 14 '22 at 02:55

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

6 Answers6

The solution

The setup

Add a new property which has identity set to false

Change references from `Foo.Id` to `Foo.NewId`

Make `Foo.NewId` the primary key

Update `Bar.FooId`

Done!

Linked

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

6 Answers6

The solution

The setup

Add a new property which has identity set to false

Change references from Foo.Id to Foo.NewId

Make Foo.NewId the primary key

Update Bar.FooId

Done!

Linked

Change references from `Foo.Id` to `Foo.NewId`

Make `Foo.NewId` the primary key

Update `Bar.FooId`