System.Data.SQLite

When using EF+System.Data.SQLite or Linq2SQL+System.Data.SQLite some objects are not being disposed properly. I inserted line "KillSQLiteInterop(); GC.Collect();" after using{} to illustrate that.

using System;
using System.Data.Linq.Mapping;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;

namespace Test
{
    public static class Program
    {
        public static void Main()
        {
            var tempFolder = Path.GetTempPath();
            string tempFileName = Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString("N") + ".sqlite");
            using (var connMain = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + tempFileName + ";Pooling=false;Enlist=N;FailIfMissing=False;Journal Mode=Off;"))
            {
                connMain.Open();
                ExecuteSqlQuery(connMain, "PRAGMA locking_mode = NORMAL");
                ExecuteSqlQuery(connMain, "CREATE TABLE INSIGHTS(PK INTEGER PRIMARY KEY, DATE VARCHAR, TEXT VARCHAR, PILLAR VARCHAR);");
                ExecuteSqlQuery(connMain, "INSERT INTO INSIGHTS(PK, DATE, TEXT, PILLAR) VALUES(1,'2013/01/01', 'test1', 'test 1');");
                using (var ctx = new System.Data.Linq.DataContext(connMain))
                {
                    var insights = ctx.GetTable<Insights>();
                    var query = from ins in insights where ins.Pillar == "test 1" select ins;
                    var list = query.ToList();
                }
                connMain.Close();
            }// basically all SQLite stuff should be disposed here
            KillSQLiteInterop(); GC.Collect(); //at that point we will have an AccessViolationException
            try { File.Delete(tempFileName); } //if we would comment out the line above, we will have another exception here
            catch (Exception ex) { Console.WriteLine(ex.ToString()); }
            Console.WriteLine("Done!");
            Console.ReadLine();
        }

        public static int ExecuteSqlQuery(System.Data.SQLite.SQLiteConnection conn, string query)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = query;
                return cmd.ExecuteNonQuery();
            }
        }

        [Table()]
        public class Insights
        {
            [Column]
            public int PK { get; set; }
            [Column]
            public string Date { get; set; }
            [Column]
            public string Text { get; set; }
            [Column]
            public string Pillar { get; set; }
        }

        private static void KillSQLiteInterop()
        {
            var handle = GetModuleHandle("SQLite.Interop.dll");
            if (handle != IntPtr.Zero) while (FreeLibrary(handle)) ;
        }

        [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Auto)]
        private static extern IntPtr GetModuleHandle([In]string lpModuleName);
        [DllImport("kernel32.dll", SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        static extern bool FreeLibrary([In]IntPtr hModule);
    }
}

Exception info:

System.AccessViolationException was unhandled
  Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
  Source=System.Data.SQLite
  StackTrace:
       at System.Data.SQLite.UnsafeNativeMethods.sqlite3_finalize_interop(IntPtr stmt)
       at System.Data.SQLite.SQLiteBase.FinalizeStatement(SQLiteConnectionHandle hdl, IntPtr stmt)
       at System.Data.SQLite.SQLiteStatementHandle.ReleaseHandle()
       at System.Runtime.InteropServices.CriticalHandle.Cleanup()
       at System.Runtime.InteropServices.CriticalHandle.Dispose(Boolean disposing)
       at System.Runtime.InteropServices.CriticalHandle.Finalize()

Disposing of SQLiteConnection should explicitely dispose all connected SQLiteConnectionHandles and SQLiteStatementHandles. If that will introduce some breaking changes, at least, please fire some events when objects with unmanaged part are being created - that will allow to manually dispose them when they are no longer needed.

Also see: http://www.mail-archive.com/sqlite-users@sqlite.org/msg67226.html

I suspect that LINQ is not disposing of the IDisposable objects that it creates
internally to satisfy the query, in the following block of code:

using (var ctx = new System.Data.Linq.DataContext(connMain))
{
  var insights = ctx.GetTable<Insights>();
  var query = from ins in insights where ins.Pillar == "test 1" select ins;
  var list = query.ToList();
}

Thanks for quick reply.

Basically, yes, as SQLite + pure ADO.Net runs without any noticeable issues.
I know about at least one issue with System.Data.Objects.ObjectContext.ExecuteStoreQueryInternal<TElement>. But I doubt if Microsoft will fix one of the .Net FrameWork core assembly because the same code with SQL CE provider runs without any issues.

Most important - these objects are being created internally and developer has no control over it.

As a temporary workaround for my project, I made a local copy of System.Data.SQLite code and added firing of a static event from SQLiteConnectionHandle(IntPtr db, bool ownHandle) and SQLiteStatementHandle(SQLiteConnectionHandle cnn, IntPtr stmt) constructors. This allows me to monitor such objects (along with SQLiteConnection and SQLiteCommand) and dispose them explicitely if they were not disposed after using statement. But local fork is not a best approach.

Could you propose a more reliable solution which will be compatible with EF?

Changing to feature request.  I'm going to add events to help monitor when any
IDisposable objects are created.  That way the developer can dispose of them even
if the framework decides not to.

See check-in [85f27cc9f8].  The static OnChanged event for the connection should
now be capable of notifying the application properly when public IDisposable
objects are created.

Thank you. I added my SQLiteResourceScope class to monitor these objects. I believe that solved most of issues for Linq2SLQ, but not for EF; for EF I still see the exception below and the DB file remains locked.

System.AccessViolationException occurred
  Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
  Source=System.Data.SQLite
  StackTrace:
       at System.Data.SQLite.UnsafeNativeMethods.sqlite3_finalize_interop(IntPtr stmt)
       at System.Data.SQLite.SQLiteBase.FinalizeStatement(SQLiteConnectionHandle hdl, IntPtr stmt) in SQLiteBase.cs:line 672

Please find the example for EF and SQLiteResourceScope below.

using System;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Data.SQLite;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Common;

namespace Test
{
    public static class Program
    {
        public static void Main()
        {
            var tempFolder = Path.GetTempPath();
            string tempFileName = Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString("N") + ".sqlite");
            using (var scope = new SQLiteResourceScope())
            using (var connMain = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + tempFileName + ";Pooling=false;Enlist=N;FailIfMissing=False;Journal Mode=Off;"))
            {
                connMain.Open();
                ExecuteSqlQuery(connMain, "PRAGMA locking_mode = NORMAL");
                ExecuteSqlQuery(connMain, "CREATE TABLE INSIGHTS(PK INTEGER PRIMARY KEY, DATE VARCHAR, TEXT VARCHAR, PILLAR VARCHAR);");
                connMain.Close();
                using (var ctx = new MobileImageContext(connMain, false))
                {
                    var dummy = ctx.Insights.Take(0).FirstOrDefault();
                    ctx.Database.Connection.Open();
                    ctx.Database.ExecuteSqlCommand("PRAGMA locking_mode = NORMAL");
                    var insight = ctx.Insights.Create();
                    insight.PK = 1; insight.Date = "2013/01/01"; insight.Text = "test1"; insight.Pillar = "test 1";
                    ctx.Insights.Add(insight);
                    ctx.Entry(insight).State = System.Data.EntityState.Added;
                    ctx.SaveChanges();
                    var query = from ins in ctx.Insights where ins.Pillar == "test 1" select ins;
                    var list = query.ToList();
                    ctx.Database.Connection.Close();
                }
            }
            KillSQLiteInterop(); GC.Collect();
            try { File.Delete(tempFileName); }
            catch (Exception ex) { Console.WriteLine(ex.ToString()); }
            Console.WriteLine("Done!");
            Console.ReadLine();
        }

        public static int ExecuteSqlQuery(System.Data.SQLite.SQLiteConnection conn, string query)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = query;
                return cmd.ExecuteNonQuery();
            }
        }
        
        private static void KillSQLiteInterop()
        {
            var handle = GetModuleHandle("SQLite.Interop.dll");
            if (handle != IntPtr.Zero)
                while (FreeLibrary(handle)) ;
        }

        [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Auto)]
        private static extern IntPtr GetModuleHandle([In]string lpModuleName);

        [DllImport("kernel32.dll", SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        static extern bool FreeLibrary([In]IntPtr hModule);
    }

    public class MobileImageContext : System.Data.Entity.DbContext
    {
        static MobileImageContext()
        {
            Database.SetInitializer(new MobileImageContextInitializer());
        }

        public MobileImageContext(DbConnection existingConnection, bool contextOwnsConnection) :
            base(existingConnection, contextOwnsConnection)
        {
            this.Configuration.LazyLoadingEnabled = false;
            this.Configuration.ProxyCreationEnabled = false;
            this.Configuration.AutoDetectChangesEnabled = false;
        }

        public DbSet<Insight> Insights { get; set; }
    }

    public class MobileImageContextInitializer : IDatabaseInitializer<MobileImageContext>
    {
        public virtual void InitializeDatabase(MobileImageContext context)
        {
            if (context.Database.Exists() == false)
            {
                context.Database.Create();
                this.Seed(context);
                context.SaveChanges();
            }
        }

        private void Seed(MobileImageContext context)
        {
            throw new System.NotImplementedException();
        }
    }

    [Table("Insights")]
    public class Insight
    {
        [Column]
        [Key]
        public long PK { get; set; }
        [Column]
        public string Date { get; set; }
        [Column]
        public string Text { get; set; }
        [Column]
        public string Pillar { get; set; }
    }

    public class SQLiteResourceScope : IDisposable
    {
        static SQLiteResourceScope CurrentInstance { get; set; }
        System.Collections.Generic.List<WeakReference> Items { get; set; }
        static object instanceLocker = new object();
        object itemsLocker;
        bool disposed;
        SQLiteConnectionEventHandler handler;

        public SQLiteResourceScope()
        {
            lock (instanceLocker)
            {
                if (CurrentInstance != null)
                    throw new InvalidOperationException("Nested scopes are not allowed.");
                System.Threading.Thread.MemoryBarrier();
                CurrentInstance = this;
                handler = new SQLiteConnectionEventHandler(SQLiteConnection_Changed);
                SQLiteConnection.Changed += handler;
            }
            itemsLocker = new object();
            Items = new System.Collections.Generic.List<WeakReference>();
        }

        public void Dispose()
        {
            Dispose(true);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposed) return;
            lock (itemsLocker)
            {
                foreach (var item in Items)
                {
                    DisposeItem(item);
                }
                Items.Clear();
                Items = null;
            }
            SQLiteConnection.Changed -= handler;
            disposed = true;
        }

        protected virtual void DisposeItem(WeakReference item)
        {
            if (item == null)
                return;
            var disposable = item.IsAlive ? item.Target as IDisposable : null;
            if (disposable == null)
                return;
            disposable.Dispose();
            item.Target = null;
        }

        protected WeakReference TryAddItem(IDisposable item)
        {
            if (item == null) return null;
            lock (itemsLocker)
            {
                foreach (var weakref in Items)
                {
                    if (weakref.Target == item)
                        return null;
                }
                WeakReference result;
                this.Items.Add(result = new WeakReference(item));
                return result;
            }
        }

        protected void SQLiteConnection_Changed(object sender, ConnectionEventArgs e)
        {
            if (e == null) return;
            switch (e.EventType)
            {
                case SQLiteConnectionEventType.NewCommand:
                    TryAddItem(e.Command);
                    break;
                case SQLiteConnectionEventType.Opened:
                    TryAddItem(sender as SQLiteConnection);
                    break;
                case SQLiteConnectionEventType.NewDataReader:
                    if (e.Data is Array)
                    {
                        var data = e.Data as object[];
                        if (data != null && data.Length > 1)
                        {
                            var reader = data[0] as SQLiteDataReader;
                            if (reader != null)
                                TryAddItem(reader);
                        }
                    }
                    break;
                case SQLiteConnectionEventType.NewTransaction:
                    TryAddItem(e.Transaction);
                    break;
                default: break;
            }
        }
    }
}

Your example code seems reasonable.  Is the exception still being thrown via the
GC.Collect() method or somewhere else?

Actually, thinking more about this...  I do not think your "resource scope" class
should use WeakReference objects.  Rather, it should store real references to all
the IDisposable objects and then simply dispose of them upon its disposal.

Yes, exception was thrown at GC.Collect: I'm using "KillSQLiteInterop(); GC.Collect();" construction to indicate what unmanaged objects were lost and why DB file remains locked. KillSQLiteInterop() method unloads SQLite.Interop.dll and thus makes impossible any further calls to unmanaged SQLite resourses.

I'm using WeakReferences to let GC collect such objects - my intentions are just to force disposing of "lost" resources. If I will use direct references, this will hit badly memory management: in production application we need to create several 100MB databases from the scratch and therefore I must ensure that objects can be scraped as soon as they are no longer needed.

As I said, as long as I'm additionally monitoring SQLiteConnectionHandles and SQLiteStatementHandles (and disposing them) - everything is fine. No sure if I should monitor SQLiteStatements as well.

A couple questions:

1. Does the issue reproduce if you use GC.Collect() prior to the "kill" call?
2. Does the problem reproduce if you use real references instead of weak ones?

>1. Does the issue reproduce if you use GC.Collect() prior to the "kill" call?
No, when I added "GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();" prior to the KillSQLiteInterop() call I was not able to reproduce that exception (in my local dev environment, with low CPU load)

2. Does the problem reproduce if you use real references instead of weak ones?
Yes, I changed type SQLiteResourceScope.Items to List<IDisposable> and I still observed the same exception.

Here is statistics for the example above (with WeakReferences)
28 objects were listed
Object added to the scope - exists (was not collected by GC) at the time of scope dispose:disposed state
SQLiteCommand     - no
SQLiteDataReader  - no
SQLiteConnection  - yes:True
SQLiteCommand     - no
SQLiteDataReader  - no
SQLiteCommand     - no
SQLiteDataReader  - no
SQLiteCommand     - yes:True
SQLiteDataReader  - yes:True
SQLiteConnection  - yes:False
SQLiteCommand     - yes:True
SQLiteDataReader  - yes:True
SQLiteDataReader  - yes:True
SQLiteCommand     - yes:True
SQLiteDataReader  - yes:True
SQLiteCommand     - yes:False
SQLiteDataReader  - yes:True
SQLiteCommand     - yes:True
SQLiteDataReader  - yes:True
SQLiteCommand     - yes:True
SQLiteDataReader  - yes:True
SQLiteTransaction - yes:True
SQLiteDataReader  - yes:True
SQLiteCommand     - yes:True
SQLiteDataReader  - yes:True
SQLiteCommand     - yes:True
SQLiteDataReader  - yes:True
SQLiteDataReader  - yes:True

BTW, here is statistics for the example with WeakReferences + SQLiteConnectionHandle / SQLiteStatementHandle / SQLiteStatement tracking added (it solves the problem).
64 objects were listed
Object added to the scope - exists (was not collected by GC) at the time of scope dispose : disposed state
SQLiteConnectionHandle - no
SQLiteCommand          - no
SQLiteDataReader       - no
SQLiteStatementHandle  - no
SQLiteStatement        - no
SQLiteConnection       - yes : true
SQLiteCommand          - no
SQLiteDataReader       - no
SQLiteStatementHandle  - no
SQLiteStatement        - no
SQLiteCommand          - no
SQLiteDataReader       - no
SQLiteStatementHandle  - no
SQLiteStatement        - no
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteConnection       - yes : false
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteCommand          - yes : false
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteTransaction      - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false

From your responses, it sounds like I may need to add the CriticalHandle derived
classes to the OnChanged event so that they can be explicitly disposed as well?

Would that completely solve the problem?

Another thing to try is to dispose of the remaining IDisposable objects in the
following order:

1. All SQLiteDataReader objects.
2. All SQLiteCommand objects.
3. All SQLiteConnection objects.

See check-in [2843f57cae] on the "onChanged" branch.

Does this code help to resolve the issue?

mistachkin added on 2013-09-04 01:40:42:
Another thing to try is to dispose of the remaining IDisposable objects in the
following order:

1. All SQLiteDataReader objects.
2. All SQLiteCommand objects.
3. All SQLiteConnection objects.

I disposed all SQLiteDataReaders, then all SQLiteCommands, then all SQLiteConnections. As you can see, there are some SQLiteStatements/SQLiteStatementHandles remains undisposed.

SQLiteConnectionHandle - no
SQLiteCommand          - no
SQLiteDataReader       - no
SQLiteStatementHandle  - no
SQLiteStatement        - no
SQLiteConnection       - yes : true
SQLiteCommand          - no
SQLiteDataReader       - no
SQLiteStatementHandle  - no
SQLiteStatement        - no
SQLiteCommand          - no
SQLiteDataReader       - no
SQLiteStatementHandle  - no
SQLiteStatement        - no
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteConnection       - yes : true
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteTransaction      - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteConnectionHandle - yes : true
SQLiteCommand          - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : true
SQLiteStatement        - yes : true
SQLiteDataReader       - yes : true
SQLiteStatementHandle  - yes : false
SQLiteStatement        - yes : false

mistachkin added on 2013-09-04 06:52:34:

See check-in [2843f57cae] on the "onChanged" branch.
Does this code help to resolve the issue?

Yes, that helps.

BTW, could you additionally consider adding next lines to the SQLiteCommand.Dispose(bool disposing) method?

if (_statementList != null)
{
    lock (_statementList)
    {
        for (int i = 0; i < _statementList.Count; i++)
            _statementList[i].Dispose();
        _statementList.Clear();
    }
    _statementList = null;
}

I'll try adding code similar to that; however, the disposal ordering for the
SQLiteCommand/SQLiteDataReader stuff has been the source of issues before, when
things were disposed too aggressively.

Further analysis reveals there would be no net impact when adding statement list
disposal code to the SQLiteCommand.Dispose method.  This is primarily due to how
the disposal of the SQLiteCommand object ends up being deferred if there is an
active SQLiteDataReader using it.

Thanks a lot. I believe this will cover most of the issues with releasing of resources.

Please let me know if you are able to fully resolve this issue with the current
System.Data.SQLite code on the onChanged branch.  If so, I'll close this ticket
as resolved fixed and merge the changes to trunk.

Yes, it resolved this issue , thanks.

Changes checked-in on trunk [7e2f11fd49], including tests.

I don't believe this fix is sufficient.

I have the code, and am running into two problems:
1) CurrentInstance is never set to null, so creating a second SQLiteResourceScope is impossible.  This is easily fixed by setting CurrentInstance = null in SQLiteResourceScope.Dispose.
2) After disposing a SQLiteResourceScope, I can no longer create a new instance of my DbContext and use it.  It seems that Entity Framework caches DbCommand objects; I am getting an ObjectDisposedException when I try to query on the second object.

Can somebody please look into this further?

Thanks
Joe Strommen
joe.strommen@gmail.com

        public class TestContext : DbContext
        {
            public TestContext(DbConnection existingConnection)
                : base(existingConnection, false)
            {                
            }

            public DbSet<TestEntity> TestEntities { get; set; }
        }

        public class TestEntity
        {
            public long Id { get; set; }

            public string Value { get; set; }
        }

        static void Main(string[] args)
        {
            var path = "test.sqlite";
            if (File.Exists(path)) { File.Delete(path); }
            using (var sqliteConnection = new SQLiteConnection("Data Source=" + path))
            {
                sqliteConnection.Open();

                using (var sqliteCmd = sqliteConnection.CreateCommand())
                {
                    sqliteCmd.CommandText =
                        "CREATE TABLE TestEntities (Id INTEGER PRIMARY KEY AUTOINCREMENT, Value TEXT);" +
                        "INSERT INTO TestEntities ('Value') VALUES ('Value1'), ('Value2'), ('Value3');";
                    sqliteCmd.ExecuteNonQuery();
                }

                using (new SQLiteResourceScope())
                using (var efContext = new TestContext(sqliteConnection))
                {
                    var entityCount = efContext.TestEntities.Count();
                }

                using (new SQLiteResourceScope()) // Problem #1
                using (var efContext = new TestContext(sqliteConnection))
                {
                    var entityCount = efContext.TestEntities.Count(); // Problem #2
                }
            }
        }

What is a SQLiteResourceScope?  I do not see that class in the source code for
System.Data.SQLite.

Related mailing list thread:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg74770.html

FYI, just realized that the SQLiteResourceScope class is not part of the official
System.Data.SQLite project.

I was able to get this to work by building System.Data.SQLite with INTEROP_LEGACY_CLOSE enabled.

It seems to me that until/unless the underlying issues get worked out, this should be the default.  LINQ/EF6 support is pretty broken without it.

I think we'll have to agree to disagree on this point.  Firstly, the issue is not
with either SQLite nor System.Data.SQLite, but with the Entity Framework handling
of IDisposable derived resources.  Secondly, not being able to delete the
underlying file for the database while the application is running is not really
a critical feature.

I've attempted to reproduce this issue locally with Entity Framework 6 and the
latest System.Data.SQLite release and everything seems to work properly.

The issue may be with the SQLiteResourceScope class, which is not part of the
System.Data.SQLite project and appears to no longer be necessary as of the
Entity Framework 6 release.

Being unable to delete the file is not my main concern - it's just the easiest-to-observe symptom of the resource leak that is occurring.

The following code, using the most recent System.Data.SQLite NuGet package 1.0.91.3 (updated 3/7) still fails.

    class Program
    {
        public class TestContext : DbContext
        {
            public TestContext(DbConnection existingConnection)
                : base(existingConnection, false)
            {                
            }

            public DbSet<TestEntity> TestEntities { get; set; }
        }

        public class TestEntity
        {
            public long Id { get; set; }

            public string Value { get; set; }
        }

        static void Main(string[] args)
        {
            var path = "test.sqlite";
            if (File.Exists(path)) { File.Delete(path); }       
            using (var sqliteConnection = new SQLiteConnection("Data Source=" + path))
            {
                sqliteConnection.Open();

                using (var sqliteCmd = sqliteConnection.CreateCommand())
                {
                    sqliteCmd.CommandText =
                        "CREATE TABLE TestEntities (Id INTEGER PRIMARY KEY AUTOINCREMENT, Value TEXT);" +
                        "INSERT INTO TestEntities ('Value') VALUES ('Value1'), ('Value2'), ('Value3');";
                    sqliteCmd.ExecuteNonQuery();
                }

                using (var efContext = new TestContext(sqliteConnection))
                {
                    var entityCount = efContext.TestEntities.Count();
                }
            }
            File.Delete(path); // Fails; the file is still locked.
        }
    }

I did a very careful analysis of the issue, in the VSdebugger, with resource
creation hooks in place.  The extra objects not being disposed are 100% for
sure coming from inside the Entity Framework.  I'm not sure how to force
these objects to be disposed; however, that is the root cause of the issue.

This is not a bug in System.Data.SQLite nor SQLite.