❮ Table of contents

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 OpenCommands in ThreadLocal. Now it worked across threads, but when using async/await, I started getting exceptions. Then I found AsyncLocal 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.