Delete all related rows to user using EF .NET CORE

I have little problem with data delete. Let s assume that I have to delete user and all related rows to this user. I wrote some code in c#, but it takes ages to complete. Here is my some of my code. My question is how to delete all related data to user the fastest solution. I thought about some trigger on database, is it good ?

_context.ChangeTracker.LazyLoadingEnabled = false;
        _context.ChangeTracker.AutoDetectChangesEnabled = false;

        IQueryable<Domain.Entities.Identity.User> customObjectQueryable = _context.Users.Where(x => x.Id == request.UserId);

        var firstQuery = customObjectQueryable
            .Include(x => x.NotificationsCreated)
            .Include(x => x.UsersMeetings)
            .Include(x => x.UserVisibility)
            .Include(x => x.UsersGroups)
            .Include(x => x.Roles)
            .Include(x => x.RefreshTokens)
            .Include(x => x.Groups)
            .Include(x => x.Notifications)
            .Include(x => x.Meetings)
            .AsNoTracking();

Answer

You rely on CascadeOnDelete when deleting your Users. This is a relatively slow process, because for every user that is to be deleted, your database management system will have to check all related tables to see if there is a relation with the user that is to be deleted.

It is way faster to first delete the related objects and then delete the customer:

using(var dbContext =  new MyDbContext(...))
{
    Customer userToDelete = dbContext.Users.Where(...).FirstOrDefault();

    // before deleting this user, remove all related items
    var rolesToRemove = dbContext.Roles.Where(role => role.UserId == userToDelete.Id);
    dbContext.Roles.RemoveRange(rolesToRemove.ToList());

    var notificationsToRemove = dbContext.Notifications
        .Where(notification => notification.UserId == userToDelete.Id);
    dbContext.Notifications.RemoveRange(notificationsToRemove.ToList());

    ... // etc.
    dbContext.User.Remove(userToDelete();
    dbContext.SaveChanges();
}

The problem with Entity Framework is, that you need to fetch the items before you can remove them.

If you need to remove users often, then you can bypass this fetching by creating a stored procedure.

class MyDbContext : DbContext
{
    public DbSet<User> Users {get; set;}
    ... // etc

    protected override OnModelCreating(...)
    {
         ... // fluent API, table names, column names, relations between tables, ...

         this.CreateStoredProcedureRemoveUser()
    }

    private void CreateStoredProcedureUpdateUsageCosts(DemoContext context)
    {
        const string sqlText = @"Create Procedure RemoveUser @UserId int as
        Begin
           ... // Sql code to remove UserMeetings, Roles, Groups, etc
           ... // Sql code to remove the user
        End";

        this.Database.ExecuteSqlComment(sqlText);
    }

    // Procedure to remove the user:
    public void RemoveUser(int userId)
    {
        const string sqlCommandRemoveUser= @"Exec RemoveUser @UserId";
        object[] commandParameters = new object[]
        {
            new SqlParameter(@"@UserId", userId),
        };
        this.Database.ExecuteSqlCommand(sqlCommandRemoveUser, commandParameters);

    }
}

Usage:

int userId = ...
using (var dbContext = new MyDbContext())
{
    dbContext.RemoveUser(userId);
}

Careful: because the procedure is executed immediately you can’t go back once you’ve called the method. If you want a fallback, for instance, you get an exception when removing Groups after the Roles are removed, use DbContext.Database.BeginTranscation / EndTransaction, to get back to the original situation.