Getting exception when deleting entities with `DeleteBehavior.ClientCascade` defined

Question

I am getting an Exception InnerException = {"SQLite Error 19: 'FOREIGN KEY constraint failed'."} while deleting tables that have foreign keys pointing to the deleting Account entities. I have marked the line of code which throws an exception (in DeleteAllUserAccounts method). I have defined properly DeleteBehavior.ClientCascade in configuration section of my DataContext (see below). Why am I still getting the exception?

 public class DataContext : IdentityDbContext<Account>
{
    private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
    public DbSet<SystemInfo> SystemInformation { get; set; }

    public DbSet<Account> Accounts { get; set; }
    public DbSet<Schedule> Schedules { get; set; }

    public DbSet<Function> UserFunctions { get; set; }
    public DbSet<SchedulePoolElement> SchedulePoolElements { get; set; }

    public DbSet<RefreshToken> RefreshTokens { get; set; }

    private readonly IConfiguration Configuration;


    
    public DataContext(IConfiguration configuration, DbContextOptions options) : base(options)
    {
        Configuration = configuration;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        // connect to sqlite database
        options.UseSqlite(Configuration.GetConnectionString("WebApiDatabase"));
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        //modelBuilder.Entity<Account>().HasMany(e => e.RefreshTokens).WithOne(e => e.Account).IsRequired();
        modelBuilder.Entity<SystemInfo>().HasData(
        new SystemInfo
        {
            Id = 1,
            NoOfEmailsSentDayily = 1,
            autoEmail = false
        });
        modelBuilder.Entity<Account>()
            .HasMany<Schedule>(a => a.Schedules)
            .WithOne()
            .OnDelete(DeleteBehavior.ClientCascade);
        modelBuilder.Entity<Account>()
            .HasMany<Function>(a => a.UserFunctions)
            .WithOne()
            .OnDelete(DeleteBehavior.ClientCascade);
        modelBuilder.Entity<Account>()
            .HasMany<RefreshToken>(a => a.RefreshTokens)
            .WithOne(r=> r.Account)
            .OnDelete(DeleteBehavior.ClientCascade);
    }
}


public IEnumerable<AccountResponse> DeleteAllUserAccounts()
        {
            log.Info("DeleteAllUserAccounts before locking");
            semaphoreObject.Wait();

            using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
            {
                try
                {
                    var foundAccounts = _context.Accounts.Where(x => x.Role != Role.Admin).ToArray().ToList();
                    int count = foundAccounts.Count();
                    _context.Accounts.RemoveRange(foundAccounts);

                    _context.SaveChanges(); <----- Exception(InnerException = {"SQLite Error 19: 'FOREIGN KEY constraint failed'."})
                    transaction.Commit();

                    var accounts = _context.Accounts;
                    return _mapper.Map<IList<AccountResponse>>(accounts);
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Console.WriteLine(Thread.CurrentThread.Name + "Error occurred.");
                    log.Error(Thread.CurrentThread.Name + "Error occurred in Delete:", ex);
                    throw;
                }
                finally
                {
                    semaphoreObject.Release();
                    log.Info("Delete after locking");
                }
            }
        }

public class Account : IdentityUser
    { 
        ...

        public List<Schedule> Schedules { get; set; }
        public List<Function> UserFunctions { get; set; }
        public List<RefreshToken> RefreshTokens { get; set; }

        public bool OwnsToken(string token) 
        {
            return this.RefreshTokens?.Find(x => x.Token == token) != null;
        }
    }

Answer 1

Likely need to enable foreign key cascade deletes with PRAGMA foreign_keys = ON, check answer here: ON DELETE CASCADE in sqlite3 And here for running that query from c#: How to enable foreign key cascade delete by default in SQLite?

Without this setting you could also just manually delete the related records beforehand.

Answer 2

Looking at your code, it appears you are removing the accounts first, and then trying to save changes. The constraint is failing since the child entities still reference what you deleted.

To solve, try reversing the order. e.g. remove the child entities first, then remove the account.

Or you can use enable cascade deletes.

Answer 3

Thanks for both replies. I found another solution to remove accounts in two lines of code:

var foundAccounts = _context.Accounts.Include(x => x.RefreshTokens).Include(x => x.Schedules).Include(x => x.UserFunctions).Where(x => x.Role != Role.Admin).ToArray().ToList();
                    _context.Accounts.RemoveRange(foundAccounts);
_context.SaveChanges();
                    transaction.Commit();

Works like charm.