Introducing a FOREIGN KEY constraint into a table can lead to loops or several cascading paths leading to Database.SetInitializer not working?

Question

My first database worked fine. If I made changes to my database context, the database would be updated the next time the application was launched. But then I added some models to the database and got this error when restarting the application:

Introducing FOREIGN KEY constraint 'FK_OrderDetails_Orders_OrderId' on table 'OrderDetails' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.

One of the weird things is that if I run the application again without changing anything, I get the following error:

Model compatibility cannot be checked because the database does not contain model metadata. Model compatibility can only be checked for databases created using Code First or Code First Migrations.

In order for the first error to happen again, I must delete my .mdf and .ldf files (database) and replace only the .mdf file with a copy from my change history.

Why is this happening in the world?

---

For reference:

My Global.asax.cs file has this value in the Application_Start() method:

Database.SetInitializer<EfDbContext>(new EfDbContextInitializer());

Which looks like this:

 public class EfDbContextInitializer : DropCreateDatabaseIfModelChanges<EfDbContext> { protected override void Seed(EfDbContext context) { var orders = new List<Order> { . . . }; orders.ForEach(s => context.Orders.Add(s)); . . . etc. . . context.SaveChanges(); } } 

My connection string is from Web.config :

<add name="EFDbContext" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;database=pos;AttachDBFilename=|DataDirectory|pos.mdf;MultipleActiveResultSets=true;User Instance=true" providerName="System.Data.SqlClient" />

And finally, my Order and OrderDetails models (that the first error is directly referenced):

 public class Order { public int OrderId { get; set; } public List<OrderDetail> OrderDetails { get; set; } public int EstablishmentId { get; set; } public virtual Establishment Establishment { get; set; } } public class OrderDetail { public int OrderDetailId { get; set; } public int OrderId { get; set; } public int ProductId { get; set; } public int Quantity { get; set; } public decimal UnitPrice { get; set; } public virtual Product Product { get; set; } public virtual Order Order { get; set; } } 

Update / Note. If I comment on public int OrderId { get; set; } public int OrderId { get; set; } public int OrderId { get; set; } in my OrderDetail class, the project will start working fine (although I don't get the desired ability to add OrderId (of course).

Answer 1

This problem is caused by possible cyclic cascading deletion. This can happen in many forms, but it comes down to the fact that a record is deleted by two or more cascading delete rules at a time.

For example, let's say you have a parent table and two child tables. Then you also have another table that is linked to two child tables:

 Parent ------ ParentId Name ChildOne -------- ChildOneId ParentId Name ChildTwo -------- ChildTwoId ParentId Name SharedChild ----------- SharedChildId ChildOneId ChildTwoId Name 

When you delete a record from the parent table, perhaps this delete will be cascaded for ChildOne and ChildTwo. These two deletes can then be further cascaded into SharedChild, and here we get the problem: two paths try to delete the same record from SharedChild.

I am not saying that your situation is exactly the same, but somehow, something similar happens with you. To solve this problem, you can decide to allow one branch to allow the cascade further down the chain and prevent it in another chain.

The reason you get this error only when you first start the application, and then every time you delete the database, is because (I believe) the Entity Framework stops generating the database when the error occurs, and you are left with incomplete database. This is why you get a different error in other situations.

If you need extra help to resolve circular cascading deletions, you probably need to show your entire database and its relationships.

Answer 2

to solve this problem, you need to remove the foreign key attribute and just leave the object as a virtual link:

here is what i did:

old code when I had this error:

  public class OperatingSystemType { public int OperatingSystemTypeID { get; set; } [StringLength(50)] [Required] public string OperatingSystemName { get; set; } public int CompanyID { get; set; } [ForeignKey("CompanyID")] public virtual Company Company { get; set; } } 

new code after solving this error:

  public class OperatingSystemType { public int OperatingSystemTypeID { get; set; } [StringLength(50)] [Required] public string OperatingSystemName { get; set; } public virtual Company Company { get; set; } } 

then in the seed you add data, as shown below, since we simply add the Company object as one of the parameter values ​​for OperatingSystemType:

  protected override void Seed(MyAPPMVC.Infrastructure.MyContext context) { string myusername = "myuser"; string MicrosoftName = "Microsoft"; context.Companies.AddOrUpdate(p => p.CompanyName, new Company { CompanyName = MicrosoftName, CreatedOn = DateTime.Now, CreatedBy = myusername } ); context.SaveChanges(); Company MicrosoftCompany = context.Companies.Where(p => p.CompanyName == MicrosoftName).SingleOrDefault(); context.OperationgSystemTypes.AddOrUpdate(p => p.OperatingSystemName, new OperatingSystemType { OperatingSystemName = "Windows 7 Professional", Company = MicrosoftCompany, CreatedOn = DateTime.Now, CreatedBy = myusername }, new OperatingSystemType { OperatingSystemName = "Windows 8 Professional", Company = MicrosoftCompany, CreatedOn = DateTime.Now, CreatedBy = myusername } );