Change Primarykey Entityframework

Asked

Viewed 182 times

0

How to change a Primary key using Entityframework?

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

c# sql-server entity-framework

  • It is not very feasible to do this through the Entity Framework. You have a good reason for this?

  • I know it’s not a good practice, but unfortunately I need to do it

1 answer

0

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.

Browser other questions tagged c# sql-server entity-framework

You are not signed in. Login or sign up in order to post.