Entity Framework Core Multiple Many-to-many with linking table (join entity)

Question

How to add multiple many-to-many relationships between Entities in EF Core 5.x, with join entity (linking tables)?

What I what is two many-to-many relationships between Card and Game. One relationship is not a problem but two (or more) I'm not able to configure it correctly.

What's working at the moment for me is that I created two different classes DeckGameCard and TableGameCard (join entity), which forces EF to create two tables. The classes are the same except for the name. Is it possible to have only one class (join entity) and two linking tables in the database?

What I want is this:

public class Game
{
    [Key]
    public int Id { get; set; }
    
    public ICollection Deck { get; set; }
    
    public ICollection OnTable { get; set; }
    ...

But at the moment this (the code below) is my solution (not optimal, because of code duplication in DeckGameCard and TableGameCard).

public class DeckGameCard
{
    public int GameId { get; set; }
    public Game Game { get; set; }
    
    public int Order { get; set; }

    public int CardId { get; set; }
    public Card Card { get; set; }
}

public class TableGameCard
{
    public int GameId { get; set; }
    public Game Game { get; set; }
    
    public int Order { get; set; }

    public int CardId { get; set; }
    public Card Card { get; set; }
}


public class Game
{
    [Key]
    public int Id { get; set; }
    
    public ICollection Deck { get; set; }
    
    public ICollection OnTable { get; set; }
    
    [Required]
    public int CardIndex { get; set; }
    
    [Required]
    public int PlayerId { get; set; }
    [Required]
    public Player Player { get; set; }
}

public class Card : IEntity
{
    [Key]
    public int Id { get; set; }

    [Required]
    public Shape Shape { get; set; }
    [Required]
    public Fill Fill { get; set; }
    [Required]
    public Color Color { get; set; }
    [Required]
    public int NrOfShapes { get; set; }
    
    public ICollection Deck { get; set; }
    
    public ICollection OnTable { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity()
        .HasKey(x => new {x.GameId, x.CardId});

    modelBuilder.Entity()
        .HasOne(gc => gc.Card)
        .WithMany(b => b.Deck)
        .HasForeignKey(gc => gc.CardId);

    modelBuilder.Entity()
        .HasOne(gc => gc.Game)
        .WithMany(g => g.Deck)
        .HasForeignKey(gc => gc.GameId);


    modelBuilder.Entity()
        .HasKey(x => new {x.GameId, x.CardId});

    modelBuilder.Entity()
        .HasOne(gc => gc.Card)
        .WithMany(b => b.OnTable)
        .HasForeignKey(bc => bc.CardId);

    modelBuilder.Entity()
        .HasOne(gc => gc.Game)
        .WithMany(g => g.OnTable)
        .HasForeignKey(gc => gc.GameId);
}

user_5202563 · Answer

Yes, it's possible by using the EF Core 5.0 introduced Shared-type entity types, but not sure it's worth since the type of an object no more uniquely identifies the entity type, so most if not all generic and non generic entity services (methods) of DbContext won't work, and you have to use the corresponding DbSet methods, obtaining it using the Set(name) overload. And there is no equivalent of Entry method, so you might have problems with change tracking in disconnected scenarios.

With that being said, here is how it can be done at model level.

Given the model similar to:


public class Game
{
    public int Id { get; set; }
    // ...
    public ICollection Deck { get; set; }
    public ICollection OnTable { get; set; }
}

public class Card
{
    public int Id { get; set; }
    // ...
    public ICollection Deck { get; set; }
    public ICollection OnTable { get; set; }
}

public class GameCard
{
    public int GameId { get; set; }
    public Game Game { get; set; }
    public int Order { get; set; }
    public int CardId { get; set; }
    public Card Card { get; set; }
}

it can be configured as follows:


modelBuilder.SharedTypeEntity("DeckGameCard", builder =>
{
    builder.ToTable("DeckGameCard");
    builder.HasKey(e => new { e.GameId, e.CardId });
    builder.HasOne(e => e.Card).WithMany(e => e.Deck);
    builder.HasOne(e => e.Game).WithMany(e => e.Deck);
});

modelBuilder.SharedTypeEntity("TableGameCard", builder =>
{
    builder.ToTable("TableGameCard");
    builder.HasKey(e => new { e.GameId, e.CardId });
    builder.HasOne(e => e.Card).WithMany(e => e.OnTable);
    builder.HasOne(e => e.Game).WithMany(e => e.OnTable);
});

or since the only difference is the entity (table) name and the corresponding collection navigation properties, the configuration can be factored out to a method similar to


static void GameCardEntity(
    ModelBuilder modelBuilder, string name,
    Expression>> cardCollection,
    Expression>> gameCollection,
    string tableName = null
)
{
    var builder = modelBuilder.SharedTypeEntity(name);
    builder.ToTable(tableName ?? name);
    builder.HasKey(e => new { e.GameId, e.CardId });
    builder.HasOne(e => e.Card).WithMany(cardCollection);
    builder.HasOne(e => e.Game).WithMany(gameCollection);
}

so the configuration becomes simply

GameCardEntity(modelBuilder, "DeckGameCard", c => c.Deck, g => g.Deck);
GameCardEntity(modelBuilder, "TableGameCard", c => c.OnTable, g => g.OnTable);

This should answer your concrete question. But again, make sure you understand the potential problems with it. And compare to "straight forward" solution achieving the same reusability with base class (not entity) without the above drawbacks, e.g.

public class Game
{
    public int Id { get; set; }
    // ...
    public ICollection Deck { get; set; }
    public ICollection OnTable { get; set; }
}

public class Card
{
    public int Id { get; set; }
    // ...
    public ICollection Deck { get; set; }
    public ICollection OnTable { get; set; }
}

public abstract class GameCard
{
    public int GameId { get; set; }
    public Game Game { get; set; }
    public int Order { get; set; }
    public int CardId { get; set; }
    public Card Card { get; set; }
}
public class DeskGameCard : GameCard { }
public class TableGameCard : GameCard { }

with only fluent configuration needed for composite PK

modelBuilder.Entity().HasKey(e => new { e.GameId, e.CardId });
modelBuilder.Entity().HasKey(e => new { e.GameId, e.CardId });

Entity Framework Core Multiple Many-to-many with linking table (join entity)

1 Answers