Implementing Composite Keys

In this lesson, you will learn how to define a primary key that spans multiple columns to maintain data integrity in legacy database schemas where no single column provides uniqueness.

The Concept of Composite Keys

In many legacy systems, a single table might rely on a combination of columns—rather than a single ID—to identify a unique record. For example, in an Enrollment table, a student might be registered for a specific course, where the uniqueness is defined by the pair of StudentId and CourseId. In EF Core, a key consisting of two or more properties is known as a Composite Key.

Implementing via Fluent API

Since Data Annotations ([Key]) only support single-column keys, you must use the Fluent API within your DbContext to configure composite keys. You do this by overriding the OnModelCreating method and using the HasKey method to pass a lambda expression containing the properties you wish to include.

Here is how you would configure the Enrollment entity:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Enrollment>()
        .HasKey(e => new { e.StudentId, e.CourseId });
}

Why Use Composite Keys?

By defining the key as a composite, you ensure that the database enforces uniqueness on the combination of those values. If you try to add a second record with the exact same StudentId and CourseId, EF Core and the underlying database will reject the transaction, keeping your data clean.

Keep in mind that while composite keys are excellent for mapping existing legacy tables, they can add complexity to your navigation properties and entity relationships. When you query these entities, you must always provide the full set of values that make up the key. When retrieving a specific Enrollment, for example, you would pass both the StudentId and CourseId to the Find method:

context.Enrollments.Find(studentId, courseId);

Key Takeaway: Use the Fluent API's HasKey method with an anonymous object to map multiple columns as a single primary key, ensuring your model aligns with legacy database constraints.