20

For optional relationships (when Foreign Key can accept Null), a new ClientSetNull behavior has been introduced since EF Core 2.0 as the default option for delete behavior DeleteBehavior.ClientSetNull. This has SetNull semantics for tracked entities and Restrict (no action) behavior for database records not loaded into memory.

Cascade Delete behaviors

Microsoft docs say that:

If you want the database to also try to propagate null values to child foreign keys even when the child entity is not loaded, then use SetNull. However, note that the database must support this, and configuring the database like this can result in other restrictions, which in practice often makes this option impractical. This is why SetNull is not the default.

But I think it is usually normal to set FK of dependent entities to Null when the associated parent is deleted (every where in db). And also, what's those "other restrictions, which in practice often makes this option impractical.." as claimed above?

S.Serpooshan
  • 7,608
  • 4
  • 33
  • 61

1 Answers1

12

Those other restrictions the docs are referring to are, as far as I know, circular or multi path cascades.

MS Sql Server for example does not allow cascades (both delete and set null) if

  • the change would cascade to the same table it originated from
  • there are multiple cascade paths to the same table. Like table 'A' affects table 'B' and 'C', both 'B' and 'C' affect 'D'.

You can't even create the constraint.

EF core can circumvent this limitation with ClientSetNull. EF handles the set null operation, but it can only do so if all the affected entities are loaded into memory.

Kabbalah
  • 471
  • 1
  • 5
  • 16
  • thanks for answer, although i up-voted it, I think what you specify here is some rare case and it is not enough to make it the default option! – S.Serpooshan Aug 19 '20 at 05:01
  • 1
    It is weird that SQL Server doesn't support this but I am glad to learn the way to solve this problem. Just make sure they are being tracked correctly. Thanks for the great explanation. – M. Azyoksul Mar 24 '22 at 11:19