Change Primarykey Entityframework

Question

How to change a Primary key using Entityframework?

I have a table with simple PK and need to change this PK by Entityframework.

Answer 1

The solution below might work, as I have not tested it, I ask you to do so, If it doesn’t work, please comment below so I can remove this answer.

First step, you should change all your Fks that reference the key you want to change.

Since EF is part of the premise that PK will never be changed, you will not be able to do this by Migrations, so if you are using SQL Server, you can run a script like this.:

alter table Detail 
  drop constraint FK_Master_Detail

alter table Detail 
  add constraint FK_Master_Detail 
      foreign key (MasterID) 
      references Cars(MasterID) 
      --on delete cascade 
      on update cascade

The second thing to do is to install the following package.:

Z.EntityFramework.Plus.EF6

Then execute the following command...

ctx.Entities
    .Where(x => x.EntityID == oldEntityID)
    .Update(x => new Entity() { EntityID = newEntityID });

Just to remind you, I’m not giving you a guarantee that the code above will work. however you have one more option, which would be using Raw Sql, for this I advise you to use the Dapper.

await ctx.Database.Connection
    .QueryAsync("UPDATE Entities SET EntityID = @newEntityID WHERE EntityID = @oldEntityID", 
        new { newEntityID = newEntityID, oldEntityID = oldEntityID }
    );

Finally, just a suggestion, do not use natural keys, give preference to a replacement key. s you can come to declare your tables as follows.:

CREATE TABLE dbo.Entities (
    EntityID uniqueidentifier NOT NULL,
    NaturalID int NOT NULL --IDENTITY (1, 1)
) ON [PRIMARY]

ALTER TABLE dbo.Entities ADD CONSTRAINT
    PK_Entities PRIMARY KEY NONCLUSTERED (EntityID) 
    WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX IXCU_Entities 
    ON dbo.Entities (NaturalID) 
    WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

And remember, never display your replacement keys to the end user, he does not need or should not know of their existence.