Table of Contents

Class SqliteFunctions

Namespace
NextORM.Core
Assembly
nextorm.core.dll

SQLite-only SQL surface: the core scalars, the JSON1 functions/operators/aggregates, the date helpers and the math-extension functions. Exposed through Sqlite; every member is gated by SqliteFunctions and rejected by providers that do not opt in.

public class SqliteFunctions : CommonFunctions
Inheritance
SqliteFunctions
Inherited Members
Extension Methods

Remarks

The math functions require SQLite to be compiled with SQLITE_ENABLE_MATH_FUNCTIONS, the JSON functions are built in since SQLite 3.38 (opt-in SQLITE_ENABLE_JSON1 before that), jsonb/json_pretty need 3.45/3.46, unhex needs 3.41 and timediff needs 3.43. soundex is not emitted on SQLite: the shipped engine has no such function (the SQLITE_SOUNDEX compile-time option is not enabled), so preparing a call fails with NotSupportedException before anything reaches the server — including on a custom build that does enable the option. The member is kept for source/binary compatibility only.

Methods

FTS3MatchInfo(string?)

FTS3/4 matchinfo(table): the binary match statistics passed to a ranking UDF such as rank. SQL-only.

public byte[]? FTS3MatchInfo(string? table)

Parameters

table string

Returns

byte[]

FTS3MatchInfo(string?, string?)

FTS3/4 matchinfo(table, format): the match statistics under format (pcx, pcnalx, ...). SQL-only.

public byte[]? FTS3MatchInfo(string? table, string? format)

Parameters

table string
format string

Returns

byte[]

FTS3Offsets(string?)

FTS3/4 offsets(table): the <column> <term> <byte offset> <byte length> ... string. SQL-only.

public string? FTS3Offsets(string? table)

Parameters

table string

Returns

string

FTS3Snippet(string?)

FTS3/4 snippet(table): the default snippet with matches wrapped. SQL-only.

public string? FTS3Snippet(string? table)

Parameters

table string

Returns

string

FTS3Snippet(string?, string?)

FTS3/4 snippet(table, startMatch). SQL-only.

public string? FTS3Snippet(string? table, string? startMatch)

Parameters

table string
startMatch string

Returns

string

FTS3Snippet(string?, string?, string?)

FTS3/4 snippet(table, startMatch, endMatch). SQL-only.

public string? FTS3Snippet(string? table, string? startMatch, string? endMatch)

Parameters

table string
startMatch string
endMatch string

Returns

string

FTS3Snippet(string?, string?, string?, string?)

FTS3/4 snippet(table, startMatch, endMatch, ellipses). SQL-only.

public string? FTS3Snippet(string? table, string? startMatch, string? endMatch, string? ellipses)

Parameters

table string
startMatch string
endMatch string
ellipses string

Returns

string

FTS3Snippet(string?, string?, string?, string?, int)

FTS3/4 snippet(table, startMatch, endMatch, ellipses, columnIndex) (-1 selects every column). SQL-only.

public string? FTS3Snippet(string? table, string? startMatch, string? endMatch, string? ellipses, int columnIndex)

Parameters

table string
startMatch string
endMatch string
ellipses string
columnIndex int

Returns

string

FTS3Snippet(string?, string?, string?, string?, int, int)

FTS3/4 snippet(table, startMatch, endMatch, ellipses, columnIndex, tokensNumber). SQL-only.

public string? FTS3Snippet(string? table, string? startMatch, string? endMatch, string? ellipses, int columnIndex, int tokensNumber)

Parameters

table string
startMatch string
endMatch string
ellipses string
columnIndex int
tokensNumber int

Returns

string

FTS5bm25(string?)

FTS5 bm25(table): the BM25 ranking score of each matching row (smaller is a better match). SQL-only.

public double? FTS5bm25(string? table)

Parameters

table string

Returns

double?

FTS5bm25(string?, params double[])

FTS5 bm25(table, weight1, ...): BM25 with a per-column weight. SQL-only.

public double? FTS5bm25(string? table, params double[] weights)

Parameters

table string
weights double[]

Returns

double?

Highlight(string?, int, string?, string?)

FTS5 highlight(table, columnIndex, startMatch, endMatch): the text with every match wrapped. SQL-only.

public string? Highlight(string? table, int columnIndex, string? startMatch, string? endMatch)

Parameters

table string
columnIndex int
startMatch string
endMatch string

Returns

string

Match(object?, string?)

table MATCH query or column MATCH query: the full-text search predicate shared by FTS3, FTS4 and FTS5. tableOrColumn may be a mapped column expression (x.Title) or a trusted constant token naming the FTS table/alias (rendered verbatim, never parameterised); match is the FTS query and stays parameterised.

public bool Match(object? tableOrColumn, string? match)

Parameters

tableOrColumn object
match string

Returns

bool

Remarks

SQL-only; the in-memory provider throws.

MatchTable<TEntity>(string?, string?)

FTS5 table(query) as a FROM source: the table-valued form of an FTS5 search over table using the match query. TEntity is the mapped row shape of the FTS table; project it like any other source. Use through ctx.From(() => ...). table is a trusted constant token emitted verbatim (never built from user input); match stays parameterised. FTS5 only.

public TvfEntityBuilder<TEntity> MatchTable<TEntity>(string? table, string? match)

Parameters

table string
match string

Returns

TvfEntityBuilder<TEntity>

Type Parameters

TEntity

Rank(byte[]?)

FTS3/4 rank(matchinfo(...)): ranks a matching row through the connection-registered rank UDF fed with matchInfo (see FTS3MatchInfo(string?)). SQL-only.

public double? Rank(byte[]? matchInfo)

Parameters

matchInfo byte[]

Returns

double?

Rank(string?)

FTS5 table.rank: the hidden rank column (the default order is by ascending rank). SQL-only.

public double? Rank(string? table)

Parameters

table string

Returns

double?

RowId(string?)

FTS3/4 table.rowid: the hidden rowid column. SQL-only.

public long? RowId(string? table)

Parameters

table string

Returns

long?

Snippet(string?, int, string?, string?, string?, int)

FTS5 snippet(table, columnIndex, startMatch, endMatch, ellipses, tokensNumber): the fragment with matches wrapped. SQL-only.

public string? Snippet(string? table, int columnIndex, string? startMatch, string? endMatch, string? ellipses, int tokensNumber)

Parameters

table string
columnIndex int
startMatch string
endMatch string
ellipses string
tokensNumber int

Returns

string

acos(double?)

Arccosine, in radians. Requires the math extension.

public double? acos(double? x)

Parameters

x double?

Returns

double?

acosh(double?)

Hyperbolic arccosine. Requires the math extension.

public double? acosh(double? x)

Parameters

x double?

Returns

double?

asin(double?)

Arcsine, in radians. Requires the math extension.

public double? asin(double? x)

Parameters

x double?

Returns

double?

asinh(double?)

Hyperbolic arcsine. Requires the math extension.

public double? asinh(double? x)

Parameters

x double?

Returns

double?

atan(double?)

Arctangent, in radians. Requires the math extension.

public double? atan(double? x)

Parameters

x double?

Returns

double?

atan2(double?, double?)

Arctangent of y/x in the correct quadrant. Requires the math extension.

public double? atan2(double? y, double? x)

Parameters

y double?
x double?

Returns

double?

atanh(double?)

Hyperbolic arctangent. Requires the math extension.

public double? atanh(double? x)

Parameters

x double?

Returns

double?

char(params int[])

char(X1, ..., XN): a string built from the given Unicode code points.

public string? @char(params int[] codes)

Parameters

codes int[]

Returns

string

cosh(double?)

Hyperbolic cosine. Requires the math extension.

public double? cosh(double? x)

Parameters

x double?

Returns

double?

format(string?, params object?[])

format(format, ...): the SQLite 3.38+ spelling of printf(string?, params object?[]).

public string? format(string? format, params object?[] args)

Parameters

format string
args object[]

Returns

string

glob(string?, string?)

glob(pattern, value): true when value matches the GLOB pattern.

public bool? glob(string? pattern, string? value)

Parameters

pattern string
value string

Returns

bool?

hex(object?)

hex(X): the upper-case hexadecimal rendering of the value treated as a BLOB.

public string? hex(object? value)

Parameters

value object

Returns

string

if<TResult>(bool, TResult?, TResult?)

if(condition, whenTrue, whenFalse): the SQLite 3.48+ two-argument-per-pair conditional.

public TResult? @if<TResult>(bool condition, TResult? whenTrue, TResult? whenFalse)

Parameters

condition bool
whenTrue TResult
whenFalse TResult

Returns

TResult

Type Parameters

TResult

ifnull<T>(T?, T?)

ifnull(X,Y): the first non-null argument (the two-argument coalesce).

public T? ifnull<T>(T? value, T? other)

Parameters

value T
other T

Returns

T

Type Parameters

T

json(object?)

json(X): validates and minifies the JSON document.

public string? json(object? value)

Parameters

value object

Returns

string

json_array(params object?[])

json_array(value1, ...): a JSON array built from the arguments.

public string? json_array(params object?[] values)

Parameters

values object[]

Returns

string

json_array_insert(string?, params object?[])

json_array_insert(json, path, value, ...): inserts path/value pairs into an array.

public string? json_array_insert(string? json, params object?[] pathValuePairs)

Parameters

json string
pathValuePairs object[]

Returns

string

json_each(string?)

json_each(json) as a FROM source: one row per immediate child (see SqlFunctions.IJsonEachRow).

public TvfEntityBuilder<SqlFunctions.IJsonEachRow> json_each(string? json)

Parameters

json string

Returns

TvfEntityBuilder<SqlFunctions.IJsonEachRow>

json_each(string?, string?)

json_each(json, path) as a FROM source, walking the element selected by path.

public TvfEntityBuilder<SqlFunctions.IJsonEachRow> json_each(string? json, string? path)

Parameters

json string
path string

Returns

TvfEntityBuilder<SqlFunctions.IJsonEachRow>

json_extract<T>(object?, string?)

json_extract(json, path): the value selected by path, read as T (a SQL scalar for a primitive element, or the JSON text for an array/object). Use json_get(object?, string?) / json_get_text(object?, string?) for the operator forms.

public T? json_extract<T>(object? json, string? path)

Parameters

json object
path string

Returns

T

Type Parameters

T

json_get(object?, string?)

json -> path: the subcomponent selected by path as JSON text.

public string? json_get(object? json, string? path)

Parameters

json object
path string

Returns

string

json_get_text(object?, string?)

json ->> path: the subcomponent selected by path as an SQL value.

public string? json_get_text(object? json, string? path)

Parameters

json object
path string

Returns

string

json_group_array<T>(T?)

json_group_array(value) aggregate: a JSON array of the group's values.

public string? json_group_array<T>(T? value)

Parameters

value T

Returns

string

Type Parameters

T

json_group_object<TKey, TValue>(TKey?, TValue?)

json_group_object(label, value) aggregate: a JSON object of the group's label/value pairs.

public string? json_group_object<TKey, TValue>(TKey? key, TValue? value)

Parameters

key TKey
value TValue

Returns

string

Type Parameters

TKey
TValue

json_insert(string?, params object?[])

json_insert(json, path, value, ...): adds the path/value pairs that do not already exist.

public string? json_insert(string? json, params object?[] pathValuePairs)

Parameters

json string
pathValuePairs object[]

Returns

string

json_object(params object?[])

json_object(label1, value1, ...): a JSON object built from the label/value pairs.

public string? json_object(params object?[] keyValuePairs)

Parameters

keyValuePairs object[]

Returns

string

json_patch(object?, object?)

json_patch(target, patch): applies the RFC-7396 merge patch to target.

public string? json_patch(object? target, object? patch)

Parameters

target object
patch object

Returns

string

json_pretty(object?)

json_pretty(json): the document with indentation (SQLite 3.46+).

public string? json_pretty(object? json)

Parameters

json object

Returns

string

json_quote(object?)

json_quote(value): the JSON representation of an SQL value.

public string? json_quote(object? value)

Parameters

value object

Returns

string

json_remove(string?, params string?[])

json_remove(json, path, ...): the document with the selected elements removed.

public string? json_remove(string? json, params string?[] paths)

Parameters

json string
paths string[]

Returns

string

json_replace(string?, params object?[])

json_replace(json, path, value, ...): replaces the path/value pairs that already exist.

public string? json_replace(string? json, params object?[] pathValuePairs)

Parameters

json string
pathValuePairs object[]

Returns

string

json_set(string?, params object?[])

json_set(json, path, value, ...): inserts or replaces the path/value pairs.

public string? json_set(string? json, params object?[] pathValuePairs)

Parameters

json string
pathValuePairs object[]

Returns

string

json_tree(string?)

json_tree(json) as a FROM source: one row per element of the recursive walk (see SqlFunctions.IJsonTreeRow).

public TvfEntityBuilder<SqlFunctions.IJsonTreeRow> json_tree(string? json)

Parameters

json string

Returns

TvfEntityBuilder<SqlFunctions.IJsonTreeRow>

json_tree(string?, string?)

json_tree(json, path) as a FROM source, walking the element selected by path.

public TvfEntityBuilder<SqlFunctions.IJsonTreeRow> json_tree(string? json, string? path)

Parameters

json string
path string

Returns

TvfEntityBuilder<SqlFunctions.IJsonTreeRow>

json_type(object?)

json_type(json): the JSON type of the outermost element.

public string? json_type(object? json)

Parameters

json object

Returns

string

json_type(object?, string?)

json_type(json, path): the JSON type of the element selected by path.

public string? json_type(object? json, string? path)

Parameters

json object
path string

Returns

string

json_valid(object?)

json_valid(json): true when the value is well-formed JSON.

public bool? json_valid(object? json)

Parameters

json object

Returns

bool?

json_valid(object?, int)

json_valid(json, flags): true when the value is well-formed JSON under the bitmask flags.

public bool? json_valid(object? json, int flags)

Parameters

json object
flags int

Returns

bool?

jsonb(object?)

jsonb(X): the JSONB binary representation of the document (SQLite 3.45+).

public byte[]? jsonb(object? value)

Parameters

value object

Returns

byte[]

julianday(DateTime?)

julianday(value): the Julian day number of the timestamp.

public double? julianday(DateTime? value)

Parameters

value DateTime?

Returns

double?

log10(double?)

The base-10 logarithm. Requires the math extension.

public double? log10(double? x)

Parameters

x double?

Returns

double?

log2(double?)

The base-2 logarithm. Requires the math extension.

public double? log2(double? x)

Parameters

x double?

Returns

double?

median<T>(T?)

median: not supported by SQLite. The shipped SQLite build has no median aggregate (it is neither a core function nor part of an enabled extension: ENABLE_PERCENTILE is off), and this library does not emulate it. Referencing this member throws NotSupportedException at translation time; no window rewrite or custom aggregate is produced.

public double? median<T>(T? value)

Parameters

value T

The value whose median would be taken.

Returns

double?

Never returns; the call is refused when the query is translated.

Type Parameters

T

The value expression's type.

mod(double?, double?)

The remainder of x/y, also for non-integer operands. Requires the math extension.

public double? mod(double? x, double? y)

Parameters

x double?
y double?

Returns

double?

octet_length(object?)

octet_length(X): the number of bytes in the encoded text representation of value.

public int? octet_length(object? value)

Parameters

value object

Returns

int?

percentile<T>(double, T?)

percentile: not supported by SQLite. There is no native percentile aggregate and no emulation is provided. Referencing this member throws NotSupportedException at translation time.

public double? percentile<T>(double fraction, T? value)

Parameters

fraction double

The percentile fraction, in [0, 1].

value T

The value whose percentile would be taken.

Returns

double?

Never returns; the call is refused when the query is translated.

Type Parameters

T

The value expression's type.

printf(string?, params object?[])

printf(format, ...): formats the arguments with a C-style % format string.

public string? printf(string? format, params object?[] args)

Parameters

format string
args object[]

Returns

string

quote(object?)

quote(X): the SQL literal of value.

public string? quote(object? value)

Parameters

value object

Returns

string

random()

random(): a pseudo-random 64-bit integer.

public long? random()

Returns

long?

randomblob(int)

randomblob(N): an count-byte BLOB of pseudo-random bytes.

public byte[]? randomblob(int count)

Parameters

count int

Returns

byte[]

sinh(double?)

Hyperbolic sine. Requires the math extension.

public double? sinh(double? x)

Parameters

x double?

Returns

double?

soundex(string?)

soundex(X): not supported on SQLite. The shipped engine does not have the function (it is built without SQLITE_SOUNDEX), so preparing a call throws NotSupportedException with the message The SQLite function 'soundex' is not supported by this provider. before execution, and no SQL is emitted; the refusal is unconditional, also on a custom engine built with SQLITE_SOUNDEX. The member is retained for source/binary compatibility only — keep using soundex(string?) (or a raw SQL fragment) when a soundex code is required.

public string? soundex(string? value)

Parameters

value string

Returns

string

tanh(double?)

Hyperbolic tangent. Requires the math extension.

public double? tanh(double? x)

Parameters

x double?

Returns

double?

timediff(DateTime?, DateTime?)

timediff(a, b): the signed difference between two timestamps as a SQLite interval string (SQLite 3.43+).

public string? timediff(DateTime? a, DateTime? b)

Parameters

a DateTime?
b DateTime?

Returns

string

total<T>(T?)

total(value) aggregate: the sum of the group's non-null values as a floating-point number. Unlike sum it never returns null — an empty or all-null group yields 0.0 — and it always computes in floating point, so large integers do not overflow.

public double total<T>(T? value)

Parameters

value T

Returns

double

Type Parameters

T

typeof(object?)

typeof(X): the storage class of the value (null, integer, real, text, blob).

public string? @typeof(object? value)

Parameters

value object

Returns

string

unhex(string?)

unhex(X): the BLOB decoded from the hexadecimal string value (SQLite 3.41+).

public byte[]? unhex(string? value)

Parameters

value string

Returns

byte[]

unhex(string?, string?)

unhex(X, Y): like unhex(string?), ignoring every character of ignored.

public byte[]? unhex(string? value, string? ignored)

Parameters

value string
ignored string

Returns

byte[]

unicode(string?)

unicode(X): the code point of the first character.

public int? unicode(string? value)

Parameters

value string

Returns

int?

unixepoch(DateTime?)

unixepoch(value): the number of seconds since 1970-01-01 (SQLite 3.38+).

public long? unixepoch(DateTime? value)

Parameters

value DateTime?

Returns

long?
Edit this page