EFCore Cascade Delete when set to DeleteBehaviour.Restrict
Hi,
I would like to force a cascade delete of all DB entries related to the entry I want to delete.
The database is configured to restrict deletions rather than cascade, however I know using the SQL syntax it is possible to override this and force a cascade. How might I achieve this in EFCore?
Thanks!
23 Replies
@Pokey The default behavior is to cascade delete. I don't understand what you want to do.
You set the model to be Restrict, and want a specific model to be Delete?
In any case, what you want will be at https://learn.microsoft.com/en-us/ef/core/saving/cascade-delete#configuring-cascading-behaviors
Cascade Delete - EF Core
Configuring cascading behaviors triggered when an entity is deleted or severed from its principal/parent
Unfortunately it is not. I have set the behaviour to be restrict, however on a very specific delete case I wish to override to Cascade. This is something I am able to do with raw SQL, I just don't know how to achieve the same with EF
On a very specific case, you mean for a table, or "just that single delete"?
Just that single delete
All deletes should be restrict except for when run through a very specific delete procedure which needs to override to cascade
Define a 2nd DbContext, or the same one with an override, and in the mapping used by that specific DbContext, set it to ClientDelete.
Ohhhu ouchie
I'd prefer to go through each navigation and delete than do that
There should be no need to client delete, the database is perfectly capable of cascading if told to do so on a restrict table
If there's no other way I may end up implementing it in raw SQL
That's not true. You're saying that every single provider, MSSQL, Oracle, Postgres, SQLite, MySQL, etc., all support that?
My target is Postgres, and any other targets would get their own copy to work with where it can be set clientdelete if necessary
My point is that most RDBMS don't support that, so it's not built in the framework most likely.
Right
If you have a Raw sql way of doing it, wrapping that in a method should be pretty easy though.
(For example, I'd use https://www.mssqltips.com/sqlservertip/6192/simulating-on-delete-cascade-in-sql-server/ for MSSQL I guess.)
((But not in a stored procedure.))
Meh
Wait does MSSQL not support cascades at all?
It does. It just doesn't support "I want that single delete to cascade"
Ahh
I've never had a single case where I want the definition to cascade something, and a query to cascade something else.
At this point it should be enforced in the BL, not the database imo.
If you're interested, the context is I have a row with many relationships, and it's important data. The only scenario it is allowed to be cascaded is if the data was invalid in the first place, which can happen
At least it's not SAP HANA 😂
Set it to cascade delete and check if it's allowed to be deleted in the specific case.
With the possibility of poky fingers on tables this is a bad idea
Not to say I don't think it's logical to do, it is logical
I shall implement a raw query for this one case I think, it's a rare occurrence anyways
If you're afraid of using poking the table, either restrict that, or accept it 😛
Good luck!
These are sensible but occasionally clumsy fingers 😛
Thank you for the advice!