.netentity-framework-coremany-to-many

.NET EF Core : how to correctly insert and update a model in a many to many relationship with a join table with a payload?


I am writing a .NET CRUD application with layered architecture with EF Core for ORM. I have two models (Players and Teams) stored in the database with a join table between them (Player in Team).

The join table isn't just to designate the many to many relationship, but also stores the payload of what position within the team a player has (ignore the Position in the Player itself - it's basically a "preferred" position, it might not coincide with the position in the team).

My question is: how to correctly insert and update a Team model with Players related to it? Do I need to basically duplicate the player lists in Players and TeamPlayers in the Team model (with added Position in TeamPlayers)? Or do I just need to populate the TeamPlayers with relevant Players and their positions and the ORM will take it from there?

Player model:

public class Player : IProfile
{
    public Guid Id { get; set; }
    public Guid UserId { get; set; }

    public string Name { get; set; }
    public string Description { get; set; }

    public bool Displayed { get; set; }

    public int PositionId { get; set; }
    public Position Position { get; set; }

    public DateTime UpdatedAt { get; set; }

    public ICollection<Hero> Heroes { get; set; } = [];
    public ICollection<Team> Teams { get; set; } = [];
    public ICollection<TeamPlayer> TeamPlayers { get; set; } = [];
}

Team model:

public class Team : IProfile, IUpdateable
{
    public Guid Id { get; set; }
    public Guid UserId { get; set; }

    public string Name { get; set; }
    public string Description { get; set; }

    public bool Displayed { get; set; }

    public DateTime UpdatedAt { get; set; }

    public int PlayerCount { get; set; }

    public ICollection<Player> Players { get; set; } = [];
    public ICollection<TeamPlayer> TeamPlayers { get; set; } = [];
}

TeamPlayer:

public class TeamPlayer
{
    public Guid TeamId { get; set; }
    public Team Team { get; set; }

    public Guid PlayerId { get; set; }
    public Player Player { get; set; }

    public int PositionId { get; set; }
    public Position Position { get; set; }
}

Solution

  • When using many-to-many relationships you have two options with an either-or condition as to which you can use. Implicit joining entity or explicit joining entity.

    Implicit:

    public class Player
    {
        // ...
        public ICollection<Team> Teams { get; protected set; } = [];
    }
    
    public class Team
    {
        // ...
        public ICollection<Player> Players { get; protected set; } = [];
    }
    

    Explicit:

    public class Player
    {
        // ...
        public ICollection<PlayerTeam> PlayerTeams { get; protected set; } = [];
    }
    
    public class Team
    {
        // ...
        public ICollection<PlayerTeams> PlayerTeams { get; protected set; } = [];
    }
    

    Implicit relationships can be inferred by EF or have an entity definition defined so you can control things like the table name etc. These are generally preferred since you just interact with the Teams of a Player or the Players of a Team without messing around with the PlayerTeam joining entity. However, this only works as long as the relationship is pure, meaning just the two FKs.

    As soon as you introduce something additional to the relationship (like a Position, IsActive, etc.) then you need to switch to the explicit mapping. What you can do to assist with working in the explicit mode is leverage unmapped helpers to expose the Player.Teams and Team.Players collection, however you cannot use these properties in Linq2EF query expressions:

    public class Player
    {
        // ...
        public ICollection<PlayerTeam> PlayerTeams { get; protected set; } = [];
        [NotMapped]
        public IReadOnlyCollection<Team>?  Teams => PlayerTeams != null 
            ? PlayerTeams.Select(x => x.Team).AsReadOnly()
            : default;
    }
    
    public class Team
    {
        // ...
        public ICollection<PlayerTeams> PlayerTeams { get; protected set; } = [];
        [NotMapped]
        public IReadOnlyCollection<Player>? Players => PlayerTeams != null 
            ? PlayerTeams.Select(x => x.Player).AsReadOnly()
            : default;
    }
    

    For the "AsReadOnly()" implementation above, this is a simple extension method to wrap the enumeration of the collection. Credit (upvote) the original here: How to turn ICollection<T> into IReadOnlyCollection<T>?

    Any querying expressions or modifications need to be done through the PlayerTeams collection.

    The two are mutually exclusive so AFAIK you cannot have both a mapped Players collection and TeamPlayers on a Team as EF would be expecting two completely separate mappings. (I.e. Players would use a different joining entity)

    On a side note, when working with collection navigation properties you should avoid public setters to discourage any external code re-initializing the collection breaking EF proxies.