EF Core – Add a foreign key

by

In this article, I’ll show how to add a foreign key using EF Core. Then I’ll show how foreign keys affect inserts and deletes.

How to add a foreign key

A Show has one or more Episodes. In this section, I’ll show how to link these two tables together to enforce the 1-to-many relationship.

In the Episode model, add a ShowId property and add the ForeignKey attribute:

using System.ComponentModel.DataAnnotations.Schema;

public class Episode
{
	[Key]
	public int Id { get; set; }

	[ForeignKey("FK_Show")]
	public int ShowId { get; set; }

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

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

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

Then in the Show model, add a List<Episode> property:

public class Show
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

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

	[Required]
	public int NumberOfSeasons { get; set; }
	
	[Required]
	public int FirstYear { get; set; }
	
	public int? LastYear { get; set; }

	public List<Episode> Episodes { get; set; }

}
Code language: C# (cs)

Now generate the migration for this database schema change:

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

Take a look at the generated migration source code in <timestamp>_Database_v7.cs. Notice that it has logic for creating the foreign key:

public partial class Database_v7 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.CreateTable(
			name: "Episode",
			columns: table => new
			{
				Id = table.Column<int>(type: "int", nullable: false)
					.Annotation("SqlServer:Identity", "1, 1"),
				ShowId = table.Column<int>(type: "int", nullable: false),
				Title = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
				Number = table.Column<int>(type: "int", nullable: false),
				Season = table.Column<int>(type: "int", nullable: false)
			},
			constraints: table =>
			{
				table.PrimaryKey("PK_Episode", x => x.Id);
				table.ForeignKey(
					name: "FK_Episode_Shows_ShowId",
					column: x => x.ShowId,
					principalTable: "Shows",
					principalColumn: "Id",
					onDelete: ReferentialAction.Cascade);
			});

		migrationBuilder.CreateIndex(
			name: "IX_Episode_ShowId",
			table: "Episode",
			column: "ShowId");
	}

	//Down() not shown
}
Code language: C# (cs)

Apply the migration with the dotnet ef tool (or apply the migration programmatically):

dotnet ef database update
Code language: PowerShell (powershell)

In the rest of the article, I’ll show how this foreign key constraint affects inserts and deletes.

Inserting data when you have a foreign key

The main purpose of a foreign key is to enforce referential integrity. A Show has one or more Episodes. These two tables are linked together (and you’ll often want to select from both tables). The foreign key is defined on the Episodes table. This means you cannot insert an Episode unless it links to an existing Show.

I’ll show an example of the foreign key constraint preventing inserting an Episode that isn’t linked to an existing Show. Then I’ll show two examples of linking Episodes to Shows and inserting them.

Example of the foreign key constraint preventing an invalid insert

The following code is trying to insert an Episode using a non-existent ShowId:

using (var context = new StreamingServiceContext(connectionString))
{
	context.Add(new Episode()
	{
		Number = 1,
		Season = 1,
		ShowId = 5,
		Title = "Test"
	});

	context.SaveChanges();

}
Code language: C# (cs)

Because there’s no Show with Id=5, this results in the following exception:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
—> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Episode_Shows_ShowId”. The conflict occurred in database “StreamingService”, table “dbo.Shows”, column ‘Id’.

Example of correctly inserting by letting EF Core automatically link the objects

There are many ways to insert an Episode. The only requirement is that its ShowId refers to an existing Show.

One way is to add the Episode object to Show.Episodes, and then save it, like this:

using (var context = new StreamingServiceContext(connectionString))
{
	context.Add(new Show
	{
		Name = "Star Trek: Picard",
		Description = "Picard goes on a space trip to stop android-hating Romulans",
		NumberOfEpisodes = 10,
		NumberOfSeasons = 1,
		FirstYear = 2020,
		LastYear = null,
		Episodes = new List<Episode>()
		{
			new Episode()
			{
				Number = 1,
				Season = 1,
				Title = "Remembrance"
			}
		}
	});


	context.SaveChanges();
}
Code language: C# (cs)

Notice this is not explicitly specifying the ShowId. The Show.Id property is an identity column, which means when you insert the Show, the id gets generated automatically. Then EF Core automatically populates Episode.ShowId with Show.Id and inserts the Episode into the database.

Example of correctly inserting by explicitly setting the foreign key id

You aren’t required to add the Episode record to Show.Episodes. You can just set Episode.ShowId to Show.Id explicitly and then save it, like this:

using (var context = new StreamingServiceContext(connectionString))
{
	var starTrek = new Show
	{
		Name = "Star Trek: Picard",
		Description = "Picard goes on a space trip to stop android-hating Romulans",
		NumberOfEpisodes = 10,
		NumberOfSeasons = 1,
		FirstYear = 2020,
		LastYear = null,
	};

	context.Add(starTrek);

	context.SaveChanges();
	
	//starTrek.Id has been populated by EF Core

	context.Add(new Episode()
	{
		Number = 1,
		Season = 1,
		ShowId = starTrek.Id,
		Title = "Remembrance"
	});

	context.SaveChanges();

}
Code language: C# (cs)

This inserted without a problem.

Note: Just make sure Show.Id is populated before trying to use it.

Cascading deletes

Foreign keys enforce referential integrity. This means you can’t have an Episode record linked to a non-existent Show. This constraint is enforced at all times, including when you try to do a delete. When you delete a Show, if there are Episodes linked to it, then the delete won’t be allowed. The common way to deal with this is with cascading deletes.

By default, EF Core configures foreign keys with cascading deletes. This means when a Show is deleted, it automatically deletes all Episodes linked to that show. If you take a look at the generated migration source code, you can see the foreign key was configured to do a cascading delete:

table.ForeignKey(
	name: "FK_Episode_Shows_ShowId",
	column: x => x.ShowId,
	principalTable: "Shows",
	principalColumn: "Id",
	onDelete: ReferentialAction.Cascade);
Code language: C# (cs)

What if you don’t want to use cascading deletes?

To change the default behavior, you can override OnModelCreating() in your DbContext class and then specify the delete behavior for the foreign keys. You can apply different delete behavior for each foreign key, or change the behavior for all foreign keys.

For example, let’s say you don’t want to use cascading deletes on any current or future foreign keys. The following code overrides the default delete behavior for all foreign keys:

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)
	{
		foreach(var e in modelBuilder.Model.GetEntityTypes())
		{
			foreach(var fk in e.GetForeignKeys())
			{
				fk.DeleteBehavior = DeleteBehavior.Restrict;
			}
		}
	}
	public DbSet<Movie> Movies { get; set; }
	public DbSet<Show> Shows { get; set; }
	public DbSet<Episode> Episodes { get; set; }
}
Code language: C# (cs)

Read more about creating a database and tables with EF Core.

Generate the migration:

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

This generated a migration with the following foreign key definition. Notice the onDelete parameter is now using ReferentialAction.Restrict instead of ReferentialAction.Cascade:

table.ForeignKey(
	name: "FK_Episodes_Shows_ShowId",
	column: x => x.ShowId,
	principalTable: "Shows",
	principalColumn: "Id",
	onDelete: ReferentialAction.Restrict);
Code language: C# (cs)

Apply the migration:

dotnet ef database update
Code language: PowerShell (powershell)

Now try to delete a Show that has linked Episodes. You’ll get the following exception:

System.InvalidOperationException: The association between entity types ‘Show’ and ‘Episode’ has been severed, but the relationship is either marked as required or is implicitly required because the foreign key is not nullable

It won’t let you delete the Show without first deleting the linked Episodes.

Related Articles

2 thoughts on “EF Core – Add a foreign key”

    • The foreign key is part of the database definition, so it’s part of the migration. The List isn’t a database definition thing, so it doesn’t appear in the migrations.

      Reply

Leave a Comment