Class SqliteDialect
SQLite dialect: || concatenation, $name parameters, limit/offset paging.
public sealed class SqliteDialect : SqlDialectBase, ISqlDialect- Inheritance
-
SqliteDialect
- Implements
- Inherited Members
- Extension Methods
Fields
Instance
Gets the shared SQLite dialect instance.
public static readonly SqliteDialect InstanceField Value
Properties
AggregateFilterStyle
How the provider attaches a filter to an aggregate. The safe default is None, which rejects the filtering overloads; PostgreSQL and SQLite return AnsiFilter and ClickHouse returns IfCombinator. Declared as a default interface method so that existing external implementations keep compiling.
public override AggregateFilterStyle AggregateFilterStyle { get; }Property Value
ConcatStringOperator
String concatenation operator, e.g. + or ||.
public override string ConcatStringOperator { get; }Property Value
EnforcesScalarSubqueryCardinality
True when a scalar subquery used as an expression raises an error if it returns more than one
row. PostgreSQL, SQL Server, MySQL, MariaDB and ClickHouse do; SQLite silently takes the first
row, so Single/SingleOrDefault cannot rely on the engine there.
public override bool EnforcesScalarSubqueryCardinality { get; }Property Value
Iif
SQLite 3.32+ renders the portable iif as iif(condition, whenTrue, whenFalse).
public override IIifRenderer Iif { get; }Property Value
IndexHints
SQLite supports INDEXED BY and NOT INDEXED.
public override IIndexHintRenderer? IndexHints { get; }Property Value
LobLocatorColumn
The name of the trailing column a dialect appends to a LOB-only projection so that the driver can
stream the payload without buffering it, or null when no locator is needed. The
payload stays ordinal 0 and the locator is rendered last; SQLite uses rowid, which makes
DbDataReader.GetStream return a seekable SqliteBlob. The safe default is
null; declared as a default interface method so existing external implementations
keep compiling.
public override string? LobLocatorColumn { get; }Property Value
NullSafeComparison
SQLite renders both null-safe comparison forms natively (IS / IS NOT).
public override INullSafeComparisonRenderer? NullSafeComparison { get; }Property Value
ScalarFunctions
SQLite renders left/right through substr, ascii through unicode and gates the functions it lacks.
public override IScalarFunctions ScalarFunctions { get; }Property Value
SessionInfoFunctions
SQLite exposes only the library version from the session/information family.
public override ISessionInfoFunctions SessionInfoFunctions { get; }Property Value
SqliteFunctions
SQLite exposes its core scalars, JSON1, date helpers and math-extension functions through SqliteFunctions.
public override ISqliteFunctions SqliteFunctions { get; }Property Value
StringFormats
SQLite renders CLR format specifiers through printf/strftime.
public override IStringFormatFunctions? StringFormats { get; }Property Value
SupportsBatch
SQLite has no DbBatch; the statements are joined with ; into one command.
public override bool SupportsBatch { get; }Property Value
SupportsBulkCopy
SQLite has no native bulk-copy API: every bulk insert takes the portable INSERT ... VALUES path, so a Mode.Native request is refused instead of silently falling back.
public override bool SupportsBulkCopy { get; }Property Value
SupportsCollation
SQLite supports a per-expression COLLATE clause (built-in collations only).
public override bool SupportsCollation { get; }Property Value
SupportsCreateTableAsSelect
SQLite supports CREATE [TEMPORARY] TABLE ... AS SELECT.
public override bool SupportsCreateTableAsSelect { get; }Property Value
SupportsCreateTableAsSelectIfNotExists
SQLite accepts IF NOT EXISTS on CREATE TABLE ... AS SELECT.
public override bool SupportsCreateTableAsSelectIfNotExists { get; }Property Value
SupportsCrossDatabase
True when the provider can qualify a table with a database/schema name that differs from the
current connection's default (SQL Server, MySQL, MariaDB, ClickHouse) or with an attached SQLite
database. The safe default is false, so a WithDatabase override is
rejected by the SQL builder on a dialect that did not opt in. Declared as a default interface
method so that existing external implementations keep compiling.
public override bool SupportsCrossDatabase { get; }Property Value
SupportsCube
True when the provider can render GROUP BY CUBE (...). The safe default is false;
MySQL/MariaDB have no CUBE, so they leave it off.
public override bool SupportsCube { get; }Property Value
SupportsDateArithmetic
True when the provider can render the date arithmetic surface of CommonFunctions
(date_add and end_of_month, also used for the DateTime.AddYears/
AddMonths/AddDays/... methods). The safe default is false; PostgreSQL,
SQL Server and ClickHouse opt in today.
public override bool SupportsDateArithmetic { get; }Property Value
SupportsDateTimeOffsetTranslation
SQLite has no native offset type; a DateTimeOffset is stored and manipulated as UTC text.
public override bool SupportsDateTimeOffsetTranslation { get; }Property Value
SupportsDefaultValues
Defaults to false; every SQL provider except ClickHouse opts into an all-defaults insert.
public override bool SupportsDefaultValues { get; }Property Value
SupportsGreatestLeast
True when the provider can render greatest(...)/least(...). The safe default is
false; PostgreSQL, MySQL/MariaDB, ClickHouse, SQL Server 2022+ and SQLite (through the
scalar max/min) opt in.
public override bool SupportsGreatestLeast { get; }Property Value
SupportsGroupingSets
True when the provider can render an explicit GROUP BY GROUPING SETS (...) list. The safe
default is false; MySQL/MariaDB have no grouping sets, so they leave it off.
public override bool SupportsGroupingSets { get; }Property Value
SupportsInsertIgnore
Defaults to false; SQLite, MySQL, MariaDB and ClickHouse opt into an INSERT ... IGNORE head.
public override bool SupportsInsertIgnore { get; }Property Value
SupportsLastInsertId
Defaults to false; MySQL/MariaDB opt into LAST_INSERT_ID().
public override bool SupportsLastInsertId { get; }Property Value
SupportsNamedWindows
SQLite 3.25+ declares named windows (WINDOW w AS (...)) and references them with OVER w.
public override bool SupportsNamedWindows { get; }Property Value
SupportsNthValue
SQLite 3.25+ supports nth_value(value, n) as a window function.
public override bool SupportsNthValue { get; }Property Value
SupportsNullsOrdering
SQLite renders the native NULLS FIRST/NULLS LAST ordering modifier.
public override bool SupportsNullsOrdering { get; }Property Value
SupportsOnConflict
Defaults to false; PostgreSQL and SQLite 3.24+ opt into ON CONFLICT ... DO UPDATE.
public override bool SupportsOnConflict { get; }Property Value
SupportsOnConflictDoNothing
Defaults to false; PostgreSQL and SQLite opt into a trailing ON CONFLICT DO NOTHING.
public override bool SupportsOnConflictDoNothing { get; }Property Value
SupportsOrdinalComparison
SQLite can express ordinal comparison through the built-in BINARY collation.
public override bool SupportsOrdinalComparison { get; }Property Value
SupportsOrdinalLike
SQLite's LIKE cannot be made byte-wise.
public override bool SupportsOrdinalLike { get; }Property Value
SupportsPercentRankCumeDist
SQLite 3.25+ supports the ANSI percent_rank/cume_dist window functions.
public override bool SupportsPercentRankCumeDist { get; }Property Value
SupportsRangeColumns
True when the provider can store a Range<T> as a pair of scalar columns declared with RangeColumnsAttribute; false by default. See SupportsRangeColumns.
public override bool SupportsRangeColumns { get; }Property Value
SupportsRawSqlSource
Defaults to false; every SQL provider overrides it to true (derived tables).
public override bool SupportsRawSqlSource { get; }Property Value
SupportsRegex
SQLite expresses a match with the registered regexp function (the REGEXP operator) and a registered regexp_replace.
public override bool SupportsRegex { get; }Property Value
SupportsReturning
Defaults to false; PostgreSQL and SQLite opt into the RETURNING clause.
public override bool SupportsReturning { get; }Property Value
SupportsRollup
True when the provider can render GROUP BY ROLLUP (...). The safe default is false;
SQL Server, PostgreSQL, SQLite, MySQL/MariaDB and ClickHouse opt in (the latter group spells it
... WITH ROLLUP).
public override bool SupportsRollup { get; }Property Value
SupportsSavepoints
SQLite supports SAVEPOINT/ROLLBACK TO/RELEASE inside a transaction.
public override bool SupportsSavepoints { get; }Property Value
SupportsSelectWhereMinMax
SQLite 3.25+ renders SelectWhereMax/SelectWhereMin with a window function.
public override bool SupportsSelectWhereMinMax { get; }Property Value
SupportsSequentialAccess
True when the provider supports SequentialAccess, which
lets a LOB column be read incrementally through DbDataReader.GetStream/GetTextReader
instead of being buffered whole. The safe default is false; PostgreSQL, SQL Server and SQLite opt in.
Declared as a default interface method so existing external implementations keep compiling.
public override bool SupportsSequentialAccess { get; }Property Value
SupportsStoredProcedures
SQLite has no stored procedures, so ExecuteProcedure is rejected by the base capability gate.
public override bool SupportsStoredProcedures { get; }Property Value
SupportsStringAgg
True when the provider can render the string_agg(value, delimiter) aggregate. Defaults to
SupportsStringArrayAggregates; SQL Server (2017+) opts in on its own.
public override bool SupportsStringAgg { get; }Property Value
SupportsTableValuedParameters
SQLite emulates a table-valued parameter with a JSON document (json_each/json_extract).
public override bool SupportsTableValuedParameters { get; }Property Value
SupportsUpdateJoin
Defaults to false; PostgreSQL, SQLite, SQL Server, MySQL and MariaDB override it for native multi-table updates.
public override bool SupportsUpdateJoin { get; }Property Value
SupportsWindowFrameExclusion
SQLite 3.28+ supports the frame EXCLUDE CURRENT ROW/GROUP/TIES/NO OTHERS clause.
public override bool SupportsWindowFrameExclusion { get; }Property Value
SupportsWindowFrameGroups
SQLite 3.28+ supports the GROUPS window frame unit.
public override bool SupportsWindowFrameGroups { get; }Property Value
Tuple
SQLite 3.15+ has no server-side row type but supports the flat row constructor (a, b) as an
operand of a row comparison ((a, b) = (c, d)); element access is not offered.
public override ITupleRenderer? Tuple { get; }Property Value
UpdateJoinRequiresFrom
Defaults to false; PostgreSQL and SQLite override it because their multi-table update uses the FROM spelling.
public override bool UpdateJoinRequiresFrom { get; }Property Value
Methods
MakeCoalesce(string, string)
Renders coalesce(v1, v2) over the already-rendered operands.
public override string MakeCoalesce(string v1, string v2)Parameters
Returns
MakeCollate(string, string, KeywordCase)
Renders value COLLATE collation over the already-rendered value.
collation is the provider-native collation name, quoted where the provider
requires it (PostgreSQL). Only called when SupportsCollation is true.
public override string MakeCollate(string value, string collation, KeywordCase keywordCase = KeywordCase.Lower)Parameters
valuestringcollationstringkeywordCaseKeywordCase
Returns
MakeDateAdd(string, string, string)
Renders date addition (value + amount of field) over the already-rendered
arguments; field is a validated date-part name.
public override string MakeDateAdd(string field, string amount, string value)Parameters
Returns
MakeDateDiff(string, string, string)
Renders the difference between two already-rendered timestamps in field units;
field is a validated date-part name.
public override string MakeDateDiff(string field, string start, string end)Parameters
Returns
MakeDateFromParts(string, string, string)
Renders a date built from its already-rendered year/month/
day parts.
public override string MakeDateFromParts(string year, string month, string day)Parameters
Returns
MakeDatePart(string, string)
SQLite extracts date parts through strftime; the result is cast back to an integer so
that it materialises like the C# DateTime.Year/Month/... int properties.
public override string MakeDatePart(string part, string value)Parameters
Returns
MakeDateTimeOffsetNow(bool)
Renders the UTC server clock with fractional seconds and an explicit +00:00 offset. The
value is text and is materialized as an offset-zero DateTimeOffset.
public override string MakeDateTimeOffsetNow(bool utc)Parameters
utcbool
Returns
MakeEndOfMonth(string)
Renders the last day of the month of the already-rendered value.
public override string MakeEndOfMonth(string value)Parameters
valuestring
Returns
MakeGreatest(IReadOnlyList<string>)
Renders greatest(...) over the already-rendered arguments.
public override string MakeGreatest(IReadOnlyList<string> args)Parameters
argsIReadOnlyList<string>
Returns
MakeLeast(IReadOnlyList<string>)
Renders least(...) over the already-rendered arguments.
public override string MakeLeast(IReadOnlyList<string> args)Parameters
argsIReadOnlyList<string>
Returns
MakeMathFunction(string, IReadOnlyList<string>)
Renders a math function call with the given already-rendered arguments.
public override string MakeMathFunction(string name, IReadOnlyList<string> args)Parameters
namestringargsIReadOnlyList<string>
Returns
MakeNow(bool)
Renders the current local or UTC date/time.
public override string MakeNow(bool utc)Parameters
utcbool
Returns
MakeOrdinal(string, bool)
Renders a string operand normalised for ordinal comparison. A provider with a per-expression
binary collation emits value COLLATE <binary>; a provider whose native String
order is already ordinal (ClickHouse) returns the value unchanged. ignoreCase
additionally case-folds the operand. Only called when SupportsOrdinalComparison is
true.
public override string MakeOrdinal(string value, bool ignoreCase)Parameters
Returns
MakePage(Paging, StringBuilder, KeywordCase)
Appends the provider's paging clause (LIMIT/OFFSET, OFFSET/FETCH or TOP) for paging to sqlBuilder.
public override void MakePage(Paging paging, StringBuilder sqlBuilder, KeywordCase keywordCase = KeywordCase.Lower)Parameters
pagingPagingsqlBuilderStringBuilderkeywordCaseKeywordCase
MakeParam(string)
Renders a parameter placeholder, e.g. @name or $name.
public override string MakeParam(string name)Parameters
namestring
Returns
MakeQualifiedTableName(string?, string?, string?, string)
Renders the SQLite single-qualifier form database.table (an attached database). A schema
and a database name the same level here, so the two overrides are mutually exclusive.
public override string MakeQualifiedTableName(string? server, string? database, string? schema, string table)Parameters
Returns
MakeRegexMatch(string, string, bool)
Renders a regular-expression match predicate over the already-rendered value.
pattern is the raw pattern text (the dialect renders and escapes the string
literal itself) and ignoreCase selects
IgnoreCase. Reached only when
SupportsRegex is true.
public override string MakeRegexMatch(string value, string pattern, bool ignoreCase)Parameters
Returns
MakeRegexReplace(string, string, string, bool)
Renders a regular-expression replace over the already-rendered value,
replacing every match as Replace(string, string, string)
does. pattern and replacement are raw text (the dialect renders
and escapes the literals). Reached only when SupportsRegex is true.
A replacement backreference uses the provider's group syntax (C# $1, SQL \1); it is
not rewritten, so a replacement with a group reference is not portable as written.
public override string MakeRegexReplace(string value, string pattern, string replacement, bool ignoreCase)Parameters
Returns
MakeRepeat(string, string)
Renders value repeated count times. The primitive behind
the new string(char, n) translation and MakePad(string, string, string, bool).
public override string MakeRepeat(string value, string count)Parameters
Returns
MakeStringAgg(string, string)
Renders the string_agg(value, delimiter) aggregate over the already-rendered arguments.
public override string MakeStringAgg(string value, string delimiter)Parameters
Returns
MakeStringPosition(string, string)
Renders the one-based position of substring in value, or
0 when it is absent (charindex, instr, strpos, position).
protected override string MakeStringPosition(string value, string substring)Parameters
Returns
RenderOrderBy(string, OrderDirection, NullsPosition)
Renders one logical ORDER BY item with an explicit NULL placement. The default emulates the
placement with a CASE rank key whose prefix is always ascending; providers that opt into
SupportsNullsOrdering override it with the native form.
public override string RenderOrderBy(string expressionSql, OrderDirection direction, NullsPosition nullsPosition)Parameters
expressionSqlstringThe already-qualified, already-quoted key SQL.
directionOrderDirectionThe requested key direction.
nullsPositionNullsPositionThe explicit NULL placement.
Returns
- string
The rendered ordering item.
SupportsDatePart(string)
SQLite additionally accepts the ISO week, the normalised weekdays and epoch.
public override bool SupportsDatePart(string part)Parameters
partstring
Returns
SupportsTableFunction(string)
SQLite's built-in JSON table-valued functions json_each/json_tree and the FTS5 table-valued search placeholder.
public override bool SupportsTableFunction(string name)Parameters
namestring
Returns
WrapTableFunction(string, string, IReadOnlyList<string>)
Wraps the rendered table-function call with its structured, already-rendered arguments, or returns it unchanged. The default forwards to WrapTableFunction(string, string); SQLite overrides it so the FTS5 table token is quoted as one identifier without reparsing the serialized call text.
public override string WrapTableFunction(string name, string call, IReadOnlyList<string> arguments)Parameters
namestringcallstringargumentsIReadOnlyList<string>