--- title: "Microsoft.Data.Sqlite and EF Core without a native SQLite library" url: https://recursiveai.org/onelite/cookbook/microsoft-data-sqlite-ef-core-without-native-library/ description: "Run Microsoft.Data.Sqlite and EF Core’s SQLite provider in C# with no native e_sqlite3 library: swap the SQLitePCLRaw bundle for RecursiveAI.OneLite.SQLitePCLRaw and call OneLiteBatteries.Init(). Complete programs, real output, a publish with no native file, NativeAOT, and the limits." version: "1.0.0" --- # Microsoft.Data.Sqlite and EF Core without a native SQLite library Keep your `Microsoft.Data.Sqlite` or EF Core code and drop the native `e_sqlite3` binary. `RecursiveAI.OneLite.SQLitePCLRaw` is, in its docs’ words, “An `ISQLite3Provider` for SQLitePCLRaw that runs on OneLite’s engine. `Microsoft.Data.Sqlite` and Entity Framework Core’s SQLite provider use SQLitePCLRaw for every database call, so they run on OneLite, with no native SQLite loaded, by swapping one package and one line of start-up code.” ```shell dotnet new console -o MdsNoNative && cd MdsNoNative dotnet add package Microsoft.Data.Sqlite.Core --version 10.0.14 dotnet add package RecursiveAI.OneLite.SQLitePCLRaw --version 1.0.0 ``` Reference `Microsoft.Data.Sqlite.Core`, not `Microsoft.Data.Sqlite`: in the docs’ words, its “default bundle brings the native library”. The packages target `net10.0`, so you need the .NET 10 SDK. Put this in `Program.cs` and run `dotnet run`: ```csharp using Microsoft.Data.Sqlite; using RecursiveAI.OneLite.SQLitePCLRaw; // Instead of SQLitePCL.Batteries_V2.Init() and the native e_sqlite3 bundle. OneLiteBatteries.Init(); using var conn = new SqliteConnection("Data Source=shop.db"); conn.Open(); using (var create = conn.CreateCommand()) { create.CommandText = """ drop table if exists products; create table products (id integer primary key, name text not null, price real not null); """; create.ExecuteNonQuery(); } using (var tx = conn.BeginTransaction()) { using var insert = conn.CreateCommand(); insert.CommandText = "insert into products (name, price) values ($name, $price)"; var name = insert.Parameters.Add("$name", SqliteType.Text); var price = insert.Parameters.Add("$price", SqliteType.Real); foreach (var (n, p) in new[] { ("Kettle", 24.5), ("Teapot", 18.0), ("Mug", 6.25) }) { name.Value = n; price.Value = p; insert.ExecuteNonQuery(); } tx.Commit(); } using var query = conn.CreateCommand(); query.CommandText = "select name, price from products where price < $max order by price"; query.Parameters.AddWithValue("$max", 20); using (var reader = query.ExecuteReader()) while (reader.Read()) Console.WriteLine($"{reader.GetString(0)}: {reader.GetDouble(1):0.00}"); using var fk = conn.CreateCommand(); fk.CommandText = "pragma foreign_keys"; Console.WriteLine($"foreign_keys: {fk.ExecuteScalar()}"); Console.WriteLine($"provider: {SQLitePCL.raw.GetNativeLibraryName()}"); Console.WriteLine($"sqlite version: {conn.ServerVersion}"); ``` ```text Mug: 6.25 Teapot: 18.00 foreign_keys: 1 provider: OneLite (managed) sqlite version: 3.53.4 ``` Everything except `OneLiteBatteries.Init()` is ordinary `Microsoft.Data.Sqlite` code: `$name` parameters, a transaction, a reader. `Init()` “calls `SQLitePCL.raw.SetProvider` with a shared provider; it is safe to call more than once”. The provider reports itself as `OneLite (managed)` and the version as 3.53.4, “the SQLite release OneLite is compatible with”. Foreign keys are on, as with the native bundle (see Limits). ## Nothing native in the output ```shell dotnet publish -c Release -r linux-x64 --self-contained false -o out ``` ```shell find out -type f -printf '%f\n' | sort ``` ```text MdsNoNative MdsNoNative.deps.json MdsNoNative.dll MdsNoNative.pdb MdsNoNative.runtimeconfig.json Microsoft.Data.Sqlite.dll RecursiveAI.OneLite.dll RecursiveAI.OneLite.SQLitePCLRaw.dll SQLitePCLRaw.core.dll ``` No `libe_sqlite3.so`, and no `runtimes/` folder of per-platform binaries. The package “is NativeAOT- and trim-clean”; this publishes one self-contained executable: ```shell dotnet publish -c Release -r linux-x64 -p:PublishAot=true -o aot ``` ```shell ./aot/MdsNoNative ``` ```text Mug: 6.25 Teapot: 18.00 foreign_keys: 1 provider: OneLite (managed) sqlite version: 3.53.4 ``` The file is an ordinary SQLite database. The `sqlite3` shell (3.53.4, from sqlite.org) reads it and checks it: ```shell sqlite3 shop.db "select * from products" "pragma integrity_check" ``` ```text 1|Kettle|24.5 2|Teapot|18.0 3|Mug|6.25 ok ``` ## With EF Core The same swap works for EF Core: reference `Microsoft.EntityFrameworkCore.Sqlite.Core` (not `Microsoft.EntityFrameworkCore.Sqlite`, which brings the native bundle) and call `OneLiteBatteries.Init()` before the first `DbContext`. ```shell cd .. && dotnet new console -o EfNoNative && cd EfNoNative dotnet add package Microsoft.EntityFrameworkCore.Sqlite.Core --version 10.0.14 dotnet add package RecursiveAI.OneLite.SQLitePCLRaw --version 1.0.0 ``` ```csharp using Microsoft.EntityFrameworkCore; using RecursiveAI.OneLite.SQLitePCLRaw; OneLiteBatteries.Init(); // before the first DbContext using (var db = new BlogContext()) { db.Database.EnsureDeleted(); db.Database.EnsureCreated(); db.Blogs.Add(new Blog { Name = "Field notes", Posts = { new Post { Title = "Hello" }, new Post { Title = "Second post" } }, }); db.SaveChanges(); } using (var db = new BlogContext()) { foreach (var blog in db.Blogs.Include(b => b.Posts).OrderBy(b => b.Name)) Console.WriteLine($"{blog.Name}: {string.Join(", ", blog.Posts.Select(p => p.Title))}"); Console.WriteLine($"posts titled like 'post': {db.Posts.Count(p => p.Title.Contains("post"))}"); } public class BlogContext : DbContext { public DbSet Blogs => Set(); public DbSet Posts => Set(); protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlite("Data Source=blog.db"); } public class Blog { public int Id { get; set; } public string Name { get; set; } = ""; public List Posts { get; } = new(); } public class Post { public int Id { get; set; } public string Title { get; set; } = ""; public int BlogId { get; set; } public Blog Blog { get; set; } = null!; } ``` ```text Field notes: Hello, Second post posts titled like 'post': 1 ``` ```shell sqlite3 blog.db ".tables" "select * from Posts" "pragma integrity_check" ``` ```text Blogs Posts 1|Hello|1 2|Second post|1 ok ``` ## How well it is tested From the [changelog](https://recursiveai.org/onelite/docs/changelog/) at 1.0.0, the project’s own runs of the two suites: “`Microsoft.Data.Sqlite`’s own tests pass 670 of 687 (7 skipped by the suite, 10 failures classified in `docs/adapter.md`) and EF Core’s SQLite functional tests 37,121 of 37,418 (289 skipped by the suites; the 8 failures are classified in `docs/adapter.md`).” The [adapter docs](https://recursiveai.org/onelite/docs/adapter/#test-results) give the versions (“`Microsoft.Data.Sqlite` 10.0.14’s and EF Core 10.0.14’s tests, with `SQLitePCLRaw` 3.0.5”) and classify every failure: `load_extension` (4), shared-cache table locks (2, plus 6 in EF Core), `Password=` (2: “the tests expect the native library to reject `Password=`; OneLite supports SQLCipher-compatible encryption, so it accepts it”), `EXPLAIN` (1), `pragma_function_list` (1), one EF test that “checks the fetched subset of the test tree against the full one”, and one EF test that “asserts a wrong answer that the native library gives”. ## Limits From the [adapter docs](https://recursiveai.org/onelite/docs/adapter/#what-is-not-supported), where “Each of these returns a documented result, never a silent success”: - **Foreign keys on by default.** “The adapter starts every connection with `PRAGMA foreign_keys=ON`”, as the native bundle does; OneLite’s own API and ADO.NET provider keep SQLite’s default, off. “Set `OneLiteBatteries.ForeignKeysByDefault = false` before opening connections to get off through the adapter; an explicit `PRAGMA foreign_keys` or `Foreign Keys=` connection-string key always wins.” - **No native extensions.** `load_extension` fails: “load_extension is not supported by OneLite; register managed extensions through the engine API”. - **No `EXPLAIN` bytecode.** “Bytecode listings are not produced (`unsupported: EXPLAIN`); `EXPLAIN QUERY PLAN` text differs.” - **Not implemented:** `set_authorizer` (“A non-null authorizer fails with `SQLITE_ERROR`”), `serialize` / `deserialize`, snapshots and `db_config` (`SQLITE_ERROR`), `PRAGMA function_list`, and user functions on a UTF-16 database. `trace` and `profile` are “Accepted and never called”. - **Shared cache.** “a reader sees an uncommitted write instead of `SQLITE_LOCKED`” on a shared in-memory database. - **Text.** “Invalid UTF-8 in a bound text value is replaced, not preserved byte for byte (bind a `BLOB` for arbitrary bytes).” - **The engine’s own differences apply**: every known difference from SQLite 3.53.4 is on the [compatibility page](https://recursiveai.org/onelite/docs/compatibility/). Starting a new project? OneLite’s own API needs no SQLitePCLRaw at all: [SQLite in C# without native dependencies](https://recursiveai.org/onelite/cookbook/sqlite-csharp-without-native-dependencies/). [Adapter docs](https://recursiveai.org/onelite/docs/adapter/) · [Getting started](https://recursiveai.org/onelite/docs/getting-started/) · [All recipes](https://recursiveai.org/onelite/cookbook/)