Thread safe and async friendly SQLite3 with Entity Framework 6
SQLite3 and Entity Framework 6 works really well together. No actual code required, just write
the types and EF6 handles everything else. But there's a catch: EF6 doesn't always dispose
SqlCommand's properly. This is normally not a problem, and the garbage collector takes care
of cleaning up at some point, but when using SQLite this becomes a problem, because the database
file is locked until the garbage collector kicks in. A quick hack such as
GC.Collect(); GC.WaitForPendingFinalizers(); releases the file, but is also a
total no-go on a web server. After almost giving up, I found a
four year old answer on Stack Overflow,
well hidden between lots of other answers, that showed a brilliant way to solve this problem.
But it quickly became apparent that it didn't work with multiple threads and async code, so I wrapped
Now it worked across threads, but when using async/await, I started getting exceptions. Then I found
which is basically ThreadLocal with async support. Now all SqlCommand's are disposed when the
connection to SQLite is closed, and the file is released immediately:
It seems the problem have been fixed in Entity Framework Core, so if you're lucky enough to use .NET Core, you can skip this fix. One could of course import EFCore in a .NET "classic" project, but the amount of backported libraries required to do so was a huge motivator to find this fix.