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 lt Match gt  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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

Try this   public class Team       public int TeamId   get  set        public string Name   get  set         public virtual ICollection lt Match gt  HomeMatches   get  set        public virtual ICollection lt Match gt  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 lt Match gt                         HasRequired m   gt  m HomeTeam                       WithMany t   gt  t HomeMatches                       HasForeignKey m   gt  m HomeTeamId                       WillCascadeOnDelete false            modelBuilder Entity lt Match gt                         HasRequired m   gt  m GuestTeam                       WithMany t   gt  t AwayMatches                       HasForeignKey m   gt  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

User · Answer

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 lt Match gt  HomeMatches  get  set          InverseProperty nameof Match GuestTeam        public ICollection lt Match gt  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

User · Answer

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 lt Match gt  HomeMatches   get  set          InverseProperty  GuestTeam        public virtual ICollection lt Match gt  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 amp MSPPError -2147217396 Relationships

[c#] Entity Framework Code First - two Foreign Keys from same table

Examples related to c#

Examples related to entity-framework

Examples related to orm

Examples related to code-first

Examples related to entity-framework-4.1