SQLite connections not closed in nested queries

[ivashin]

I'm using BLToolkit 4.2 together with System.Data.SQLite 1.0.93. I've noticed that sometimes DB file remains locked after running complex queries. After some debugging I've found that an SQLiteConnection remains open. Further debugging showed that:

• When BLToolkit executes a subquery, it clones the root DataContextInfo (see QueryContext.GetDataContext()) which in turn clones DbManager.
• DbManager clones connection via DataProvider (see DataProviderBase.CloneConnection())
• System.Data.SQLite's SQLiteConnection.Clone() creates a new connection and opens it if the original connection is open:

public SQLiteConnection(SQLiteConnection connection)
  : this(connection.ConnectionString, connection.ParseViaFramework)
{
  if (connection.State == ConnectionState.Open)
  {
    Open();

• Nested DbManager's _closeConnection flag is never set because connection is already open and OpenConnection() is not executed.
• When the root DbManager is disposed it correctly disposes its own connection and the nested DbManager, but the cloned connection is not closed/disposed as _closeConnection == false (see DbManager.Close()).

Consequently, new connection was created in DbManager, but was not disposed in it. The following small sample demonstrates this behavior:

Program.cs

using System;
using System.IO;
using System.Linq;
using System.Reflection;
using BLToolkit.Data;
using BLToolkit.Data.DataProvider;

namespace BLToolkitSQLiteTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // Configuration
            var dataProvider = new SQLiteDataProvider();
            DbManager.AddDataProvider(dataProvider);

            // Clean up and init DB
            if (File.Exists("test.db"))
                File.Delete("test.db");

            using (var db = new DbManager(dataProvider, GetConnectionString("test.db")))
            {
                using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("BLToolkitSQLiteTest.init.sql"))
                using (var reader = new StreamReader(stream))
                {
                    var query = reader.ReadToEnd();
                    db.SetCommand(query).ExecuteNonQuery();
                }
            }

            using (var db = new DbManager(dataProvider, GetConnectionString("test.db")))
            {
                // Execute complex query
                var table1 = db.GetTable<Asset_Type>();
                var table2 = db.GetTable<Asset_Type>();
                var result = table1.GroupJoin(table2, t1 => t1.Type_Id, t2 => t2.Type_Id,
                    (t1, t2) => t1.Type_Name + "; " + String.Join(", ", t2.Select(_ => _.Type_Name))).ToList();
            }

            try
            {
                // Check if file is locked
                File.Delete("test.db");
                Console.WriteLine("OK");
            }
            catch (Exception e)
            {
                Console.WriteLine("ERROR");
                Console.WriteLine(e);
            }
            Console.ReadKey();
        }

        protected static string GetConnectionString(string path)
        {
            return "Data Source=" + path + ";Version=3;Compress=True;foreign keys=true;";
        }
    }

    public class Asset_Type
    {
        public int Type_Id { get; set; }
        public string Type_Name { get; set; }
    }
}

init.sql

CREATE TABLE Asset_Type ( 
    Type_Id   INTEGER PRIMARY KEY NOT NULL,
    Type_Name TEXT    NOT NULL UNIQUE 
);
INSERT INTO [Asset_Type] ([Type_Id], [Type_Name]) VALUES (0, 'Test1');
INSERT INTO [Asset_Type] ([Type_Id], [Type_Name]) VALUES (1, 'Test2');

My workaround was to check if connection is already open after cloning and set DbManager._closeConnection flag if it is:

public virtual DbManager Clone()
{
...
    if (_connection != null)
    {
        clone._connection = CloneConnection();
        if (clone._connection.State == ConnectionState.Open)
            clone._closeConnection = true;
    }

    return clone;
}

This helped with my issue but I am not sure if it may affect other scenarios/data provider types, and I'm not able to run unit test properly as I don't have proper environment with different databases configured. Please advise if this is an acceptable solution.