System.Data.SQLite

Steps to reproduce:
- Build an Appdomain in a different directory
- Load a dll in that appdomain in that different directory
- Open a connection to SQLite
- Close connection
- Unload Appdomain
- Delete Directory

Expected Result:
Directory can be fully deleted

Actual Result:
SQLite.Interop.dll is still in use and directory can't be deleted

Workaround:
GetModuleHandle/FreeLibrary manually

Solution:
See my repro, listen to the AppDomain.Unload event and free the library properly.


Note: This is for a program with plugins where the plugins are executed in a different directory and the directory is deleted after the plugin is finished. I noticed that I couldn't delete directories where the sqlite library was used.

I have a repro here:
https://gist.github.com/Tornhoof/04bb8543d80be04c84a579d4c98059ff

For completeness I attached the full source here too:
using System;
using System.Data.SQLite;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Threading;

namespace SqliteRepro
{
    class Program
    {
        static void Main(string[] args)
        {
            var binPath = Path.Combine(Environment.CurrentDirectory, "HelloWorld");
            if (Directory.Exists(binPath))
            {
                Directory.Delete(binPath, true);
            }

            Directory.CreateDirectory(binPath);
            DirectoryCopy(Environment.CurrentDirectory, binPath, true);
            Create(binPath, binPath);
        }

        private static void DirectoryCopy(string sourceDirName, string destDirName, bool copySubDirs)
        {
            // Get the subdirectories for the specified directory.
            DirectoryInfo dir = new DirectoryInfo(sourceDirName);

            if (!dir.Exists)
            {
                throw new DirectoryNotFoundException(
                    "Source directory does not exist or could not be found: "
                    + sourceDirName);
            }

            DirectoryInfo[] dirs = dir.GetDirectories();
            // If the destination directory doesn't exist, create it.
            if (!Directory.Exists(destDirName))
            {
                Directory.CreateDirectory(destDirName);
            }

            // Get the files in the directory and copy them to the new location.
            FileInfo[] files = dir.GetFiles();
            foreach (FileInfo file in files)
            {
                string temppath = Path.Combine(destDirName, file.Name);
                file.CopyTo(temppath, false);
            }

            // If copying subdirectories, copy them and their contents to new location.
            if (copySubDirs)
            {
                foreach (DirectoryInfo subdir in dirs)
                {
                    string temppath = Path.Combine(destDirName, subdir.Name);
                    DirectoryCopy(subdir.FullName, temppath, copySubDirs);
                }
            }
        }

        private static void AppDomainOnDomainUnload(object o, EventArgs eventArgs)
        {
         //   NativeMethods.TryToKillSQLite(); // uncomment this to see missing freelibrary
        }

        public class Demo : MarshalByRefObject
        {
            public void HelloWorld()
            {
                using (var connection = new SQLiteConnection())
                {
                    Console.WriteLine($"Hello World from {Assembly.GetExecutingAssembly().Location}");
                }
            }


            public override object InitializeLifetimeService()
            {
                return null;
            }
        }

        public static void Create(string inputPath, string binPath)
        {
            AppDomain appDomain = null;
            try
            {
                appDomain = AppDomain.CreateDomain("HelloWorld", null, inputPath, binPath, false);
                appDomain.DomainUnload += AppDomainOnDomainUnload;
                var helloWorld = appDomain.CreateInstanceAndUnwrap(typeof(Demo).Assembly.FullName, typeof(Demo).FullName) as Demo;
                if (helloWorld == null)
                {
                    throw new InvalidOperationException("Could not create HellWorld");
                }
                helloWorld.HelloWorld();
            }
            finally
            {
                if (appDomain != null)
                {
                    AppDomain.Unload(appDomain);
                }
            }
            Thread.Sleep(1000);
            Directory.Delete(binPath, true);
        }
    }

    internal static class NativeMethods
    {
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        static extern IntPtr GetModuleHandle(string lpModuleName);

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


        /// <summary>
        /// This method tries to unload sqlite.interop.dll as the sqlite library does not properly unload itself
        /// </summary>
        public static void TryToKillSQLite()
        {
            try
            {
                var handle = GetModuleHandle("SQLite.Interop.dll");
                if (handle != IntPtr.Zero)
                {
                    FreeLibrary(handle); // do it twice (just to make sure it properly unloads)
                    FreeLibrary(handle);
                }
            }
            catch (Exception)
            {
                Console.WriteLine("Couldn't unload SQLite.Interop.dll");
            }
        }
    }
}

Your workaround seems good for your particular use case.

However, given how the native library pre-loading feature works, the SQLite
interop assembly cannot normally be unloaded gracefully.

One problem is that native libraries are not loaded on a per-AppDomain basis;
they are process-wide.

Simply calling FreeLibrary on the handle may result in other AppDomains
trying to make use of a native DLL that has been unloaded, which will
result in undefined behavior and/or native exceptions.

I think you are correct that my approach is specific for my use case, afaik on Windows the Library is not fully freed until the refcounter is zero, So if the refcounter is increased for each AppDomain my approach still would work, but obviously this would need a fair amount of investigation.

I don't know how far this applies to other platforms, as I don't know how much appdomains work e.g. in Mono (on .NET Core they are basically just stubs afaik). So if multiple platforms can have AppDomains it might be useful just to add a method to your library to unload it, as you already have multi-platform loading in place and let the user decide when to do it (e.g. like in my example)

It is technically possible to unload a native DLL cleanly from a .NET app;
however, in this case it would require a rewrite of the P/Invoke layer in
order to do it.  And that is not without risks, both to overall robustness
and to backward compatibility.