EF 4.1 Code First Mapping two columns in one table to list

Question

I'm having an issue figuring out how to make this work for EF 4.1 Code First. I've looked around and found a similar problem, but I couldn't get it to work for me and it sounds like it didn't get answered for this person either.

Here is a simplified version of the two objects in question.

public class Team
{
  public int TeamId {get; set;}
  public virtual IList<Game> Games {get; set;}
}

public class Game
{
  public int GameId {get; set; }
  public int AwayTeamId {get; set;}
  public int HomeTeamId {get; set;}

  public virtual Team HomeTeam { get; set; }
  public virtual Team AwayTeam { get; set; }  
}

And here is my code for registering the FKs

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Game>()
        .HasRequired(a => a.HomeTeam)
        .WithMany()
        .HasForeignKey(u => u.HomeTeamId).WillCascadeOnDelete(false);

    modelBuilder.Entity<Game>()
        .HasRequired(a => a.AwayTeam)
        .WithMany()
        .HasForeignKey(u => u.AwayTeamId).WillCascadeOnDelete(false);
}

I want to bring back all games (home or away) that a team is in. Right now EF is creating a TeamId column in my database that never gets populated. If what I want is impossible, then I could do lists of HomeGames and AwayGames and another list of Games that is a combination of the two, but I'd like to try and avoid it if possible. I'm still learning this so any extra explanations or tips would be appreciated.

Here was a similar question: stackoverflow.com/questions/6155953/… Basically your own proposal is the right way to go. It's impossible to map two endpoints on one side to a single endpoint on the other side of an association. — Slauma
– Slauma, Commented Jul 3, 2011 at 12:06

btt · Accepted Answer · 2011-07-03 14:33:23Z

1

What you're asking of EF is a read only collection, i.e., an add operation would be meaningless for such a collection since EF wouldnt know into which table to insert, which I believe is unsupported.

Judging by your problem description, I would so something similar to your own suggestion and create a method such as GetGames() which would return just a unioned set of both home and away games. I think this is conceptually cleaner anyway.

edited Jul 3, 2011 at 14:33

answered Jul 3, 2011 at 4:24

btt

4226 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ladislav Mrnka · Accepted Answer · 2011-07-03 19:22:05Z

0

EF is not able to map two relations into single navigation property. Your additional TeamId column is created because Games navigation property is considered as separate relation (your mapping says that neither HomeTeam or AwayTeam are part of that relation) so you instructed EF to create three relations for your.

You must define your model this way:

public class Team
{
  public int TeamId {get; set;}
  public virtual ICollection<Game> HomeGames { get; set; }
  public virtual ICollection<Game> AwayGames { get; set; }
}

And mapping must be:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Game>()
        .HasRequired(a => a.HomeTeam)
        .WithMany(t => t.HomeGames)
        .HasForeignKey(u => u.HomeTeamId).WillCascadeOnDelete(false);

    modelBuilder.Entity<Game>()
        .HasRequired(a => a.AwayTeam)
        .WithMany(t => t.AwayGames)
        .HasForeignKey(u => u.AwayTeamId).WillCascadeOnDelete(false);
}

Now if you want all team's games you can use simply:

var games = team.HomeGames.Concat(team.AwayGames);

You can wrap this into method returning IEnumerable or into property with just getter returning IEnumerable.

answered Jul 3, 2011 at 19:22

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

1 Comment

schwechel Over a year ago

This is exactly the code that I ended up implementing, with a property called GetGames that returned an IEnumerable of the combined HomeGames and AwayGames. I did stumble because at first I used IList instead of IEnumerable and it was still creating the TeamId column in the database. Once I switched it to IEnumerable, the column stopped coming back.

Collectives™ on Stack Overflow

EF 4.1 Code First Mapping two columns in one table to list

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related