Changes from 6.1.0 to 7.0.0
This release focuses on tying up loose ends and fixing hundreds of bugs. Starting with this release, new features will only be added upon request via the issues page, as I have implemented everything I had set out to build with the framework.
How to upgrade
Per-table configuration (Index, Default, Check, Computed, ForeignKey, Strict, WithoutRowId, HasKey, AutoIncrement and the rest) moved off db.Table<T>().Schema and into OnModelCreating, configured through builder.Entity<T>(). It runs once before any table is used, so create, migrate and validate read the same definition.
Before (6.1.0):
db.Table<Book>().Schema
.Index(b => b.AuthorId)
.Default(b => b.Rating, 0)
.Check(b => b.Price > 0, name: "CK_Price_Positive")
.CreateTable();After (7.0.0):
public class AppDatabase : SQLiteDatabase
{
public AppDatabase(SQLiteOptions options) : base(options) { }
protected override void OnModelCreating(SQLiteModelBuilder builder)
{
builder.Entity<Book>()
.Index(b => b.AuthorId)
.Default(b => b.Rating, 0)
.Check(b => b.Price > 0, name: "CK_Price_Positive");
}
}
// The schema operations now run on their own:
db.Schema.CreateTable<Book>();The SQLite native dependency also changed. A security issue on the discontinued SQLitePCLRaw.bundle_green package forced the default SQLite.Framework package to move to SQLitePCLRaw.provider.sqlite3. If you need a SQLite binary shipped with your app, switch to the SQLite.Framework.Bundled package.
See all breakin changes below.
Breaking changes
SQLite native dependency changed
A security issue on the discontinued SQLitePCLRaw.bundle_green package forced a change of the SQLite native dependency. The default SQLite.Framework package now provides a native binding on all platforms except on Android where a bundled version is provided instead. On Windows the binding goes to winsqlite3.dll, the SQLite library that ships with Windows, so no separate sqlite3.dll is needed. The previous package provided a bundled version on all platforms except iOS.
Schema configuration moved to OnModelCreating
Model configuration moved to OnModelCreating and builder.Entity<T>(). db.Schema.Table<T>() now returns SQLiteTableSchema<T>, which carries only the schema operations (CreateTable, Migrate, AddColumn and so on). The builder also declares shadow columns with .Column(name, type).
Text storage is now culture-invariant
decimal in Text mode and the text date and time modes now read and write with the invariant culture, so 0.5 is stored as 0.5, not 0,5. A file written under a non-invariant culture reads differently after upgrading. Reading any other text-stored number (such as a text column into a double) now also uses the invariant culture.
Text date and time formats keep sub-second precision
This affects only the opt-in text storage modes, not the default ones. The default storage stays Integer for DateTime, Ticks for DateTimeOffset and Integer for TimeOnly and none of those change.
If you chose a text mode without passing your own format, the default format string changed to keep the fractional part of a second. DateTime in TextFormatted mode now uses yyyy-MM-dd HH:mm:ss.FFFFFFF, DateTimeOffset in TextFormatted mode uses yyyy-MM-dd HH:mm:ss.FFFFFFF zzz and TimeOnly in Text mode uses HH:mm:ss.FFFFFFF. Before, these kept only whole seconds, so the sub-second part was lost on a round trip.
A value with no fractional part is still written the same as before, with no trailing dot, so a row written by an older version reads back fine. A value with a fractional part is now written with the extra digits, so the stored text differs from what an older version wrote and outside tooling that expects a fixed yyyy-MM-dd HH:mm:ss shape sees the longer form. To keep the old shape, pass the old format string yourself, for example UseDateTimeStorage(DateTimeStorageMode.TextFormatted, "yyyy-MM-dd HH:mm:ss").
GroupBy(...).Count() now counts groups
Count and LongCount after a GroupBy now return the number of distinct groups. Review existing calls.
JournalMode pragma is now an enum
db.Pragmas.JournalMode uses the new SQLiteJournalMode enum (Delete, Truncate, Persist, Memory, Wal, Off) instead of a raw string.
First, Single and ElementAt after Take respect the limit
These and their OrDefault/Single variants now run inside the Take window. Take(0).First() throws and Take(n).ElementAt(m) past the window throws instead of reading an excluded row.
string.IsNullOrWhiteSpace and char.IsWhiteSpace match all whitespace
Both now treat tab, line feed, vertical tab, form feed and carriage return as whitespace, not only the space. They also recognize Unicode whitespace such as the non-breaking space.
Set operations reject OrderBy, Take, Skip and Reverse on either side
Concat, Union, Intersect and Except now throw NotSupportedException when either side carries OrderBy, Take, Skip or Reverse. This covers the receiver and the operand passed in. In 6.1.0 the query ran, but SQLite applied the ORDER BY or LIMIT to the whole combined result instead of that one side and Reverse ran in memory over the combined result, so the rows came back wrong. Materialize the ordered, paged or reversed side into a list before combining.
Sum and Average with a selector after Distinct now throw
Select(x => x.A).Distinct().Sum(x => x.A % 2) and the same shape over a multi-column projection, now throw NotSupportedException. In 6.1.0 the call ran but put DISTINCT on the selector result instead of on the distinct rows, so the number was wrong when two rows mapped to the same selector value. Project to the single column first with Select(x => x.A).Distinct().Sum() or read the rows with ToList and aggregate in memory. Distinct().Sum() and Distinct().Average() without a selector are unchanged.
Trigram tokenizer keeps diacritics by default
[TrigramTokenizer] now defaults RemoveDiacritics to false, matching SQLite's own trigram default. In 6.1.0 it defaulted to true, so a trigram FTS5 table folded diacritics and a search for "cafe" also matched the accented form. A table created or rebuilt under 7.0.0 no longer folds them. Set RemoveDiacritics = true to keep the old behavior. That option needs SQLite 3.45.0 or newer, so the new default also keeps the table portable to older SQLite.
ToString on a [Flags] enum in Where or OrderBy now throws
Calling the default name form of ToString on a [Flags] enum inside a query is no longer translated to SQL, because the flag name decomposition cannot be reproduced faithfully. In a top-level Select it runs on the client and returns the correct .NET name. Inside Where, OrderBy, a join or a subquery it now throws NotSupportedException, where 6.1.0 ran it in SQL. The "D" (number) and "X" (hex) formats still translate everywhere. Non-flags enums are unchanged.
Window Avg over an integer column now returns a double
SQLiteWindowFunctions.Avg gained typed overloads for the integer types, all returning SQLiteWindow<double>. Avg(intColumn) now binds to the double overload and keeps the fraction, like LINQ Average. In 6.1.0 it bound to the generic Avg<T> and returned SQLiteWindow<int> with a truncated result. Code that assigned the result to an int window or relied on the truncation, now sees a double.
CreateTrigger and the model Trigger no longer take forEachRow
The optional forEachRow parameter was removed from db.Schema.CreateTrigger<T>, its async form and builder.Entity<T>().Trigger. Triggers are always written as FOR EACH ROW. Code that passed forEachRow: by name no longer compiles.
ISQLiteCommandInterceptor has two new members
The interface gained two members. OnRowRead is called once for each row a reader returns. OnReaderClosing is called when the reader is disposed and carries the number of rows the caller read. A custom interceptor written against 6.1.0 must add both methods to compile. The built-in logging uses them to report how many rows a query returned.
Some builder types were renamed and moved namespace
The Upsert builder types moved into the SQLite.Framework namespace and gained the SQLite prefix, so UpsertBuilder<T>, UpsertConflictTarget<T> and UpsertAction<T> are now SQLiteUpsertBuilder<T>, SQLiteUpsertConflictTarget<T> and SQLiteUpsertAction<T>. The fluent SQLiteWhereBuilder<T> also moved from SQLite.Framework.Models into SQLite.Framework. Lambda calls like c => c.OnConflict(...) keep working, because the type is inferred. Only code that named one of these types or imported SQLite.Framework.Models for them, needs to change.
New features
Versioned schema migrations
db.Schema.Migrations() runs ordered, versioned migrations. Declare each schema version with Version(n, ...) or register one class per version with Add<T>(), then call Migrate to bring the database from the version it records up to the highest declared version. The reached version is stored in PRAGMA user_version, so a version that already ran is skipped on the next run. A whole run happens in one transaction, so a failure rolls the database back to the version it started at.
Inside a version, CreateTable<T>() makes a new table and TableChanged<T>() reconciles an existing one to the current model. Reconcile adds new columns, drops removed columns and brings indexes and triggers in line. It changes the table in place where it can and rebuilds and copies the rows otherwise. Pass rebuild: true to always rebuild, which works on any SQLite version. TableChanged<T>(s => s.Set(...)) fills new NOT NULL columns for existing rows. The step also has RenameColumn, DropColumn, DropTable and a raw Sql method. Plan() reports what a run would do without changing anything. Async wrappers are available.
await db.Schema.Migrations()
.Version(1, m => m.CreateTable<Book>().CreateTable<Author>())
.Version(2, m => m.TableChanged<Book>(s => s.Set(b => b.Genre, "Unknown")))
.Version(3, m => m.CreateTable<Magazine>())
.MigrateAsync();To keep each version in its own file, implement ISQLiteMigration once per version and register the classes with Add<T>().
public sealed class M0002_AddBookGenre : ISQLiteMigration
{
public static int Version => 2;
public void Apply(SQLiteMigrationStep step)
{
step.TableChanged<Book>(s => s.Set(b => b.Genre, "Unknown"));
}
}
await db.Schema.Migrations()
.Add<M0001_InitialSchema>()
.Add<M0002_AddBookGenre>()
.MigrateAsync();UPSERT with ON CONFLICT (...) DO UPDATE
Upsert builds the full ON CONFLICT (...) DO UPDATE syntax: update chosen or all non-key columns, add a WHERE guard, target a partial unique index and set columns from the stored and excluded rows. Requires SQLite 3.24.0 or newer.
await db.Table<Book>().UpsertAsync(book, c => c
.OnConflict(b => b.Id)
.DoUpdate(s => s
.Set(b => b.Price, (current, excluded) => current.Price + excluded.Price)
.Set(b => b.Title, (current, excluded) => excluded.Title))
.Where((current, excluded) => excluded.Price > current.Price));Read and write shadow columns
A shadow column lives in the table but has no CLR property. Declare it with .Column(name, type), read it with SQLiteColumn.Of<T>(row, "Name") in a query and set it with the new WithColumns method on a save.
await db.Table<Book>()
.WithColumns(c => c.Set(b => SQLiteColumn.Of<long>(b, "UpdatedAt"), _ => SQLiteFunctions.UnixEpoch()))
.UpdateAsync(book);Case-sensitive string comparison
UseCaseSensitiveStringComparison() makes Contains, StartsWith and EndsWith translate to case-sensitive instr/substr instead of the default LIKE. The StringComparison.OrdinalIgnoreCase overloads stay case-insensitive.
Wider subquery support
The translator now wraps a query in a subquery when the next operator cannot fold into the current SELECT, so a Where, second OrderBy, Distinct, GroupBy or join after a limiting Take/Skip translates correctly.
Validate the model against the live database
ValidateModel reports schema drift (missing table, missing or extra columns, wrong types, key or nullability differences, missing indexes or foreign keys) as findings instead of throwing, so you can catch drift at startup. An async wrapper is available.
Inline multi-row values with ValuesRange
SQLiteDatabase.ValuesRange lifts an in-memory list into a query without a temporary table, so you can join or filter against a small set.
List<string> titles = (
from id in db.ValuesRange(new[] { 1, 2, 3 })
join book in db.Table<Book>() on id equals book.Id
select book.Title).ToList();Filter and sort JSON object properties
A property read on a JSON-mapped object (registered through AddJsonContext) now translates to json_extract in Where and OrderBy, not only Select. Before, it threw in a filter or sort.
LINQ-typed trigger bodies
A new CreateTrigger<T> overload writes a trigger body in typed LINQ: reference Old/New, set an optional When guard and add Update, Insert or Delete statements, so a wrong column is caught at compile time.
db.Schema.CreateTrigger<Book>("trg_book_history", SQLiteTriggerTiming.After, SQLiteTriggerEvent.Update, t => t
.When(() => t.Old.Price != t.New.Price)
.Insert(db.Table<BookHistory>(), s => s
.Set(h => h.BookId, _ => t.New.Id)
.Set(h => h.NewPrice, _ => t.New.Price)));Enum default values
[DefaultValue(MyEnum.Active)] and the fluent Default(column, value) now accept an enum and write its underlying integer as the default, instead of throwing.
RETURNING on UPSERT
The Returning wrapper now works with Upsert and UpsertRange, handing back the written row through your projection. The result is default when the conflict resolves to no write. Requires SQLite 3.35.0 or newer.
NULLS FIRST and NULLS LAST in ORDER BY
OrderBy, OrderByDescending, ThenBy and ThenByDescending take an optional SQLiteNullsOrder to emit NULLS FIRST/NULLS LAST. The default keeps SQLite's placement. A non-default value requires SQLite 3.30.0 or newer.
FILTER on aggregate window functions
Chain Filter on an aggregate window function so only matching rows feed the aggregate, mapping to SUM(x) FILTER (WHERE pred) OVER (...). Works on aggregates, not ranking functions. Requires SQLite 3.30.0 or newer.
SQLiteWindowFunctions.Sum(o.Amount).Filter(o.Amount > 100).Over().PartitionBy(o.CustomerId).AsValue()EXCLUDE on window frames
The Rows, Range and Groups frame methods take an optional SQLiteFrameExclude (CurrentRow, Group, Ties) to drop rows near the current row. The default NoOthers is unchanged. A non-default value requires SQLite 3.28.0 or newer.
FULL OUTER JOIN
The new FullOuterJoin returns matched rows plus unmatched rows from both sides, with a null outer or inner row where only one side matched. Requires SQLite 3.39.0 or newer.
await db.Table<Author>()
.FullOuterJoin(db.Table<Book>(), a => a.Id, b => b.AuthorId,
(a, b) => new { Author = a == null ? null : a.Name, Book = b == null ? null : b.Title })
.ToListAsync();Typed LINQ queries against attached databases
Pass a schema name to db.Table<T>(schema) to read an attached file with the full typed LINQ surface. The query emits the schema-qualified name "aux"."Table", so you can join or filter an attached table against a main table in one query. The returned table is read-only, because writes through the typed API still go to the main database. If the attached file has its own SQLiteDatabase, call db.AttachDatabase(aux, "aux") and tables read through that other context resolve the schema prefix on their own.
await db.AttachDatabaseAsync("other.db", "aux");
List<string> titles = (
from a in db.Table<Author>()
join b in db.Table<Book>("aux") on a.Id equals b.AuthorId
select b.Title).ToList();Any with an equality predicate over a local list
A local in-memory list with Any and a single-parameter predicate now translates to an IN test against table columns. The predicate must compare list element members to table columns with ==, optionally joined by &&. One key column becomes column IN (...) and two or more become a row-value IN. A NULL list value matches a NULL column and an empty list matches no row. Before, this shape threw NotSupportedException.
List<Key> keys = [new Key { Code = 10, Id = 1 }, new Key { Code = 10, Id = 3 }];
await db.Table<Row>()
.Where(a => keys.Any(f => f.Code == a.Code && f.Id == a.Id))
.ToListAsync();Client evaluation of untranslatable values in a projection
A scalar method or member that the translator cannot turn into SQL now runs in memory inside a Select, instead of throwing. The column values it needs are read first and the rest is computed in C#, so a projection like (x.Value * 2).ToString("X4") works. This only applies inside a top-level Select. The same call in Where or OrderBy still throws, because it has to run in SQL there.
Force the runtime materializer for one query
UseReflectionMaterializer is the opposite of the source generator. Call it on a single query to skip the generated materializer and build the result with runtime reflection instead. It also exempts that one query from the DisableReflectionFallback throw. Every other query keeps its normal materializer.
var rows = await db.Table<Book>()
.Select(b => new { b.Id, b.Title })
.UseReflectionMaterializer()
.ToListAsync();Store char as an integer code point
UseCharStorage(CharStorageMode.Integer) makes char columns store the UTF-16 code unit as an INTEGER, instead of a single-character TEXT string. This round-trips every char value exactly, including the null char and lone surrogates that the default text mode cannot keep. Char comparisons and ORDER BY work by code point and char helpers like char.ToLower and char.IsAsciiDigit keep working. The default stays CharStorageMode.Text, so existing databases are unchanged.
New readers on SQLiteDataReader
SQLiteDataReader gains typed value readers GetDateTimeValue, GetDateTimeOffsetValue, GetTimeSpanValue, GetDateOnlyValue, GetTimeOnlyValue, GetGuidValue and GetDecimalValue. Each reads its type without boxing, returns the default for a NULL column and honors the configured storage mode. GetBlobSpan(index) reads a BLOB column as a ReadOnlySpan<byte> over SQLite's own buffer with no per-row copy. The span is only valid until the next Read or Dispose, so consume it before moving on.
Fixes
This release includes 100s of bug fixes. Far too many to fit here. They span query translation (joins, set operations, nullable and three-valued logic, subqueries), string and enum handling, date and time math, JSON-stored collections, FTS5, UPSERT, window functions and schema and transaction safety.
Full Changelog: 6.1.0...7.0.0