EF Many to Many Relationship on 3 Tables(Fluent API)

Question

I have 3 tables that needs to be combined in one table. On below you can see the codes.

The problem is each player could play on one or more team on each tournament. Players and teams arranger by every new tournament.

So how can i map it with fluent api or maybe there is a better way to solve it. Thanks from now on.

 public class Player
    {
        public int PlayerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Phone { get; set; }
        public DateTime TimeStamp { get; set; }
        public ICollection<Team> Teams { get; set; }
        public ICollection<Tournament> Tournaments { get; set; }

    }

public class Team
    {
        public int TeamID { get; set; }
        public string Name { get; set; }
        public DateTime TimeStamp { get; set; }
        public ICollection<Player> Players { get; set; }
        public ICollection<Tournament> Tournaments { get; set; }
    }

public class Tournament
    {
        public int TournamentID { get; set; }
        public DateTime Date { get; set; }
        public int PlaceID { get; set; }
        public DateTime TimeStamp { get; set; }
        public virtual Place Place { get; set; }
        public ICollection<Player> Players { get; set; }
        public ICollection<Team> Teams { get; set; }
    }



  public class BPContext : DbContext
    {
        public DbSet<Tournament> Tournaments { get; set; }
        public DbSet<Place> Places { get; set; }
        public DbSet<Table> Tables { get; set; }
        public DbSet<Player> Players { get; set; }
        public DbSet<Team> Teams { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        }
    }

Why would you combine everything into 1 table? – Sidharth Mudgal Oct 01 '12 at 21:48 — Sidharth Mudgal, Oct 01 '12 at 21:48

score 1 · Accepted Answer · answered Oct 02 '12 at 05:19

I don't see what are you trying to accomplish with this. You have tables that contain all players, all teams and all tournaments.

I suppose there will be a match to be played? What you can do is create another table Matches, to use entity similar to this:

public class Match
{

 [Key]
 public int MatchId {get;set;}

 [ForeignKey("Tournament")]
 public int TournamentId {get;set;}

 [InverseProperty("Matches")]
 public virtual List<Team> Teams {get;set;}

 [InverseProperty("Matches")]
 public virtual List<Player> Players {get;set;}

 [InverseProperty("Matches")]
 public virtual Tournament Tournament {get;set;}
}

This new entity holds all 3 previous entities. However you have to modify previous ones to include these changes:

public class Player
{
    public int PlayerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Phone { get; set; }
    public DateTime TimeStamp { get; set; }

    [InverseProperty("Players")]
    public virtual List<Match> Matches { get; set; }

    [InverseProperty("Players")]
    public virtual List<Team> Teams {get;set;}
}

Getting all tournaments for player can be done with LINQ : ctx.Players.Where(x => x.PlayerId == 15).Matches.Select(x => x.TournamentId).

If you want to see all the players in a tournament: ctx.Matches.Where(x => x.TournamentId ==15).Players.Select(x => x.Name).

public class Team
{
    public int TeamID { get; set; }
    public string Name { get; set; }
    public DateTime TimeStamp { get; set; }

    [InverseProperty("Teams")]
    public List<Match> Matches {get;set;}

    [InverseProperty("Teams")]
    public List<Player> Players {get;set;}
}

public class Tournament
{
    public int TournamentID { get; set; }
    public DateTime Date { get; set; }
    public int PlaceID { get; set; }
    public DateTime TimeStamp { get; set; }
    public virtual Place Place { get; set; }

    [InverseProperty("Tournament")]
    public virtual List<Match> Matches {get;set;}
}

EF Many to Many Relationship on 3 Tables(Fluent API)

1 Answers1