DbContext doesn't release SQLite database

Question

First, these are my intentions:

1. Create a DbContext on SQLite
2. Read and write from/to it
3. Close context
4. Move the file to another location

Points 1-3 work perfectly. The problem starts when I try to move the database. I get an error stating that:

'The process cannot access the file because it is being used by another process.' 

How can I resolve this?

First, I create a context. I have to use it in several methods, and I don't want to create it every time I need it. So I am storing it as a member.

_sqliteContext = new SqlLiteContext(sqliteContextName);

Then I want to access a table called sync and get its latest entry.

var sync = _sqliteContext.Syncs.OrderByDescending(s => s.Date);
_lastSync = sync.Any() ? sync.First().Date : new DateTime(0);

That's it. Then I close the context.

_sqliteContext.Dispose();

And try to move the file.

File.Move(sqliteUploadLocation, sqliteDownloadLocation);

This is where I get the exception.

When I replace the selection with an insert, like the following:

var sync = new Sync { Id = Guid.NewGuid().ToString(), Date = DateTime.Now };
_sqliteContext.Syncs.Add(sync);
_sqliteContext.SaveChanges();

This works, and I can move the database. Any ideas why my selection doesn't release its lock?

Update

---

// Start synchronisation.
new SyncManager(mssqlString, sqliteUploadLocation).Start();

// Move file from upload to download location.
try
{
    File.Move(sqliteUploadLocation, sqliteDownloadLocation);
}
catch (Exception ex)
{
    Console.WriteLine("Moving failed!");
    Console.WriteLine(ex.Message);
}

public void Start()
{
    // Create connection string for the sqlite database.
    const string sqliteContextName = "SqLiteContext";
    var sqliteConnStringSettings = new ConnectionStringSettings
        {
            Name = sqliteContextName,
            ConnectionString = "Data Source=" + _sqliteUploadLocation + ";Version=3;BinaryGUID=False;",
            ProviderName = "System.Data.SQLite"
        };

    // Read configuration, delete available connection strings and add ours.
    var conf = ConfigurationManager.OpenMachineConfiguration();
    var connStrings = conf.ConnectionStrings;
    connStrings.ConnectionStrings.Remove(sqliteContextName);
    connStrings.ConnectionStrings.Add(sqliteConnStringSettings);
    try
    {
        conf.Save(ConfigurationSaveMode.Minimal);
    }
    catch (Exception)
    {
        // Insufficient rights to save.
        return;
    }

    ConfigurationManager.RefreshSection("connectionStrings");

    // Create connection to the sqlite database.
    _sqliteContext = new SqlLiteContext(sqliteContextName);

    // Create connection to the mssql database.
    _mssqlContext = new MsSqlContext(_mssqlConnString);

    // Read last sync date.
    var sync = _sqliteContext.Syncs.OrderByDescending(s => s.Date);
    _lastSync = sync.Any() ? sync.First().Date : new DateTime(0);

    // Synchronize tables.
    //SyncTablePerson();
    //SyncTableAddressAllocation();

    // Creates an entry for this synchronisation.
    CreateSyncEntry();

    // Release resources.
    _sqliteContext.Dispose();
    _mssqlContext.Dispose();
}

private void CreateSyncEntry()
{
    var sync = new Sync { Id = Guid.NewGuid().ToString(), Date = DateTime.Now };
    _sqliteContext.Syncs.Add(sync);
    _sqliteContext.SaveChanges();
}

Update 2

---

public class SqlLiteContext : Context
{
    public DbSet<Sync> Syncs { get; set; }

    public SqlLiteContext(string connectionString)
        : base(connectionString)
    {
        Database.SetInitializer(new NoOperationStrategy<SqlLiteContext>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new PersonConfig());
        modelBuilder.Configurations.Add(new AddressAllocationConfig());
        modelBuilder.Configurations.Add(new AddressConfig());
        modelBuilder.Configurations.Add(new SyncConfig());
    }
}

public class NoOperationStrategy<T> : IDatabaseInitializer<T> where T : DbContext
{
    public void InitializeDatabase(T context)
    {
    }
}

public abstract class Context : DbContext
{
    public DbSet<Person> Persons { get; set; }
    public DbSet<AddressAllocation> AddressAllocations { get; set; }
    public DbSet<Address> Addresses { get; set; }

    protected Context(string connectionString)
        : base(connectionString)
    {
    }
}

Refactoring with using

---

using (var sqliteContext = new SqlLiteContext(_sqliteContextName))
{
    // Read last sync date.
    var sync = sqliteContext.Syncs.Select(s => s).OrderByDescending(s => s.Date);
    var lastSync = sync.Any() ? sync.First().Date : new DateTime(1900, 1, 1);

    using (var mssqlContext = new MsSqlContext(_mssqlConnString))
    {
        SyncTablePerson(sqliteContext, mssqlContext, lastSync);
        SyncTableAddressAllocation(sqliteContext, mssqlContext, lastSync);

        // Save server changes.
        mssqlContext.SaveChanges();
    }

    // Creates an entry for this synchronisation.
    sqliteContext.Syncs.Add(new Sync { Id = Guid.NewGuid().ToString(), Date = DateTime.Now });

    // Save local changes.
    sqliteContext.SaveChanges();
}

Answer 1

I found another topic with the same problem. After i refactored my code, i added

GC.Collect();

That removed the file lock and i could move the file.

see: https://stackoverflow.com/a/14151917/2462736

Answer 2

Two things jump to mind:

1. Make sure Visual Studio isn't locking the database file. Open Server Explorer and if there is a connection to the file make sure its closed or removed altogether.
2. It's likely that connection pooling is what is holding the connection open. Disable pooling in your connection string like this:

Data Source=e:\mydb.db;Version=3;Pooling=False;

As Matt pointed out you should really use a using statement rather than calling dispose manually. That way if there is an exception the resources are always released properly.