CODEWITHSUNDEEP
c#.netentity-framework.net-coreentity-framework-core
Umut Gür
Umut Gür

Reputation: 387

How to change single primary key to composite primary key with EF Core

I want to delete the single primary key Id column in a SQL table that has already been created and does not contain data and define a composite primary key instead.

My current entity definition is as follows:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

public long IpFrom { get; set; }

public long IpTo { get; set; }
...

And I want to update to this:

[Key, Column(Order = 0)]
public long IpFrom { get; set; }

[Key, Column(Order = 1)]
public long IpTo { get; set; }
...

I have already made the following definition using fluent api:

protected override void OnModelCreating(ModelBuilder builder)
{
   base.OnModelCreating(builder);

   builder.Entity<IPv4LocationEntity>()
     .HasKey(b => new {b.IpFrom, b.IpTo})
     .HasName("PK_TableName");
}

Then I used EF Tool to create migration files and got the following migration file.

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropPrimaryKey(
            name: "PK_TableName",
            table: "TableName");

        migrationBuilder.DropColumn(
            name: "Id",
            table: "TableName");

        migrationBuilder.AddPrimaryKey(
            name: "PK_TableName",
            table: "TableName",
            columns: new[] { "IpFrom", "IpTo" });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropPrimaryKey(
            name: "PK_TableName",
            table: "TableName");

        migrationBuilder.AddColumn<int>(
            name: "Id",
            table: "TableName",
            type: "int",
            nullable: false,
            defaultValue: 0)
            .Annotation("SqlServer:Identity", "1, 1");

        migrationBuilder.AddPrimaryKey(
            name: "PK_TableName",
            table: "TableName",
            column: "Id");
    }
}

I get the following error when I want to update the database with the EF tool even though everything seems right to me:

ALTER TABLE DROP COLUMN failed because column 'Id' does not exist in table 'TableName'.

Thanks for your help.

Upvotes: 2

Views: 3575

Answers (1)

feradz
feradz

Reputation: 1362

I tried this and running dotnet ef database update does not apply the migration.

Instead, generated SQL file from the migration and execute the SQL queries. Try doing this.

cd ToYourDbModelProject
dotnet ef  --startup-project ../PathToStartupProject/ migrations script previous_migration current_migration -o current_migration.sql --context Namespace.To.Your.DbContext

Upvotes: 1

Related Questions