Multiple foreign keys to same primary key table

entity-framework

Solution

Apparently EF doesn't detect your `int` properties like `AwayTeamId` as the foreign key for the navigation properties like `AwayTeam` because the primary key property in `Team` is not `Id` but `TeamId`. It would probably detect the FKs if they are named like `AwayTeamTeamId` or if the primary key property in `Team` has the name `Id`.

If you don't want to change those property names according to EF convention you can define the FKs with data annotations:

[ForeignKey("AwayTeam")]
public int AwayTeamId { get; set; }
public virtual Team AwayTeam { get; set; }

// the same for the other three FKs

Or Fluent API:

modelBuilder.Entity<Fixture>()
    .HasRequired(f => f.AwayTeam)
    .WithMany()
    .HasForeignKey(f => f.AwayTeamId)
    .WillCascadeOnDelete(false);

// the same for the other three FKs

I have disabled cascading delete because it will be enabled by default for a required relationship. But because you have two required relationships to the `Team` table (and the `Coach` table as well) it would result in two cascading delete paths from `Fixture` to `Team` and `Coach`. Multiple cascading delete paths are forbidden in SQL Server, so you must disable cascading delete for at least one of the two relationships between `Fixture` and `Team` (and between `Fixture` and `Coach`).

Problem

I have a table with multiple fields that are foreign keys to a primary key in another table. For example : ``` Fixture Id (PK) HomeTeamId (FK to Team.TeamId) AwayTeamId (FK to Team.TeamId) HomeTeamCoachId (FK to Coach.CoachId) AwayTeamCoachId (FK to Coach.CoachId) ``` Would it be better to separate this data into 2 tables HomeTeam and AwayTeam with a foreign key to FixtureId? This is currently what was generated by Entity Framework : ``` FixtureId PK HomeTeamId int AwayTeamId int HomeTeamCoachId int AwayTeamCoachId int AwayTeam_TeamId FK HomeTeam_TeamId FK AwayTeamCoach_CoachId FK HomeTeamCoach_CoachId FK ``` This was generated through this class : ``` public partial class Fixture { public int FixtureId { get; set; } //foreign key public int AwayTeamId { get; set; } //navigation properties public virtual Team AwayTeam { get; set; } //foreign key public int HomeTeamId { get; set; } //navigation properties public virtual Team HomeTeam { get; set; } //foreign key public int AwayCoachId { get; set; } //navigation properties public virtual Coach AwayCoach { get; set; } //foreign key public int HomeCoachId { get; set; } //navigation properties public virtual Coach HomeCoach { get; set; } } ``` Can anybody tell me if this is the correct way to do this? EDIT : In reply to Slauma So my classes would basically look like this? Or does the configuration in OnModelCreating mean I don't need some of the foreign key related code in my Fixture class? ``` protected override void OnModelCreating(DbModelBuilder modelBuilder) { // Entity Type Configuration modelBuilder.Configurations.Add(new TeamConfiguration()); modelBuilder.Configurations.Add(new CoachConfiguration()); modelBuilder.Configurations.Add(new FixtureConfiguration()); modelBuilder.Entity<Fixture>() .HasRequired(f => f.AwayTeam) .WithMany() .HasForeignKey(f => f.AwayTeamId) .WillCascadeOnDelete(false); modelBuilder.Entity<Fixture>() .HasRequired(f => f.HomeTeam) .WithMany() .HasForeignKey(f => f.HomeTeamId) .WillCascadeOnDelete(false); modelBuilder.Entity<Fixture>() .HasRequired(f => f.AwayCoach) .WithMany() .HasForeignKey(f => f.AwayCoachId) .WillCascadeOnDelete(false); modelBuilder.Entity<Fixture>() .HasRequired(f => f.HomeCoach) .WithMany() .HasForeignKey(f => f.HomeCoachId) .WillCascadeOnDelete(false); } public partial class Fixture { public int FixtureId { get; set; } public string Season { get; set; } public byte Week { get; set; } //foreign key public int AwayTeamId { get; set; } //navigation properties public virtual Team AwayTeam { get; set; } //foreign key public int HomeTeamId { get; set; } //navigation properties public virtual Team HomeTeam { get; set; } //foreign key public int AwayCoachId { get; set; } //navigation properties public virtual Coach AwayCoach { get; set; } //foreign key public int HomeCoachId { get; set; } //navigation properties public virtual Coach HomeCoach { get; set; } public byte AwayTeamScore { get; set; } public byte HomeTeamScore { get; set; } } ```

Original source