Entity 7 structure in several ASP.NET MVC6 foreign keys for one table

Question

Hi, I have the same problem as the old post, which here, the proposed solution for me does not work in MVC 6 with EF7 is simple

public class Match { [Key] public int MatchId { get; set; } public DateTime playday { get; set; } public float HomePoints { get; set; } public float GuestPoints { get; set; } public int HomeTeamId { get; set; } public int GuestTeamId { get; set; } [ForeignKey("HomeTeamId")] [InverseProperty("HomeMatches")] public virtual Team HomeTeam { get; set; } [ForeignKey("GuestTeamId")] [InverseProperty("AwayMatches")] public virtual Team GuestTeam { get; set; } } 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; } } 

this is the best way i found because i can add a new migration and everything is ok but when i update the database i get an error like this

Representation of the FOREIGN KEY constraint "FK_Match_Team_HomeTeamId" in the "Match" table may cause loops or multiple cascading paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION or change other FOREIGN KEY constraints. Failed to create constraint or index. See Previous Errors.

Answer 1

I analyzed the problem in detail during the preparation of the answer , and I can offer you two solutions to the problem.

The problem exists due to two properties in the Match class

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

and the foreign keys HomeTeamId and GuestTeamId that will be generated. EF7 will generate foreign keys using ON DELETE CASCADE , which can no longer be used as a single foreign key. The current implementation of Entity Framework (RC1) does not have an annotation attribute that you can use to change behavior.

The first solution to this problem is to use properties with a null value, such as

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

or

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

The maximum single property must be invalid. As a result, the problem will be solved, but it will have a slight flaw, which may not be important for some scenarios. A field in the database table for the nullable property will not have the NOT NULL property in the column definition.

If you need to hold both HomeTeamId and GuestTeamId non-nullable, then you can solve the problem by changing the context class (inherited from DbContext) where the Match and Team classes will be used.

You already have a specific context class line below

 public class MyDBContext : DbContext { DbSet<Team> Teams { get; set; } DbSet<Match> Matches { get; set; } } 

To solve the description problem, you can add protected OnModelCreating to a class that explicitly sets

 public class MyDBContext : DbContext { protected override void OnModelCreating(ModelBuilder modelbuilder) { base.OnModelCreating(modelbuilder); modelbuilder.Entity(typeof (Match)) .HasOne(typeof (Team), "GuestTeam") .WithMany() .HasForeignKey("GuestTeamId") .OnDelete(DeleteBehavior.Restrict); // no ON DELETE modelbuilder.Entity(typeof (Match)) .HasOne(typeof (Team), "HomeTeam") .WithMany() .HasForeignKey("GuestTeamId") .OnDelete(DeleteBehavior.Cascade); // set ON DELETE CASCADE } DbSet<Team> Teams { get; set; } DbSet<Match> Matches { get; set; } } 

You can use the DeleteBehavior.Restrict reason for both foreign keys (instead of using DeleteBehavior.Cascade on one). It is important to note that the latter approach allows you to keep both HomeTeamId and GuestTeamId , as well as the corresponding fields in the database, as non-empty.

See the documentation for more information.

Answer 2

If you prefer, you can manually create this FK with cascading deletion in the database and check why you use this cyclic cascade loop. To understand that we need other tables or a database structure. Please check your database (for example, on a diagram), connect your connected tables, your FKs and plan an operation for each study (cascade deletion, no action, etc.) and direction them. You do a loop search when you try to insert this FK by mistake.

The next step is to understand whether you want it or not, is an unwanted database design? Or just do not you need this action FK? In this case, you can suppress it at different levels: by model, settings, ... by another message or EF manual for this.