Why is ON DELETE SET NULL still not implemented in the Entity Framework 6? Is there a snag?

Question

It still is not possible to configure a relation with the ON DELETE SET NULL rule using Entity Framework code first. As a workaround you have to load all the related entities in memory and then on deletion of the parent entity EF will issue SQL commands to set their foreign keys to Null.

This, while it is trivial to implement this yourself using something like:

protected override void Seed(Context context) {     context.Database.ExecuteSqlCommand("ALTER TABLE dbo.Guests DROP CONSTRAINT Guest_PreferredLanguage");     context.Database.ExecuteSqlCommand("ALTER TABLE dbo.Guests ADD CONSTRAINT Guest_PreferredLanguage FOREIGN KEY (LanguageID) REFERENCES dbo.Languages(LanguageID) ON UPDATE NO ACTION ON DELETE SET NULL"); } 

(Example take from this post.)

I can see no problems with this approach: Loaded child entities will remain in sync with the database because EF will update (set to null) their foreign keys and Reference properties, and that other records in the database are affected does no harm as they have not been loaded anyway.

So, why is this feature still missing then? Is there some hidden snag?

Answer 1

The feature is probably not implemented because normally changes only affect the objects which are actually in the unit of work. Cascades are not scalable.

And I also think soft deletes are better in most cases. Maybe thats something for you?

You might also want to look into Domain Driven design. That also covers the correct use of units of work (with aggregates).

Btw your solution edits the database in the seed method. It might be better to do that a Up() method of a migration.