Delete cascade a tree in EF core (SQL Server)

Question

I have the following code:

    public class A
    {
        public Guid Id { get; set; }
        public List<A> AList { get; set; }
    }

Configured with fluent Api:

            modelBuilder
                .Entity<A>()
                .HasMany(x => x.AList)
                .WithOne()
                .OnDelete(DeleteBehavior.Cascade);

Creating a Migration works fine but when doing "Update-Database" I get the following error:

"Introducing FOREIGN KEY constraint 'FK_A_A_AId' on table 'A' 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. See previous errors."

How do I delete cascade a tree in EF Core then?

Answer 1

You can’t “automatically” cascade delete; you must recursively delete child records in client code or create an INSTEAD OF DELETE trigger in the DB with recursive CTE to query for and delete all child records

SO answer for recursively removing children in client code:

Implementing Cascade Delete in a self referencing table in EF Core 2

SO answer for trigger:

On delete cascade for self-referencing table

Alternatively, implement the trigger logic as a stored procedure and configure EFCore to use that sproc for deletes (not built in functionality like EF6 for MapToStoredProcedures so some effort is required:

EF Core - What is MapToStoredProcedures replacement in EF Core 3.1 or 5

Answer 2

With the way the current model is, the same instance of A can appear multiple times in the hierarchy. This would prevent even a database engine from doing a cascade delete.

An alternative would be (depending on your requirement) to add on a "parent" foreign key property to your entity, like the following:

public class A
{
    public Guid Id { get; set; }
    
    public Guid? ParentId { get; set; }

    public A Parent { get; set; }
    
    public List<A> Children { get; set; }
}

And in your model builder...

modelBuilder.Entity<A>
    .HasKey(x => x.Id);

modelBuilder.Entity<A>
    .HasMany(x => x.Children)
    .WithOne(x => x.Parent)
    .HasForeignKey(x => x.ParentId)
    .OnDelete(DeleteBehavior.Cascade);

If you did need the same exact instance of a node in the hierarchy appearing in multiple places in the hierarchy, then some additional modeling would be needed to make it work.