SQLite.Framework.SourceGenerator 5.0.2

SQLite.Framework

LINQ-to-SQL for SQLite, with the LINQ surface of EF Core but without the runtime weight - and without the trimming and AOT pain that EF Core still has on Native AOT today. Built for .NET MAUI, Avalonia, and any other AOT-published .NET 8/9/10 app where you want to use full-featured IQueryable instead of hand-written SQL.

SQLiteOptions options = new SQLiteOptionsBuilder("library.db").Build();
using var db = new SQLiteDatabase(options);

var authors = await (
    from b in db.Table<Book>()
    join a in db.Table<Author>() on b.AuthorId equals a.Id
    where b.Price > 10
    group b by a.Name into g
    orderby g.Sum(x => x.Price) descending
    select new
    {
        Author = g.Key,
        Books = g.Count(),
        Revenue = g.Sum(x => x.Price)
    }
).Skip(10).Take(20).ToListAsync();

That whole expression is one SQL query. No client-side fallback, no reflection at runtime when you opt into the source generator.

Why this and not...

See the Migrating from sqlite-net-pcl page if that's your starting point.

Performance

Head-to-head against EF Core 10 and sqlite-net-pcl 1.9 on the same in-process SQLite file. 100 rows per operation, .NET 10, BenchmarkDotNet. Lower is better.

Read 100 rows into a List<Book>:

Bulk insert 100 rows (single transaction):

Bulk update 100 rows by predicate:

Join + project (1000 Books and 50 Authors, filter Price > 50, sort, project to a DTO):

sqlite-net-pcl's TableQuery<T> is IEnumerable<T>, not IQueryable<T>, so the LINQ join binds to Enumerable.Join. The whole Books and Authors tables load into memory before the filter and join run client-side.

Status

The library is exercised by 9000+ test cases across all suites at 100% line coverage; the main test project alone runs 1800+ tests. The library targets .NET 8, 9, and 10. SemVer is followed for breaking changes.

Documentation

• GitHub Wiki
• GitHub Pages

Installation

dotnet add package SQLite.Framework

The provider packages all expose the same API and assembly name, so you can swap between them without touching code:

JSON, JSONB, FTS5, and window functions are built into all four.

Quick Start

1. Define your model.

public class Person
{
    [Key, AutoIncrement]
    public int Id { get; set; }
    public required string Name { get; set; }
    public DateTime? BirthDate { get; set; }
}

Per-class attributes: [Table], [WithoutRowId]. Per-property: [Column], [NotMapped], [Key], [Index], [AutoIncrement], [Required]. Columns are NOT NULL by default; use ? to mark them as nullable.

2. Open a database.

using SQLite.Framework;

var options = new SQLiteOptionsBuilder("app.db").Build();
using var db = new SQLiteDatabase(options);
db.Schema.CreateTable<Person>();

3. Write LINQ queries.

db.Table<Person>().Add(new Person { Name = "Alice" });

var adults = (
    from p in db.Table<Person>()
    where p.BirthDate < DateTime.Now.AddYears(-18)
    orderby p.Name
    select new { p.Id, p.Name }
).ToList();

4. Async works the same way.

await db.Table<Person>().AddAsync(new Person { Name = "Alice" });

var ids = await (
    from p in db.Table<Person>()
    select p.Id
).ToListAsync();

5. Joins, groupings, projections - all translate to SQL.

var result = await (
    from b in db.Table<Book>()
    join a in db.Table<Author>() on b.AuthorId equals a.Id
    group b by a.Name into g
    select new
    {
        Author = g.Key,
        Count = g.Count()
    }
).ToListAsync();

AOT Support

Install SQLite.Framework.SourceGenerator and call UseGeneratedMaterializers():

dotnet add package SQLite.Framework.SourceGenerator

using SQLite.Framework.Generated;

var options = new SQLiteOptionsBuilder("app.db")
    .UseGeneratedMaterializers()
    .Build();

The generator writes the row-to-object code at build time, so the trimmer keeps every type used in a Select and there's no per-query reflection. The UseGeneratedMaterializers extension is generated internal per project, so each project that builds queries needs its own reference.

Without the generator, the library still runs under AOT but uses reflection for queries. Make sure the model classes are reachable from the entry assembly so the trimmer keeps them. Full setup on the Source Generator and Native AOT pages.

Contributing

Bug reports, missing-feature requests, and PRs are welcome.

License

MIT © Nikolay Kostadinov