v6.1.0

Release notes for 6.1.0

Adds three new aggregate features: group_concat, total, and filtered aggregates.

String join with group_concat

string.Join over an IQueryable now translates to SQLite's group_concat. The rows are joined into one string inside SQL instead of being pulled into memory.

var titlesPerAuthor = await (
    from a in db.Table<Author>()
    select new
    {
        Author = a.Name,
        Titles = string.Join(", ", db.Table<Book>()
            .Where(b => b.AuthorId == a.Id)
            .Select(b => b.Title))
    }
).ToListAsync();

For a single roundtrip at the root of a query, use the new StringJoin and StringJoinAsync extensions:

string allTitles = db.Table<Book>()
    .OrderBy(b => b.Id)
    .Select(b => b.Title)
    .StringJoin(", ");

A plain string.Join(sep, queryable) at the root still works but enumerates rows in memory. Use StringJoin when you want SQL to do the work.

Total aggregate

SQLiteFunctions.Total(...) translates to SQLite's total(X). It is like Sum but always returns a REAL value and returns 0.0 for empty input instead of NULL.

var revenueByAuthor = await (
    from book in db.Table<Book>()
    group book by book.AuthorId into g
    select new
    {
        AuthorId = g.Key,
        Revenue = SQLiteFunctions.Total(g.Select(b => b.Price))
    }
).ToListAsync();

At the root of a query, call Total or TotalAsync directly on the queryable:

double revenue = db.Table<Book>()
    .Where(b => b.AuthorId == 1)
    .Total(b => b.Price);

Filtered aggregates

Chain a Where before any grouping aggregate to restrict which rows it sees. This emits SQLite's FILTER (WHERE ...) clause (SQLite 3.30+).

var rows = await (
    from book in db.Table<Book>()
    group book by book.AuthorId into g
    select new
    {
        AuthorId = g.Key,
        PriceyRevenue = g.Where(x => x.Price >= 10).Sum(x => x.Price),
        TotalRevenue = g.Sum(x => x.Price)
    }
).ToListAsync();

Full Changelog: 6.0.1...6.1.0

v6.0.1

Release notes for 6.0.1

This version only updates nuget's README to properly point to the new https://sqlite-framework.net/ site.

Full Changelog: 6.0.0...6.0.1

v6.0.0

Changes from 5.3.2 to 6.0.0

How to upgrade

The separateConnection: true flag is gone from every transaction method and from the range write methods.

Before (5.3.2):

using SQLiteTransaction tx = db.BeginTransaction(separateConnection: true);
db.Table<Book>().AddRange(books, separateConnection: true);

After (6.0.0):

using SQLiteTransaction tx = db.BeginTransaction();
db.Table<Book>().AddRange(books);

The feature did not deliver what its name implied. SQLite serializes writers at the file level no matter how many connections you open, so opening a side connection never gave you parallel writes. After measuring the two paths against each other, the separate path was slower because of the extra connection setup.

Breaking changes

separateConnection removed

Removed from BeginTransaction, BeginTransactionAsync, and the range methods (AddRange, UpdateRange, RemoveRange, AddOrUpdateRange, UpsertRange) on SQLiteTable<T> and SQLiteReturningTable<T, TResult>, sync and async. Drop the argument where you pass it.

Async methods moved to extension methods

Some async methods that used to live on the core classes are now extension methods.

• SQLiteDatabase.LockAsync and ReadLockAsync are now on AsyncDatabaseExtensions.
• SQLiteTransaction.CommitAsync and RollbackAsync are now on AsyncTransactionExtensions.

Obsolete table shims removed

SQLiteTable<T>.CreateTable(), SQLiteTable<T>.DropTable(), and their async equivalents (CreateTableAsync, DropTableAsync) were marked obsolete in 5.0.0 and are now removed. Use db.Schema.CreateTable<T>(), db.Schema.DropTable<T>(), and the async variants on AsyncSchemaExtensions instead.

New features

Minimum SQLite version

The same code can land on Android 5 (SQLite 3.8.6) and Android 14 (3.41), or iOS 11 (3.19) and iOS 17 (3.43). A method that compiles fine fails at runtime on the older device.

Declare the floor your app commits to:

SQLiteOptions options = new SQLiteOptionsBuilder("app.db")
    .UseMinimumSqliteVersion(SQLiteMinimumVersion.V3_35)
    .Build();

At connection open and on method calls the framework checks the loaded SQLite meets the floor. Any later call that needs a newer SQLite version throws a clear NotSupportedException naming the feature and the required version, instead of falling through to SQLite's no such function.

The enum has an entry for every SQLite minor release from 3.8 through 3.50, with each entry naming the iOS and Android versions whose system SQLite meets that floor. Available on the main SQLite.Framework package and on SQLite.Framework.Base. The SQLite.Framework.Bundled and SQLite.Framework.Cipher packages ship a SQLite with a known version, so the floor is forced to Unspecified.

R-Tree virtual tables

A class can now be mapped to a SQLite R-Tree virtual table with [RTreeIndex]. The framework reads [Key], [RTreeMin], [RTreeMax], and [RTreeAuxiliary] to build the schema, supports Add, Update, Remove, and queries against the bounding box. Both rtree (float) and rtree_i32 (integer) modules are supported. Requires SQLite 3.24.0 or newer.

[RTreeIndex]
public class Region
{
    [Key] public int Id { get; set; }
    [RTreeMin("X")] public float MinX { get; set; }
    [RTreeMax("X")] public float MaxX { get; set; }
    [RTreeMin("Y")] public float MinY { get; set; }
    [RTreeMax("Y")] public float MaxY { get; set; }
    [RTreeAuxiliary] public string? Label { get; set; }
}

db.Schema.CreateTable<Region>();
db.Table<Region>().Add(new Region { Id = 1, MinX = 0, MaxX = 10, MinY = 0, MaxY = 10, Label = "A" });

List<Region> hits = db.Table<Region>()
    .Where(r => r.MinX <= 5 && r.MaxX >= 5 && r.MinY <= 5 && r.MaxY >= 5)
    .ToList();

STRICT tables

Mark a class with [StrictTable] to create the table in SQLite STRICT mode. SQLite then enforces the declared column types on every insert and update instead of accepting any type. Requires SQLite 3.37.0 or newer.

[StrictTable]
public class Book
{
    [Key]
    public int Id { get; set; }

    public required string Title { get; set; }
}

The fluent builder has the same option:

db.Table<Book>().Schema.Strict().CreateTable();

[StrictTable] combines with [WithoutRowId]. The framework writes the two modifiers as WITHOUT ROWID, STRICT.

Stream-based BLOB I/O

SQLiteDatabase.OpenBlobStream(...) returns a SQLiteBlobStream (a Stream) over a single BLOB column on a single row, using SQLite's incremental BLOB I/O.

using SQLiteBlobStream stream = db.OpenBlobStream<Book>(book.Id, b => b.Cover);
await stream.CopyToAsync(outputFile);

db.Table<Book>().Add(new Book { Id = 1, Cover = new byte[payload.Length] });
using SQLiteBlobStream write = db.OpenBlobStream<Book>(1, b => b.Cover, writable: true);
await write.WriteAsync(payload);

The stream is fixed-length, SQLite incremental I/O cannot grow a blob. Pre-allocate with a sized byte[] through Add or Update, or with raw SQL using zeroblob(n). Overloads accept either a strongly-typed column selector or raw table and column names. OpenBlobStreamAsync waits for the connection lock asynchronously.

UPDATE FROM

ExecuteUpdate now accepts a Join chain. SQLite emits the joined sources as a FROM clause and the join keys as WHERE conditions. Requires SQLite 3.33.0 or newer.

db.Table<Book>()
    .Join(db.Table<Author>(), b => b.AuthorId, a => a.Id, (b, a) => new { b, a })
    .Where(x => x.a.Country == "US")
    .ExecuteUpdate(s => s.Set(x => x.b.Title, x => x.a.Name + " - bestseller"));

The Set lvalue must reference the target table (the first Table<T> in the chain). Referencing a joined source throws.

COLLATE in indexes

Indexes can now declare a collation per column. Useful for case-insensitive or trailing-space-insensitive lookups that the query planner can actually use.

[Indexed(Collation = SQLiteCollation.NoCase)]
public required string Email { get; set; }

The fluent builder takes a single collation for all columns, or an array for per-column control on a composite index:

db.Table<Book>().Schema
    .Index(b => b.Email, unique: true, collation: SQLiteCollation.NoCase)
    .Index(b => new { b.LastName, b.FirstName },
        collations: [SQLiteCollation.NoCase, SQLiteCollation.NoCase])
    .CreateTable();

SQLiteCollation has four values: Inherit (no clause emitted, the column's declared collation wins), Binary, NoCase, and Rtrim. For ad-hoc query use, SQLiteFunctions.Collate(expr, collation) wraps an expression in a COLLATE clause.

Expression and functional indexes

The fluent Index(...) builder now accepts any expression body, so b => b.Title.ToLower() and composite slots that mix columns with expressions create an expression index. Requires SQLite 3.9.0 or newer.

db.Table<Book>().Schema
    .Index(b => b.Title.ToLower(), name: "IX_Book_TitleLower")
    .CreateTable();

Per-column ASC and DESC in indexes

Index(...) and [Indexed] now accept a direction for every slot of the index. The default SQLiteIndexDirection.Inherit emits no clause, Ascending emits ASC, and Descending emits DESC.

db.Table<Book>().Schema
    .Index(b => new { b.AuthorId, b.PublishedAt },
        name: "IX_Book_AuthorAndPublished",
        directions: [SQLiteIndexDirection.Ascending, SQLiteIndexDirection.Descending])
    .CreateTable();

Column defaults

A column can declare its DEFAULT clause via the [DefaultValue] attribute, the fluent builder, or the AddColumn migration helper.

[DefaultValue(0)]
public int Rating { get; set; }

[DefaultValue("Unknown")]
public string Genre { get; set; }

The fluent builder has two overloads. One for literals and one for a translated SQL expression:

db.Table<Book>().Schema
    .Default(b => b.Rating, 0)
    .Default(b => b.Slug, () => SQLiteFunctions.SqliteVersion())
    .CreateTable();

AddColumn accepts the same two shapes:

db.Schema.AddColumn<Book>(b => b.Rating, () => 7 * 6);

MATERIALIZED and NOT MATERIALIZED CTE hints

db.With(...) and db.WithRecursive(...) now accept a SQLiteCteMaterialization argument. The default emits no hint. Materialized emits AS MATERIALIZED (, NotMaterialized emits AS NOT MATERIALIZED (. Requires SQLite 3.35.0 or newer to set a non-default value.

SQLiteCte<Book> cte = db.With(
    () => db.Table<Book>().Where(b => b.Price > 100),
    SQLiteCteMaterialization.Materialized);

VACUUM and VACUUM INTO

SQLiteDatabase now exposes Vacuum(schema?) to rebuild the database file and VacuumInto(path, schema?) to write a clean copy to a separate file. Both have async wrappers. VACUUM is universally supported. VACUUM INTO requires SQLite 3.27.0 or newer.

db.Vacuum();
db.VacuumInto("backup.db");

REINDEX

SQLiteDatabase.Reindex(name?) rebuilds indexes. No argument rebuilds every index in every attached database. Pass a table, index, or collation name to limit the scope. ReindexAsync is also available.

db.Reindex();
db.Reindex("Books");
db.Reindex("NOCASE");

Direct date and time SQL functions

A new SQLiteDateFunctions class exposes SQLite's date and time functions with modifier strings: Date, Time, Datetime, JulianDay, Strftime, Timediff. Each accepts a time value plus any number of modifier strings like "+7 days", "start of month", "unixepoch", or "utc". Timediff (SQLite 3.43.0+) is not available in SQLCipher and emits a compile-time error there.

string month = db.Table<Book>()
    .Select(...

v5.3.2

Release notes for 5.3.2

AddColumn now accepts a default value.

AddColumn default value

AddColumn and AddColumnAsync take an optional defaultValue argument. When set, the framework writes it as the SQL DEFAULT clause and SQLite uses it to fill existing rows.

The emitted SQL is:

ALTER TABLE "Books" ADD COLUMN Pages INTEGER NOT NULL DEFAULT 0
ALTER TABLE "Books" ADD COLUMN Genre TEXT NULL DEFAULT 'Unknown'

Full Changelog: 5.3.1...5.3.2

v5.3.1

Release notes for 5.3.1

Composite indexes are now correctly working for both the attribute layer and the fluent builder.

Composite indexes

When multiple properties carry [Indexed] with the same name, the framework now groups them into one CREATE INDEX statement ordered by Order. Previously, each attribute produced its own single-column index, which was ignored at runtime when two properties shared the same index name.

[Indexed("IX_Book_AuthorGenre", 0)]
public int AuthorId { get; set; }

[Indexed("IX_Book_AuthorGenre", 1)]
public string? Genre { get; set; }

This now produces a single index:

CREATE INDEX "IX_Book_AuthorGenre" ON "Books" (AuthorId, Genre)

Set IsUnique = true on any of the attributes to make the composite index unique.

The fluent builder also accepts an anonymous object to create a composite index in one call:

db.Schema.Table<Book>()
    .Index(b => new { b.AuthorId, b.Genre }, name: "IX_Book_AuthorGenre")
    .CreateTable();

Full Changelog: 5.3.0...5.3.1

v5.3.0

Release notes for 5.3.0

Foreign keys, SQLite's RETURNING clause, one-call JSON converter registration and faster row materialization.

Foreign keys

Declare a foreign key with [ReferencesTable(typeof(Parent))]. For composite keys use the fluent db.Schema.Table<T>().ForeignKey<TParent>(...) builder.

public class Book
{
    [Key]
    public int Id { get; set; }
    public required string Title { get; set; }

    [ReferencesTable(typeof(Author), OnDelete = SQLiteForeignKeyAction.Cascade)]
    public int AuthorId { get; set; }
}

The framework also reads [System.ComponentModel.DataAnnotations.Schema.ForeignKey("Author")] (string target, primary key inferred, NoAction defaults). Use [ReferencesTable] when you need an action other than NoAction, deferred enforcement, or typeof targeting.

Enforcement is on by default. The framework runs PRAGMA foreign_keys = ON on every connection open. Pass UseForeignKeys(false) to the builder to opt out, or flip it at runtime with db.Pragmas.ForeignKeys = false.

RETURNING

Returning wraps a query or a table so the next write returns the affected rows instead of the row count. Requires SQLite 3.35 or later.

List<int> archivedIds = await db.Table<Book>()
    .Where(b => b.Price > 100)
    .Returning(b => b.Id)
    .ExecuteDeleteAsync();

Book? added = await db.Table<Book>()
    .Returning()
    .AddAsync(new Book { Title = "Clean Code", AuthorId = 1, Price = 29.99m });

The RETURNING clause can only reference columns from the table being modified. SQLite rejects projections that touch a joined entity.

Dictionary row materialization

Pass Dictionary<string, object?> (or Dictionary<string, object>) as the element type to get each row as a column-name to value map. Values come back in their SQLite-native form (long, double, string, byte[], or null).

List<Dictionary<string, object?>> rows = db.Query<Dictionary<string, object?>>(
    "SELECT Id, Name FROM Books");

var streamed = db.CreateCommand("SELECT * FROM Books", [])
    .ExecuteQuery<Dictionary<string, object?>>()
    .ToList();

JSON graph registration

SQLiteOptionsBuilder.AddJsonContext(context) and AddJsonbContext(context) register a JSON converter for every type in your JsonSerializerContext and walks nested property types. Saves the per-type AddTypeConverter<T>(new SQLiteJsonConverter<T>(...)) boilerplate.

[JsonSerializable(typeof(Order))]
[JsonSerializable(typeof(Customer))]
public partial class AppJsonContext : JsonSerializerContext;

SQLiteOptions options = new SQLiteOptionsBuilder("app.db")
    .AddJsonContext(AppJsonContext.Default)
    .Build();

AddJsonbContext does the same with JSONB storage. Both methods skip any type that already has a converter.

Faster row materialization

The runtime and source-generated materializers now resolve column indices once per query instead of once per row. The row body uses typed reader calls bound to those indices.

100-row entity read, .NET 10, JIT:

Path	Before	After
SQLite.Framework + SourceGenerator	39.0 μs / 21.8 KB	36.5 μs / 22.1 KB
SQLite.Framework	69.7 μs / 36.1 KB	37.9 μs / 22.2 KB

EntityMaterializers is now a builder dictionary

The EntityMaterializers map changed from Func<SQLiteQueryContext, object?> (per row) to Func<SQLiteQueryContext, Func<SQLiteQueryContext, object?>> (factory per query that returns the row materializer).

If you registered a materializer manually, change it like this:

// Before
builder.EntityMaterializers[typeof(Book)] = ctx =>
{
    int id = (int)ctx.Reader!.GetValue(ctx.Columns!["Id"], ctx.Reader.GetColumnType(ctx.Columns["Id"]), typeof(long))!;
    return new Book { Id = id, /* ... */ };
};

// After
builder.EntityMaterializers[typeof(Book)] = setupCtx =>
{
    int idIndex = setupCtx.Columns!["Id"];
    return rowCtx => new Book { Id = rowCtx.Reader!.GetInt32(idIndex), /* ... */ };
};

The source-generated UseGeneratedMaterializers extension is updated automatically when you bump SQLite.Framework.SourceGenerator.

Platform support

A few public APIs that needed a newer SQLite than older iOS / Android system SQLite ships with now carry [SupportedOSPlatform] attributes, so CA1416 warns when you target an OS version below the floor. Only applies to the SQLite.Framework package.

API	Min iOS	Min Android	SQLite
SQLiteJsonFunctions JSON1 methods (Extract, Set, Insert, Replace, Remove, Type, Valid, Patch, ArrayLength, Minify)	10.0	24.0	3.9
SQLiteFunctions.UnixEpoch() / UnixEpoch(string)	16.0	34.0	3.38
SQLitePragmas.TableInfo / IndexList / ForeignKeyList	11.0	26.0	3.16
SQLiteOptionsBuilder.AddJsonbContext (new this release)	unsupported	36.0	3.45
Returning and the SQLiteReturningQueryable / SQLiteReturningTable write methods (new this release)	15.0	34.0	3.35

Full Changelog: 5.2.0...5.3.0

v5.2.0

Release notes for 5.2.0

This release adds typed access to SQLite's internal tables and pragma table-valued functions, makes LINQ subquery syntax work, and ships a few new query and configuration helpers.

Pragmas and system tables

db.Pragmas now exposes typed wrappers for the SQLite tables and table-valued functions used to inspect a database.

• db.Pragmas.Master is a LINQ-queryable view over sqlite_master.
• db.Pragmas.Sequence is a LINQ-queryable view over sqlite_sequence.
• db.Pragmas.TableInfo(name) wraps pragma_table_info.
• db.Pragmas.IndexList(name) wraps pragma_index_list.
• db.Pragmas.ForeignKeyList(name) wraps pragma_foreign_key_list.

The pragma wrappers can be used inside an outer query, with the argument bound to a column of the outer source. The framework emits a single SQL statement.

var rows = (
    from m in db.Pragmas.Master
    from p in db.Pragmas.TableInfo(m.Name)
    where m.Type == "table" && !m.Name.StartsWith("sqlite_")
    select new { Table = m.Name, Column = p.Name, Type = p.Type }
).ToList();

LINQ subquery syntax emits real SQL subqueries

When the source of a from is a query that contains Select, Where, GroupBy, Take, Skip, Distinct, or Reverse, the framework now wraps it as a real SQL subquery instead of flattening it into the root query.

from b in (from a in db.Table<Book>() where a.AuthorId == 1 select a)
from u in db.Table<Author>()
select new { b.Title, u.Name }

emits

SELECT b1.Title, a1.AuthorName
FROM ( SELECT ... FROM "Books" WHERE BookAuthorId = @p0 ) AS b1
CROSS JOIN "Authors" AS a1

Read-only tables

db.ReadOnlyTable<T>() returns a ReadOnlySQLiteTable<T> with the full LINQ surface (Select, Where, Join, OrderBy, and so on) but without Add, Update, Remove, or Clear. Used by the new pragma tables.

WhereBuilder

IQueryable<T>.WhereBuilder(...) lets you compose a single WHERE clause from many AND / OR predicates. Helpful when filters are built up at runtime and chaining Where would not capture the desired logic.

var query = db.Table<Book>().WhereBuilder(b =>
{
    b.And(x => x.Price > 0);
    if (searchTerm != null)
    {
        b.Or(x => x.Title.Contains(searchTerm));
        b.Or(x => x.Author.Contains(searchTerm));
    }
});

An empty builder leaves the query unchanged.

Command interceptors and logging

ISQLiteCommandInterceptor (in SQLite.Framework) lets you observe every command the framework runs. Methods are OnExecuting(SQLiteCommand), OnExecuted(SQLiteCommand, int? rowsAffected), and OnFailed(SQLiteCommand, Exception). Register through the options builder:

builder.AddCommandInterceptor(new MyInterceptor());

For a quick log of every SQL statement, use the shorthand:

builder.LogCommands(Console.WriteLine);

Output looks like:

(12ms, 3 rows) SELECT b0.BookId AS "Id", b0.BookTitle AS "Title" FROM "Books" AS b0 WHERE b0.BookAuthorId = @p0 | @p0=?

Parameter values are masked with ? by default. Call EnableSensitiveParameterLogging() on the builder to inline real values in the log output.

The previous SQLiteDatabase.CommandCreated event has been replaced by the new interceptor surface, which also reports the elapsed time and outcome.

Views and triggers

db.Schema now has typed builders for views and triggers.

db.Schema.CreateView<BookSummary>(() =>
    from b in db.Table<Book>()
    where b.Price > 0
    select new BookSummary { Id = b.Id, Title = b.Title, Price = b.Price });

bool exists = db.Schema.ViewExists<BookSummary>();
db.Schema.DropView<BookSummary>();

db.Schema.CreateTrigger<Book>(
    name: "trg_book_history",
    timing: TriggerTiming.After,
    @event: TriggerEvent.Update,
    body: "INSERT INTO BookHistory(BookId, OldPrice, NewPrice) VALUES (NEW.Id, OLD.BookPrice, NEW.BookPrice)",
    when: "OLD.BookPrice <> NEW.BookPrice");

db.Schema.DropTrigger("trg_book_history");

The view name comes from the [Table("...")] attribute on the entity. Pair the view with db.ReadOnlyTable<T>() to query it. Trigger bodies and the optional WHEN predicate are raw SQL strings that use OLD and NEW to refer to the row. Async wrappers (CreateViewAsync, DropViewAsync, ViewExistsAsync, ListViewsAsync, CreateTriggerAsync, DropTriggerAsync) are also provided.

EXPLAIN QUERY PLAN

IQueryable<T>.ExplainQueryPlan() runs EXPLAIN QUERY PLAN for the query and returns a tree of SQLiteQueryPlanNodes. Useful for checking whether the planner picks the index you expect.

SQLiteQueryPlan plan = db.Table<Book>()
    .Where(b => b.AuthorId == 1)
    .ExplainQueryPlan();

Console.WriteLine(plan);

prints

QUERY PLAN
> SEARCH b0 USING INDEX IX_Book_AuthorId (BookAuthorId=?)

ToString() renders an ASCII tree with two-space indents. There is also ExplainQueryPlanAsync for the async path.

Configuration

• SQLiteOptionsBuilder.UseBlockReadsDuringTransaction() makes reads from other async contexts wait for the active transaction to finish. Off by default.

Platform support

SQLiteSchema.RenameColumn, SQLiteSchema.DropColumn, the matching async wrappers, and the fluent Computed (generated columns) builder method are now marked with SupportedOSPlatform attributes. iOS and Android projects targeting an OS version older than the one that ships the required SQLite get a build-time warning.

Method	Min iOS	Min Android	SQLite version
ExplainQueryPlan / ExplainQueryPlanAsync	12.0	30.0	3.24.0
RenameColumn / RenameColumnAsync	13.0	30.0	3.25.0
Computed (fluent generated columns)	14.0	31.0	3.31.0
DropColumn / DropColumnAsync	15.0	34.0	3.35.0

Only applies to the SQLite.Framework package (which uses the OS-provided SQLite on iOS and Android). The SQLite.Framework.Bundled and SQLite.Framework.Base packages do not get the attributes.

JSON

• Now handles more shapes, including nested collections inside projections and mixes of JSON paths with regular columns.
• Now correctly handles SelectMany over JSON arrays and propagates the element type through chained operators.

Async

• Async methods now don't block the background thread while waiting for a transaction to finish.
• The async extension methods now cover LongCount, ToDictionary without an element selector, and pass cancellation tokens by parameter on Add, Update, Remove, AddOrUpdate, and Upsert overloads.
• Async queryable methods now rethrow the inner exception cleanly so call-site stack traces are no longer wrapped in an AggregateException.
• New AsyncSQLiteCommandExtensions lets you await ExecuteNonQuery, ExecuteScalar, ExecuteQuery, and ExecuteReader directly on SQLiteCommand.

Window functions

The window builder accepts more frame shapes (rows-between, range-between, groups-between) and the Window accessor on IGrouping now flows through OrderBy / ThenBy.

Internals

• SQLiteExpression was reworked to reduce allocations and increase speed.

Samples

• New ASP.NET sample under Sample/SQLite.Framework.AspNet.

Package updates

• SQLite.Framework.Base now ships with SQLitePCLRaw.core version 3.0.3.
• SQLite.Framework.Bundled now ships with SQLitePCLRaw.bundle_e_sqlite3 version 3.0.3.

Full Changelog: 5.1.0...5.2.0

v5.1.0

Release notes for 5.1.0

This release is focused on expanding LINQ translations and improving exceptions in queries.

New LINQ translations

Math

• Math.Sin, Math.Cos, Math.Tan map to SIN, COS, TAN.
• Math.Asin, Math.Acos, Math.Atan, Math.Atan2 map to ASIN, ACOS, ATAN, ATAN2.
• Math.Sinh, Math.Cosh, Math.Tanh, Math.Asinh, Math.Acosh, Math.Atanh map to the matching SQLite hyperbolic functions.
• Math.Cbrt(x) maps to POWER(..., 1.0/3.0) and keeps the sign, so it works for negative numbers too.
• Math.Log2(x) maps to LOG2(x).
• Math.Clamp(x, min, max) maps to a CASE WHEN ... THEN ... ELSE ... END.
• Math.Truncate(x) maps to TRUNC(x).
• Math.Round(x, digits, MidpointRounding.AwayFromZero) is now translated, so the 3-argument overload no longer needs to run on the client.

String

• s[i] (string indexer) maps to SUBSTR(s, i + 1, 1).
• s.CompareTo(other) maps to CASE WHEN s = other THEN 0 WHEN s < other THEN -1 ELSE 1 END, the same shape as string.Compare.

DateTime / DateTimeOffset

• Date and TimeOfDay are now translated for both DateTime and DateTimeOffset. Before, they were silently passed through with no SQL.

Nullable

• Nullable<T>.GetValueOrDefault() maps to COALESCE(x, 0).
• Nullable<T>.GetValueOrDefault(default) maps to COALESCE(x, default).

Collections

• IEnumerable.Contains over a constant collection now also works when the compiler wraps the array in an implicit conversion (for example, C# 12 turns int[] into ReadOnlySpan<int> via op_Implicit). The visitor unwraps the conversion and emits IN (...).
• GroupBy(x => new { x.A, x.B }) now lets you read the composite key as g.Key.A and g.Key.B in the projection. Before, those member accesses could not be resolved.
• Distinct now correctly applies inside Sum, Avg, and Count aggregates as SUM(DISTINCT col) and so on, when chained as .Select(...).Distinct().Sum().

Behavior fixes

• Constructor not being accepted for entity materialization: used to throw an exception. Now works as intended, this also makes it possible to use primary constructor record types.
• Sum on an empty source: used to throw NullReferenceException. Now returns 0 for non-nullable numeric types via COALESCE(SUM(...), 0). Nullable result types still return null.
• Max, Min, Average on an empty source: used to throw NullReferenceException. Now throws InvalidOperationException("Sequence contains no elements") for non-nullable result types, the same way LINQ-to-Objects does. Nullable result types return null.
• Take(N).Take(M): the second call used to replace the first, so Take(2).Take(5) returned 5 rows. Now it takes the smaller of the two values.
• Skip(N).Skip(M): the second call used to replace the first. Now both values are added together, so the total skip is N + M.
• Take(N).Skip(M): used to translate directly to LIMIT N OFFSET M, which is the wrong order for LINQ. Now it follows LINQ rules: take N rows first, then skip M of those. The effective limit becomes max(0, N - M) and the offset adds M.
• OrderBy(...).OrderByDescending(...): the second call did not reset the sort because the check had a typo (nameof(Queryable.OrderDescending) instead of OrderByDescending). It silently behaved like ThenByDescending. Fixed.
• Aggregates after Take or Skip: used to produce wrong SQL because LIMIT/OFFSET was placed next to COUNT(*) or SUM(...). Now throws a clear NotSupportedException and tells the user to load the rows into memory first.
• Operations after Concat, Union, Intersect, Except: Where, non-identity Select, Distinct, GroupBy, aggregates, Reverse, Contains, Join, SelectMany, and the predicate forms of First, Single, Any, All used to produce silently wrong SQL, because the operation was applied only to the first query and the second was left as-is. All of these now throw a clear NotSupportedException and suggest applying the operation to each side before combining, or loading the rows into memory first. OrderBy, Take, Skip, the identity Select, the no-predicate forms of First and Any, and chained set operations still work as before.
• Aggregate after Distinct on a multi-column projection: now throws a clear NotSupportedException instead of building invalid SQL.
• GroupJoin (the LINQ into <name> form) used as a sequence: when the result selector calls sequence methods on the group (such as bg.Count() or bg.Sum(...)) instead of flattening with from x in bg.DefaultIfEmpty(), the framework now throws a clear error and points to the correlated-subquery shape.
• Chained scalar Select with a method or property access: code like db.Table<X>().Select(x => x.Title).Select(t => t.Length) used to drop the second member access and emit SELECT Title AS "Length", which then crashed when the runtime tried to read the string as an int. The visitor now recognises that the outer parameter is a single-column projection and applies the member access (so Length becomes LENGTH(Title)).
• Member access on the result of .First/.Single/etc. when the source returns an entity: code like db.Table<X>().First(x => p).Name used to emit (SELECT * FROM X WHERE p) and SQLite then complained sub-select returns 4 columns - expected 1. The visitor now throws a clear NotSupportedException pointing the user to the .Where(p).Select(x => x.Name).First() rewrite.
• string.Equals(other, StringComparison): the instance overload silently dropped the StringComparison argument. b.Title.Equals("apple", StringComparison.OrdinalIgnoreCase) translated to b.Title = 'apple', which is case-sensitive in SQLite and so never matched. The visitor now applies COLLATE NOCASE for the three case-insensitive comparisons (OrdinalIgnoreCase, CurrentCultureIgnoreCase, InvariantCultureIgnoreCase) and leaves the other modes case-sensitive, matching the existing string.Equals(a, b, StringComparison) static overload.

Source generator

• Anonymous-type materializers now handle nested entity initialization correctly.
• Registration for source-generated column writers was improved together with the materializer changes.
• Runtime materializer (the reflection path used when the source generator is not in use) now also accepts entities with a private parameterless constructor, matching what the source generator's reflection emitter has always supported.

Samples

• New Avalonia sample under Sample/SQLite.Framework.Avalonia.
• Both MAUI and Avalonia samples now ship a SQLITE_FRAMEWORK.md cheat sheet you can drop into a project so coding agents have an accurate reference.

Documentation

• New wiki/Overview.md page, used as the landing page for the documentation site.
• New wiki/AI Assistance.md page that explains how to point Claude Code, Cursor, Copilot, and other agents at the cheat sheet.
• wiki/Expressions.md updated with all the new Math, String, and DateTime translations.

Full Changelog: 5.0.3...5.1.0

v5.0.3

• Fixed: AddOrUpdate now respects a non-default [AutoIncrement] primary key set on the entity.
• New option: PreserveExplicitAutoIncrementKeys() makes Add and AddRange use a non-default [AutoIncrement] key the same way EF Core does.
• New wiki page: a guide for people moving from Entity Framework Core.
• Wiki upgrade: full-text search across all pages, offline support through a service worker and PWA manifest, and several smoothness fixes.

Full Changelog: 5.0.2...5.0.3

v5.0.2

AddRange, UpdateRange and other Range methods have had a more than 3x performance increase with around 10x reduced memory allocation

Full Changelog: 5.0.1...5.0.2