Many-to-many relationship with the "Join table" that has extra data

Question

I would like to ask if anyone can help me with EF Core 5. I have two tables that are in "many-to-many" relationship: on the Join table, in addition to the columns that act as foreign keys I also have other columns that I would like to map in EF Core. The only solution I can think of is to create the relationship as it was done in EF Core 3, that is to use a "one to many" relationship with the join table.

Answer 1

The only solution I can think of is to create the relationship as it was done in EF Core 3, that is to use a "one to many" relationship with the join table.

You can have your cake and eat it too.

EF Core 5 supports custom linking entities and using skip-level navigation at the same time.

There is an example in the docs where the linking entity is in the model, has additional properties, but the main entities skip over the linking entity with Collection Navigation Properties.

internal class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> options)
        : base(options)
    {
    }

    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasMany(p => p.Tags)
            .WithMany(p => p.Posts)
            .UsingEntity<PostTag>(
                j => j
                    .HasOne(pt => pt.Tag)
                    .WithMany(t => t.PostTags)
                    .HasForeignKey(pt => pt.TagId),
                j => j
                    .HasOne(pt => pt.Post)
                    .WithMany(p => p.PostTags)
                    .HasForeignKey(pt => pt.PostId),
                j =>
                {
                    j.Property(pt => pt.PublicationDate).HasDefaultValueSql("CURRENT_TIMESTAMP");
                    j.HasKey(t => new { t.PostId, t.TagId });
                });
    }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public ICollection<Tag> Tags { get; set; }
    public List<PostTag> PostTags { get; set; }
}

public class Tag
{
    public string TagId { get; set; }

    public ICollection<Post> Posts { get; set; }
    public List<PostTag> PostTags { get; set; }
}

public class PostTag
{
    public DateTime PublicationDate { get; set; }

    public int PostId { get; set; }
    public Post Post { get; set; }

    public string TagId { get; set; }
    public Tag Tag { get; set; }
}

PostTag is a regular entity, and you can access it with db.Set<PostTag>() or from a Post or a Tag. Note the (optional) navigation properties from Post and Tag to PostTag.