Error after 'update-database' in Entity Framework 6

Question

I'am working on WEB API 2 with Entity Framework 6 project and have problem after update-database.

Error:

Introducing FOREIGN KEY constraint 'FK_dbo.Rates_dbo.Users_Id_User' on table 'Rates' 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 or index. See previous errors.

Here's my Rate class:

public class Rate
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id_Rate { get; set; }
        public int Id_User { get; set; }
        public int Id_Recipe { get; set; }
        public int Value_Rate { get; set; } //1-5

        public virtual User User { get; set; }
        public virtual Recipe Recipe { get; set; }
    }

and my User class:

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id_User { get; set; }
    public int Id_List_Products { get; set; }
    public int Id_List_Black_Products { get; set; }
    public string Login { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public Boolean Social_Account { get; set; } // 1 - social account, 0 - normal account
    public string URL_Avatar { get; set; } //URL of avatar thumbnail

    public virtual List_Products List_Products { get; set; }
    public virtual List_Black_Products List_Black_Products { get; set; }
}

I have no idea where's problem. Any hints?

Post your ConnectionString Model class.. the one that includes `modelBuilder` — Grizzly, Aug 30 '17 at 18:57
You need to specify what should happen to Users when you delete a Rate. If you have User reference Rate, and rate reference User, cascade delete would cause issues. — C. Helling, Aug 30 '17 at 18:58
Possible duplicate of [Entity Framework: how to solve "FOREIGN KEY constraint may cause cycles or multiple cascade paths"?](https://stackoverflow.com/questions/14489676/entity-framework-how-to-solve-foreign-key-constraint-may-cause-cycles-or-multi) — C. Helling, Aug 30 '17 at 19:00

Afnan Makhdoom · Accepted Answer · 2017-08-30T19:11:07.167

This problem occurs due to multiple pathways of relations. If you delete one record, it will end up deleting other records from another table which will end up deleting more records from another table which in turn will end up deleting records from the table you started with and the cycle will repeat. Truly catastrophic, a developer's worst nightmare. To address this issue, add custom attributes, for instance, in your migration:

CreateTable(
            "dbo.SomeTable",
            c => new
            {
                id = c.Int(nullable: false, identity: true),
                createdon = c.DateTime(),
                createdby = c.String(),
            })
            .PrimaryKey(t => t.id)
            .ForeignKey("dbo.Customers", t => t.Customerid, cascadeDelete: true)
            .ForeignKey("dbo.AnotherTable", t => t.Anotherid, cascadeDelete: false)
            .Index(t => t.Customerid)
            .Index(t => t.Scopeid);

Set the cascadeDelete to false or AutoUpdate to false of the affected tables/(model classes backing) and update the database accordingly.

Simply put, your relation is cyclic, for instance: Customer -> Salary -> Payroll -> Customer So when you delete a customer, its respective salary record(s) are/is deleted which delete the respected Payroll records which loop back to deleting linked customers and this cycle keeps repeating causing chaos. So Entity Framework and SQL understand this well and prompt the user to turn off affected/error-causing deletion/updation connections.

score 0 · Answer 2 · answered Aug 30 '17 at 19:44

Okey, I changed cascadeDelete to false in:

CreateTable(
            "dbo.Rates",
            c => new
                {
                    Id_Rate = c.Int(nullable: false, identity: true),
                    Id_User = c.Int(nullable: false),
                    Id_Recipe = c.Int(nullable: false),
                    Value_Rate = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id_Rate)
            .ForeignKey("dbo.Recipes", t => t.Id_Recipe, cascadeDelete: true)
            .ForeignKey("dbo.Users", t => t.Id_User, cascadeDelete: false)
            .Index(t => t.Id_User)
            .Index(t => t.Id_Recipe);

and it works ;)

Error after 'update-database' in Entity Framework 6

2 Answers2