Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths causing Database.SetInitializer to not work?

Question

My code-first database was working great. If I made a change to my database context the database would be updated the next time I started the application. But then I added some models to the database and got this error when I restarted my 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 start the application up again without changing anything, I then get this 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.

To get the first error to happen again, I have to delete my .mdf and .ldf files (the database) and replace just the .mdf file with a copy from my revision history.

Why in the world is this happening?

---

For reference:

My Global.asax.cs file has this within 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 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 (what the first error is directly referencing):

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 out public int OrderId { get; set; } in my OrderDetail class, the project starts up fine (although I do not get the desired ability to add an OrderId (of course).

Answer 1

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

For example, lets say you have Parent table and two Child tables. Then you also have another table which is linked to both 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, it is possible this delete will cascade to both ChildOne and ChildTwo. These two deletes can then further cascade to SharedChild and here we get the problem: two paths trying to delete the same record from SharedChild.

I'm not saying your situation is exactly the same, but one way or another, you have something similar going on. To solve this, you can decide to let one branch be allowed to cascade further down the chain and prevent it in the other chain.

The reason you get this error only the first time you run your app and then every time you delete the database, is because (I believe) Entity Framework stops generating the database at the point the error occurs and you are left with an incomplete database. That's why you're getting the other error in the other situations.

If you need more help resolving the cyclic cascading delete, you will probably need to show your entire datamodel and its relations.

Answer 2

to solve this issue, what you need to do is remove the foreign key attribute, and just leave the object referenced as virtual:

here's what I did:

the old code when I had this bug:

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

the new code after solving this bug:

    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 like this shown below, as we just add the Company object as one of the parameter value 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 }
            );