EF Core - Creating a Composite Primary Key

In EF Core, you can use the [Key] attribute to define a primary key with a single column. However, to define a composite primary key, you must override DbContext.OnModelCreating() and specify all the columns that make up the primary key.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Instructor>()
        .HasKey(nameof(Instructor.Department), nameof(Instructor.CourseCode));
}

In this article, I'll walk you through an example of creating a composite primary key, as well as modifying an existing primary key by adding or removing columns.

Adding a Composite Primary Key

Let’s say we have an Instructor model with the following definition:

using System.ComponentModel.DataAnnotations;

public class Instructor
{
    [Required]
    [MaxLength(10)]
    public string Department { get; set; }

    [Required]
    [MaxLength(10)]
    public string CourseCode { get; set; }
}

In this scenario, we want to create a composite primary key that includes the Department and CourseCode properties.

To implement this composite primary key, you need to override DbContext.OnModelCreating() and specify these two columns as part of the key definition:

public class UniversityContext : DbContext
{
    private readonly string ConnectionString;
    
    public UniversityContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(ConnectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Instructor>()
            .HasKey(nameof(Instructor.Department), nameof(Instructor.CourseCode));
    }

    public DbSet<Instructor> Instructors { get; set; }
    public DbSet<Course> Courses { get; set; }
}

Note: In reality, you should ensure that these columns are unique combinations to avoid any conflicts in primary key constraints.

After making these changes, generate a migration:

dotnet ef migrations add AddCompositePrimaryKey

Check the generated migration code:

public partial class AddCompositePrimaryKey : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Instructors",
            columns: table => new
            {
                Department = table.Column<string>(type: "nvarchar(10)", maxLength: 10, nullable: false),
                CourseCode = table.Column<string>(type: "nvarchar(10)", maxLength: 10, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Instructors", x => new { x.Department, x.CourseCode });
            });
    }

    // Down() not shown
}

Finally, apply the migration:

dotnet ef database update

The Instructors table will now have a composite primary key consisting of the Department and CourseCode columns.

Changing a Primary Key

In EF Core, changing a primary key involves dropping the old key and defining a new one. Let’s see how to add or remove columns from a primary key.

Adding Another Column to the Primary Key

Let’s say we want to add a new column called Year to the primary key.

First, update the Instructor model to include the Year property:

public class Instructor
{
    [Required]
    [MaxLength(10)]
    public string Department { get; set; }

    [Required]
    [MaxLength(10)]
    public string CourseCode { get; set; }

    [Required]
    public int Year { get; set; }
}

Then, modify OnModelCreating() to include Year in the composite primary key:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Instructor>()
        .HasKey(nameof(Instructor.Department), nameof(Instructor.CourseCode), nameof(Instructor.Year));
}

Generate the migration:

dotnet ef migrations add AddYearToPrimaryKey

Check the migration code:

public partial class AddYearToPrimaryKey : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropPrimaryKey(
            name: "PK_Instructors",
            table: "Instructors");

        migrationBuilder.AddColumn<int>(
            name: "Year",
            table: "Instructors",
            nullable: false,
            defaultValue: 0);

        migrationBuilder.AddPrimaryKey(
            name: "PK_Instructors",
            table: "Instructors",
            columns: new[] { "Department", "CourseCode", "Year" });
    }

    // Down() not shown
}

Apply the migration:

dotnet ef database update

The primary key of the Instructors table is now updated to include the Year column.

Removing a Column from the Primary Key

Now, suppose we no longer want Year to be part of the primary key.

Modify OnModelCreating() to exclude Year from the primary key:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Instructor>()
        .HasKey(nameof(Instructor.Department), nameof(Instructor.CourseCode)); // Removed Year
}

Generate the migration:

dotnet ef migrations add RemoveYearFromPrimaryKey

Inspect the migration code:

public partial class RemoveYearFromPrimaryKey : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropPrimaryKey(
            name: "PK_Instructors",
            table: "Instructors");

        migrationBuilder.AddPrimaryKey(
            name: "PK_Instructors",
            table: "Instructors",
            columns: new[] { "Department", "CourseCode" });
    }

    // Down() not shown
}

Apply the migration:

dotnet ef database update

The primary key is now back to just the Department and CourseCode.

Error When Removing a Column from the Primary Key

Sometimes, you might run into an error when dropping columns from the primary key if there are duplicate records.

Let’s say the table has the following records:

When attempting to remove Year from the primary key, SQL Server will encounter a conflict since Department and CourseCode aren’t unique on their own. You’ll get an error similar to this:

Microsoft.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.Instructors’ and the index name ‘PK_Instructors’.

This error will prevent the migration from being applied, and the primary key will remain unchanged.