ASP.Net MVC 3 EF "Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths"

Question

I am creating an ASP.Net MVC 3 application and I am running into a foreign key constraint problem when trying to update my database using migrations. I am using Code-First, and the error I am getting is:

Introducing FOREIGN KEY constraint 'FK_CategoryItemValues_CategoryProperties_CategoryPropertyId' on table 'CategoryItemValues' 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.

Here are my classes:

public class Category
{
    public int Id { get; set; }
    [Display(Name = "Category Name")]
    public string CategoryName { get; set; }
    [Display(Name = "Display Name")]
    public string DisplayName { get; set; }
    [Display(Name = "Display Order")]
    public int DisplayOrder { get; set; }
    public bool IsTab { get; set; }
    public bool Active { get; set; }

    public virtual List<CategoryProperty> Properties { get; set; }
}

public class CategoryProperty
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    [Display(Name="Property Name")]
    public string PropertyName { get; set; }
    [Display(Name = "Display Order")]
    public int DisplayOrder { get; set; }

    public virtual Category Category { get; set; }
}

public class CategoryItem
{
    public int Id { get; set; }
    public int CategoryId { get; set; }

    public virtual Category Category { get; set; }
    public virtual List<CategoryItemValue> Values { get; set; }
}

public class CategoryItemValue
{
    public int Id { get; set; }
    public int CategoryItemId { get; set; }
    public int CategoryPropertyId { get; set; }
    public string Value { get; set; }

    public virtual CategoryItem Item { get; set; }
    public virtual CategoryProperty Property { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // I know that the solution needs to go here!
}

It seems like I need to disable Cascade on Delete for CategoryItemValues, but I am not sure how to do that. I know I need to do something like:

modelBuilder.Entity<...>() .HasRequired(...) .WithMany(...) .HasForeignKey(...) .WillCascadeOnDelete(false);

But I cannot get it exactly right.

Answer 1

this should work...

public class Category
{
    public int Id { get; set; }
    public string CategoryName { get; set; }
    public string DisplayName { get; set; }
    public int DisplayOrder { get; set; }
    public bool IsTab { get; set; }
    public bool Active { get; set; }
    public virtual List<CategoryProperty> Properties { get; set; }
    public virtual List<CategoryItem> Items { get; set; }
}
public class CategoryProperty
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public string PropertyName { get; set; }
    public int DisplayOrder { get; set; }
    public virtual Category Category { get; set; }
    public virtual List<CategoryItemValue> Values { get; set; }
}
public class CategoryItem
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public virtual Category Category { get; set; }
    public virtual List<CategoryItemValue> Values { get; set; }
}
public class CategoryItemValue
{
    public int Id { get; set; }
    public int CategoryItemId { get; set; }
    public int CategoryPropertyId { get; set; }
    public string Value { get; set; }
    public virtual CategoryItem Item { get; set; }
    public virtual CategoryProperty Property { get; set; }
}

...and the 'gist'...

modelBuilder.Entity<CategoryProperty>()
    .HasKey(i => i.Id);

modelBuilder.Entity<CategoryProperty>()
    .HasRequired(i => i.Category)
    .WithMany(u => u.Properties)
    .HasForeignKey(i => i.CategoryId)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<CategoryItem>()
    .HasKey(i => i.Id);

modelBuilder.Entity<CategoryItem>()
    .HasRequired(i => i.Category)
    .WithMany(u => u.Items)
    .HasForeignKey(i => i.CategoryId)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<CategoryItemValue>()
    .HasKey(i => i.Id);

modelBuilder.Entity<CategoryItemValue>()
    .HasRequired(i => i.Item)
    .WithMany(u => u.Values)
    .HasForeignKey(i => i.CategoryItemId)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<CategoryItemValue>()
    .HasRequired(i => i.Property)
    .WithMany(u => u.Values)
    .HasForeignKey(i => i.CategoryPropertyId)
    .WillCascadeOnDelete(false);