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://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api,fluent-api-composite-key,simple-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?

CodePudding user response：

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