Entity Framework Code First - two Foreign Keys from same table

Question

I've just started using EF code first, so I'm a total beginner in this topic.

I wanted to create relations between Teams and Matches:

1 match = 2 teams (home, guest) and result.

I thought it's easy to create such a model, so I started coding:

public class Team
{
    [Key]
    public int TeamId { get; set;} 
    public string Name { get; set; }

    public virtual ICollection<Match> Matches { get; set; }
}


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

    [ForeignKey("HomeTeam"), Column(Order = 0)]
    public int HomeTeamId { get; set; }
    [ForeignKey("GuestTeam"), Column(Order = 1)]
    public int GuestTeamId { get; set; }

    public float HomePoints { get; set; }
    public float GuestPoints { get; set; }
    public DateTime Date { get; set; }

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

And I get an exception:

The referential relationship will result in a cyclical reference that is not allowed. [ Constraint name = Match_GuestTeam ]

How can I create such a model, with 2 foreign keys to the same table?

Breastplate

Answer 1

Try this:

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

    public virtual ICollection<Match> HomeMatches { get; set; }
    public virtual ICollection<Match> AwayMatches { get; set; }
}

public class Match
{
    public int MatchId { get; set; }

    public int HomeTeamId { get; set; }
    public int GuestTeamId { get; set; }

    public float HomePoints { get; set; }
    public float GuestPoints { get; set; }
    public DateTime Date { get; set; }

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


public class Context : DbContext
{
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Match>()
                    .HasRequired(m => m.HomeTeam)
                    .WithMany(t => t.HomeMatches)
                    .HasForeignKey(m => m.HomeTeamId)
                    .WillCascadeOnDelete(false);

        modelBuilder.Entity<Match>()
                    .HasRequired(m => m.GuestTeam)
                    .WithMany(t => t.AwayMatches)
                    .HasForeignKey(m => m.GuestTeamId)
                    .WillCascadeOnDelete(false);
    }
}

Primary keys are mapped by default convention. Team must have two collection of matches. You can't have single collection referenced by two FKs. Match is mapped without cascading delete because it doesn't work in these self referencing many-to-many.

Cockfight

What if two teams are allowed to play only once? – Ofeliaofella

@NickW: That is something you have to handle in your application and not in the mapping. From the mapping perspective, pairs are allowed to play twice (each is guest and home once). – Cockfight

I have a similar model. What is the proper way to handle cascade delete if a team is removed? I looked into creating an INSTEAD OF DELETE trigger but not sure if there is a better solution? I would prefer to handle this in the DB, not the application. – Dualpurpose

@MikeSheehan I am facing same issue with delete. Have you resolved cascade delete problem? – Eternal

I also saw this (#19865474) as a solution. It uses the 'inverse property' annotation. Is there any negative to that versus what is done here? – Reimburse

@mrshickadance: It is the same. One approach uses fluent API and another data annotations. – Cockfight

If I use WillCascadeOnDelete false then If I want to delete the Team Then it is throwing error. A relationship from the 'Team_HomeMatches' AssociationSet is in the 'Deleted' state. Given multiplicity constraints, a corresponding 'Team_HomeMatches_Target' must also in the 'Deleted' state. – Moiety

I added [JsonIgnore] to both ICollections' and removed virtual from both referencing foreign keys to avoid problems with loops. – Gaudy

After reading this answer it made total sense to me. I'm actually kind of ashamed i didn't realize straight away that i cannot retrieve two kinds of information in a single collection. Quite obvious i'd need two. Thanks m8. upvoted – Wilden

Looking at table definition after use this method.There isn't any foreign key defined for HomeTeam And GuestTeam .Instead they defined as NONCLUSTERED INDEX. – Chianti

Why there's a cyclic problem in the first place? If I delete a Team, where's the cycle in deleting the Match? – Lioness

Answer 2

It's also possible to specify the ForeignKey() attribute on the navigation property:

[ForeignKey("HomeTeamID")]
public virtual Team HomeTeam { get; set; }
[ForeignKey("GuestTeamID")]
public virtual Team GuestTeam { get; set; }

That way you don't need to add any code to the OnModelCreate method

Negrito

I get the same exception either way. – Disbelieve

This is my standard way of specifying foreign keys which works for all cases EXCEPT when an entity contains more than one nav property of the same type (similar to the HomeTeam and GuestTeam scenario), in which case EF gets confused in generating the SQL. Solution is to add code to OnModelCreate as per the accepted answer as well as the two collections for both sides of the relationship. – Toast

i use onmodelcreating in all cases except the mentioned case , i use the data annotation foreign key , as well i don't know why it is not accepted !! – Batchelder

Answer 3

I know it's a several years old post and you may solve your problem with above solution. However, i just want to suggest using InverseProperty for someone who still need. At least you don't need to change anything in OnModelCreating.

The below code is un-tested.

public class Team
{
    [Key]
    public int TeamId { get; set;} 
    public string Name { get; set; }

    [InverseProperty("HomeTeam")]
    public virtual ICollection<Match> HomeMatches { get; set; }

    [InverseProperty("GuestTeam")]
    public virtual ICollection<Match> GuestMatches { get; set; }
}


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

    public float HomePoints { get; set; }
    public float GuestPoints { get; set; }
    public DateTime Date { get; set; }

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

You can read more about InverseProperty on MSDN: https://msdn.microsoft.com/en-us/data/jj591583?f=255&MSPPError=-2147217396#Relationships

Banjermasin

Thanks for this answer, however it makes the Foreign Key columns nullable in the Match table. – Stopoff

This worked great for me in EF 6 where nullable collections were needed. – Shericesheridan

If you want to avoid fluent api (for whatever reason #differentdiscussion) this works fantastically. In my case I needed to include an additional foriegnKey annotation on the "Match" entity, because my fields/tables have strings for PK's. – Humphrey

This worked greatly for me. Btw. if you do not want the columns nullable you can just specify foreign key with the [ForeignKey] attribute. If the key is not nullable then you are all set. – Anthurium

Answer 4

You can try this too:

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

    [ForeignKey("HomeTeam"), Column(Order = 0)]
    public int? HomeTeamId { get; set; }
    [ForeignKey("GuestTeam"), Column(Order = 1)]
    public int? GuestTeamId { get; set; }

    public float HomePoints { get; set; }
    public float GuestPoints { get; set; }
    public DateTime Date { get; set; }

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

When you make a FK column allow NULLS, you are breaking the cycle. Or we are just cheating the EF schema generator.

In my case, this simple modification solve the problem.

D

Caution readers. Although this might work-around the schema definition problem, it alters the semantics. It is probably not the case that a Match can be had without two teams. – Browse

Answer 5

InverseProperty in EF Core makes the solution easy and clean.

InverseProperty

So the desired solution would be:

public class Team
{
    [Key]
    public int TeamId { get; set;} 
    public string Name { get; set; }

    [InverseProperty(nameof(Match.HomeTeam))]
    public ICollection<Match> HomeMatches{ get; set; }

    [InverseProperty(nameof(Match.GuestTeam))]
    public ICollection<Match> AwayMatches{ get; set; }
}


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

    [ForeignKey(nameof(HomeTeam)), Column(Order = 0)]
    public int HomeTeamId { get; set; }
    [ForeignKey(nameof(GuestTeam)), Column(Order = 1)]
    public int GuestTeamId { get; set; }

    public float HomePoints { get; set; }
    public float GuestPoints { get; set; }
    public DateTime Date { get; set; }

    public Team HomeTeam { get; set; }
    public Team GuestTeam { get; set; }
}

Blane

Why do we need 'Column(Order = 0)'? This solved my issue by the way – Gladiatorial

It is used to order columns. Copied from the original question. It has nothing to do with Inverse Property. – Blane

Answer 6

This is because Cascade Deletes are enabled by default. The problem is that when you call a delete on the entity, it will delete each of the f-key referenced entities as well. You should not make 'required' values nullable to fix this problem. A better option would be to remove EF Code First's Cascade delete convention:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>(); 

It's probably safer to explicitly indicate when to do a cascade delete for each of the children when mapping/config. the entity.

Zannini

So what is it after this is executed? Restrict instead of Cascade? – Disbelieve

Answer 7

I know this is pretty old question but coming here in 2021 with EF Core > 3 solution below worked for me.

1. Make sure to make foreign keys nullable

2. Specify default behavior on Delete

   public class Match 
   { 
      public int? HomeTeamId { get; set; }
      public int? GuestTeamId { get; set; }
   
      public float HomePoints { get; set; }
      public float GuestPoints { get; set; }
      public DateTime Date { get; set; }
   
      public Team HomeTeam { get; set; }
      public Team GuestTeam { get; set; }
   }
   
   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
       modelBuilder.Entity<Match>()
                   .HasRequired(m => m.HomeTeam)
                   .WithMany(t => t.HomeMatches)
                   .HasForeignKey(m => m.HomeTeamId)
                   .OnDelete(DeleteBehavior.ClientSetNull);
   
       modelBuilder.Entity<Match>()
                   .HasRequired(m => m.GuestTeam)
                   .WithMany(t => t.AwayMatches)
                   .HasForeignKey(m => m.GuestTeamId)
                   .OnDelete(DeleteBehavior.ClientSetNull);
   }
   

Anadromous