How to dispose SQLiteCommands created internally by Entity Framework?

Question

I have problems with System.Data.SQLite (v1.0.94.0) writing "SQLite error (5): database is locked" to the log when opening a new connection. This seems to be caused by Entity Framework (v6.0) not disposing all the SQLiteCommands.

I am using code first with Entity Framework and can therefore not use this.

One scenario, where the SQLiteCommand is not disposed, is using the IQueryable.

IQueryable<TEntity> query = context.Set<TEntity>();
var result = query.ToList();

How do I keep track of these undisposed commands, so I can dispose them manually?

I would like to avoid calling GC.Collect();

`DbContext` implements `IDisposable` simply dispose of that, preferably by putting it in a using statement. — Ben Robinson, Nov 04 '14 at 12:50
http://stackoverflow.com/questions/4348860/the-database-file-is-locked-with-system-data-sqlite — Paul Zahra, Nov 04 '14 at 13:13
I am already disposing the `DBContext`, but I will check if I have two contexts open at the same time. — Jens Christian Pedersen, Nov 04 '14 at 13:18
@PaulZahra The discussed forum thread does not exist anymore. I do not know how I can dispose commands and readers immediately when it is happening inside Entity Framework — Jens Christian Pedersen, Nov 04 '14 at 13:22
@BenRobinson It seems that even while the DBContext may be disposed of correctly the issue will still be there, it seems as though there are internal object keeping the connection active. http://www.mail-archive.com/sqlite-users%40sqlite.org/msg74738.html — Paul Zahra, Nov 04 '14 at 13:41
If this was a bug with EF, everyone that uses it would have issues even when targeting SQL Server. Besides, that's a discussion about an older SQLite provider version, that was supposedly fixed — Panagiotis Kanavos, Nov 04 '14 at 13:45
@JensChristianPedersen are you using connection pooling? Check whether you have "Pooling=True;" in your connection string, or try setting it to false. Pooled connections are retained and reused whenever you call Connection.Open, until the application terminates. This can be a problem in unit tests if you try to delete the database before ADO.NET had a chance to release the connections — Panagiotis Kanavos, Nov 04 '14 at 13:53
I had the same issue but found the following workaround: http://stackoverflow.com/a/38268171/1185226 — Hallupa, Jul 11 '16 at 08:08

Paul Zahra · Accepted Answer · 2014-11-04T15:25:27.217

This discussion seems to say that defining interop legacy settings is a workaround (instead of going back to an older version that works... http://www.mail-archive.com/sqlite-users%40sqlite.org/msg75310.html

" The root cause in this case is that the Entity Framework indirectly creates a SQLiteCommand object and then subsequently fails to dispose it. Furthermore, it does not appear to expose these internally created commands, nor a way to explicitly dispose of them, leaving no means for an outside caller to cleanup. This seems quite strange since almost all IDbCommand implementations "in the wild" would likely require native resources of one kind or another. Also, even the DbCommand base class provided by the .NET Framework itself implements IDisposable (i.e. the class used as the base class for SQLiteCommand). "

" Yes, the InteropLegacyClose MSBuild property in the "SQLite.NET.Settings.targets" file needs to be set to "true" and the define "INTEROP_LEGACY_CLOSE=1;" needs to be added to the INTEROP_EXTRA_DEFINES property in the SQLite.Interop.20XX.[vs]props file for the version of Visual Studio being used. "

UPDATE: dotConnect for SQLLite looks the business http://www.devart.com/dotconnect/sqlite/ there is a free and pro version which isn't free... might be worth checking it out.

UPDATE2: In response to " If this was a bug with EF, everyone that uses it would have issues even when targeting SQL Server. – Panagiotis Kanavos"

Not at all, it seems the bug is only reported by SQLLite users around 1.0.8xxx where they changed their dispose methods... the EF provider can vary, e.g. you could set it to SQLLite (bug reported) or SQLServer (bug not reported) etc like below...

<system.data>
  <DbProviderFactories>
    <add name="SQLite Data Provider"
          invariant="System.Data.SQLite"
          description="Data Provider for SQLite"
          type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
  </DbProviderFactories>
</system.data>

or

<providers> 
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> 
</providers>

If this was a bug with EF, everyone that uses it would have issues even when targeting SQL Server. — Panagiotis Kanavos, Nov 04 '14 at 13:44
Really? "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world." makes me doubt that people using EF and SQL Server would be affected. — Paul Zahra, Nov 04 '14 at 14:59
@PaulZahra a bug that appears for specific providers while the EF version remains the same is by definition a bug of the specific providers. I didn't say you can't change the providers, on the contrary, I said that if this was an EF problem it would appear in other providers as well. Failing to close a connection will appear as ever increasing connections in SQL Server connections but as a locked database in SQLite. The SQLite.NET Provider has a bug, not EF — Panagiotis Kanavos, Nov 06 '14 at 08:54
@PanagiotisKanavos If someone that makes spanners for cars makes an improved new spanner and stops making the old... it's the spanner maker that must change and not the car?, it's debatable, let's leave it at that... That is the case if there is a generic class that handles everything, if there are specialised 'disposes' for each provider, then that will not be the case... You seem to be ignoring the fact that many SQLLite / EF users report this bug, and that no SQL Server / EF users report it. — Paul Zahra, Nov 06 '14 at 09:13

score 0 · Answer 2 · edited May 23 '17 at 11:58

Oliver Wickenden posted an effective workaround in this thread: System.Data.SQLite Close() not releasing database file

public static class ClearSQLiteCommandConnectionHelper
{
    private static readonly List<SQLiteCommand> OpenCommands = new List<SQLiteCommand>();

    public static void Initialise()
    {
        SQLiteConnection.Changed += SqLiteConnectionOnChanged;
    }

    private static void SqLiteConnectionOnChanged(object sender, ConnectionEventArgs connectionEventArgs)
    {
        if (connectionEventArgs.EventType == SQLiteConnectionEventType.NewCommand && connectionEventArgs.Command is SQLiteCommand)
        {
            OpenCommands.Add((SQLiteCommand)connectionEventArgs.Command);
        }
        else if (connectionEventArgs.EventType == SQLiteConnectionEventType.DisposingCommand && connectionEventArgs.Command is SQLiteCommand)
        {
            OpenCommands.Remove((SQLiteCommand)connectionEventArgs.Command);
        }

        if (connectionEventArgs.EventType == SQLiteConnectionEventType.Closed)
        {
            var commands = OpenCommands.ToList();
            foreach (var cmd in commands)
            {
                if (cmd.Connection == null)
                {
                    OpenCommands.Remove(cmd);
                }
                else if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection = null;
                    OpenCommands.Remove(cmd);
                }
            }
        }
    }
}

He says: To use just call ClearSQLiteCommandConnectionHelper.Initialise(); at the start of application load. This will then keep a list of active commands and will set their Connection to Null when they point to a connection that is closed.

This worked a treat for me.

How to dispose SQLiteCommands created internally by Entity Framework?

2 Answers2

Linked