Provide an option to automatically bulk delete based for cascade delete

[Neme12]

In our database, we have a lot of relationships where the DeleteBehavior should really be SetNull or Cascade but can't be because

Introducing FOREIGN KEY constraint 'X' on table 'Y' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

In these cases we use ClientSetNull and ClientCascade but need to make it reliable because they only affect entities that are already tracked, so we override SaveChanges to make sure that all entities that might be affected are first loaded:

public override int SaveChanges(bool acceptAllChangesOnSuccess)
{
    ApplyClientReferentialActionsAsync(cancellationToken: default).GetAwaiter().GetResult();
    return base.SaveChanges(acceptAllChangesOnSuccess);
}

public override async Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default)
{
    await ApplyClientReferentialActionsAsync(cancellationToken);
    return await base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
}

private async Task ApplyClientReferentialActionsAsync(CancellationToken cancellationToken)
{
    // For DeleteBehavior.ClientSetNull, EF Core will automatically set the foreign key to null when the referenced entity is deleted and
    // for DeleteBehavior.ClientCascade, EF Core will automatically delete the entity when the referenced entity is deleted,
    // but only if the referenced entity has been loaded from the database. That's why we have to load all entities that have foreign keys
    // set to a deleted entity with DeleteBehavior either ClientSetNull or ClientCascade so that EF Core can perform the referential action.
    // Otherwise, the database operation would fail.

    foreach (var entry in ChangeTracker.Entries())
    {
        if (entry.State == EntityState.Deleted)
        {
            await (entry.Entity switch
            {
                ApplicationUser user => ApplyApplicationUserReferentialActionsAsync(user.Id, cancellationToken),
                CompanyChange companyChange => ApplyCompanyChangeReferentialActionsAsync(companyChange.Id, cancellationToken),
                ProfileChange profileChange => ApplyProfileChangeReferentialActionsAsync(profileChange.Id, cancellationToken),
                _ => Task.CompletedTask,
            });
        }
    }
}

private async Task ApplyApplicationUserReferentialActionsAsync(Guid userId, CancellationToken cancellationToken)
{
    // UserAction.UserId has DeleteBehavior.ClientSetNull.
    await ProfileChanges.Where(change => change.Created.UserId == userId || change.Updated.UserId == userId || change.Approved!.UserId == userId).LoadAsync(cancellationToken);
    await CompanyChanges.Where(change => change.Created.UserId == userId || change.Approved!.UserId == userId).LoadAsync(cancellationToken);
    await AddressChanges.Where(change => change.Created.UserId == userId || change.Updated.UserId == userId || change.Approved!.UserId == userId).LoadAsync(cancellationToken);
    await Users.Where(user => user.Created.UserId == userId).LoadAsync(cancellationToken);
    await Roles.Where(role => role.Created.UserId == userId).LoadAsync(cancellationToken);
}

private async Task ApplyCompanyChangeReferentialActionsAsync(int companyChangeId, CancellationToken cancellationToken)
{
    // AddressChange.CompanyChangeId has DeleteBehavior.ClientCascade.
    await AddressChanges.Where(change => change.CompanyChangeId == companyChangeId).LoadAsync(cancellationToken);
}

private async Task ApplyProfileChangeReferentialActionsAsync(int profileChangeId, CancellationToken cancellationToken)
{
    // AddressChange.ProfileChangeId has DeleteBehavior.ClientCascade.
    await AddressChanges.Where(change => change.ProfileChangeId == profileChangeId).LoadAsync(cancellationToken);
}

This does the trick but isn't a very elegant solution. It's not easy to see why the code is doing what it's doing (that's why the extensive comments) and it's a maintanence burden.

Since EF Core already knows all the relationships and their DeleteBehavior, it would be nice if it could do this automatically. Maybe there could be a few more options added to DeleteBehavior that behave like ClientSetNull and ClientCascade but find all affected entities to update? Or maybe this could be a global option that would instead apply to all ClientSetNull and ClientCascade relationships?

[ajcvickers]

@Neme12 This is something we have discussed before in a slightly different form. However, the idea is to use a bulk update instead of loading all dependent entities into the context. Putting this on the backlog as a separate issue to consider in the future.

[iuridosanjos]

Is there any other way around this while it's not yet implemented in EF Core?

[ajcvickers]

@iuridosanjos Use context.Database.ExecuteSqlRaw() or one of the related overloads to execute raw SQL queries.

[ajcvickers]

See also #21521.