Entity Framework Many To Many between two classes

Question

I am working on a simple MVC application using Entity Framework to track the scores of a Badminton league. I have the following two classes:

public class Game
{
    public int Id { get; set; }
    public Player Player1 { get; set; }
    public Player Player2 { get; set; }
    public int Player1Score { get; set; }
    public int Player2Score { get; set; }
}

and

public class Player
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string Surname { get; set; }
    public List<Game> Games { get; set; }
}

The problem I have is when I have an instance of a player the Games property is returning an empty list. When I request my list of players I use the following:-

var players = badmintonDB.Players.Include("Games").ToList();

From a search on SO I have attempted to override OnModelCreating. I have tried the following with and without the Map(). This creates another table in my database but it doesn't contain any records.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Player>().HasMany(p => p.Games).WithMany()
            .Map(m =>
            {
                m.ToTable("PlayerGames");
                m.MapLeftKey("Player_Id");
                m.MapRightKey("Game_Id");
            });

        base.OnModelCreating(modelBuilder);

    }

I cannot see where I am going wrong, whether I need to rethink the design of my POCO's or if I have the syntax wrong when overriding OnModelCreating.

Any help would be appreciated.

score 2 · Accepted Answer · answered Jan 22 '13 at 17:56

I'm not sure if that design can work the way you want it to. What if you made a new "Score" entity to tie players to games like so:

public class Game
{
    public int Id { get; set; }
    public virtual ICollection<Score> Scores { get; set; }
}

public class Player
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string Surname { get; set; }
    public virtual ICollection<Score> Scores { get; set; }
}

public class Score
{
    public int ScoreId { get; set; }
    public virtual  Player Player { get; set; }
    public virtual Game Game { get; set; }
    public int Score { get; set; }
}

Thanks Britton I used your thoughts when I redesigned my models. I now have a game entity which has a list of results. one for each player who took part in the game. This allows me to access all the linked information via EF. — seanzi, Jan 30 '13 at 19:55

Stephan Keller · Answer 2 · 2013-01-22T15:07:56.727

1

Update:
On thinking some more about it I am not sure if this can be done with automatic navigation properties at all. Reason is: you have two foreign keys in Game. So for a player to load its list of games EF would have to create a select on two foreign keys.

Old Answer (that I now think is wrong):
EF tries to autodetect navigational properties. Maybe this fails since Game has two Player.

Declare the navigation yourself:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder
         .Entity<Game>()
         .HasRequired(g => g.Player1)
         .WithMany()
         .WillCascadeOnDelete(false);

        modelBuilder
         .Entity<Game>()
         .HasRequired(g => g.Player2)
         .WithMany()
         .WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);

    }

edited Jan 22 '13 at 15:07

answered Jan 22 '13 at 13:35

Stephan Keller

1,623
11
13

Hello Stephan, Thanks for your advice. I made the changes you suggested to OnModelCreating however an exception is now being thrown: `System.Data.Entity.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'Game_Player1_Source' in relationship 'Game_Player1'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be '1'.` is there something else which I need to set? – seanzi Jan 22 '13 at 13:49
Hello Stephan, it appears that I may need to tweak my design to allow this to work. I have tried changing the Player1 and Player2 properties in the Game class to be integers which represent the Id from the Players table. However I still need a way of accessing the player objects. Any ideas? – seanzi Jan 22 '13 at 17:19
Hello 53AN. I think you need to treat Player one and two as a List or better, List<> of a new class "score". @Britton is on the right track there. – Stephan Keller Jan 23 '13 at 06:36

score 0 · Answer 3 · edited May 23 '17 at 11:52

0

Please try:

public virtual List<Game> Games { get; set; }

With code first API, you need to specify navigation properties as virtual if you want to use lazy loading. See C# EF Code First virtual keyword, what does it do?

edited May 23 '17 at 11:52

Community

1
1

answered Jan 22 '13 at 12:48

ken2k

48,145
10
116
176

Hello Ken, I changed the Games property to be virtual however the list is still empty. Even though I know I have games in my database for the given user. I think the virtual keyword allows for lazy-loading, in my current scenario I am always going to need the list of games for a given player. I think somehow I need to instruct EF to insert the values into the new PlayerGames table and this relationship would be enough for it to be able to populate the list of games. – seanzi Jan 22 '13 at 13:00

score 0 · Answer 4 · answered Jan 22 '13 at 13:44

public class Player
{
    public Player()
    {
        this.Games = new List<Game>();
    }

    public int Id { get; set; }
    public string FirstName { get; set; }
    public string Surname { get; set; }
    public List<Game> Games { get; set; }
}

var players = badmintonDB.Players; should return all players with all the games associated with them if lazy-loading is on and you did not modify any of your relations like you did with Map();.

But be aware when loading data with lazy-loading that it really depends on what you output. If you will, for example output all the data in JSON string it will load all games. If not, it won't bother since it will think that you won't need any games to show.

Entity Framework Many To Many between two classes

4 Answers4