C# EF - How to unit test a SQLite file in memory?

Question

In my unit tests, I'm currently making a temp copy of an SQLite file, running the method I'm testing, and then deleting the file.

The issue I'm running into with this, is when I have multiple unit tests using the same file, the tests will either all pass or just one of them will fail with the error System.IO.IOException : The process cannot access the file 'C:\~\temp_飲newKanji_食欠人良resourceKanji_decks.anki2' because it is being used by another process.. I even ran each test by itself 20 times in a row to make sure it wasn't just randomly failing by itself and they all passed.

I had a similar issue where this error would show up after I tried deleting the temp file and would always fail the test, even alone. The issue turned out to be that EF was holding the connection, even after I disposed it, and I was able to fix it by calling SqliteConnection.ClearPool() before _context.Dispose().

The stack trace is showing that it's still failing at the delete temp file line, but I'm not sure what else could be causing it. Besides maybe that the first unit test is some how keeping the file open, but if that was the case it should also be failing when it tries to delete the file in its own unit test.

The only alternative I could find was copying the file into memory, which shouldn't keep any files open, but I can't seem to figure out how to achieve that. Everything I've seen regarding in-memory SQLite has new DbContextOptionsBuilder<YourDbContext>().UseSqlite("DataSource=:memory:").Options and then manually adding all the data, but I can't find any examples on how to set this up using data from an existing SQLite file. How should I go about doing this?

I'm using C#, .NET 8, EF Core 8 Sqlite, and xUnit.

public class Anki2Context : DbContext, IAnki2Context
{
    public DbSet<Card> Cards { get; protected set; }
    public DbSet<Deck> Decks { get; protected set; }
    public DbSet<Note> Notes { get; protected set; }

    public Anki2Context(string dbPath) : base(GetOptions(dbPath))
    {
    }

    public Anki2Context(DbContextOptions<Anki2Context> options) : base(options)
    {
    }

    private static DbContextOptions<Anki2Context> GetOptions(string dbPath)
    {
        return new DbContextOptionsBuilder<Anki2Context>()
            .UseSqlite($"Data Source={dbPath}")
            .Options;
    }
}

public class Anki2Controller : IDisposable
{
    private readonly Anki2Context _context;

    public Anki2Controller(Anki2Context context)
    {
        _context = context;
    }

    public Anki2Controller(string dbPath) : this(new Anki2Context(dbPath))
    {
    }

    public void Dispose()
    {
        SqliteConnection.ClearPool((SqliteConnection) _context.Database.GetDbConnection());
        _context.Dispose();
    }
}

[Theory]
[InlineData("飲newKanji_食欠人良resourceKanji_decks.anki2", new[] { 1707169497960, 1707169570657, 1707169983389, 1707170000793 }, 1707160682667)]
public void Move_notes_between_decks(string anki2File, long[] noteIdsToMove, long deckIdToMoveTo)
{
    //Arrange
    string originalInputFilePath = _anki2FolderPath + anki2File;
    string tempInputFilePath = _anki2FolderPath + "temp_" + anki2File;
    File.Copy(originalInputFilePath, tempInputFilePath, true);//Copy the input file to prevent changes between unit tests
    Anki2Controller anki2Controller = new Anki2Controller(tempInputFilePath);
    List<Card> originalNoteDeckJunctions = anki2Controller.GetTable<Card>()
                                                        .Where(c => noteIdsToMove.Contains(c.NoteId))
                                                        .ToList();//Grab the current note/deck relations for the give note ids

    //Act
    bool movedNotes = anki2Controller.MoveNotesBetweenDecks(noteIdsToMove, deckIdToMoveTo);

    //Assert
    movedNotes.Should().BeTrue();//Function completed successfully
    List<Card> finalNoteDeckJunctions = anki2Controller.GetTable<Card>()
                                                        .Where(c => noteIdsToMove.Contains(c.NoteId))
                                                        .ToList();//Grab the current note/deck relations for the give note ids after running the function
    finalNoteDeckJunctions.Count().Should().Be(originalNoteDeckJunctions.Count());//No note/deck relations should have been removed/added
    finalNoteDeckJunctions.Select(c => c.DeckId).Should().AllBeEquivalentTo(deckIdToMoveTo);//All junction deckIds should be the given deckId

    //Cleanup
    anki2Controller.Dispose();
    File.Delete(tempInputFilePath);
}

Answer 1

This is how you can copy sqlite from physical file to in-memory:

Create a Fixture class:

public class TestDatabaseFixture: IDisposable
{
    private static readonly object _lock = new();
    private static bool _databaseInitialized;
    private const string SourceFile = @"This is absolute path to Sqlite file";
    private readonly SqliteConnection? _inMemoryDbConnection;
    private readonly DbContextOptions<ApplicationDbContext>? _contextOptions;

    public TestDatabaseFixture()
    {
        lock (_lock)
        {
            if (!_databaseInitialized)
            {
                _inMemoryDbConnection = new SqliteConnection(String.Format("Data Source = {0}", ":memory:"));
                _inMemoryDbConnection.Open();
                using (SqliteConnection source = new SqliteConnection(String.Format("Data Source = {0}", SourceFile)))
                {
                    source.Open();
                    source.BackupDatabase(_inMemoryDbConnection);
                }

                _contextOptions = new DbContextOptionsBuilder<ApplicationDbContext>()
                    .UseSqlite(_inMemoryDbConnection)
                    .Options;

                _databaseInitialized = true;
            }
        }
    }
    public ApplicationDbContext CreateContext()
        => new ApplicationDbContext(_contextOptions);

    public void Dispose()
    {
        _inMemoryDbConnection.Close();
        _inMemoryDbConnection.Dispose();
    }
}

Then you can use the DbContext in your test:

public class UnitTests1: IClassFixture<TestDatabaseFixture>
{
    TestDatabaseFixture fixture;

    public UnitTests1(TestDatabaseFixture fixture)
    {

        this.fixture = fixture;
    }

    [Fact]
    public void Test1()
    {
        var context = fixture.CreateContext();
        using var viewCommand = context.Database.GetDbConnection().CreateCommand();
        viewCommand.CommandText = @"
            SELECT count(*) FROM Artist;
        ";

        var result = viewCommand.ExecuteScalar();

        Assert.IsType<long>(result);
        Assert.NotEqual(0, result);
    }
}