Relationships in EF Core, Part 1: One-to-Many, Foreign Keys and Cascade Delete

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First

An author has a single book. We load the author together with their books and delete them:

Author beck = db.Authors.Include(a => a.Books).Single(a => a.Name == "Kent Beck");
db.Authors.Remove(beck);
db.SaveChanges();

Another author we delete without loading their books:

Author fowler = db.Authors.Single(a => a.Name == "Martin Fowler");
db.Authors.Remove(fowler);
db.SaveChanges();

In each case, how many DELETEs does SaveChanges send to the database? In the second case, what happens to the books EF Core knows nothing about: are they deleted, left orphaned, or does the operation fail? The answer depends on how you define the relationship. By the end of this post we will have seen three different answers, each with real output.

Author and Book: Building the Model

An author can have many books, and each book has one author. That is a one-to-many relationship. In the Bookstore model it looks like this:

namespace Bookstore;

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }

    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public class Order
{
    public int Id { get; set; }
    public DateTime OrderedAt { get; set; }
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int Quantity { get; set; }

    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;

    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
}

Let's pin down the terms once. Author is the principal, the "owner" of the relationship. Book is the dependent; the side that carries the relationship as a column in its own table. That column is the foreign key: AuthorId. Book.Author is a reference navigation and Author.Books a collection navigation. Navigations let you walk from object to object in C#, while the foreign key is what actually holds the relationship in the database.

The Author Author { get; set; } = null!; line is a consequence of living with NRT. A book always has an author, so the type is not Author?. But its value doesn't come from the constructor; it arrives when EF Core loads it or when you assign it. null! is the way to tell the compiler "I guarantee this". The collection navigation, on the other hand, is initialized with new() so that adding books to a new author needs no null check.

How Does the Convention Find the Relationship?

You actually don't need to write a single line to set up this relationship. EF Core sees Author.Books and Book.Author, understands that one is the inverse of the other and maps them as a single relationship. For the foreign key, it looks for a property matching the <NavigationName>Id or <PrincipalName>Id pattern; AuthorId matches both. Had it found none, it would have added a shadow property, as we saw in the earlier posts.

The schema after EnsureCreated:

CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "Price" TEXT NOT NULL,
    "AuthorId" INTEGER NOT NULL,
    CONSTRAINT "FK_Books_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id") ON DELETE CASCADE
);
CREATE TABLE "OrderItem" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_OrderItem" PRIMARY KEY AUTOINCREMENT,
    "Quantity" INTEGER NOT NULL,
    "OrderId" INTEGER NOT NULL,
    "BookId" INTEGER NOT NULL,
    CONSTRAINT "FK_OrderItem_Books_BookId" FOREIGN KEY ("BookId") REFERENCES "Books" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_OrderItem_Orders_OrderId" FOREIGN KEY ("OrderId") REFERENCES "Orders" ("Id") ON DELETE CASCADE
);
CREATE INDEX "IX_Books_AuthorId" ON "Books" ("AuthorId");
CREATE INDEX "IX_OrderItem_BookId" ON "OrderItem" ("BookId");
CREATE INDEX "IX_OrderItem_OrderId" ON "OrderItem" ("OrderId");

Two things stand out. EF Core put an index on every foreign key column on its own, because queries and deletes through the relationship search that column constantly. Since AuthorId is an int, meaning it can't be null, the relationship was treated as required and got ON DELETE CASCADE. The RESTRICT on the OrderItem → Book relationship is our own decision; let's look at it now.

Spelling It Out with the Fluent API

Writing out what the convention found is a must when you want to change the relationship's behavior, and when you don't, it still shows the reader your intent:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>()
        .HasOne(b => b.Author)
        .WithMany(a => a.Books)
        .HasForeignKey(b => b.AuthorId)
        .OnDelete(DeleteBehavior.Cascade);

    modelBuilder.Entity<OrderItem>()
        .HasOne(i => i.Book)
        .WithMany()
        .HasForeignKey(i => i.BookId)
        .OnDelete(DeleteBehavior.Restrict);
}

It reads like a natural sentence: "a Book has one Author, an Author has many Books, the foreign key is AuthorId, and when the author is deleted, delete their books too." In the second relationship WithMany() takes no argument, because we don't want a navigation from Book to its order items. Having a navigation on only one side is perfectly valid.

Restrict reflects a business rule: a book that has been ordered must not be deleted from the catalog. The order history is worth more than removing the product.

Adding a Graph and Fixup

To add related data you don't need to deal with foreign keys. Connect the objects to each other and add the root:

Author fowler = new() { Name = "Martin Fowler" };
fowler.Books.Add(new Book { Title = "Refactoring", Price = 42.50m });
fowler.Books.Add(new Book { Title = "Patterns of Enterprise Application Architecture", Price = 49.00m });

db.Authors.Add(fowler);
db.SaveChanges();

Console.WriteLine($"Author Id: {fowler.Id}");
foreach (Book book in fowler.Books)
{
    Console.WriteLine($"{book.Title} -> AuthorId {book.AuthorId}, Author {book.Author.Name}");
}

      INSERT INTO "Authors" ("Name")
      VALUES (@p0);
      SELECT "Id"
      FROM "Authors"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();
      INSERT INTO "Books" ("AuthorId", "Price", "Title")
      VALUES (@p1, @p2, @p3);
      SELECT "Id"
      FROM "Books"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();
      INSERT INTO "Books" ("AuthorId", "Price", "Title")
      VALUES (@p0, @p1, @p2);
      SELECT "Id"
      FROM "Books"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();
Author Id: 1
Refactoring -> AuthorId 1, Author Martin Fowler
Patterns of Enterprise Application Architecture -> AuthorId 1, Author Martin Fowler

Add didn't just add the author; it followed the navigations and marked the whole graph as Added. EF Core also decided the order itself: the principal first (the author's Id is needed), then the dependents. It wrote the author's Id returned by the database into the books' AuthorId and filled in book.Author. We never gave a foreign key a value.

The other direction works too. If the author is already in the database, you can add a book by giving only the foreign key, without loading the author: new Book { Title = "Test-Driven Development", Price = 35.00m, AuthorId = 2 }. That is a good reason to keep the foreign key explicitly in the class; with a shadow property you couldn't do this as comfortably.

EF Core wires up the entities it tracks in the same context on its own. This is called fixup:

Author author = db.Authors.Single(a => a.Name == "Kent Beck");
Console.WriteLine($"Books before: {author.Books.Count}");
List<Book> books = db.Books.Where(b => b.AuthorId == author.Id).ToList();
Console.WriteLine($"Books after: {author.Books.Count}");

Books before: 0
Books after: 1

We never touched author.Books. The books came in a separate query, the change tracker matched their AuthorId values with the author it was tracking and filled the collection. The proper ways to load related data (Include, explicit and lazy loading) are the topic of a later post; fixup is the mechanism underneath all of them.

Required or Optional?

Whether a relationship is required is decided by the type of the foreign key. int AuthorId can't be null, which means every book must have an author. If you also want to keep books whose author is unknown, you make the foreign key and the navigation nullable:

public int? AuthorId { get; set; }
public Author? Author { get; set; }

When we remove the OnDelete line from the Fluent API and leave it to the convention, the schema changes:

CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "Price" TEXT NOT NULL,
    "AuthorId" INTEGER NULL,
    CONSTRAINT "FK_Books_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id")
);

The column accepts NULL and there is no ON DELETE part. The convention's defaults are: DeleteBehavior.Cascade for a required relationship and DeleteBehavior.ClientSetNull for an optional one.

Cascade Delete Behaviors

What happens to dependents when a principal is deleted is decided in two separate places: what EF Core does to the entities it tracks in the change tracker, and the rule written into the database's foreign key constraint. DeleteBehavior sets both together.

Cascade: EF Core deletes the tracked dependents as well, and the database gets ON DELETE CASCADE. ClientSetNull: EF Core sets the foreign key of tracked dependents to null, but writes no rule into the database. SetNull: does the same, but also writes ON DELETE SET NULL into the database. Restrict: EF Core doesn't touch tracked dependents, and the database gets ON DELETE RESTRICT.

Besides these there are NoAction, ClientCascade and ClientNoAction. They are useful mainly when you want EF Core rather than the database to handle deletion, for example on cascade paths SQL Server doesn't allow. For the bookstore, the first four are enough.

Loaded and Unloaded Dependents

Back to the guessing question. Required relationship, Cascade, author loaded together with their book:

Book state: Deleted
      DELETE FROM "Books"
      WHERE "Id" = @p0;
      SELECT changes();
      DELETE FROM "Authors"
      WHERE "Id" = @p1;
      SELECT changes();

The moment Remove was called, the book became Deleted too. EF Core sent two DELETEs, the dependent first, then the principal. When the books were not loaded, only a single DELETE FROM "Authors" went out; afterwards db.Books.Count() returned 0. The books were deleted by the database's ON DELETE CASCADE rule, and EF Core never saw it happen.

With an optional relationship and ClientSetNull, the same two attempts give different results. With the book loaded, EF Core first sets the book's foreign key to null:

      UPDATE "Books" SET "AuthorId" = @p0
      WHERE "Id" = @p1;
      SELECT changes();
      DELETE FROM "Authors"
      WHERE "Id" = @p2;
      SELECT changes();
AuthorId after delete: null

With the books not loaded, there is nothing EF Core can set to null, and there is no rule in the database either. The result:

SQLite Error 19: 'FOREIGN KEY constraint failed'.

The word "client" in ClientSetNull says exactly this: the behavior applies only to the entities the application can see.

The third answer is Restrict. When we try to delete a book that has been ordered and the order items are not loaded, the error comes from the database: FOREIGN KEY constraint failed again. If the items are tracked in the same context, EF Core stops you before you even reach SaveChanges, on the Remove line:

The association between entity types 'Book' and 'OrderItem' has been severed, but the relationship is either marked as required or is implicitly required because the foreign key is not nullable. If the dependent/child entity should be deleted when a required relationship is severed, configure the relationship to use cascade deletes. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the key values.

Common Pitfalls

Trusting EF Core with ordering in a single SaveChanges. When we deleted the order and the author in the same SaveChanges (with the items and books not loaded), we got FOREIGN KEY constraint failed. EF Core orders commands only by the dependencies between the entities it tracks. Deleting the author made the database cascade-delete the books, while the order items still pointing to those books ran into RESTRICT. Deleting the order first, in its own SaveChanges, solved the problem.

Catching the Restrict error in the wrong place. If the dependents are tracked, the exception is thrown in Remove, not in SaveChanges; if your try block only wraps SaveChanges, you miss it.

Setting the foreign key and the navigation so that they contradict each other. If you write both book.AuthorId = 2 and book.Author = someOtherAuthor, you have to think about which one wins. Pick one and stick with it.

Leaving cascade on everywhere. Since cascade is the default for required relationships, a single delete can spread across more tables than you expect. For every relationship where deletion is a problem from a business point of view, consider Restrict or soft delete; we will look at soft delete in the global query filters post.

A Small Experiment

★☆☆ Remove the AuthorId property from Book and the HasForeignKey line from the Fluent API, keeping the navigation. What did the column's name and type in the schema end up as? What do you now have to use when adding a book?

★★☆ Configure the optional relationship with OnDelete(DeleteBehavior.SetNull). See the foreign key line in the schema change, then delete the author without loading their books. Did you get an error this time? What happened to AuthorId?

★★★ The relationship between Order and OrderItem currently comes from the convention. Verify in two ways that deleting an order also deletes its items: with the items loaded, through the DELETEs in the log, and with them not loaded, through the rule in the schema.

One-to-many is the most common relationship but not the only one. Each author having a single profile page is one-to-one; a book having several tags and a tag belonging to several books is many-to-many. In the next post we will look at both: how the dependent side is determined, skip navigations, the join table, and UsingEntity for join entities that carry a payload.