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();
}

Formication

Could you show your actual code, so everything is in the same block (I mean, it's easier to see the actual execution flow which is important because of the context lifetime)? – Aigrette

@Aigrette Done. Do you need more? – Formication

What is SqlLiteContext? Does it encapsulate an Entity Framework context? – Aigrette

An idea: Is there anything like connection pool for SQLite? In such case turn it off. – Acephalous

hey :) i added the implementation of sqlitecontext. indeed its an ecapsulation of the entity framework context. below update 2 you see its implementation. hope this helps... – Formication

You should really prefer usage of "using" instead of manually calling Dispose. If anything throws (any call between the initializer and the Dispose), it will not call Dispose over the SqlLiteContext. Give that a try and see if the behavior is different from directly calling Dispose as well-- its resolution of how it calls the internal protected method Dispose(bool) may be different. – Catatonia

i already used "using", but no difference there. in my researches with google i found the source of the problem. dispose() of the sqlitcontext doesn't dispose every connection. with an sqlitedataadapter i could dispose every connection type manually (select, insert, update, delete). but here i don't see any solution. that sucks... – Formication

Why do you need to move the database file? – Pity

the database will be uploaded with a wcf service on a server. there it will be synchronized with a mssql db. after that process is finished, the database will be moved to a download location where it can be downloaded again... because the sync process isnt connected to the wcf service, and there's no way to check if the sync process is finished, i move the file after the sync process is done to signalize the wcf service that the sync is complete... – Formication

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://mcmap.net/q/175278/-sqlite-keeps-the-database-locked-even-after-the-connection-is-closed

Formication

To make sure GC really does its job synchronously, also call GC.WaitForPendingFinalizers() – Ogle

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.

Congou

Thx for the hints. I refactored my code but still, the database lock doesn't go away. I added my new code to the question at the top. By adding 'pooling=false' to the data source didn't helped... i'm really frustrated... :( – Formication

Thanks, Pooling=false fixed the issue with the file used by another process – Coonskin

Any idea why Pooling=false would be keeping it open? – Heinrick