EF Core – How to create a composite primary key

by

A composite primary key is a primary key that contains more than one column.

In EF Core, when you are creating (or modifying) a table, you can, you can use the [Key] attribute to create primary key with one column. To create a composite primary key with multiple columns, you have to override DbContext.OnModelCreating() and then specify all of the columns included in the primary key, like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<Actor>()
		.HasKey(nameof(Actor.FirstName), nameof(Actor.LastName));
}
Code language: C# (cs)

In this article, I’ll show an example of adding a composite primary key. Then I’ll show how to change an existing primary key by adding or removing a column.

Example of adding a composite primary key

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

using System.ComponentModel.DataAnnotations;

public class Actor
{
	[Required]
	[MaxLength(20)]
	public string FirstName { get; set; }

	[Required]
	[MaxLength(20)]
	public string LastName { get; set; }
}
Code language: C# (cs)

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

To create this composite primary key with these two columns, override DbContext.OnModelCreating(), and then use ModelBuilder to specify the columns included in the primary key, like this:

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

	public DbSet<Movie> Movies { get; set; }
	public DbSet<Show> Shows { get; set; }
	public DbSet<Episode> Episodes { get; set; }
	public DbSet<Actor> Actors { get; set; }
}
Code language: C# (cs)

Note: There are many actors with the same first and last name, so this isn’t a good primary key in the real world.

Because this is a database schema change, you’ll want to generate a migration:

dotnet ef migrations add Database_v11
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v11.cs, and notice that it’s adding a primary key with FirstName and LastName:

public partial class Database_v11 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.CreateTable(
			name: "Actors",
			columns: table => new
			{
				FirstName = table.Column<string>(type: "nvarchar(20)", maxLength: 20, nullable: false),
				LastName = table.Column<string>(type: "nvarchar(20)", maxLength: 20, nullable: false)
			},
			constraints: table =>
			{
				table.PrimaryKey("PK_Actors", x => new { x.FirstName, x.LastName });
			});
	}
	//Down() not shown
}
Code language: C# (cs)

Now apply the migration programmatically or from the command line:

dotnet ef database update
Code language: PowerShell (powershell)

The database will now have the Actors table with a primary key containing the FirstName and LastName columns.

Changing a primary key

When you add/remove a column from a primary key, it’ll drop the primary key and recreate it with the new definition.

Let’s see how this works in EF Core.

Adding another column to the primary key

The Actors table currently has a primary key with the FirstName and LastName columns. Let’s see what happens when we add a new column – BirthDate – to the primary key.

First, add the BirthDate property to the Actor model:

using System.ComponentModel.DataAnnotations;

public class Actor
{
	[Required]
	[MaxLength(20)]
	public string FirstName { get; set; }

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

	[Required]
	public DateTime BirthDate { get; set; }
}
Code language: C# (cs)

Then update the overriden DbContext.OnModelCreating() method by adding BirthDate to the key definition:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<Actor>()
		.HasKey(nameof(Actor.FirstName), nameof(Actor.LastName), nameof(Actor.BirthDate));
}
Code language: C# (cs)

Generate the migration:

dotnet ef migrations add Database_v12
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v12.cs. Notice that it’s dropping the primary key and then re-adding it with the BirthDate column added to the definition:

public partial class Database_v12 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.DropPrimaryKey(
			name: "PK_Actors",
			table: "Actors");

		migrationBuilder.AddColumn<DateTime>(
			name: "BirthDate",
			table: "Actors",
			type: "datetime2",
			nullable: false,
			defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

		migrationBuilder.AddPrimaryKey(
			name: "PK_Actors",
			table: "Actors",
			columns: new[] { "FirstName", "LastName", "BirthDate" });
	}
	
	//Down() not shown
}
Code language: C# (cs)

Apply the migration:

dotnet ef database update
Code language: PowerShell (powershell)

The table will now have the updated primary key definition with the BirthDate column.

Removing a column from the primary key

Let’s say we want to remove the BirthDate column from the primary key. First, update the overriden DbContext.OnModelCreating() method by removing BirthDate from the list of key columns:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<Actor>()
		.HasKey(nameof(Actor.FirstName), nameof(Actor.LastName)); //Removed BirthDate column
}
Code language: C# (cs)

Generate the migration:

dotnet ef migrations add Database_v13
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v13.cs. It dropped the primary key and then re-added it without the BirthDate column:

public partial class Database_v13 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.DropPrimaryKey(
			name: "PK_Actors",
			table: "Actors");

		migrationBuilder.AddPrimaryKey(
			name: "PK_Actors",
			table: "Actors",
			columns: new[] { "FirstName", "LastName" });
	}
//Down() not shown
}
Code language: C# (cs)

Apply the migration:

dotnet ef database update
Code language: PowerShell (powershell)

The table will now have primary key without the BirthDate column.

When you remove columns from the primary key, there’s a chance that you’ll run into an error due the new primary key column combo not being unique. I’ll show this scenario below.

Error when creating the primary key due to existing duplicates in the table

Let’s say the primary key currently contains the three columns: FirstName, LastName, and BirthDate. The table has the following two records:

FirstName	LastName	BirthDate
Michael		Douglas		1944-09-25
Michael		Douglas		1951-09-05 //aka Michael Keaton
Code language: plaintext (plaintext)

Now let’s we’re given a new requirement that says BirthDate can’t be part of the primary key, which means the BirthDate column will have to be removed, leaving us with a primary key with just FirstName and LastName.

Try to apply the migration with the primary key change, and you’ll get the following error:

ALTER TABLE [Actors] ADD CONSTRAINT [PK_Actors] PRIMARY KEY ([FirstName], [LastName]);
Microsoft.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.Actors’ and the index name ‘PK_Actors’. The duplicate key value is (Michael , Douglas).

This error blocks the migration from being applied, and the primary key is left as is.

Note: Migrations are executed within a transaction, so when an error happens like this, the transaction is rolled back.

Related Articles