SQLite Functions

SQLite Functions

SQLiteFunctions is a static class with helpers for SQLite functions that have no plain C# equivalent. Use these inside a LINQ query. The framework swaps them for the right SQL.

The class also holds the FTS5 helpers (Match, Rank, Snippet, Highlight). Those have their own page in Full Text Search.

What is in here

Method	What it maps to
Random()	RANDOM()
RandomBlob(n)	RANDOMBLOB(n)
Glob(pattern, value)	value GLOB pattern
UnixEpoch()	unixepoch()
UnixEpoch(when)	unixepoch(when)
Printf(format, args)	printf(format, ...)
Regexp(value, pattern)	value REGEXP pattern
Between(value, low, high)	value BETWEEN low AND high
In(value, v0, v1, ...)	value IN (v0, v1, ...)
Coalesce(v0, v1, ...)	coalesce(v0, v1, ...)
Nullif(a, b)	nullif(a, b)
Iif(condition, whenTrue, whenFalse)	iif(condition, whenTrue, whenFalse)
Typeof(x)	typeof(x)
Hex(bytes)	hex(bytes)
Unhex(value) / Unhex(value, ignoreChars)	unhex(...) (SQLite 3.41+)
Format(format, args)	format(format, ...) (SQLite 3.38+)
Unicode(value)	unicode(value)
Char(c0, c1, ...)	char(c0, c1, ...)
Quote(x)	quote(x)
Zeroblob(n)	zeroblob(n)
Instr(haystack, needle)	instr(haystack, needle)
LastInsertRowId()	last_insert_rowid()
SqliteVersion()	sqlite_version()
Min(v0, v1, ...)	min(v0, v1, ...) (scalar, two or more args)
Max(v0, v1, ...)	max(v0, v1, ...) (scalar, two or more args)
Total(g.Select(x => x.Col))	total(col) (aggregate, returns REAL, 0.0 for empty input)
Changes()	changes()
TotalChanges()	total_changes()

Random and random blob

List<Book> randomFive = db.Table<Book>()
    .OrderBy(b => SQLiteFunctions.Random())
    .Take(5)
    .ToList();

byte[] sessionToken = db.Table<Book>()
    .Select(b => SQLiteFunctions.RandomBlob(16))
    .First();

GLOB matching

Glob is like LIKE but uses Unix shell wildcards. * matches any string, ? matches one character.

List<Book> rows = db.Table<Book>()
    .Where(b => SQLiteFunctions.Glob("Clean*", b.Title))
    .ToList();

The order is Glob(pattern, value). The SQL is value GLOB pattern.

Unix timestamps

long now = db.Table<Book>().Select(b => SQLiteFunctions.UnixEpoch()).First();

long y2024 = db.Table<Book>()
    .Select(b => SQLiteFunctions.UnixEpoch("2024-01-01"))
    .First();

Printf formatting

string formatted = db.Table<Book>()
    .Select(b => SQLiteFunctions.Printf("Book %d: %s", b.Id, b.Title))
    .First();

Regular expressions

Regexp only works when the SQLite build has a regex extension loaded. The default builds do not include one.

List<Book> rows = db.Table<Book>()
    .Where(b => SQLiteFunctions.Regexp(b.Title, "^[A-Z]"))
    .ToList();

Between

Between(value, low, high) is the same as value >= low && value <= high, but emits SQLite's BETWEEN operator. Both ends are inclusive. To get NOT BETWEEN, wrap the call with !.

List<Book> rows = db.Table<Book>()
    .Where(b => SQLiteFunctions.Between(b.Id, 2, 4))
    .ToList();

List<Book> outside = db.Table<Book>()
    .Where(b => !SQLiteFunctions.Between(b.Id, 2, 4))
    .ToList();

In

In(value, v0, v1, ...) checks whether value matches any of the listed values. The list can be a params argument list or a captured array.

List<Book> picked = db.Table<Book>()
    .Where(b => SQLiteFunctions.In(b.Id, 1, 3, 5))
    .ToList();

int[] wanted = [1, 3, 5];
List<Book> sameThing = db.Table<Book>()
    .Where(b => SQLiteFunctions.In(b.Id, wanted))
    .ToList();

List<Book> excluded = db.Table<Book>()
    .Where(b => !SQLiteFunctions.In(b.Id, 1, 3, 5))
    .ToList();

Coalesce and nullif

Coalesce picks the first non-null value. Nullif(a, b) returns null when a == b, otherwise a.

string title = db.Table<Book>()
    .Select(b => SQLiteFunctions.Coalesce(b.Title, "(untitled)"))
    .First();

string? trimmed = db.Table<Book>()
    .Select(b => SQLiteFunctions.Nullif(b.Title, ""))
    .First();

Type and encoding helpers

Typeof returns the SQLite storage class as a lowercase string ("null", "integer", "real", "text", "blob"). Hex returns the upper-case hex of a blob. Quote returns the SQL literal form of a value. Zeroblob(n) returns a blob of n zero bytes.

string kind = db.Table<Book>().Select(b => SQLiteFunctions.Typeof(b.Price)).First();

byte[] data = [0xDE, 0xAD];
string hex = db.Table<Book>().Select(b => SQLiteFunctions.Hex(data)).First();

string literal = db.Table<Book>().Select(b => SQLiteFunctions.Quote(b.Title)).First();
byte[] padding = db.Table<Book>().Select(b => SQLiteFunctions.Zeroblob(16)).First();

Instr

Instr(haystack, needle) returns the 1-based index of needle inside haystack, or 0 if not found.

List<Book> withLph = db.Table<Book>()
    .Where(b => SQLiteFunctions.Instr(b.Title, "lph") > 0)
    .ToList();

Per-row min and max

Min and Max here are the scalar form: they return the smallest or largest of their arguments for each row.

List<int> floors = db.Table<Book>()
    .Select(b => SQLiteFunctions.Min(b.Id, b.AuthorId))
    .ToList();

Always pass two or more values. Calling SQLiteFunctions.Min(x) or SQLiteFunctions.Max(x) with a single value compiles fine but is wrong. SQLite reads min(x) and max(x) as the aggregate forms, so the surrounding query silently turns into an aggregate query and returns one row instead of one per input row. For aggregates over a column, use LINQ's own Queryable.Min / Queryable.Max instead.

Total aggregate

Total translates to SQLite's total(X) aggregate. It is like Queryable.Sum but always returns a REAL value and returns 0.0 for an empty input set instead of NULL. Pass a Select projection over a grouping enumerable.

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

The SQL is:

SELECT b0.BookAuthorId AS "AuthorId",
       total(b0.BookPrice) AS "Revenue"
FROM "Books" AS b0
GROUP BY b0.BookAuthorId

total shines when the aggregated column has NULL values or when the projected input is empty. sum returns NULL in those cases. total returns 0.0 so callers do not need a special case for empty groups.

Last insert rowid and SQLite version

long newId = db.Table<Book>().Select(b => SQLiteFunctions.LastInsertRowId()).First();
string version = db.Table<Book>().Select(b => SQLiteFunctions.SqliteVersion()).First();

Changes counters

long sinceLastWrite = db.Table<Book>().Select(b => SQLiteFunctions.Changes()).First();
long sinceConnectionOpen = db.Table<Book>().Select(b => SQLiteFunctions.TotalChanges()).First();

Date and time functions

SQLiteDateFunctions exposes SQLite's date and time SQL functions directly. Each accepts a time value plus any number of modifier strings like "+7 days", "start of month", "unixepoch", or "utc". Time values can be ISO 8601 strings, the literal "now", Julian day numbers, or column references.

Method	What it maps to
Date()	date()
Date(when, modifiers)	date(when, modifiers)
Time()	time()
Time(when, modifiers)	time(when, modifiers)
Datetime()	datetime()
Datetime(when, modifiers)	datetime(when, modifiers)
JulianDay()	julianday()
JulianDay(when, modifiers)	julianday(when, modifiers)
Strftime(format, when, modifiers)	strftime(format, when, modifiers)
Timediff(when1, when2)	timediff(when1, when2) (SQLite 3.43+, not available in SQLCipher)

string thisMonth = db.Table<Book>()
    .Select(b => SQLiteDateFunctions.Strftime("%Y-%m", b.CreatedAt))
    .First();

string nextWeek = db.Table<Book>()
    .Select(b => SQLiteDateFunctions.Date(b.CreatedAt, "+7 days"))
    .First();

Calling outside a query

These methods throw InvalidOperationException if you call them outside a LINQ query. They are markers for the translator, not real C# code.