Relationships in EF Core, Part 2: One-to-One, Many-to-Many and UsingEntity

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

Guess First

A book can have many tags, and a tag can belong to many books. We write the model in the simplest possible form: two classes, two lists, nothing else:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public List<Tag> Tags { get; set; } = new();
}

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

There is no foreign key, no class in between, and OnModelCreating is empty. How many tables does EnsureCreated create? If a table in the middle appears, what is it called and what are its columns? When you remove a tag from a book, which row is deleted in the database: the tag itself or something else?

One-to-One: Author and AuthorProfile

Let's start with the simpler one. Every author has at most one profile page: information like a biography and a website that we don't want to carry around every time we list authors:

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public AuthorProfile? Profile { get; set; }
}

public class AuthorProfile
{
    public int Id { get; set; }
    public string Bio { get; set; } = "";
    public string? Website { get; set; }

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

The schema:

CREATE TABLE "AuthorProfile" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_AuthorProfile" PRIMARY KEY AUTOINCREMENT,
    "Bio" TEXT NOT NULL,
    "Website" TEXT NULL,
    "AuthorId" INTEGER NOT NULL,
    CONSTRAINT "FK_AuthorProfile_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id") ON DELETE CASCADE
);
CREATE UNIQUE INDEX "IX_AuthorProfile_AuthorId" ON "AuthorProfile" ("AuthorId");

It looks a lot like the one-to-many schema; the only difference is in the last line: this time the index on the foreign key is UNIQUE. That is what enforces the rule "an author has at most one profile" in the database. When we try to add a second profile for the same author:

db.Set<AuthorProfile>().Add(new AuthorProfile { Bio = "Duplicate", AuthorId = 1 });
db.SaveChanges();

SQLite Error 19: 'UNIQUE constraint failed: AuthorProfile.AuthorId'.

Adding the profile together with the author is the graph insert we already know: new Author { Name = "Martin Fowler", Profile = new AuthorProfile { ... } } produces two INSERTs, the author first, then the profile. After saving, Profile.AuthorId came back as 1.

Which Side Is the Dependent?

In one-to-many, EF Core had an easy job: the side opposite the collection is always the dependent. In one-to-one, both sides carry a single reference navigation. How does it decide which table gets the foreign key? In the example above we gave the answer: the AuthorId property sits in AuthorProfile, so that is the dependent.

What happens if we remove the foreign key property? When we deleted AuthorId from AuthorProfile and left only the navigations, the model wasn't built at all:

The dependent side could not be determined for the one-to-one relationship between 'Author.Profile' and 'AuthorProfile.Author'. To identify the dependent side of the relationship, configure the foreign key property. If these navigations should not be part of the same relationship, configure them independently via separate method chains in 'OnModelCreating'. See http://go.microsoft.com/fwlink/?LinkId=724062 for more details.

The message is the same on EF Core 6 and 10; only EF Core 10 gives the link with https. EF Core doesn't guess, because both options are valid. The profile could depend on the author, or the author on the profile. Which depends on which is a design decision, and you have to make it.

Writing One-to-One with the Fluent API

To state the decision explicitly:

modelBuilder.Entity<Author>()
    .HasOne(a => a.Profile)
    .WithOne(p => p.Author)
    .HasForeignKey<AuthorProfile>(p => p.AuthorId);

HasOne and WithOne connect the two ends of the relationship. The part to watch is the generic parameter in HasForeignKey<AuthorProfile>. In one-to-many it was obvious which entity holds the foreign key; here it isn't, so you state the entity type explicitly. That parameter is exactly what decides the dependent.

Let's also look at whether the relationship is required. Author.Profile is nullable: an author can exist without a profile. AuthorProfile.AuthorId is an int: a profile can't exist without an author. That is why the schema has ON DELETE CASCADE; when the author is deleted, the profile goes too. An alternative is to make the profile's primary key its foreign key as well: AuthorProfile.Id becomes the author's Id directly, and there is no need for a separate AuthorId column and a unique index. Writing HasForeignKey<AuthorProfile>(p => p.Id) is all it takes.

Many-to-Many: Skip Navigations

Now back to the guessing question. With the Book and Tag model, EnsureCreated produced this:

CREATE TABLE "BookTag" (
    "BooksId" INTEGER NOT NULL,
    "TagsId" INTEGER NOT NULL,
    CONSTRAINT "PK_BookTag" PRIMARY KEY ("BooksId", "TagsId"),
    CONSTRAINT "FK_BookTag_Books_BooksId" FOREIGN KEY ("BooksId") REFERENCES "Books" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_BookTag_Tags_TagsId" FOREIGN KEY ("TagsId") REFERENCES "Tags" ("Id") ON DELETE CASCADE
);
CREATE INDEX "IX_BookTag_TagsId" ON "BookTag" ("TagsId");

Next to Books and Tags there is a third table. In a relational database many-to-many is always built with a table in the middle; since EF Core 5 you don't have to write the class representing that table. The table name combines the two entity names (BookTag), and the column names are the navigation name plus the key name (BooksId, TagsId). The primary key is the combination of both columns, so the same book-tag pair can't be added twice. Both foreign keys cascade: when a book or a tag is deleted, its link rows are deleted too.

Book.Tags and Tag.Books are called skip navigations, because they "skip" the join entity in the middle and go straight to the other side. EF Core defines that join entity itself behind the scenes: a shared-type entity of type Dictionary<string, object> that behaves like a property bag.

Adding is a graph insert again:

Tag architecture = new() { Name = "architecture" };
Tag refactoringTag = new() { Name = "refactoring" };

db.Books.Add(new Book { Title = "Refactoring", Tags = { refactoringTag, architecture } });
db.Books.Add(new Book { Title = "Patterns of Enterprise Application Architecture", Tags = { architecture } });
db.SaveChanges();

The log contains INSERTs for two books, two tags and three link rows. Here is the join table part of the EF Core 10 output:

      INSERT INTO "BookTag" ("BooksId", "TagsId")
      VALUES (@p1, @p2);
      INSERT INTO "BookTag" ("BooksId", "TagsId")
      VALUES (@p0, @p1);
      INSERT INTO "BookTag" ("BooksId", "TagsId")
      VALUES (@p0, @p1);

Querying Through Skip Navigations

The query never mentions the join table:

List<string> titles = db.Books
    .Where(b => b.Tags.Any(t => t.Name == "architecture"))
    .OrderBy(b => b.Title)
    .Select(b => b.Title)
    .ToList();

SELECT "b"."Title"
FROM "Books" AS "b"
WHERE EXISTS (
    SELECT 1
    FROM "BookTag" AS "b0"
    INNER JOIN "Tags" AS "t" ON "b0"."TagsId" = "t"."Id"
    WHERE ("b"."Id" = "b0"."BooksId") AND ("t"."Name" = 'architecture'))
ORDER BY "b"."Title"

EF Core wrote the join. The result: Patterns of Enterprise Application Architecture | Refactoring. EF Core 10 produces the same SQL without the unnecessary parentheses in the WHERE line.

The last part of the guessing question: removing a tag from a book.

Book book = db.Books.Include(b => b.Tags).Single(b => b.Title == "Refactoring");
Tag tag = book.Tags.Single(t => t.Name == "architecture");
book.Tags.Remove(tag);
db.SaveChanges();
Console.WriteLine($"Tags left in database: {db.Tags.Count()}");

      DELETE FROM "BookTag"
      WHERE "BooksId" = @p0 AND "TagsId" = @p1;
      SELECT changes();
Tags left in database: 2

What got deleted is the link row, not the tag. The architecture tag stays where it is and remains linked to the other book. Removing from the collection means "these two are no longer related"; if you really want to delete the tag, you write db.Tags.Remove(tag).

Naming the Join Table with UsingEntity

The BookTag name the convention picked may not fit your team's naming rules. The door to configuring the join entity is UsingEntity:

modelBuilder.Entity<Book>()
    .HasMany(b => b.Tags)
    .WithMany(t => t.Books)
    .UsingEntity(j => j.ToTable("BookTags"));

CREATE TABLE "BookTags" (
    "BooksId" INTEGER NOT NULL,
    "TagsId" INTEGER NOT NULL,
    CONSTRAINT "PK_BookTags" PRIMARY KEY ("BooksId", "TagsId"),
    CONSTRAINT "FK_BookTags_Books_BooksId" FOREIGN KEY ("BooksId") REFERENCES "Books" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_BookTags_Tags_TagsId" FOREIGN KEY ("TagsId") REFERENCES "Tags" ("Id") ON DELETE CASCADE
);

HasMany and WithMany are the two ends of the relationship, and the lambda you pass to UsingEntity is the join entity's builder. The table name changed and the constraint names followed. Nothing changed in the code; queries and inserts work exactly as before.

A Join Entity with a Payload

One day the question "when was this tag added to the book?" comes up. Now the join table holds more than two foreign keys, in other words a payload. That is when you write the join entity as a class:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public List<Tag> Tags { get; set; } = new();
    public List<BookTag> BookTags { get; set; } = new();
}

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

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

    public int TagId { get; set; }
    public Tag Tag { get; set; } = null!;

    public DateTime TaggedAt { get; set; }
}

We keep the skip navigations and introduce the join entity with EF Core 6's UsingEntity<TJoin> overload:

modelBuilder.Entity<Book>()
    .HasMany(b => b.Tags)
    .WithMany(t => t.Books)
    .UsingEntity<BookTag>(
        j => j.HasOne(bt => bt.Tag).WithMany(t => t.BookTags).HasForeignKey(bt => bt.TagId),
        j => j.HasOne(bt => bt.Book).WithMany(b => b.BookTags).HasForeignKey(bt => bt.BookId),
        j =>
        {
            j.ToTable("BookTags");
            j.HasKey(bt => new { bt.BookId, bt.TagId });
            j.Property(bt => bt.TaggedAt).HasDefaultValueSql("CURRENT_TIMESTAMP");
        });

The first lambda sets up the one-to-many from the join entity to the right side (Tag), the second to the left side (Book). The third configures the join entity itself: the table name, the composite key and the payload column. A many-to-many is really two one-to-many relationships stacked on top of each other; this overload makes that explicit.

CREATE TABLE "BookTags" (
    "BookId" INTEGER NOT NULL,
    "TagId" INTEGER NOT NULL,
    "TaggedAt" TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    CONSTRAINT "PK_BookTags" PRIMARY KEY ("BookId", "TagId"),
    CONSTRAINT "FK_BookTags_Books_BookId" FOREIGN KEY ("BookId") REFERENCES "Books" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_BookTags_Tags_TagId" FOREIGN KEY ("TagId") REFERENCES "Tags" ("Id") ON DELETE CASCADE
);

Now you can create a link in two ways. When you add through the skip navigation (Tags = { classic }), you can't give the payload a value; EF Core leaves TaggedAt out of the INSERT and the database default is written. When you add through the join entity, the payload is in your hands:

Book book = db.Books.Single(b => b.Title == "Refactoring");
Tag bestseller = new() { Name = "bestseller" };
book.BookTags.Add(new BookTag { Tag = bestseller, TaggedAt = new DateTime(2026, 11, 1) });
db.SaveChanges();

You can also query the join entity directly:

var rows = db.Set<BookTag>()
    .OrderBy(bt => bt.TaggedAt)
    .Select(bt => new { bt.Book.Title, Tag = bt.Tag.Name, bt.TaggedAt })
    .ToList();

Refactoring - bestseller - 2026-11-01
Refactoring - classic - 2026-11-03

The database supplied the date for classic, we supplied the one for bestseller. Queries through b.Tags keep working too; the skip navigations and the join entity live side by side.

As the join entity gains its own identity and business meaning, you should stop seeing it as a detail of a many-to-many. The OrderItem from the previous post is exactly that: it sits between Order and Book, but with its own Id, its quantity and the price at order time it is an entity in its own right. Modeling it with two one-to-many relationships rather than skip navigations is the right choice.

Common Pitfalls

Thinking that removing from a collection deletes. book.Tags.Remove(tag) only deletes the row in the join table. The tag itself and its links to other books stay in place.

Forgetting how renaming navigations affects the schema. The column names of a join table created by convention come from the navigation names (BooksId, TagsId). Renaming Tags to Labels means a column rename in a migration. Naming the join table with UsingEntity from the start reduces this surprise.

Setting up the dependent the wrong way round in one-to-one. If you put the foreign key on the wrong side, the relationship still works but its meaning changes. The side that is optional should be the principal and the side that depends on it the dependent. Here an author can exist without a profile, but a profile can't exist without an author.

Forgetting the payload's default. You can't give the payload a value for links added through skip navigations. If you haven't defined a default in the database, EF Core fills the column with the CLR default. When we removed the HasDefaultValueSql line and added a tag through the skip navigation, we got no error; TaggedAt was silently saved as 0001-01-01T00:00:00.

A Small Experiment

★☆☆ In the Book-Tag model, try adding the same tag to the same book twice. Where does the error come from: EF Core or the database? Which line of the schema causes it?

★★☆ Rebuild the one-to-one with a shared primary key: remove AuthorId from AuthorProfile and use HasForeignKey<AuthorProfile>(p => p.Id). Compare the resulting schema with the previous one; where did the unique index go?

★★★ Add a second payload to the payload model: public string? AddedBy { get; set; }. Add one link through the skip navigation and another through the join entity with AddedBy set. Then write the query that returns the book titles of only the links with AddedBy filled in, in two ways: through db.Set<BookTag>() and through db.Books with b.BookTags. Compare the SQL of the two queries.

Our model is now ready: entities, conventions, configuration and three kinds of relationships. But so far we have always built the database from scratch with EnsureCreated. In a real application the database has to be updated without losing data as the model changes. The next post is about migrations: migrations add and remove, database update, the anatomy of a migration file, generating SQL scripts and migration bundles.