Sqlite: Deleting a database previously referenced by a different connection string throws

[ajcvickers]

I suspect this is because connection pooling still has a connection open for the database, but this is not discovered because the two connections use different connection strings. (One has Command Timeout set, the other does not.)

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
}

public class SomeContext : DbContext
{
    private readonly string _connectionString;

    public SomeContext(string connectionString)
    {
        _connectionString = connectionString;
    }

    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlite(_connectionString);
}


public static class SqliteSamples
{
    public static void Main()
    {
        using (var context = new SomeContext("DataSource=test.db"))
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }

        using (var context = new SomeContext("Command Timeout=60;DataSource=test.db"))
        {
            context.Database.EnsureDeleted(); // Throws here
            context.Database.EnsureCreated();
        }
    }
}

Unhandled exception. System.IO.IOException: The process cannot access the file 'C:\dotnet\efdocs\samples\core\Miscellaneous\NewInEFCore6\bin\Debug\net6.0\test.db' because it is being used by another process.
   at System.IO.FileSystem.DeleteFile(String fullPath) in System.Private.CoreLib.dll:token 0x6005f1a+0x15
   at System.IO.File.Delete(String path) in System.Private.CoreLib.dll:token 0x6005e48+0x0
   at Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteDatabaseCreator.Delete() in Microsoft.EntityFrameworkCore.Sqlite.dll:token 0x6000146+0x63
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureDeleted() in Microsoft.EntityFrameworkCore.Relational.dll:token 0x600057c+0x8
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureDeleted() in Microsoft.EntityFrameworkCore.dll:token 0x6001b36+0x0
   at SqliteSamples.Main() in C:\dotnet\efdocs\samples\core\Miscellaneous\NewInEFCore6\SqliteSamples.cs:line 41

[ajcvickers]

Notes from triage: this could be fixed in EF Core by clearing all pools before deleting the file. However, it might also make sense for M.D.Sqlite to handle connections to the same file in some way.

[ajcvickers]

Decision from triage: call ClearAllPools in EF.

[ajcvickers]

Reverted to get test runs working reliably again.

[bricelam]

A better fix might be to update the pooling code to use the same connection pool for the same database file as much as possible. In other words, it could normalize the path used in Data Source and ignore these connection string keywords:

• Foreign Keys
• Recursive Triggers
• Default Timeout