LINQ to SQLite
Use IQueryable for joins, grouping, subqueries, window functions and common table expressions. The framework translates them to SQLite SQL.
SQLite.Framework turns C# LINQ queries into SQLite SQL. Use typed tables, joins and async APIs in .NET MAUI, Avalonia, ASP.NET and Blazor. Native AOT support with a build-time source generator.
dotnet add package SQLite.Framework
var options = new SQLiteOptionsBuilder("books.db")
.UseMinimumSqliteVersion(SQLiteMinimumVersion.V3_36)
.Build();
using SQLiteDatabase db = new(options);
var topAuthors = await (
from b in db.Table<Book>()
join a in db.Table<Author>() on b.AuthorId equals a.Id
where b.Price < 30
group b by a.Name into g
orderby g.Sum(b => b.Sales) descending
select new
{
Author = g.Key,
Titles = g.Count(),
Revenue = g.Sum(b => b.Sales),
}
).Take(5).ToListAsync();
Filter, join, group and project data with IQueryable. Compare each C# LINQ example with the SQLite query it generates.
var topAuthors = await (
from b in db.Table<Book>()
join a in db.Table<Author>()
on b.AuthorId equals a.Id
where b.Price < 30
group b by a.Name into g
orderby g.Sum(b => b.Sales) descending
select new
{
Author = g.Key,
Titles = g.Count(),
Revenue = g.Sum(b => b.Sales),
}
).Take(5).ToListAsync();
SELECT a0."AuthorName" AS "Author",
COUNT(*) AS "Titles",
SUM(b0."BookSales") AS "Revenue"
FROM "Books" AS b0
INNER JOIN "Authors" AS a0
ON b0."BookAuthorId" = a0."AuthorId"
WHERE b0."BookPrice" < @p0
GROUP BY a0."AuthorName"
ORDER BY SUM(b0."BookSales") DESC
LIMIT @p1;
Query JSON, search text and update rows through one typed C# API.
Use IQueryable for joins, grouping, subqueries, window functions and common table expressions. The framework translates them to SQLite SQL.
Read, insert, update and delete data with async APIs. Use the same typed tables and LINQ queries throughout your app.
The framework never generates code at runtime. An optional source generator emits row readers at build time for Native AOT apps.
Search text with SQLite FTS5 and query spatial data with R-Tree through typed C# APIs.
Query JSON columns with LINQ. Use JSONB when your SQLite runtime supports it, starting with SQLite 3.45.
Use ExecuteUpdate and ExecuteDelete to change matching SQLite rows in one SQL statement.
From a C# expression to typed results, with no code generated at runtime.
A normal query against a typed table. The C# compiler turns it into an expression tree.
The framework walks that expression tree and writes plain SQLite SQL with bound parameters. No IL is emitted.
The SQL runs and SQLite hands back a row reader.
A row reader maps columns to C# objects. Use reflection by default or build-time readers from SQLite.Framework.SourceGenerator for Native AOT.
Publishing a SQLite app with Native AOT? Generate row readers at build time with SQLite.Framework.SourceGenerator. Read the source generator setup guide
Save Pace Stream compares inserts with SQLite.Framework and EF Core 10. Each dot is one save of 100 rows. The gap shows how long that save took. See the BenchmarkDotNet results and setup.
Choosing a SQLite ORM for C#? Compare LINQ translation, Native AOT support and SQLite features in the default setup of each library.
| Feature | SQLite.Framework | EF Core 10 | sqlite-net-pcl |
|---|---|---|---|
| LINQ IQueryable translation | Yes | Yes | No |
| Select projection to SQL | Yes | Yes | No |
| Async API | Yes | Yes | Yes |
| Native AOT ready | Yes | No | Partial |
| Bulk update and delete by predicate | Yes | Yes | No |
| Full text search (FTS5) | Yes | No | Partial |
| JSON columns | Yes | Yes | No |
| Window functions and CTEs | Yes | Partial | No |
| Encryption (SQLCipher) | Yes | No | Yes |
| Change tracking and migrations | Partial | Yes | No |
One C# API with system SQLite, bundled SQLite, SQLCipher or your own provider.
Uses system SQLite on desktop and iOS. Bundles SQLite on Android.
Bundles a SQLite binary for a fixed version across supported platforms.
Encrypt SQLite databases with SQLCipher. Set the key with UseEncryptionKey.
Choose your own SQLitePCLRaw provider. No SQLite binary is included.
Yes. Add SQLite.Framework.SourceGenerator and call UseGeneratedMaterializers to generate row readers at build time. The framework does not generate code at runtime. See the Native AOT setup and trimming guide.
Yes. Create one SQLiteDatabase and share it across the app. Commands take a connection lock automatically. Turn on WAL mode so reads and writes do not block each other. See Multi-threading.
Yes. Use the SQLite.Framework.Cipher package, which ships SQLCipher. Call UseEncryptionKey on the options builder.
.NET 8, 9, 10 and 11. It uses the SQLite that ships with your OS by default. You can set a minimum version on the builder or ship a pinned binary with the Bundled package.
Yes. A versioned migration runner handles SQLite schema changes. Write hooks (OnAdd and OnUpdate) run before a save for audit and derived values. There is no automatic change tracker, unit of work or lazy loading. See the SQLite migration guide.
SQLite.Framework translates LINQ joins, grouping and Select projections to SQLite SQL. It supports Native AOT with a source generator and has no automatic change tracker. See Migrating from EF Core and Migrating from sqlite-net-pcl for API differences and setup examples.
Install SQLite.Framework from NuGet, define a C# model and run your first LINQ query. Follow a tutorial for console, .NET MAUI, Avalonia, ASP.NET or Blazor.