System.Data.SQLite vs Microsoft.Data.Sqlite

An advantage of System.Data.SQLite is that it is developed by the SQLite team who have stated a long-term commitment to keeping it supported.

An advantage of Microsoft.Data.Sqlite is that it is developed by Microsoft and can be assumed to be well tested with Entitity Framework, .NET Core etc.

I chose System.Data.SQLite for my project, one reason being that I use the GetBytes() DataReader method which is "not supported" in Microsoft.Data.Sqlite.

I have not tested performance, however it would not surprise me if Microsoft.Data.Sqlite wins since it claims to be a thinner wrapper. See below.

There is an informative comment by Brice Lambson, one of the Microsoft.Data.Sqlite developers here: https://www.bricelam.net/2018/05/24/microsoft-data-sqlite-2-1.html#comment-3980760585

He says,

"There are three main differences between Microsoft.Data.Sqlite andSystem.Data.SQLite.

"First, we don't aim to be a feature-complete ADO.NET provider.Microsoft.Data.Sqlite was created for .NET Core 1.0 when the goal wasto create a lighter-weight, modernized version of .NET. That goal of.NET Core has largely been abandoned in favor of adding as many APIsas possible to make it easier to port from other .NET runtimes.However, the goal of Microsot.Data.Sqlite is still just to provide abasic ADO.NET implementation sufficient to support modern data accessframeworks like EF Core, Dapper, etc. We tend not to add API forthings that can be done using SQL. For example, see this comment forSQL alternatives to connection string keywords.

"The second big difference is that we're much closer to the nativeSQLite behavior. We don't try to compensate for any of SQLite'squirkiness. For example, System.Data.SQLite adds .NET semantics tocolumn type names. They even have to parse every SQL statement beforesending it to the native driver to handle a custom SQL statement forspecifying the column type of results not coming directly from a tablecolumn (i.e. expressions in the SELECT clause). Instead, we embracethe fact that SQLite only supports four primitive types (INTEGER,REAL, TEXT, and BLOB) and implement ADO.NET APIs in a way that helpsyou coerce values between these and .NET types.

"Finally, we weren't written 10 years ago. :-) This allow us to createmore modern APIs that feel more natural in modern, idiomatic C#. TheAPI for registering user-defined functions is the best example ofthis."

I have tried System.Data.SQLite and Microsoft.Data.SQLite in a project, and I make a real world performace test, System.Data.SQLite is about 3x faster than Microsoft.Data.Sqlite when bulk insert.

One more finding, as of today (6th March 2020), System.Data.SQLite does not provide a compiled binary for linux arm. You have to do that manually.