How to check Foreign Key to restrict SoftDelete?

Question

I would like to know if it is possible to check FK when using SoftDelete with ASP.NET Boilerplate.

Example

Suppose these tables:

Roles: RoleId (PK) - Description
Users: UserId (PK) - Name - RoleId (FK with Roles)

Data:

Roles
1 - admin
2 - guest

Users
1 - admin - 1
2 - john - 2

So RoleId 1 should not be deleted if it was already assigned to an existing User.

Thanks in advance.

since you do have an FK relationship, the FK constraint itself enforces the behavior you described — DevilSuichiro, Apr 05 '18 at 17:39

score -1 · Answer 1 · answered Apr 06 '18 at 06:22

Briefly your goal is non-sense. SoftDelete feature marks the record as deleted and doesn't delete it physically. It's like Recycle Bin in Windows. So that you can undelete it anytime. From the database perspective, it's relationships are consistent. Because there's the data in the table.

Solution; when you prevent users to delete an in-use record you have to validate it yourself against your business rules.

score -2 · Accepted Answer · answered Apr 06 '18 at 13:52

Soft delete just sets a flag to mark the record as deleted.

In ABP, you can write your own checks in ApplyAbpConceptsForDeletedEntity of your DbContext:

public class AbpProjectNameDbContext // : ...
{
    // ...

    protected override void ApplyAbpConceptsForDeletedEntity(EntityEntry entry, long? userId, EntityChangeReport changeReport)
    {
        CheckForeignKeys(entry);

        base.ApplyAbpConceptsForDeletedEntity(entry, userId, changeReport);
    }

    private void CheckForeignKeys(EntityEntry entry)
    {
        var entity = entry.Entity;
        if (!(entity is ISoftDelete))
        {
            // Foreign key constraints checked by database
            return;
        }

        var role = entity as Role;
        if (role != null)
        {
            if (Users.Any(u => u.Roles.Any(r => r.RoleId == role.Id)))
            {
                throw new UserFriendlyException("Cannot delete assigned role!");
            }
        }
    }
}

Note that the template's RoleAppService actually removes users from the role before deleting it.

How to check Foreign Key to restrict SoftDelete?

2 Answers2