Microsoft.Data.Sqlite and EF Core without a native SQLite library

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

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}");

Mug: 6.25
Teapot: 18.00
foreign_keys: 1
provider: OneLite (managed)
sqlite version: 3.53.4

dotnet publish -c Release -r linux-x64 --self-contained false -o out

find out -type f -printf '%f\n' | sort

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

dotnet publish -c Release -r linux-x64 -p:PublishAot=true -o aot

./aot/MdsNoNative

Mug: 6.25
Teapot: 18.00
foreign_keys: 1
provider: OneLite (managed)
sqlite version: 3.53.4

sqlite3 shop.db "select * from products" "pragma integrity_check"

1|Kettle|24.5
2|Teapot|18.0
3|Mug|6.25
ok

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

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<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();
    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<Post> 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!;
}

Field notes: Hello, Second post
posts titled like 'post': 1

sqlite3 blog.db ".tables" "select * from Posts" "pragma integrity_check"

Blogs     Posts
1|Hello|1
2|Second post|1
ok