How to create a composite primary key in EF Core

In EF Core, when creating a table, you can define a primary key using the [Key] attribute for a single column. However, if you need to create a composite primary key using multiple columns, you'll need to override the DbContext.OnModelCreating() method and specify all the columns that will be part of the primary key, like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .HasKey(nameof(Customer.FirstName), nameof(Customer.LastName));
}

In this article, we will go over an example of adding a composite primary key, and then explore how to modify an existing primary key by adding or removing columns.

Adding a Composite Primary Key

Let’s say we have a Customer model defined like this:

using System.ComponentModel.DataAnnotations;

public class Customer
{
    [Required]
    [MaxLength(50)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(50)]
    public string LastName { get; set; }
}

We want to create a composite primary key using the FirstName and LastName properties.

To achieve this, override the DbContext.OnModelCreating() method and define the key using the ModelBuilder, as shown below:

public class ApplicationDbContext : DbContext
{
    private readonly string _connectionString;
    
    public ApplicationDbContext(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_connectionString);
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .HasKey(nameof(Customer.FirstName), nameof(Customer.LastName));
    }

    public DbSet<Customer> Customers { get; set; }
}

Note: While using FirstName and LastName as a composite primary key is useful for this example, in real-world applications, it's not ideal because these fields may have duplicates.

Now, to implement this schema change, generate a migration with:

dotnet ef migrations add AddCompositeKeyToCustomer

Review the generated migration file, and it will show the new primary key definition:

public partial class AddCompositeKeyToCustomer : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Customers",
            columns: table => new
            {
                FirstName = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
                LastName = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Customers", x => new { x.FirstName, x.LastName });
            });
    }
    
    // Down() not shown
}

Now apply the migration:

dotnet ef database update

The database will now have a Customers table with a composite primary key defined by the FirstName and LastName columns.

Changing a Primary Key

When adding or removing columns from a primary key, EF Core will drop the old primary key and create a new one with the updated columns.

Adding Another Column to the Primary Key

Let’s assume we want to add the DateOfBirth column to the primary key.

First, modify the Customer model to include DateOfBirth:

using System.ComponentModel.DataAnnotations;
using System;

public class Customer
{
    [Required]
    [MaxLength(50)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(50)]
    public string LastName { get; set; }

    [Required]
    public DateTime DateOfBirth { get; set; }
}

Next, update the OnModelCreating method to include DateOfBirth in the primary key:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .HasKey(nameof(Customer.FirstName), nameof(Customer.LastName), nameof(Customer.DateOfBirth));
}

Now, generate a migration to apply the changes:

dotnet ef migrations add AddDateOfBirthToCompositeKey

The migration will include code that drops the old primary key and redefines it with the new column:

public partial class AddDateOfBirthToCompositeKey : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropPrimaryKey(
            name: "PK_Customers",
            table: "Customers");

        migrationBuilder.AddColumn<DateTime>(
            name: "DateOfBirth",
            table: "Customers",
            type: "datetime2",
            nullable: false);

        migrationBuilder.AddPrimaryKey(
            name: "PK_Customers",
            table: "Customers",
            columns: new[] { "FirstName", "LastName", "DateOfBirth" });
    }
    
    // Down() not shown
}

Finally, apply the migration:

dotnet ef database update

The primary key will now include the DateOfBirth column.

Removing a Column from the Primary Key

To remove DateOfBirth from the primary key, first update the OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .HasKey(nameof(Customer.FirstName), nameof(Customer.LastName)); // Removed DateOfBirth
}

Generate a migration:

dotnet ef migrations add RemoveDateOfBirthFromCompositeKey

The migration will drop the primary key and re-apply it with only FirstName and LastName:

public partial class RemoveDateOfBirthFromCompositeKey : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropPrimaryKey(
            name: "PK_Customers",
            table: "Customers");

        migrationBuilder.AddPrimaryKey(
            name: "PK_Customers",
            table: "Customers",
            columns: new[] { "FirstName", "LastName" });
    }

    // Down() not shown
}

Apply the migration:

dotnet ef database update

The primary key will now be based only on FirstName and LastName.

Error When Creating the Primary Key Due to Existing Duplicates in the Table

Suppose the Customers table has the following data:

Now, imagine we remove DateOfBirth from the primary key and try to apply the migration.

If the table already contains duplicate values in FirstName and LastName (like in this case), an error will occur:

Microsoft.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Customers' and the index name 'PK_Customers'. The duplicate key value is ('John', 'Doe').

This error occurs because FirstName and LastName alone cannot be a unique primary key. EF Core cannot apply the migration unless the data is unique or some other solution is applied (e.g., manually fixing duplicates or adding a new unique column).

Note: Migrations in EF Core are wrapped in transactions, so if an error occurs, the transaction will be rolled back, and no changes will be applied.