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?

what dbms are you using? in sql server there's a delete cascade that can be defined between relations. — gsharp, Jun 04 '21 at 18:05
If you look at the error source, you'll see it is coming from SqlServer. So it is SqlServer database limitation, not EF Core. — Ivan Stoev, Jun 05 '21 at 07:48

Moho · Answer 1 · 2021-06-04T17:49:30.370

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

edited Jun 04 '21 at 17:49

answered Jun 04 '21 at 17:42

Moho

15,457
1
30
31

*You can't "automarically" delete...* Well, actually you *can*, in some databases, but not in SqlServer which is famous to have and keep over the years this limitation. – Ivan Stoev Jun 05 '21 at 07:54
@IvanStoev “switch to a different RDBMS” is a rather drastic solution to this issue of minor inconvenience, wouldn’t you say? – Moho Jun 05 '21 at 23:31
Of course no. It's not so "minor inconvenience" in case you have many tables and relations, but that wasn't the point. SqlServer is not mentioned anywhere in this thread - neither in tags, nor the question or answers. The answer gives an impression that one should generally (always) use the provided workarounds, while the issue is pure SqlServer specific. – Ivan Stoev Jun 06 '21 at 04:17
The error is `MSSQLSERVER_1785`; it's clear the poster is using SQL Server to anyone that has the experience dealing with the issue to answer the question and that anyone searching for an answer to this problem is using SQL Server (since the error message is SQL Server specific). As such, the only other "solution" would be to suggest they not use SQL Server, and we've covered why that's not appropriate. But to further clarify, I've edited the question topic and tags to specify `SQL Server` – Moho Jun 06 '21 at 04:55
Again, it wasn't against the answer, but the scope of it (now is ok). You are right about answerers, but not for people searching for the problem. If they know that, they won't be [posting one and the same question so many times](https://stackoverflow.com/search?q=may+cause+cycles+or+multiple+cascade+paths). Which reminds me that the question is a blatant duplicate, hence should have been closed as such rather than answered. But anyway, appreciate your efforts. – Ivan Stoev Jun 06 '21 at 06:15

score 0 · Answer 2 · answered Jun 04 '21 at 18:02

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.

This is no different than OP (just the FK name is different and uses explicit FK property rather than shadow), hence has the same exact issue (can't use `Cascade` delete behavior). — Ivan Stoev, Jun 05 '21 at 07:57

Delete cascade a tree in EF core (SQL Server)

2 Answers2