Many-to-Many Relationships: Skip Navigations, Join Entities, Payload Columns, and Ordering

Learning outcomes

Dispatchers want flexible labels such as safety, electrical, and customer-waiting. A work order can have many tags, and each tag can classify many work orders. No single FK column can represent that cardinality; the relational model needs a join row containing both foreign keys. EF Core can hide that join CLR type with skip navigations, or make it explicit when the association carries payload and lifecycle.

1. Many-to-many is two one-to-many relationships through a join row

The join table has one FK to work_orders and one FK to tags. A composite key on those two values normally prevents duplicate associations. EF's skip navigations let application code write workOrder.Tags.Add(tag), but EF still tracks and persists a join entity internally.

public sealed partial class WorkOrder{    public ICollection<Tag> Tags { get; } = new List<Tag>();}public sealed class Tag{    public int Id { get; private set; }    public string Name { get; private set; } = string.Empty;    public ICollection<WorkOrder> WorkOrders { get; } = new List<WorkOrder>();}

2. Configure a named skip-navigation join table

modelBuilder.Entity<WorkOrder>()    .HasMany(x => x.Tags)    .WithMany(x => x.WorkOrders)    .UsingEntity(        "work_order_tags",        right => right.HasOne(typeof(Tag))            .WithMany()            .HasForeignKey("tag_id"),        left => left.HasOne(typeof(WorkOrder))            .WithMany()            .HasForeignKey("work_order_id"),        join =>        {            join.HasKey("work_order_id", "tag_id");        });

String-based shared join configuration is compact, but it is less discoverable and more fragile if the association grows. Also, EF currently uses a dictionary-backed internal representation for convention-based shared joins; Microsoft explicitly warns not to depend on that implementation unless you configured it yourself.

CREATE TABLE work_order_tags (    work_order_id INTEGER NOT NULL,    tag_id INTEGER NOT NULL,    PRIMARY KEY (work_order_id, tag_id),    FOREIGN KEY (work_order_id) REFERENCES work_orders(work_order_id) ON DELETE CASCADE,    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE);

Exact constraint names and quoting are provider/version details. Review the generated migration instead of copying conceptual DDL into production.

3. Make the association an entity when it has payload

ServiceHub must know who applied a tag, when it was applied, and the order in which badges should appear. Those values belong to the relationship itself, not to WorkOrder or Tag. That is the signal to introduce an explicit join entity.

public sealed class WorkOrderTag{    public int WorkOrderId { get; private set; }    public WorkOrder WorkOrder { get; private set; } = null!;    public int TagId { get; private set; }    public Tag Tag { get; private set; } = null!;    public DateTime AppliedUtc { get; private set; }    public string AppliedBy { get; private set; } = string.Empty;    public int DisplayOrder { get; private set; }}

The composite key expresses one association per work-order/tag pair. If the domain needs tag history (same tag applied, removed, then reapplied), this key would be too restrictive; an association ID or temporal history entity would be more appropriate. Keys should reflect lifecycle.

4. Keep skip navigations and join navigations together

public sealed partial class WorkOrder{    public ICollection<Tag> Tags { get; } = new List<Tag>();    public ICollection<WorkOrderTag> WorkOrderTags { get; } = new List<WorkOrderTag>();}public sealed partial class Tag{    public ICollection<WorkOrder> WorkOrders { get; } = new List<WorkOrder>();    public ICollection<WorkOrderTag> WorkOrderTags { get; } = new List<WorkOrderTag>();}modelBuilder.Entity<WorkOrder>()    .HasMany(x => x.Tags)    .WithMany(x => x.WorkOrders)    .UsingEntity<WorkOrderTag>(        right => right.HasOne(x => x.Tag)            .WithMany(x => x.WorkOrderTags)            .HasForeignKey(x => x.TagId),        left => left.HasOne(x => x.WorkOrder)            .WithMany(x => x.WorkOrderTags)            .HasForeignKey(x => x.WorkOrderId),        join =>        {            join.ToTable("work_order_tags");            join.HasKey(x => new { x.WorkOrderId, x.TagId });            join.Property(x => x.AppliedUtc).HasColumnName("applied_utc");            join.Property(x => x.AppliedBy).HasColumnName("applied_by").HasMaxLength(80);            join.Property(x => x.DisplayOrder).HasColumnName("display_order");        });

This gives two views of the same association: skip navigations for “which tags?” and explicit join entities for payload. Do not independently manipulate both representations without understanding fixup; EF keeps them synchronized when tracked.

5. Ordering belongs to the association when the order is per work order

A global Tag.SortOrder would impose one order everywhere. ServiceHub needs different badge ordering for each work order, so DisplayOrder belongs on WorkOrderTag. Query the join entity when you need payload-aware ordering:

var badges = await db.Set<WorkOrderTag>()    .Where(x => x.WorkOrderId == workOrderId)    .OrderBy(x => x.DisplayOrder)    .Select(x => new    {        x.Tag.Name,        x.AppliedUtc,        x.AppliedBy,        x.DisplayOrder    })    .ToListAsync(ct);

6. Adding through a skip navigation creates join state

var order = await db.WorkOrders    .Include(x => x.Tags)    .SingleAsync(x => x.WorkOrderNumber == "WO-2026-000201", ct);var tag = await db.Tags.SingleAsync(x => x.Name == "safety", ct);order.Tags.Add(tag);Console.WriteLine(db.ChangeTracker.DebugView.LongView);await db.SaveChangesAsync(ct);

With a pure skip-navigation mapping, EF creates the join instance internally. With an explicit payload entity, adding only the skip navigation may not provide required payload values. If AppliedBy is required, construct/configure the join entity explicitly or provide defaults through a clearly documented mechanism.

7. Deliberately wrong design: required payload with no creation policy

join.Property(x => x.AppliedBy)    .IsRequired();// Later:order.Tags.Add(tag); // Who supplies AppliedBy?

The object operation expresses only association membership. It does not answer who applied it. If the join payload is required business data, expose a domain operation that creates WorkOrderTag explicitly with the actor/time/order.

public void ApplyTag(Tag tag, string actor, DateTime utcNow, int displayOrder){    WorkOrderTags.Add(new WorkOrderTag(        Id,        tag.Id,        utcNow,        actor,        displayOrder));}

For generated keys, creating a join with raw numeric IDs before both principals are persisted may need navigation-based construction instead. The exact constructor design should respect entity lifecycle; the point is to make payload ownership explicit.

8. Shared-type join entities are specialized, not magic

A shared-type entity can be useful for infrastructure joins without a dedicated CLR class, but string property names reduce refactor safety. Do not assume EF's implicit join CLR type will always be Dictionary<string, object>; configure it explicitly if code depends on that type.

modelBuilder.SharedTypeEntity<Dictionary<string, object>>(    "WorkOrderSkill",    b =>    {        b.ToTable("work_order_skills");        b.IndexerProperty<int>("WorkOrderId");        b.IndexerProperty<int>("SkillId");        b.HasKey("WorkOrderId", "SkillId");    });

9. Hands-on lab: promote a pure association into payload data

1. Add Tag and start with a pure many-to-many skip-navigation mapping.
2. Generate/review the migration; verify the composite join key and both FKs.
3. Seed deterministic tags.
4. Add and remove one tag through WorkOrder.Tags; inspect DebugView and generated insert/delete SQL.
5. Reset the lab database.
6. Promote the join to WorkOrderTag with AppliedUtc, AppliedBy, and DisplayOrder.
7. Generate a new migration and review how the join table changes.
8. Create one payload-aware association explicitly and query it ordered by DisplayOrder.
9. Attempt the broken required-payload/skip-only add and document the resulting validation/store failure if your mapping makes the payload required.
10. Inspect SQLite schema and foreign keys for work_order_tags.

Verification checklist

• The join row is visible in both EF tracker state and the database.
• The composite key prevents duplicate active associations.
• Payload fields live on the association, not duplicated on principals.
• Required payload has an explicit creation policy.
• No code depends on an undocumented implicit join CLR type.

10. Production judgment and bridge

Use skip navigations for truly payload-free associations. The moment the join carries business meaning, promote it to a named entity and review its key, lifecycle, cascade rules, indexes, and concurrency requirements like any other table. Do not hide audit or authorization-relevant association state behind convenience APIs.

Lesson 4 now examines what happens when principals, dependents, or relationships are removed: database cascades, client cascades, nulling, restrict/no-action behavior, conceptual nulls, and orphan deletion.

Authoritative references

• Many-to-many relationships - EF Core — skip navigations, explicit joins, payloads, shared joins, keys, and delete behavior
• Relationship conventions - EF Core — many-to-many discovery and join conventions
• Changing Foreign Keys and Navigations — tracked relationship and many-to-many fixup
• Saving Related Data - EF Core — inserting/removing related entities and relationship side effects
• SQLite foreign keys — join-table FK enforcement in the mandatory lab