我在删除具有指向正在删除的帐户实体的外键的表时收到异常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;
}
}
字符串
3条答案
按热度按时间rur96b6h1#
可能需要使用
PRAGMA foreign_keys = ON
启用外键级联删除,请在此处检查答案:ON DELETE CASCADE in sqlite3以及在此处从c#运行该查询:How to enable foreign key cascade delete by default in SQLite?如果没有此设置,您也可以事先手动删除相关记录。
ktca8awb2#
查看您的代码,您似乎是先删除帐户,然后尝试保存更改。约束失败,因为子实体仍然引用您删除的内容。
要解决这个问题,请尝试颠倒顺序。例如,先删除子实体,然后删除帐户。
也可以使用启用级联删除。
kmbjn2e33#
感谢两个答复.我发现了另一个解决方案,以删除帐户在两行代码:
字符串
很有魅力。