CODEWITHSUNDEEP
sql-serverentity-framework-core
Fabien Dezautez
Fabien Dezautez

Reputation: 129

ForeignKey SetNull OnDelete

I would like to know if it possible, with a composite foreign key, to set only one column of this foreign key to null when I delete related row.

Indeed, the second column (the one i don't want to be null) is used for other foreign keys that I don't want to be deactivated.

Precision : I use Entity framework Core (last version).

Upvotes: 2

Views: 286

Answers (3)

Fabien Dezautez
Fabien Dezautez

Reputation: 129

Finally, I overrode the SaveChanges method to explore the childs and set all the column part of the FK to null (except the TenantId).

Here is the code permitting to iterate on one entity navigations property : 

foreach (var navigationEntry in entry.Navigations
                            .Where(n => !n.Metadata.IsDependentToPrincipal()))
{
    if (navigationEntry is CollectionEntry collectionEntry)
    {
        // FK uses DeleteBehavior.ClientSetNull -> let's set it to NULL
        if (((Microsoft.EntityFrameworkCore.Metadata.Internal.Navigation)((MemberEntry)navigationEntry).Metadata).Builder.Metadata.ForeignKey.DeleteBehavior == DeleteBehavior.ClientSetNull)
        {
            // getting all fields composing the FK (except TenantId)
            List<string> fieldsToSetToNull = ((Microsoft.EntityFrameworkCore.Metadata.Internal.Navigation)((MemberEntry)navigationEntry).Metadata).Builder.Metadata.ForeignKey.Properties.Where(x => x.Name != nameof(IMustHaveTenant.TenantId)).Select(x => x.Name).ToList();

            List<object> dependentEntitiesList = new List<object>((IEnumerable<object>)collectionEntry.CurrentValue);
            for (var i = dependentEntitiesList.Count - 1; i >= 0; i--)
            {
                // setting all fields to NULL
                foreach (string fi in fieldsToSetToNull)
                {
                    var childEntry = this.Entry(dependentEntitiesList[i]);
                    childEntry.CurrentValues[fi] = null;
                }
            }
        }
    }
}

Upvotes: 0

Fabien Dezautez
Fabien Dezautez

Reputation: 129

Thanks for your answer.

I agree with everything you said except one thing :

As far as I know this is not possible, because your foreign key value then would reference an unexistent record.

The fact that one of the columns is nullable (the one I want to be set to NULL), permits me to make the following FK { ParentId : null, TenantId : 4 } references no row. If I set the ParentId, the FK now references a row.

In my case, I just want that if I delete the Parent, only the ParentId is set to null and not the TenantId that is used for some others FK and also in the PK. But I came to the same conclusion that you that it is not possible...

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 77045

As far as I know this is not possible, because your foreign key value then would reference an unexistent record. However, there are workarounds that you might use:

Another column

You might create another column with the same value and your old column will only reference the table where you intend to do deletion, the foreign key to the other tables would be linked to the new column with the same value

Not defining a foreign key for this table

You can avoid defining a foreign key for the table where you might remove records, so no such constraints would affect you.

Turning off foreign key checking

This would "solve" your problem, but should be avoided if possible.

Virtual removal

In the target table you could create a deleted flag and set that to true instead of actual removal.

Upvotes: 2

Related Questions