Foreign key in two columns and navigation property

Question

I have one entity named "Team" and one named "Match"

In the "Match" entity I would like to have two properties, int HomeTeam, int AwayTeam which would contain the TeamId as a FK.

Is it possible to use a navagation property to fetch all matches(home and away games) for a specifik team with this setup?

If so, how is that done? I have only used navigation properties when the Id is in one column only.

Answer 1

Your classes should look something like this:

public class Team
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Match
{
    public int Id { get; set; }
    public string Name { get; set; }

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

And your context like this:

public class MyEntities : DbContext
{
    public DbSet<Team> Teams { get; set; }
    public DbSet<Match> Matches { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Team>().ToTable("Team");
        modelBuilder.Entity<Match>().ToTable("Match");

        modelBuilder.Entity<Match>().HasOptional(x => x.HomeTeam).WithMany().Map(m => m.MapKey("HomeTeamId"));
        modelBuilder.Entity<Match>().HasOptional(x => x.AwayTeam).WithMany().Map(m => m.MapKey("AwayTeamId"));
    }
}

Basically you are using Fluent API to describe how your tables and relationships are configured.

EDIT

To answer your example query, get a list of all matches (both home and away) played by Team ID 1:

MyEntities db = new MyEntities();

List<Match> matches = db.Matches.Where(
    t => t.HomeTeam.Id == 1 || t.AwayTeam.Id == 1
).ToList();