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?

score 347 · Accepted Answer · answered Apr 05 '11 at 22:23

347

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.

answered Apr 05 '11 at 22:23

Ladislav Mrnka

360,892
59
660
670

4

What if two teams are allowed to play only once? – ca9163d9 Jun 08 '13 at 18:53
5

@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). – Ladislav Mrnka Jun 08 '13 at 22:08
2

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. – Woodchipper Apr 30 '14 at 00:47
@MikeSheehan I am facing same issue with delete. Have you resolved cascade delete problem? – py3r3str Jul 24 '14 at 09:07
I also saw this (http://stackoverflow.com/questions/19864974/code-first-entity-framework-multiple-foreign-keys-for-the-same-table) as a solution. It uses the 'inverse property' annotation. Is there any negative to that versus what is done here? – mrshickadance Aug 05 '14 at 01:56
1

@mrshickadance: It is the same. One approach uses fluent API and another data annotations. – Ladislav Mrnka Aug 08 '14 at 17:15
1

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. – Rupesh Kumar Tiwari Jun 30 '15 at 20:06
I added `[JsonIgnore] to both `ICollections' and removed `virtual` from both referencing foreign keys to avoid problems with loops. – CularBytes Oct 21 '15 at 21:30
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 – v1n1akabozo Mar 31 '16 at 19:59
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. – harsini May 10 '16 at 08:06
Why there's a cyclic problem in the first place? If I delete a `Team`, where's the cycle in deleting the `Match`? – Maroun Sep 09 '21 at 08:08

score 69 · Answer 2 · answered Jan 16 '13 at 19:48

69

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

answered Jan 16 '13 at 19:48

ShaneA

1,325
10
18

5

I get the same exception either way. – Jo Smo Dec 01 '15 at 03:44
18

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. – Steven Manuel Jun 17 '16 at 02:54
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 !! – hosam hemaily Mar 01 '19 at 16:24

score 65 · Answer 3 · answered Jun 26 '16 at 10:36

65

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

answered Jun 26 '16 at 10:36

khoa_chung_89

975
9
25

1

Thanks for this answer, however it makes the Foreign Key columns nullable in the Match table. – RobHurd Aug 21 '16 at 14:41
This worked great for me in EF 6 where nullable collections were needed. – Pynt Sep 12 '16 at 14:14
1

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. – DiscipleMichael Apr 26 '17 at 15:09
2

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. – Jakub Holovsky Feb 12 '18 at 16:10

score 20 · Answer 4 · answered Jan 09 '12 at 23:09

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.

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. — N8allan, Aug 18 '15 at 05:25

score 17 · Answer 5 · answered Aug 13 '19 at 03:57

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; }
}

Why do we need 'Column(Order = 0)'? This solved my issue by the way — Arturio, Oct 23 '20 at 13:19
It is used to order columns. Copied from the original question. It has nothing to do with Inverse Property. — pritesh agrawal, Oct 24 '20 at 01:36

score 15 · Answer 6 · edited Nov 20 '13 at 10:35

15

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.

edited Nov 20 '13 at 10:35

Yossarian

5,226
1
37
59

answered Nov 20 '13 at 10:14

juls

151
1
2

So what is it after this is executed? `Restrict` instead of `Cascade`? – Jo Smo Dec 01 '15 at 03:52

score 0 · Answer 7 · answered Oct 24 '21 at 01:53

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

Make sure to make foreign keys nullable

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);
}

Entity Framework Code First - two Foreign Keys from same table

7 Answers7

Linked

Related