Database file not resetting properly, unless the app is restarted

Question

I'm creating a .NET MAUI Android app which uses a built-in local SQLite database file, CommunityMVVMToolKit simply because it's great and EntityFramework Core for mapping objects into database records and vice-versa.

I'm new to this framework, and unsure on how a database file should be handled, I settled on putting a fresh database with all the table schemas and no records as a raw resource in Resources/Raw folder. Upon starting, the app checks the predetermined location in which the DB file should exists, and if it's not there, it creates a copy of the fresh DB file in the said location. The reason why, is because (as far as I could find on the internet) assets bundled with the app are read-only, including the database file, so they have to be extracted to be modifiable.

The app comunicates well with the file, the data inside it persists between app launches, but I've run into a small problem when trying to implement a Reset functionality.

I wrote the code snippet below for testing purposes to see if the DatabaseFile methods work correctly:

[RelayCommand]
void PerformDatabaseFileTests()
{
    ClearText();
    try
    {
        //After starting the app the DB should already be there, check for it
        AppendText($"DATABASE FILE EXISTS: {DatabaseFile.Exists()}");

        //Delete it and check if it deleted
        AppendText("DELETING DB FILE...");
        DatabaseFile.Delete();
        AppendText($"DATABASE FILE EXISTS: {DatabaseFile.Exists()}");

        //Create it again
        AppendText("CREATING DB FILE...");
        MainThread.InvokeOnMainThreadAsync(DatabaseFile.Create);
        AppendText($"DATABASE FILE EXISTS: {DatabaseFile.Exists()}");
    }
    catch (Exception ex)
    {
        AppendText("EXCEPTION WAS THROWN!");
        AppendText(ex.ToString());
        AppendText(ex.Message);
    }
}

This method contained inside a viewmodel of a debug page does the following:

• Checks if the DB file exists in the location

• Try to delete the DB file and check if it exists

• Create a fresh copy and check if it exists

Getting to the point, the problem is that when I modify the records (adding, updating, removing) in the database, perform the aformentioned DB file test method and try to read the contents of the database file, the modifications persist. In a file which was supposed to be deleted and reconstructed from a fresh template file.

The underlying File.Exists() method does report that the DB file exists before the deletion, and does not exist after deletion, then exists again after creation.

Also, worth mentioning that performing the DB file test method, turning the app off and then back on without querying any data from it, seems to correctly reset the DB file. While this workaround does work, I really wish the reset could be done without the need of restarting the app.

Below are contents of the DatabaseFile class:

public static class DatabaseFile 
{ 
    public static string FullPath => 
        Path.Combine(FileSystem.Current.AppDataDirectory, Filename);  
  
    public static async Task Create()
    {
        using Stream inputStream = await FileSystem.Current.OpenAppPackageFileAsync(Filename);
        using FileStream outputStream = File.Create(FullPath);
        await inputStream.CopyToAsync(outputStream);
    }
    public static void Delete()
    {
        if (Exists()) 
            File.Delete(FullPath);
    }
    public static bool Exists() =>
        File.Exists(FullPath);
}

Perhaps a database file shouldn't be handled like any other MauiAsset, at all. Is there a better way of shipping an Android app with a built-in local database?

Edit: The connection to my DB file, AFAIK, is established everytime a new DbContext instance is created. Below are contents of the OnConfiguring() method inside my DatabaseContext class, which derives the DbContext:

public partial class DatabaseContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            SqliteConnectionStringBuilder builder = new SqliteConnectionStringBuilder()
            {
                DataSource = Path.Combine(FileSystem.Current.AppDataDirectory, DatabaseFile.Filename),
                Mode = SqliteOpenMode.ReadWriteCreate,
                ForeignKeys = true
            };
        optionsBuilder.UseSqlite(builder.ToString());
    }
}

Did I incorrectly assume that the connection is automatically closed when an instance of DbContext is disposed?

Answer 1

I've messed around and finally found a solution, here's what I've done:

Changed DatabaseFile.Delete method to the following:

    public static async Task Delete()
    {
        using var context = new DatabaseContext();
        await context.Database.EnsureDeletedAsync();
    }

The EnsureDeleted is the essential method that I was looking for. I also changed the DatabaseFile.Delete method to be asynchronous and awaited whenever its called, just to be sure.

After executing my slightly changed testing method, the modifications are no longer persisting in the fresh database. The only change in that method was awaiting the DatabaseFile.Delete method:

    AppendText("DELETING DB FILE...");
    await DatabaseFile.Delete();
    AppendText($"DATABASE FILE EXISTS: {DatabaseFile.Exists()}");