SQLite.Framework.SourceGenerator 5.2.0

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. The framework keeps the generated SQL close to the shape of the LINQ chain you wrote and does not wrap it in extra subqueries or rewrite it behind your back to make a method work. When a LINQ method does not map cleanly to SQL, you get a clear NotSupportedException instead of a silently rewritten query. No client-side fallback that pulls rows into memory, and no reflection at runtime when you opt into the source generator.

Why this and not...

See the Migrating from sqlite-net-pcl or Migrating from EF Core 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 at 100% code coverage. It targets .NET 8, 9, and 10.

Documentation

The full docs live at destbg.github.io/SQLite.Framework. Start with the Overview and Getting Started pages.

The same content is also mirrored on the GitHub Wiki, but GitHub Pages is the primary source for the docs.

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 and missing-feature requests are welcome.

License

MIT © Nikolay Kostadinov