C#•2y ago

Drop composite PK and add new column PK with identity with EF Code first

Hello. I have following table ClaimFeeType(picture below). I want to remove composite PK constraint and add new column and assign identity PK to it withtou losing data. I achieved that with pure SQL in MSSQL studio following way:

BEGIN TRAN
ALTER TABLE dbo.ClaimFeeType
DROP CONSTRAINT PK_ClaimFeeType;
ALTER TABLE dbo.ClaimFeeType ADD ClaimFeeTypeId int identity(1,1) not null
GO
ALTER TABLE dbo.ClaimFeeType
add CONSTRAINT PK_ClaimFeeTypeId primary key(ClaimFeeTypeId)
GO
ROLLBACK TRAN

BEGIN TRAN
ALTER TABLE dbo.ClaimFeeType
DROP CONSTRAINT PK_ClaimFeeType;
ALTER TABLE dbo.ClaimFeeType ADD ClaimFeeTypeId int identity(1,1) not null
GO
ALTER TABLE dbo.ClaimFeeType
add CONSTRAINT PK_ClaimFeeTypeId primary key(ClaimFeeTypeId)
GO
ROLLBACK TRAN

But I am not sure how to do that with EF Code first migrations, as my code base uses Entity Framework. All in all, my database is already in production so can't afford losing data, want to remove composite key PK, add new int column PK with auto increment, and populate new PK field with values. Done with MSSQL, don't know how to write migration for EF.

1 Reply

pwnage•2y ago

I've tried something like this,

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropPrimaryKey(name: "PK_ClaimFeeType", "ClaimFeeType");
            migrationBuilder.AddColumn<int>(
                name: "ClaimFeeTypeId",
                table: "ClaimFeeType",
                type: "int",
                nullable: false);
            
            migrationBuilder.AddPrimaryKey(name: "PK_ClaimFeeTypeId", table: "ClaimFeeType", column: "ClaimFeeTypeId");

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropPrimaryKey(name: "PK_ClaimFeeType", "ClaimFeeType");
            migrationBuilder.AddColumn<int>(
                name: "ClaimFeeTypeId",
                table: "ClaimFeeType",
                type: "int",
                nullable: false);
            
            migrationBuilder.AddPrimaryKey(name: "PK_ClaimFeeTypeId", table: "ClaimFeeType", column: "ClaimFeeTypeId");

but i get following error:

The seed entity for entity type 'ClaimFeeType' cannot be added because a non-zero value is required for property 'ClaimFeeTypeId'. Consider providing a negative value to avoid collisions with non-seed data.

Gaming

Programming

Drop composite PK and add new column PK with identity with EF Code first