How to set on delete cascade for self reference Fo

Question

I am developing an ASP.NET MVC project. I am using Entity Framework code first approach to interact with database. But I am having a problem with setting on cascade delete for self-reference foreign key for an entity. Please see my scenario below.

This is my entity class with self reference foreign key

public class Category
    {
        public int Id { get; set; }
        [Required]
        [MaxLength(50)]
        public string Name { get; set; }
        [MaxLength(55)]
        public string MmName { get; set; }
        public int? ParentId { get; set; }

        [ForeignKey("ParentId")]
        public virtual Category ParentCategory { get; set; }
        public virtual ICollection<Category> Categories { get; set; }
    }

This is my context

public class StoreContext : DbContext
    {
        public StoreContext():base("DefaultConnection")
        {

        }

        public DbSet<Category> Categories { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Category>().HasOptional(x => x.ParentCategory).WithMany(c => c.Categories).WillCascadeOnDelete();
        }
    }

When I run, multiple cascade path errors throw

Introducing FOREIGN KEY constraint 'FK_dbo.Categories_dbo.Categories_ParentId' on table 'Categories' 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.

Is it possible set on delete cascade for self-referencing foreign key in entity framework code first?

Answer 1

i also faced the similar issue, and if i remember correctly what i found is EF doesn't support cassade delete on self reference, so we need to handle it by code. What i followed is

• Remove the cascade delete from fluent api or generated migration.
  
• Add code to delte/setnull all self-reference and then delete.