OnDelete(DeleteBehavior.Cascade) may cause cycles or multiple cascade paths on

Question

I have a Products table and a Categories table. I'm trying to make a many-to-many relationship between Product and Category. So I have a Table Called: ProductCategories - I followed the official doc:

https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-composite-key%2Csimple-key

public class ProductCategory
{
    public Guid ProductId { get; set; }
    public Product  Product { get; set; }
    
    public Guid CategoryId { get; set; }
    public Category  Category { get; set; }
}

 public class Product
{
     public Product()
    {
        ProductFiles = new Collection<ProductFiles>();
    }
    
    public Company Company { get; set; }
    public Guid CompanyId { get; set; }
    
    public string Name { get; set; }
    public string Description { get; set; }
    public decimal SalePrice { get; set; }
    public decimal? CostPrice { get; set; }
    public int VatPercentage { get; set; } = 25;

    public ICollection<ProductFiles> ProductFiles { get; set; }
    
    public ICollection<ProductCategory> Categories { get; set; }
}

public class Category
{
   public string Title { get; set; }
    
    public Guid CompanyId { get; set; }
    public Company Company { get; set; }
    public Guid? ParentCategoryId { get; set; }
    public virtual ICollection<Category> SubCategories { get; set; }
    public virtual Category ParentCategory { get; set; }
    
    public bool Visible { get; set; } = true;

    public int SortOrder { get; set; } = 1;
    
    public ICollection<ProductCategory> Products { get; set; }
}

And in modelbuilder I've specified the relations

        builder.Entity<Company>()
            .HasMany(c => c.Products)
            .WithOne(e => e.Company);
        

        builder.Entity<Product>()
            .Property(p => p.CostPrice)
            .HasColumnType("decimal(18,2)"); 
        
        builder.Entity<Product>()
            .Property(p => p.SalePrice)
            .HasColumnType("decimal(18,2)");

        builder.Entity<Category>()
            .HasMany<Category>(c => c.SubCategories)
            .WithOne(c => c.ParentCategory)
            .HasForeignKey(c => c.ParentCategoryId)
            .OnDelete(DeleteBehavior.Restrict);
        
        builder.Entity<Company>()
            .HasMany<Category>(p => p.Categories)
            .WithOne(p => p.Company)
            .HasForeignKey(p => p.CompanyId).OnDelete(DeleteBehavior.Cascade);

builder.Entity() .HasKey(x => new {x.ProductId, x.CategoryId});

        builder.Entity<ProductCategory>()
            .HasOne<Product>(pc => pc.Product)
            .WithMany(p => p.Categories)
            .HasForeignKey(p => p.ProductId);

        builder.Entity<ProductCategory>()
            .HasOne<Category>(p => p.Category)
            .WithMany(p => p.Products)
            .HasForeignKey(p => p.CategoryId);

The problem is that when I try to Update database I get an error "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."

What I want is when someone in the system delete a category/product, then it should delete the record in the ProductCategories - when I try to add onDelete no action, then I cannot delete the category/product without their relationship in ProductsCategory table.

Any suggestion on how to solve this the best way?

I guess your class structure should be changed. First of all, your `Product` can't consist of `Categories` just. It should have Id, some product properties, and just then list of Categories. Then Categories will be list of categories, not list of products. And ProductCategories will be lookup table of products and categories (but it will contain just IDs). Now class structure is not correct. — kosist, Nov 27 '21 at 19:04
I have multiple properties in Products and Category. I just removed them for not making the post too big :) — newbieDev, Nov 27 '21 at 19:05
Also, you removed other associations that cause this error. The model you show can't throw it. Either way, the error is a hard database constraint. It can only be solved by making at least one association not cascading. — Gert Arnold, Nov 27 '21 at 19:12
I've added everything that I have in Category and in Products table :) — newbieDev, Nov 27 '21 at 19:16
Anyway, I guess `ProductCategory` class is not correct. First of all, such table will be added by EF Core if Product and Category will refer to collection of each other (see MS KB which you refer to), and it should not contain Product and Category class itself - it is enough to have there ProductId and CategoryId. Although, your Product class does not have Id - which is strange to me. — kosist, Nov 27 '21 at 19:21
See [my question](https://stackoverflow.com/questions/61178355/how-can-these-constraints-may-cause-cycles-or-multiple-cascade-paths) and [explained article with solution](https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths/3548225#3548225) — Luke Vo, Nov 27 '21 at 20:09

David Browne - Microsoft · Accepted Answer · 2021-11-27T22:15:59.193

Company is configured to cascade deletes to both Category and Product. That's "multiple cascade paths" and is not allowed. Put all your FK configuration together to see more easily which relationships can cascade. eg

    builder.Entity<Company>()
        .HasMany(c => c.Products)
        .WithOne(e => e.Company)
        .OnDelete(DeleteBehavior.Cascade);

    builder.Entity<Company>()
       .HasMany(p => p.Categories)
       .WithOne(p => p.Company)
       .HasForeignKey(p => p.CompanyId)
       .OnDelete(DeleteBehavior.Restrict);

    builder.Entity<Category>()
        .HasMany(c => c.SubCategories)
        .WithOne(c => c.ParentCategory)
        .HasForeignKey(c => c.ParentCategoryId)
        .OnDelete(DeleteBehavior.Restrict);

    builder.Entity<ProductCategory>()
        .HasOne(pc => pc.Product)
        .WithMany(p => p.Categories)
        .HasForeignKey(p => p.ProductId)
        .OnDelete(DeleteBehavior.Cascade);

    builder.Entity<ProductCategory>()
        .HasOne(p => p.Category)
        .WithMany(p => p.Products)
        .HasForeignKey(p => p.CategoryId)
        .OnDelete(DeleteBehavior.Cascade);

OnDelete(DeleteBehavior.Cascade) may cause cycles or multiple cascade paths on

1 Answers1