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?

Solution 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.

Solution 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

Solution 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

Solution 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.

Solution 5

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.

View more solutions

Comments

[ca9163d9]

What if two teams are allowed to play only once?

[Ladislav Mrnka]

@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).

[Woodchipper]

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.

[py3r3str]

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

[mrshickadance]

I also saw this (stackoverflow.com/questions/19864974/…) as a solution. It uses the 'inverse property' annotation. Is there any negative to that versus what is done here?

[Ladislav Mrnka]

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

[Rupesh Kumar Tiwari]

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.

[N8allan]

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.

[CularBytes]

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

[Jo Smo]

I get the same exception either way.

[Jo Smo]

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

[v1n1akabozo]

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

[harsini]

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.

[Steven Manuel]

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.

[hosam hemaily]

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 !!

[Maroun]

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