SQLite with MVC Core

I'm trying to create a database in memory and run a query with .NET Core 2.1 framework. To do this I have installed a package called Microsoft.Data.Sqlite.Core and then tried to do the following:

var connection = new SqliteConnection("Data Source=:memory:");
connection.Execute("Some Create Table Query");

And my code will crash with the following error:

You need to call SQLitePCL.raw.SetProvider(). If you are using a bundle package, this is done by calling SQLitePCL.Batteries.Init().

I've done a lot of digging to find a solution and one of them suggested calling this:

SQLitePCL.raw.SetProvider(new SQLite3Provider_e_sqlite3());

This produces me a new error:

'Unable to load DLL 'e_sqlite3' or one of its dependencies: The specified module could not be found. (Exception from HRESULT: 0x8007007E)'

I am really lost at this point how to accomplish this. All I want to do is create an in-memory database so I can run some queries for purposes of unit testing.

I'm not sure if this is relevant but I am not using EF6, I am using Dapper and also I am using SQL Server in my actual project. I wanted to plug in Sqlite in unit tests so I can also test my queries.

I have a feeling I am using wrong packages, but looking around there are so many and I can't find any clear documentation on how to use Sqlite with MVC Core projects.

Try adding the SQLitePCLRaw.bundle_green package to your project.

dotnet add package SQLitePCLRaw.bundle_green

At that point, the following program (inspired by this) works:

using Microsoft.Data.Sqlite;

class Program
{
    static void Main()
    {
        var connection = new SqliteConnection("Data Source=:memory:");
        connection.Open();

        var createCommand = connection.CreateCommand();
        createCommand.CommandText =
        @"
            CREATE TABLE data (
                value TEXT
            )
        ";

        createCommand.ExecuteNonQuery();
    }
}

This is the test that I ran locally.

cd C:/temp
dotnet new console
dotnet add package Microsoft.Data.Sqlite.Core
dotnet add package SQLitePCLRaw.bundle_green
//
// paste the above program into Program.cs, then...
//
dotnet run 

What is bundle_green? bundle_green is one of the Sqlite "bundle packages", which are expressly designed to ease cross-platform development. Here is a description from the official repository:

These packages automatically bring in the right dependencies for each platform. They also provide a single Init() call that is the same for all platforms... SQLitePCLRaw.bundle_green is a bundle that uses e_sqlite3 everywhere except iOS, where the system-provided SQLite is used.