Entity Framework relationship between entities, cycles or multiple cascade paths

Question

I am newbie with EF and trying to design my database using Code First. Following is my three Entities (I have not written all properties). I have an ode situation. The administrator is not allowed to edit an Expense, so I had to create another entity (EditedExpense) to have all edited expenses in another table. So when Administrator tries to change (edit) an expense and clicks save, a new post in EditedExpense will be created.

public class Expense
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ExpenseId { get; set; }

    [Required]
    public int CategoryId{ get; set; }
    [ForeignKey("CategoryId")]
    public virtual Category Category { get; set; }

    public virtual List<EditedExpense> EditedExpenses { get; set; }
}
public class EditedExpense
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int EditedExpenseId { get; set; }

    [Required]
    public int CategoryId{ get; set; }
    [ForeignKey("CategoryId")]
    public virtual Category Category { get; set; }

    public int ExpenseId { get; set; }

}
public class Category
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int CategoryId{ get; set; }

    public string Title

    public virtual List<Expense> Expenses { get; set; }
    public virtual List<EditedExpense> EditedExpenses { get; set; }
}

It generates this error Introducing FOREIGN KEY constraint 'Expense_EditedExpenses' on table 'EditedExpense' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints

Since EditedExpense is a child of both Category and Expense, deleting a Category or an Expense should trigger an automatic (cascaded) delete of the associated EditedExpenses as well. However, SQL server does not allow this: the cascading delete can only be triggered from one of the parents, not from both. So, you have to explicitely remove the cascading delete between Category and EditedExpense or between Expense and EditedExpense. You can do this using the Annotation or using the fluent API. — Dabblernl, Jul 17 '14 at 09:07

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

You need to turn off the cascade delete, either on Category or EditedExpenses on Expense entity by doing this.

public class YourDbContext : DbContext
{
   // other code ommitted

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Remove cascade delete from Category.
        modelBuilder.Entity<Expense>()
            .HasRequired(e => e.Category)
            .WithMany(c => c.Expenses)
            .WillCascadeOnDelete(false);

        // Or remove cascade delete from Edited Expense.
        //modelBuilder.Entity<Expense>()
        //    .HasMany(e => e.EditedExpenses)
        //    .WithRequired()
        //    .WillCascadeOnDelete(false);
    }
}

Here is the explanation why multiple cascade is not allowed.

edited May 23 '17 at 12:29

Community

1
1

answered Jul 17 '14 at 09:10

Yuliam Chandra

14,494
12
52
67

I used your solution it worked however when use a foreach loop to go through all Expenses and thier EditedPensese and thier Categories it casts an exception and says The ObjectContext instance has been disposed and can no longer be used for operations that require a connection this is the code: var expenses = db.Expenses.Include(exp => exp.EditedExpenses).Include(exp => exp.Category); the foreach: myexpense.myEditedExpense.Category – user217648 Jul 18 '14 at 08:17
@user217648, please post in a new question so you can explain the problem more descriptive and you can also post the new code – Yuliam Chandra Jul 18 '14 at 08:28

Entity Framework relationship between entities, cycles or multiple cascade paths

1 Answers1