sqlite 删除定义了`DeleteBehavior.ClientCascade`的实体时出现异常

uinbv5nw  于 12个月前  发布在  SQLite
关注(0)|答案(3)|浏览(219)

我在删除具有指向正在删除的帐户实体的外键的表时收到异常InnerException = {"SQLite Error 19: 'FOREIGN KEY constraint failed'."}。我已经标记了抛出异常的代码行(在DeleteAllUserAccounts方法中)。我已经在DataContext的配置部分正确定义了DeleteBehavior.ClientCascade(见下文)。为什么我仍然收到异常?

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;
        }
    }

字符串

rur96b6h

rur96b6h1#

可能需要使用PRAGMA foreign_keys = ON启用外键级联删除,请在此处检查答案:ON DELETE CASCADE in sqlite3以及在此处从c#运行该查询:How to enable foreign key cascade delete by default in SQLite?
如果没有此设置,您也可以事先手动删除相关记录。

ktca8awb

ktca8awb2#

查看您的代码,您似乎是先删除帐户,然后尝试保存更改。约束失败,因为子实体仍然引用您删除的内容。
要解决这个问题,请尝试颠倒顺序。例如,先删除子实体,然后删除帐户。
也可以使用启用级联删除。

kmbjn2e3

kmbjn2e33#

感谢两个答复.我发现了另一个解决方案,以删除帐户在两行代码:

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();

字符串
很有魅力。

相关问题