c# - force the creation of an intermediate table `Blog_Post`

Question

Is there a way (ef-core-5.0) to force the creation of an intermediate table Blog_Post for the following code?

Blog <= 0,n => Blog_Post <= 1,1 => Post

the code is the following

class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

    }
}

public class Blog
{
    public int Id { get; set; }
    public string Url { get; set; }
}

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

From one side, I want to separate concerns, and not add too much DB stuff on the business classes. So I would prefer do not use as much as possible an explicit DB configuration, attributes, ad-hoc table classes etc, or probably better a Fluent configuration...

From another, there's no many to many relation between Blog and Post.

However, such link tables are necessary to be built for a specific reason in the DB.

Answer 1

With EFCore 5 if you declare the relationship between A - B as .HasMany .WithMany EF will create the joining table automatically. If you just want to control the creation/naming of the joining you can use .UsingEntity after .WithMany to define an entity to outline the join. Note that Entity A can still contain an ICollection<B> while B contains an ICollection<A> respectively, not A_B. See: https://dev.to/ruben_j/many-to-many-relations-in-entity-framework-core-3-1-and-5-59ei About 1/2 way through he covers off EF Core 5 including UsingEntity.

If you do want more control and to expose A_B so that you can have additional properties, then the relationship between A - B needs to be updated to use A_B to form effectively a one-to-many-to-one form of navigation:

public class A
{
    [Key]
    public int AId { get; set; }
    public virtual ICollection<AB> ABs { get; internal set; } = new List<AB>();
}

public class B
{
    [Key]
    public int BId { get; set; }
    public virtual ICollection<AB> ABs { get; internal set; } = new List<AB>();
}

[Table("A_B")]
public class AB
{
    [Key, Column(Order=0), ForeignKey("A")]
    public int AId { get; set; }
    [Key, Column(Order=1), ForeignKey("B")]
    public int BId { get; set; }

    public virtual A { get; set; }
    public virtual B { get; set; }
}

This approach allows you to associate additional columns into AB such as tracking CreatedAt/By ModifiedAt/By for when associations are created/updated, or other properties. This doesn't require UsingEntity when done this way. This is more in line with how to create many-to-many relationships in EFCore prior to EF Core 5.