Sqlite Cascade delete doesnt work

SQLCipher Issues
1

We are using zetetic SQLCipher with Entity Framework Core (5.0.7) on Windows 10 (Console app).
In the basic example we have 2 tables:

  1. User table: | Id(PK) | Username |
  2. Notification table: | UserID (FK) | Notification data |

We configure DBContext to delete Notification records by FK when User records are removed.
This doesn’t work in configuration zetetic SQLCipher + EFCore.
The same code works ok with default SQLite and EFCore.

The only difference in final binaries:
zetetic config: SQLitePCLRaw.bundle_zetetic provides SQLitePCLRaw.batteries_v2.dll v. 2.0.4.976
and sqlcipher.dll

basic config: e_sqlite.dll is used instead of sqlcipher.dll and standard SQLitePCLRaw.batteries_v2.dll

2

Hi @Oleksandr_Shchukin

There are a few scenarios where cascade deletes may not apply (documented here) within Entity Framework Core. Would you mind posting a small sample application (excluding the SQLCipher native library dependency) that replicates the behavior you are seeing? We would be happy to investigate this further.

3

CascadeDeletes.zip (12.7 KB)

Hi @developernotes - I have attached sample - please check Readme.md files on how to setup, build samples and how to reproduce the issue

4

Hi @Oleksandr_Shchukin

I was able to get the cascade deletes to work with SQLCipher. I adjusted your model builder example a bit:

modelBuilder
  .Entity<NotificationTest>()
  .HasOne(n => n.User)
  .WithMany(u => u.NotificationTests)
  .HasForeignKey(n => n.UserTestId)
  .OnDelete(DeleteBehavior.Cascade)
  .IsRequired();

I added a public navigation property to the UserTest model:

public List<NotificationTest> NotificationTests {get; set;}

Finally, I included the relationship in the user query (as documented in deleting a principal/parent):

var user = context.Users.OrderBy(e => e.Username)
  .Include(e => e.NotificationTests).First();