Table of Contents

Class SqlServerFunctions

Namespace
NextORM.Core
Assembly
nextorm.core.dll

Text-JSON surface (SQL Server and MySQL/MariaDB), the SQL Server-only choose conditional function (the portable iif lives on CommonFunctions), the SQL Server T-SQL-only scalar library (the string, trigonometric, date, binary/system and SQL/JSON functions gated per name by ISqlServerFunctions), the SQL Server postfix XML data-type methods (xml_value/xml_query/xml_exist and the xml_nodes rowset) and the SQL Server table functions. Exposed through SqlServer; every member is gated by a capability flag and rejected by providers that do not opt in.

public class SqlServerFunctions : CommonFunctions
Inheritance
SqlServerFunctions
Inherited Members
Extension Methods

Methods

acos(double?)

acos(value): the arc cosine, in radians. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public double? acos(double? value)

Parameters

value double?

Returns

double?

any_value<T>(T?)

ANY_VALUE(value) is refused: SQL Server build 17.0.5005.3 rejected the function with Msg 195, in both the aggregate and the OVER () forms, at compatibility levels 160 and 170 and with PREVIEW_FEATURES = ON, so no supported SQL Server execution contract is established. The result is a callable placeholder; every invocation throws NotSupportedException with the stable diagnostic. This is not a replacement for any_agg<T>(T?), whose dispatch on the providers that enable it is unchanged.

public T? any_value<T>(T? value)

Parameters

value T

The value whose arbitrary row would be picked.

Returns

T

Type Parameters

T

The value type.

app_name()

The application name the client reported on connect (APP_NAME()); SQL NULL when it did not. The value is client-supplied and untrusted. Requires a provider that exposes the SQL Server identity surface (see SupportsSessionIdentity; SQL Server).

public string? app_name()

Returns

string

approx_count_distinct<T>(T?)

approx_count_distinct(value): the approximate number of distinct non-null value rows in the aggregate group, returned as bigint. An empty group and an all-NULL group both yield 0 (not SQL NULL). Rendered in an aggregate context only; the engine is not a windowing function here, so an OVER clause is rejected by the server (Msg 4113, observed on 17.0.5005.3). A NULL operand is rendered as a typed NULL (approx_count_distinct(cast(null as int))), because a bare NULL operand is rejected with Msg 8117. Requires SQL Server 2019 (major version 15) and the version-bound SupportsApproxCountDistinctAggregate gate; a lower or unversioned declaration is refused before SQL.

public long approx_count_distinct<T>(T? value)

Parameters

value T

The value whose distinct non-null occurrences are counted.

Returns

long

The approximate distinct count as bigint; 0 for an empty or all-NULL group.

Type Parameters

T

The value type.

approx_percentile_cont<T>(double, T?)

approx_percentile_cont(fraction) within group (order by value): the approximate continuous (interpolated) percentile of the non-null value rows, returned as float(53). The engine accepts an integer sort expression even though the result is float; an empty group and an all-NULL group yield SQL NULL, and the endpoints 0 and 1 are exact. The fraction must be constant or captured and finite in [0, 1]; a row-dependent, NaN/infinite or out-of-range fraction is refused before SQL. The sort expression must be a column, a parameter or a computed expression: SQL Server rejects a NULL or literal constant ORDER BY inside WITHIN GROUP (Msg 5309), so such a call is refused before SQL too. The aggregate is ordered-set only: there is no OVER overload, and the engine's own form is WITHIN GROUP without OVER (Msg 4113 for an OVER shape). Requires SQL Server 2022 (major version 16) and the name-specific SupportsApproxPercentileCont gate.

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

Parameters

fraction double

The percentile fraction, in [0, 1].

value T

The value to take the percentile of.

Returns

double?

The approximate interpolated percentile, or SQL NULL when the group has no non-null value.

Type Parameters

T

The sort-input type.

approx_percentile_disc<T>(double, T?)

approx_percentile_disc(fraction) within group (order by value): the approximate discrete percentile of the non-null value rows, returned as the sort input type (the engine's APPROX_PERCENTILE_DISC result type tracks the input, e.g. int for an integer sort expression; a decimal sort expression is rejected by the engine, Msg 402). An empty group and an all-NULL group yield SQL NULL, and the endpoints 0 and 1 are exact. The fraction must be constant or captured and finite in [0, 1]; a row-dependent, NaN/infinite or out-of-range fraction is refused before SQL. The sort expression must be a column, a parameter or a computed expression (a NULL or literal constant ORDER BY is Msg 5309 and is refused before SQL). Ordered-set only: there is no OVER overload (the engine raises Msg 4113 for an OVER shape). Requires SQL Server 2022 (major version 16) and the name-specific SupportsApproxPercentileDisc gate.

public T? approx_percentile_disc<T>(double fraction, T? value) where T : struct

Parameters

fraction double

The percentile fraction, in [0, 1].

value T?

The value to take the percentile of.

Returns

T?

The approximate discrete percentile, or SQL NULL when the group has no non-null value.

Type Parameters

T

The sort-input type; a value type, because the discrete result is an input value.

asin(double?)

asin(value): the arc sine, in radians. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public double? asin(double? value)

Parameters

value double?

Returns

double?

atan(double?)

atan(value): the arc tangent, in radians. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public double? atan(double? value)

Parameters

value double?

Returns

double?

atn2(double?, double?)

atn2(y, x): the arc tangent of y / x (the T-SQL spelling of two-argument arc tangent). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

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

Parameters

y double?
x double?

Returns

double?

binary_checksum(params object?[])

binary_checksum(value, ...): a checksum computed over the binary representations of the listed values. At least one value is required. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? binary_checksum(params object?[] values)

Parameters

values object[]

Returns

int?

bit_count(byte?)

bit_count(value) for a byte operand: the number of one bits in the value. The T-SQL result is bigint, so the CLR result is long?; the rendered call types the operand as tinyint and a NULL operand yields cast(null as bigint). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? bit_count(byte? value)

Parameters

value byte?

The tinyint operand.

Returns

long?

The number of set bits, or SQL NULL when the operand is NULL.

bit_count(short?)

bit_count(value) for a short operand. The T-SQL result is bigint, so the CLR result is long?; the rendered call types the operand as smallint and a NULL operand yields cast(null as bigint). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? bit_count(short? value)

Parameters

value short?

The smallint operand.

Returns

long?

The number of set bits, or SQL NULL when the operand is NULL.

bit_count(int?)

bit_count(value) for an int operand. The T-SQL result is bigint, so the CLR result is long?; the rendered call types the operand as int and a NULL operand yields cast(null as bigint). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? bit_count(int? value)

Parameters

value int?

The int operand.

Returns

long?

The number of set bits, or SQL NULL when the operand is NULL.

bit_count(long?)

bit_count(value) for a long operand. The T-SQL result is bigint, so the CLR result is long?; the rendered call types the operand as bigint and a NULL operand yields cast(null as bigint). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? bit_count(long? value)

Parameters

value long?

The bigint operand.

Returns

long?

The number of set bits, or SQL NULL when the operand is NULL.

checksum(params object?[])

checksum(value, ...): a checksum computed over the listed values. At least one value is required; the wildcard checksum(*) form is not exposed. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? checksum(params object?[] values)

Parameters

values object[]

Returns

int?

checksum_agg(int?)

checksum_agg(value): the checksum of the non-null value rows in the aggregate group, returned as int. SQL NULL rows are skipped and the result is order-insensitive but not a stable identity (identical values cancel: two 5 rows and one 5 row checksum identically); an empty group and an all-NULL group yield SQL NULL. Rendered as checksum_agg(value); unlike the approximate percentiles the engine does allow an OVER clause (observed on 17.0.5005.3), but this surface offers the aggregate form only. A NULL operand is rendered as a typed NULL (checksum_agg(cast(null as int))), because a bare NULL operand is rejected with Msg 8117. SQL Server has the function without a version floor, so the gate is a plain provider opt-in (SupportsChecksumAggregate).

public int? checksum_agg(int? value)

Parameters

value int?

The value to checksum across the group.

Returns

int?

The checksum as int, or SQL NULL when the group has no non-null value.

choose<TResult>(int, params TResult?[])

choose(index, value, ...): the 1-based index-th value (NULL when out of range). Requires a provider that supports it (see SupportsChoose; SQL Server).

public TResult? choose<TResult>(int index, params TResult?[] values)

Parameters

index int
values TResult[]

Returns

TResult

Type Parameters

TResult

col_length(string?, string?)

col_length(table, column): the defined length, in bytes, of column (NULL when the object or column does not exist). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? col_length(string? table, string? column)

Parameters

table string
column string

Returns

int?

col_name(int?, int?)

col_name(table_id, column_id): the name of the column with the given ids (NULL when they do not identify a column). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? col_name(int? tableId, int? columnId)

Parameters

tableId int?
columnId int?

Returns

string

compress(byte[]?)

compress(value): GZIP-compresses a binary value. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public byte[]? compress(byte[]? value)

Parameters

value byte[]

Returns

byte[]

compress(string?)

compress(value): GZIP-compresses a string into varbinary(max). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public byte[]? compress(string? value)

Parameters

value string

Returns

byte[]

containstable<TKey>(string, string, string)

CONTAINSTABLE(table, column, search) as a FROM source (SQL Server full-text search with ranking). Join it back to the full-text-indexed table on Key and project Rank: ctx.From<IDocument>().Join(ctx.From(() => SqlFunctions.SqlServer.containstable<int>("documents", "title", search)), (d, k) => d.Id == k.Key).OrderByDescending(p => p.Item2.Rank). table and column are emitted verbatim as identifiers (the table name or its alias exactly as it appears in the query); only pass trusted values.

public TvfEntityBuilder<SqlFunctions.IKeyRankRow<TKey>> containstable<TKey>(string table, string column, string search)

Parameters

table string
column string
search string

Returns

TvfEntityBuilder<SqlFunctions.IKeyRankRow<TKey>>

Type Parameters

TKey

current_request_id()

current_request_id(): the id of the current request on the current connection (NULL outside a request scope). Requires a provider that supports it (see SqlServerFunctions; SQL Server 2016+).

public short? current_request_id()

Returns

short?

current_timezone()

current_timezone(): the name of the current time zone as configured on the server. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2016+).

public string? current_timezone()

Returns

string

current_timezone_id()

current_timezone_id(): the id of the current time zone. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2016+).

public int? current_timezone_id()

Returns

int?

current_transaction_id()

current_transaction_id(): the id of the current transaction (NULL outside a transaction scope). Requires a provider that supports it (see SqlServerFunctions; SQL Server 2016+).

public long? current_transaction_id()

Returns

long?

date_bucket(string?, int, DateTime?)

date_bucket(datepart, width, date): the start of the width-wide bucket of datepart units that contains date, using the default origin (1900-01-01). datepart must be a constant string. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2022+).

public DateTime? date_bucket(string? datepart, int width, DateTime? date)

Parameters

datepart string
width int
date DateTime?

Returns

DateTime?

date_bucket(string?, int, DateTime?, DateTime?)

date_bucket(datepart, width, date, origin): as date_bucket(string?, int, DateTime?) measured from origin. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2022+).

public DateTime? date_bucket(string? datepart, int width, DateTime? date, DateTime? origin)

Parameters

datepart string
width int
date DateTime?
origin DateTime?

Returns

DateTime?

datename(string?, DateTime?)

datename(datepart, date): the name of the requested datepart (for example the month or weekday name). datepart must be a constant string. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? datename(string? datepart, DateTime? date)

Parameters

datepart string
date DateTime?

Returns

string

datetime2fromparts(int?, int?, int?, int?, int?, int?, int?, int?)

datetime2fromparts(year, month, day, hour, minute, seconds, fractions, precision): builds a datetime2. precision must be a constant between 0 and 7. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTime? datetime2fromparts(int? year, int? month, int? day, int? hour, int? minute, int? seconds, int? fractions, int? precision)

Parameters

year int?
month int?
day int?
hour int?
minute int?
seconds int?
fractions int?
precision int?

Returns

DateTime?

datetimefromparts(int?, int?, int?, int?, int?, int?, int?)

datetimefromparts(year, month, day, hour, minute, seconds, milliseconds): builds a datetime. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTime? datetimefromparts(int? year, int? month, int? day, int? hour, int? minute, int? seconds, int? milliseconds)

Parameters

year int?
month int?
day int?
hour int?
minute int?
seconds int?
milliseconds int?

Returns

DateTime?

datetimeoffsetfromparts(int?, int?, int?, int?, int?, int?, int?, int?, int?, int?)

datetimeoffsetfromparts(year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision): builds a datetimeoffset. precision must be a constant between 0 and 7. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTimeOffset? datetimeoffsetfromparts(int? year, int? month, int? day, int? hour, int? minute, int? seconds, int? fractions, int? hourOffset, int? minuteOffset, int? precision)

Parameters

year int?
month int?
day int?
hour int?
minute int?
seconds int?
fractions int?
hourOffset int?
minuteOffset int?
precision int?

Returns

DateTimeOffset?

db_id()

db_id(): the id of the current database. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? db_id()

Returns

int?

db_id(string?)

db_id(database_name): the id of the named database. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? db_id(string? database)

Parameters

database string

Returns

int?

db_name()

db_name(): the name of the current database. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? db_name()

Returns

string

db_name(int?)

db_name(database_id): the name of the database with the given id. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? db_name(int? databaseId)

Parameters

databaseId int?

Returns

string

decompress(byte[]?)

decompress(value): decompresses a value produced by compress (SQL Server returns null for an invalid or truncated input). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public byte[]? decompress(byte[]? value)

Parameters

value byte[]

Returns

byte[]

difference(string?, string?)

difference(first, second): how similar the Soundex codes of the two strings are, as a value from 0 (not similar) to 4 (very similar). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? difference(string? first, string? second)

Parameters

first string
second string

Returns

int?

edit_distance(string?, string?)

edit_distance(input, pattern): the Levenshtein edit distance between the two strings, or SQL NULL when either operand is NULL. Rendered as edit_distance(input, pattern). Requires SQL Server 2025 (major version 17) and the version-bound SupportsSqlServer2025Functions gate. The major-17 gate does not promise the runtime prerequisite: the function additionally needs database compatibility level 170, PREVIEW_FEATURES = ON and a Windows collation (a SQL_* collation raises Msg 9847); those are observed environment facts, documented and asserted live, not modelled by the version flag.

public int? edit_distance(string? input, string? pattern)

Parameters

input string

The first string.

pattern string

The second string.

Returns

int?

The edit distance as int, or SQL NULL when either operand is NULL.

edit_distance(string?, string?, int?)

edit_distance(input, pattern, maximum_distance): the bounded form. The native result is the true distance when it is at most maximum_distance, and maximum_distance + 1 otherwise (observed: 0 for equal strings, 1 for a positive distance capped at 0, 2 capped at 1); a negative maximum is treated as uncapped by the engine. Omitting the argument and passing a NULL maximum are distinct: a supplied NULL yields SQL NULL. The argument is passed through as an int; a wider CLR operand is refused before SQL rather than truncated.

public int? edit_distance(string? input, string? pattern, int? maximum_distance)

Parameters

input string

The first string.

pattern string

The second string.

maximum_distance int?

The native distance cap, or SQL NULL.

Returns

int?

The bounded distance as int, or SQL NULL when an operand is NULL.

edit_distance_similarity(string?, string?)

edit_distance_similarity(input, pattern): the edit-distance similarity as an int in 0..100 (57 for kitten/sitting on the tested build), or SQL NULL when either operand is NULL. Requires SQL Server 2025 (major version 17) and the same runtime prerequisites as edit_distance(string?, string?) (compatibility 170, PREVIEW_FEATURES = ON, Windows collation).

public int? edit_distance_similarity(string? input, string? pattern)

Parameters

input string

The first string.

pattern string

The second string.

Returns

int?

The similarity as int, or SQL NULL when either operand is NULL.

error()

@@ERROR is refused: it describes the previous statement and is statement-scoped, so a per-row query projection cannot expose that value faithfully. The result is a callable placeholder; every invocation throws NotSupportedException with the stable diagnostic.

public int error()

Returns

int

error_line()

ERROR_LINE() is refused for the same CATCH-block scope reason as error_number(); the result is a callable placeholder and every invocation throws NotSupportedException with the stable diagnostic.

public int? error_line()

Returns

int?

error_message()

ERROR_MESSAGE() is refused for the same CATCH-block scope reason as error_number(); the result is a callable placeholder and every invocation throws NotSupportedException with the stable diagnostic.

public string? error_message()

Returns

string

error_number()

ERROR_NUMBER() is refused: it requires a SQL Server CATCH-block scope and returns NULL outside CATCH, which nextorm does not establish. The result is a callable placeholder; every invocation throws NotSupportedException with the stable diagnostic.

public int? error_number()

Returns

int?

error_procedure()

ERROR_PROCEDURE() is refused for the same CATCH-block scope reason as error_number(); the result is a callable placeholder and every invocation throws NotSupportedException with the stable diagnostic.

public string? error_procedure()

Returns

string

error_severity()

ERROR_SEVERITY() is refused for the same CATCH-block scope reason as error_number(); the result is a callable placeholder and every invocation throws NotSupportedException with the stable diagnostic.

public int? error_severity()

Returns

int?

error_state()

ERROR_STATE() is refused for the same CATCH-block scope reason as error_number(); the result is a callable placeholder and every invocation throws NotSupportedException with the stable diagnostic.

public int? error_state()

Returns

int?

file_id(string?)

file_id(file_name): the id of the database file with the given logical name. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? file_id(string? fileName)

Parameters

fileName string

Returns

int?

file_idex(string?)

file_idex(file_name): as file_id(string?), but without the bounds of the current database. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? file_idex(string? fileName)

Parameters

fileName string

Returns

int?

file_name(int?)

file_name(file_id): the logical name of the database file with the given id. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? file_name(int? fileId)

Parameters

fileId int?

Returns

string

filegroup_id(string?)

filegroup_id(filegroup_name): the id of the named filegroup. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? filegroup_id(string? filegroupName)

Parameters

filegroupName string

Returns

int?

filegroup_name(int?)

filegroup_name(filegroup_id): the name of the filegroup with the given id. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? filegroup_name(int? filegroupId)

Parameters

filegroupId int?

Returns

string

format(object?, string?)

format(value, format): formats value with a CLR standard or custom format string (the .NET formatting rules). Distinct from the CLR Format(string, object) translation; this is the native T-SQL FORMAT. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? format(object? value, string? format)

Parameters

value object
format string

Returns

string

format(object?, string?, string?)

format(value, format, culture): as format(object?, string?) with an explicit culture (for example "en-US"). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? format(object? value, string? format, string? culture)

Parameters

value object
format string
culture string

Returns

string

formatmessage(int?, params object?[])

formatmessage(msg_number, param, ...): builds the message stored under messageId in sys.messages with up to 20 arguments. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? formatmessage(int? messageId, params object?[] values)

Parameters

messageId int?
values object[]

Returns

string

formatmessage(string?, params object?[])

formatmessage(msg_string, param, ...): builds a message from a format string and up to 20 arguments. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? formatmessage(string? message, params object?[] values)

Parameters

message string
values object[]

Returns

string

freetexttable<TKey>(string, string, string)

FREETEXTTABLE(table, column, search) as a FROM source; the natural-language counterpart of containstable<TKey>(string, string, string) (same KEY/RANK columns).

public TvfEntityBuilder<SqlFunctions.IKeyRankRow<TKey>> freetexttable<TKey>(string table, string column, string search)

Parameters

table string
column string
search string

Returns

TvfEntityBuilder<SqlFunctions.IKeyRankRow<TKey>>

Type Parameters

TKey

get_bit(byte?, int?)

get_bit(value, offset) for a byte operand: the bit at the zero-based offset. The T-SQL result is bit, read as bool?; the rendered call types the operand as tinyint, the offset as int, and a NULL argument yields cast(null as bit). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public bool? get_bit(byte? value, int? offset)

Parameters

value byte?

The tinyint operand.

offset int?

The zero-based bit position.

Returns

bool?

The bit as a boolean, or SQL NULL when either argument is NULL.

get_bit(short?, int?)

get_bit(value, offset) for a short operand. The T-SQL result is bit, read as bool?; the rendered call types the operand as smallint, the offset as int, and a NULL argument yields cast(null as bit). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public bool? get_bit(short? value, int? offset)

Parameters

value short?

The smallint operand.

offset int?

The zero-based bit position.

Returns

bool?

The bit as a boolean, or SQL NULL when either argument is NULL.

get_bit(int?, int?)

get_bit(value, offset) for an int operand. The T-SQL result is bit, read as bool?; the rendered call types the operand as int, the offset as int, and a NULL argument yields cast(null as bit). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public bool? get_bit(int? value, int? offset)

Parameters

value int?

The int operand.

offset int?

The zero-based bit position.

Returns

bool?

The bit as a boolean, or SQL NULL when either argument is NULL.

get_bit(long?, int?)

get_bit(value, offset) for a long operand. The T-SQL result is bit, read as bool?; the rendered call types the operand as bigint, the offset as int, and a NULL argument yields cast(null as bit). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public bool? get_bit(long? value, int? offset)

Parameters

value long?

The bigint operand.

offset int?

The zero-based bit position.

Returns

bool?

The bit as a boolean, or SQL NULL when either argument is NULL.

getansinull()

getansinull(): whether the current database allows nulls by default. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? getansinull()

Returns

int?

getansinull(string?)

getansinull(database): whether the named database allows nulls by default. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? getansinull(string? database)

Parameters

database string

Returns

int?

grouping_id(params object?[])

grouping_id(expressions): the bit mask identifying which of the listed expressions are aggregated away by the current grouping set, returned as int (0 under a plain GROUP BY; the ROLLUP/GROUPING SETS subtotal bits otherwise). Every argument must be a GROUP BY expression: the server reports Msg 8161 without a GROUP BY and for an argument that is not grouped, and this library enforces the same obligation before SQL is executed (a missing grouping context or a mismatched argument is refused with a BuildSqlCommandException and zero statements run). Allowed only in the SELECT list, HAVING and ORDER BY of a grouped command. SQL Server has the function without a version floor, so the gate is a plain provider opt-in (SupportsGroupingIdAggregate).

public int grouping_id(params object?[] expressions)

Parameters

expressions object[]

The grouping expressions whose subtotal bits are reported.

Returns

int

The grouping bit mask as int.

hashbytes(string?, byte[]?)

hashbytes(algorithm, data): the hash of data using the named algorithm (MD5, SHA1, SHA2_256, SHA2_512, SHA3_256, …; the algorithm must be a constant string). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public byte[]? hashbytes(string? algorithm, byte[]? data)

Parameters

algorithm string
data byte[]

Returns

byte[]

host_id()

The identifier of the client host (HOST_ID()) as the server recorded it; SQL NULL when the client did not report one. Requires a provider that exposes the SQL Server identity surface (see SupportsSessionIdentity; SQL Server).

public string? host_id()

Returns

string

host_name()

The name of the client host (HOST_NAME()) as the client reported it; SQL NULL when it did not. The value is client-supplied and therefore not a trusted identity — it is the client, not the server (server_name(); on ClickHouse the server host is host_name()). Requires a provider that exposes the SQL Server identity surface (see SupportsSessionIdentity; SQL Server).

public string? host_name()

Returns

string

ident_current(string?)

ident_current(table_or_view): the last identity value generated by any session for the named table or view. It is not a last-insert API for the current session and is racy under concurrent inserts. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public decimal? ident_current(string? tableOrView)

Parameters

tableOrView string

Returns

decimal?

ident_incr(string?)

ident_incr(table_or_view): the increment of the identity column of the table or view (NULL on error). T-SQL returns numeric(38,0), whose ADO.NET mapping is decimal (not int), so an increment above the 32-bit range does not overflow on materialisation. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public decimal? ident_incr(string? table)

Parameters

table string

Returns

decimal?

ident_seed(string?)

ident_seed(table_or_view): the seed of the identity column of the table or view (NULL on error). T-SQL returns numeric(38,0), whose ADO.NET mapping is decimal (not int), so a seed above the 32-bit range does not overflow on materialisation. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public decimal? ident_seed(string? table)

Parameters

table string

Returns

decimal?

index_col(string?, int?, int?)

index_col(table, index_id, key_id): the name of the indexed column (NULL when the index or key does not exist). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? index_col(string? table, int? indexId, int? keyId)

Parameters

table string
indexId int?
keyId int?

Returns

string

isdate(object?)

isdate(value): 1 when value is a valid datetime, otherwise 0 (the T-SQL form returns int, not bit). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? isdate(object? value)

Parameters

value object

Returns

int?

isjson(string?)

isjson(value): true when the text is valid JSON. Requires a provider that supports it (see SupportsTextJson).

public bool isjson(string? value)

Parameters

value string

Returns

bool

isnumeric(object?)

isnumeric(value): 1 when value is a valid numeric type, otherwise 0 (the T-SQL form returns int, not bit). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? isnumeric(object? value)

Parameters

value object

Returns

int?

jaro_winkler_distance(string?, string?)

jaro_winkler_distance(input, pattern): the Jaro-Winkler distance as float(53) (0.0388888888888862 for martha/marhta on the tested build), or SQL NULL when either operand is NULL. Requires SQL Server 2025 (major version 17) and the same runtime prerequisites as edit_distance(string?, string?).

public double? jaro_winkler_distance(string? input, string? pattern)

Parameters

input string

The first string.

pattern string

The second string.

Returns

double?

The distance as double, or SQL NULL when either operand is NULL.

jaro_winkler_similarity(string?, string?)

jaro_winkler_similarity(input, pattern): the Jaro-Winkler similarity as an int in 0..100 (96 for martha/marhta on the tested build), or SQL NULL when either operand is NULL. Requires SQL Server 2025 (major version 17) and the same runtime prerequisites as edit_distance(string?, string?).

public int? jaro_winkler_similarity(string? input, string? pattern)

Parameters

input string

The first string.

pattern string

The second string.

Returns

int?

The similarity as int, or SQL NULL when either operand is NULL.

json_array(params object?[])

json_array(value, ...): constructs a JSON array text from the arguments. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2022+).

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

Parameters

values object[]

Returns

string

json_arrayagg<T>(T?)

json_arrayagg(value): aggregates the values of a group into a JSON array. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2025+).

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

Parameters

value T

Returns

string

Type Parameters

T

json_contains(string?, string?, string?)

json_contains(json, searchValue, path): true when json contains searchValue at path. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2025+). Distinct from the PostgreSQL/MySQL containment operator, which is expressed as a JSON predicate.

public bool json_contains(string? json, string? searchValue, string? path)

Parameters

json string
searchValue string
path string

Returns

bool

json_modify(string?, string?, string?)

json_modify(json, path, value): a copy of json with path updated. Requires a provider that supports it (see SupportsTextJson).

public string? json_modify(string? json, string? path, string? value)

Parameters

json string
path string
value string

Returns

string

json_object(params object?[])

json_object(key, value, ...): constructs a JSON object text from the alternating key and value arguments (keys must be string literals). Requires a provider that supports it (see SqlServerFunctions; SQL Server 2022+).

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

Parameters

keyValuePairs object[]

Returns

string

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

json_objectagg(key, value): aggregates the key/value pairs of a group into a JSON object. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2025+).

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

Parameters

key TKey
value TValue

Returns

string

Type Parameters

TKey
TValue

json_path_exists(string?, string?)

json_path_exists(json, path): true when path selects a value in json. Requires a provider that supports it (see SqlServerFunctions; SQL Server 2022+).

public bool json_path_exists(string? json, string? path)

Parameters

json string
path string

Returns

bool

json_query(string?, string?)

json_query(json, path): the JSON fragment (object/array) at path. Requires a provider that supports it (see SupportsTextJson).

public string? json_query(string? json, string? path)

Parameters

json string
path string

Returns

string

json_value(string?, string?)

json_value(json, path): the scalar value at path. Here JSON is stored in a plain text column rather than a native JSON type. Requires a provider that supports it (see SupportsTextJson).

public string? json_value(string? json, string? path)

Parameters

json string
path string

Returns

string

left_shift(byte?, int?)

left_shift(value, shift) for a byte operand. Rendered with the operand typed as tinyint and the shift amount as bigint, behind a typed-NULL guard (case when … then cast(null as tinyint) else … end) because a bare left_shift(null, …) raises Msg 8116. Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public byte? left_shift(byte? value, int? shift)

Parameters

value byte?

The tinyint operand.

shift int?

The number of bits to shift.

Returns

byte?

The shifted byte, or SQL NULL when either argument is NULL.

left_shift(short?, int?)

left_shift(value, shift) for a short operand. Rendered with the operand typed as smallint and the shift amount as bigint, behind the same typed-NULL guard (a NULL yields cast(null as smallint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public short? left_shift(short? value, int? shift)

Parameters

value short?

The smallint operand.

shift int?

The number of bits to shift.

Returns

short?

The shifted short, or SQL NULL when either argument is NULL.

left_shift(int?, int?)

left_shift(value, shift): the bitwise left shift of value by shift bits. Rendered with typed arguments and a typed-NULL guard (case when value is null or shift is null then cast(null as int) else left_shift(cast(value as int), cast(shift as bigint)) end) because a bare left_shift(null, …) raises Msg 8116. Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate; a lower or unversioned declaration is refused before SQL.

public int? left_shift(int? value, int? shift)

Parameters

value int?

The int operand.

shift int?

The number of bits to shift; SQL NULL when either operand is NULL.

Returns

int?

The shifted int, or SQL NULL when either operand is NULL.

Remarks

Overload resolution. The bit family is one overload set per operand type (byte?/short?/int?/long?), so an untyped constant binds to the smallest operand overload that can hold it: left_shift(1, 2) selects byte? and renders tinyint, and left_shift(-8, 1) selects short?. The rendered SQL type therefore follows the compiler's pick, not the literal's intent. Cast the operand explicitly (left_shift((int?)1, 2)) whenever a specific SQL Server type is required. The overload set is deliberately not collapsed in this release.

left_shift(long?, int?)

left_shift(value, shift) for a long operand. Rendered with the operand typed as bigint and the shift amount as bigint, behind the same typed-NULL guard (a NULL yields cast(null as bigint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? left_shift(long? value, int? shift)

Parameters

value long?

The bigint operand.

shift int?

The number of bits to shift.

Returns

long?

The shifted long, or SQL NULL when either argument is NULL.

median<T>(T?)

percentile_cont(0.5) within group (order by value) over (...): the median as the native analytic composition (T-SQL has no median function). Complete the call with Over(...): a partition-only window (or an empty over ()) is supported, while a window ORDER BY or a frame is refused. The result keeps one row per input row, unlike an aggregate. Requires the window percentile capability (see SupportsPercentileWindow; SQL Server and MariaDB).

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

Parameters

value T

The value to take the median of.

Returns

WindowFunction<double?>

The interpolated median of the window, or SQL NULL when the window has no non-null values.

Type Parameters

T

The value expression's type.

min_active_rowversion()

min_active_rowversion(): the oldest active rowversion, as binary(8) rather than a numeric counter. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public byte[] min_active_rowversion()

Returns

byte[]

nchar(int)

nchar(code): the Unicode character with the given UTF-16 code unit. The Unicode counterpart of char(int). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? nchar(int code)

Parameters

code int

Returns

string

newsequentialid()

newsequentialid(): a sequentially increasing GUID. T-SQL accepts it only as the DEFAULT of a uniqueidentifier column, not as a value in an ordinary SELECT list (the server raises an error there). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public Guid? newsequentialid()

Returns

Guid?

next_value_for(string)

NEXT VALUE FOR [sequence]: the next value of the named sequence object (SQL Server 2012+). sequence must be a constant string naming one object, written as either one component ([name]) or two ([schema].[name]); each component is bracket-quoted with an embedded ] doubled, and the name is never bound as a SQL parameter. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public long next_value_for(string sequence)

Parameters

sequence string

Returns

long

nextval(string)

nextval(sequence): an alias of next_value_for(string) that renders the same NEXT VALUE FOR [sequence] expression (SQL Server 2012+). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public long nextval(string sequence)

Parameters

sequence string

Returns

long

object_definition(int?)

object_definition(object_id): the T-SQL source text of the object (NULL when it does not exist or the caller lacks permission). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? object_definition(int? objectId)

Parameters

objectId int?

Returns

string

object_id(string?)

object_id(object_name): the id of the schema-scoped object (NULL when it does not exist). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? object_id(string? objectName)

Parameters

objectName string

Returns

int?

object_id(string?, string?)

object_id(object_name, object_type): as object_id(string?) restricted to objectType (for example "U" for a user table). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? object_id(string? objectName, string? objectType)

Parameters

objectName string
objectType string

Returns

int?

object_name(int?)

object_name(object_id): the name of the schema-scoped object with the given id (NULL when it does not exist). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? object_name(int? objectId)

Parameters

objectId int?

Returns

string

object_name(int?, int?)

object_name(object_id, database_id): as object_name(int?) resolved in databaseId rather than the current database. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? object_name(int? objectId, int? databaseId)

Parameters

objectId int?
databaseId int?

Returns

string

object_schema_name(int?)

object_schema_name(object_id): the name of the schema that owns the object with the given id (NULL when it does not exist). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? object_schema_name(int? objectId)

Parameters

objectId int?

Returns

string

object_schema_name(int?, int?)

object_schema_name(object_id, database_id): as object_schema_name(int?) resolved in databaseId. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? object_schema_name(int? objectId, int? databaseId)

Parameters

objectId int?
databaseId int?

Returns

string

openjson(string?)

openjson(json) as a FROM source (SQL Server 2016+); select Key/Value/ Type. Use through ctx.From(() => ...). The default schema yields the properties of a JSON object or the elements of a JSON array; for a typed projection declare a runtime factory whose row shape matches the WithClause schema (for example WithClause = "name nvarchar(50) '$.name'").

public TvfEntityBuilder<SqlFunctions.IOpenJsonRow> openjson(string? json)

Parameters

json string

Returns

TvfEntityBuilder<SqlFunctions.IOpenJsonRow>

original_db_name()

original_db_name(): the database name the client connected to (the original name in a contained-database redirect). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? original_db_name()

Returns

string

original_login()

The login that originally connected the session (ORIGINAL_LOGIN()), which survives an EXECUTE AS context switch. Requires a provider that exposes the SQL Server identity surface (see SupportsSessionIdentity; SQL Server).

public string? original_login()

Returns

string

parse(string?)

PARSE(value AS decimal(38,18) USING N'en-US'): parses value as a decimal with 18 fractional digits. The culture is fixed to en-US and is not a parameter, so the result does not depend on the session language; the portable alternative for other target types is not provided. SQL NULL (including a NULL argument) yields SQL NULL. Invalid text raises the server error (a SqlException with the CLR provider) — use try_parse(string?) for the NULL-returning form. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public decimal? parse(string? value)

Parameters

value string

Returns

decimal?

parsename(string?, int?)

parsename(object_name, piece): the requested 1-based part of a four-part object name (NULL when the part is not a valid identifier). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? parsename(string? objectName, int? piece)

Parameters

objectName string
piece int?

Returns

string

patindex(string?, string?)

patindex('%pattern%', expression): the 1-based position of the first occurrence of pattern in expression (0 when absent), where % and _ are wildcards. Not a regular-expression match. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? patindex(string? pattern, string? expression)

Parameters

pattern string
expression string

Returns

int?

percentile<T>(double, T?)

percentile: not supported by SQL Server. The native spellings are CommonFunctions.percentile_cont and CommonFunctions.percentile_disc completed with Over(...), which differ in whether the result is interpolated or an input value; a generic alias would hide that difference. Referencing this member throws NotSupportedException at translation time.

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

Parameters

fraction double

The percentile fraction, in [0, 1].

value T

The value to take the percentile of.

Returns

WindowFunction<double?>

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

Type Parameters

T

The value expression's type.

product<T>(T?)

product(value): the product of the non-null value rows in the aggregate group (NULL rows are ignored). Rendered as product(value) in an aggregate context; no window/OVER expansion is offered. Requires SQL Server 2025 (major version 17) and the version-bound SupportsProductAggregate gate; a lower or unversioned declaration is refused before SQL.

public T? product<T>(T? value)

Parameters

value T

The value to multiply across the group.

Returns

T

The product, or SQL NULL when the group has no non-null value.

Type Parameters

T

The value type.

publishingservername()

publishingservername(): the name of the publishing server (the server name when a replicated database is published). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? publishingservername()

Returns

string

quotename(string?)

quotename(value): brackets value as a delimited identifier (NULL when value is longer than 128 characters). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? quotename(string? value)

Parameters

value string

Returns

string

quotename(string?, string?)

quotename(value, quote): delimits value with the single quote character (for example "[", "'", "`"). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? quotename(string? value, string? quote)

Parameters

value string
quote string

Returns

string

rand()

rand(): a pseudo-random float in [0, 1). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public double? rand()

Returns

double?

rand(int)

rand(seed): a pseudo-random float in [0, 1) from the given integer seed. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public double? rand(int seed)

Parameters

seed int

Returns

double?

regexp_count(string?, string?)

regexp_count(input, pattern): the number of non-overlapping matches of the regular expression pattern in input (0 when there is no match, SQL NULL when the input is NULL). Rendered as regexp_count(input, pattern). Requires SQL Server 2025 (major version 17) and the version-bound SupportsSqlServer2025Functions gate; a lower or unversioned declaration is refused before SQL.

public int? regexp_count(string? input, string? pattern)

Parameters

input string

The string to search.

pattern string

The regular expression.

Returns

int?

The match count as int, or SQL NULL when the input is NULL.

regexp_instr(string?, string?)

regexp_instr(input, pattern): the one-based position of the first match of pattern in input, 0 when there is no match and SQL NULL when an operand is NULL. Requires SQL Server 2025 (major version 17) and the version-bound SupportsSqlServer2025Functions gate; it is not compatibility-level gated.

public int? regexp_instr(string? input, string? pattern)

Parameters

input string

The string to search.

pattern string

The regular expression.

Returns

int?

The match position, 0 for no match, or SQL NULL for a NULL operand.

regexp_instr(string?, string?, int?)

regexp_instr(input, pattern, start): searches from the one-based start.

public int? regexp_instr(string? input, string? pattern, int? start)

Parameters

input string

The string to search.

pattern string

The regular expression.

start int?

The one-based search start; a value below 1 is the engine's error.

Returns

int?

The match position, 0 for no match, or SQL NULL for a NULL operand.

regexp_instr(string?, string?, int?, int?)

regexp_instr(input, pattern, start, occurrence): the occurrence-th match.

public int? regexp_instr(string? input, string? pattern, int? start, int? occurrence)

Parameters

input string

The string to search.

pattern string

The regular expression.

start int?

The one-based search start.

occurrence int?

The one-based match number.

Returns

int?

The match position, 0 for no match, or SQL NULL for a NULL operand.

regexp_instr(string?, string?, int?, int?, int?)

regexp_instr(input, pattern, start, occurrence, return_option): return_option 0 returns the match position (the default) and 1 the position immediately after the match. A supplied NULL return option behaves like the omitted form (0) in the observed engine; any other value is the engine's Msg 19301.

public int? regexp_instr(string? input, string? pattern, int? start, int? occurrence, int? return_option)

Parameters

input string

The string to search.

pattern string

The regular expression.

start int?

The one-based search start.

occurrence int?

The one-based match number.

return_option int?

The native return option (0 or 1).

Returns

int?

The requested position, 0 for no match, or SQL NULL for a NULL operand.

regexp_instr(string?, string?, int?, int?, int?, string?)

regexp_instr(input, pattern, start, occurrence, return_option, match_type): the full native form with the match_type flag string. A supplied NULL match type yields SQL NULL; invalid flags are the engine's Msg 19303.

public int? regexp_instr(string? input, string? pattern, int? start, int? occurrence, int? return_option, string? match_type)

Parameters

input string

The string to search.

pattern string

The regular expression.

start int?

The one-based search start.

occurrence int?

The one-based match number.

return_option int?

The native return option (0 or 1).

match_type string

The native match-type flags, or SQL NULL.

Returns

int?

The requested position, 0 for no match, or SQL NULL for a NULL operand.

regexp_matches(string?, string?)

regexp_matches(input, pattern) as a FROM source: returns one row per non-overlapping match with the row shape SqlFunctions.ISqlServerRegexpMatchRow (match_id bigint, start_position int, end_position int, match_value varchar(n), substring_matches varchar(max)); a string without a match produces zero rows. Use through ctx.From(() => ...). Requires SQL Server 2025 (major version 17, see SupportsSqlServer2025Functions) and database compatibility level 170: at 160 the engine raises Msg 208 for the missing function, a runtime prerequisite the dialect cannot inspect.

public TvfEntityBuilder<SqlFunctions.ISqlServerRegexpMatchRow> regexp_matches(string? input, string? pattern)

Parameters

input string

The string to search.

pattern string

The regular expression.

Returns

TvfEntityBuilder<SqlFunctions.ISqlServerRegexpMatchRow>

regexp_matches(string?, string?, string?)

regexp_matches(input, pattern, match_type): the flag-taking form (c, i, s and m). A supplied NULL match type returns zero rows; invalid flags are the engine's Msg 19303. The row shape and the compatibility-170 prerequisite are those of regexp_matches(string?, string?).

public TvfEntityBuilder<SqlFunctions.ISqlServerRegexpMatchRow> regexp_matches(string? input, string? pattern, string? match_type)

Parameters

input string

The string to search.

pattern string

The regular expression.

match_type string

The native match-type flags, or SQL NULL.

Returns

TvfEntityBuilder<SqlFunctions.ISqlServerRegexpMatchRow>

regexp_split_to_table(string?, string?)

regexp_split_to_table(input, pattern) as a FROM source: splits input on the regular expression pattern and returns one row per fragment with the row shape SqlFunctions.ISqlServerRegexpSplitToTableRow (value varchar(n), ordinal bigint). Use through ctx.From(() => ...). Requires SQL Server 2025 (major version 17, see SupportsSqlServer2025Functions) and database compatibility level 170; at 160 the engine raises Msg 208 for the missing function.

public TvfEntityBuilder<SqlFunctions.ISqlServerRegexpSplitToTableRow> regexp_split_to_table(string? input, string? pattern)

Parameters

input string

The string to split.

pattern string

The regular expression delimiter.

Returns

TvfEntityBuilder<SqlFunctions.ISqlServerRegexpSplitToTableRow>

regexp_substr(string?, string?)

regexp_substr(input, pattern): the first substring of input matching the regular expression pattern, or SQL NULL when there is no match or the input or pattern is NULL. Rendered as regexp_substr(input, pattern). Requires SQL Server 2025 (major version 17) and the version-bound SupportsSqlServer2025Functions gate; a lower or unversioned declaration is refused before SQL. The function is not compatibility-level gated: it executes at compatibility 160 on a major-17 engine.

public string? regexp_substr(string? input, string? pattern)

Parameters

input string

The string to search.

pattern string

The regular expression; invalid patterns are the engine's error.

Returns

string

The first match, or SQL NULL when there is no match or an operand is NULL.

regexp_substr(string?, string?, int?)

regexp_substr(input, pattern, start): the first match at or after the one-based start position. Omitting start and passing a NULL start are separate forms with distinct native semantics: the omitted form starts at position 1, while a supplied NULL start yields SQL NULL (probe substr_null_start).

public string? regexp_substr(string? input, string? pattern, int? start)

Parameters

input string

The string to search.

pattern string

The regular expression.

start int?

The one-based search start; a value below 1 is the engine's error.

Returns

string

The match, or SQL NULL when there is no match or an operand is NULL.

regexp_substr(string?, string?, int?, int?)

regexp_substr(input, pattern, start, occurrence): the occurrence-th (one-based) match at or after start. A supplied NULL occurrence yields SQL NULL; an occurrence below 1 is the engine's error.

public string? regexp_substr(string? input, string? pattern, int? start, int? occurrence)

Parameters

input string

The string to search.

pattern string

The regular expression.

start int?

The one-based search start.

occurrence int?

The one-based match number.

Returns

string

The match, or SQL NULL when there is no match or an operand is NULL.

regexp_substr(string?, string?, int?, int?, string?)

regexp_substr(input, pattern, start, occurrence, match_type): the full native form with the match_type flag string (c, i, s and m; any other flag is the engine's Msg 19303). A supplied NULL match type yields SQL NULL; the value is passed through, never validated client-side.

public string? regexp_substr(string? input, string? pattern, int? start, int? occurrence, string? match_type)

Parameters

input string

The string to search.

pattern string

The regular expression.

start int?

The one-based search start.

occurrence int?

The one-based match number.

match_type string

The native match-type flags, or SQL NULL.

Returns

string

The match, or SQL NULL when there is no match or an operand is NULL.

right_shift(byte?, int?)

right_shift(value, shift) for a byte operand: the logical (zero-fill) right shift of value. Rendered as right_shift(cast(value as tinyint), cast(shift as bigint)) behind a typed-NULL guard (a NULL yields cast(null as tinyint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public byte? right_shift(byte? value, int? shift)

Parameters

value byte?

The tinyint operand.

shift int?

The number of bits to shift.

Returns

byte?

The shifted byte, or SQL NULL when either argument is NULL.

right_shift(short?, int?)

right_shift(value, shift) for a short operand. Rendered as right_shift(cast(value as smallint), cast(shift as bigint)) behind a typed-NULL guard (a NULL yields cast(null as smallint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public short? right_shift(short? value, int? shift)

Parameters

value short?

The smallint operand.

shift int?

The number of bits to shift.

Returns

short?

The shifted short, or SQL NULL when either argument is NULL.

right_shift(int?, int?)

right_shift(value, shift) for an int operand. Rendered as right_shift(cast(value as int), cast(shift as bigint)) behind a typed-NULL guard (a NULL yields cast(null as int)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public int? right_shift(int? value, int? shift)

Parameters

value int?

The int operand.

shift int?

The number of bits to shift.

Returns

int?

The shifted int, or SQL NULL when either argument is NULL.

right_shift(long?, int?)

right_shift(value, shift) for a long operand. Rendered as right_shift(cast(value as bigint), cast(shift as bigint)) behind a typed-NULL guard (a NULL yields cast(null as bigint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? right_shift(long? value, int? shift)

Parameters

value long?

The bigint operand.

shift int?

The number of bits to shift.

Returns

long?

The shifted long, or SQL NULL when either argument is NULL.

rowcount()

@@ROWCOUNT is refused: it describes the previous statement and is statement-scoped, so a per-row query projection cannot expose that value faithfully. The result is a callable placeholder; every invocation throws NotSupportedException with the stable diagnostic.

public int rowcount()

Returns

int

rowcount_big()

rowcount_big(): the number of rows affected by the previous command on the same connection. nextorm executes every query as its own command, so the previous command is nextorm's own statement: use the affected-row count of the executed command instead. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public long rowcount_big()

Returns

long

schema_id()

schema_id(): the id of the caller's default schema. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? schema_id()

Returns

int?

schema_id(string?)

schema_id(schema_name): the id of the named schema. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? schema_id(string? schema)

Parameters

schema string

Returns

int?

schema_name()

schema_name(): the name of the caller's default schema. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? schema_name()

Returns

string

schema_name(int?)

schema_name(schema_id): the name of the schema with the given id. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? schema_name(int? schemaId)

Parameters

schemaId int?

Returns

string

scope_identity()

scope_identity(): the last identity value generated in the current scope, and only inside the same batch as the INSERT — a separate nextorm command sees NULL. Use ReturningIdentity<TKey>() for a last-insert key. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public decimal? scope_identity()

Returns

decimal?

server_name()

The name of the server instance the client is connected to (@@SERVERNAME); SQL NULL when the instance has no configured name. Requires a provider that exposes the SQL Server identity surface (see SupportsSessionIdentity; SQL Server). This is a server identity, not the client host (host_name()).

public string? server_name()

Returns

string

session_context(string?)

SESSION_CONTEXT(key) is refused: the native result is sql_variant, for which nextorm has no faithful CLR result contract, and no lossy cast substitution is performed. The declared object result is a callable placeholder, not a claim that the sql_variant value can be materialised: every invocation, direct or inside a query, throws NotSupportedException with the stable diagnostic before SQL is rendered.

public object? session_context(string? key)

Parameters

key string

The session-context key.

Returns

object

session_id()

SESSION_ID() is refused: it is not a SQL Server engine function — the documented function belongs to Azure Synapse. On SQL Server the session identity is @@SPID or sys.dm_exec_sessions.session_id, and no substitution is performed. The result is a callable placeholder; every invocation throws NotSupportedException with the stable diagnostic.

public string? session_id()

Returns

string

set_bit(byte?, int?)

set_bit(value, offset) for a byte operand: sets the bit at the zero-based offset to the native default value 1. Rendered as set_bit(cast(value as tinyint), cast(offset as int)) behind a typed-NULL guard (a NULL yields cast(null as tinyint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public byte? set_bit(byte? value, int? offset)

Parameters

value byte?

The tinyint operand.

offset int?

The zero-based bit position.

Returns

byte?

The value with the bit set, or SQL NULL when either argument is NULL.

set_bit(byte?, int?, bool?)

set_bit(value, offset, value_to_set) for a byte operand: sets the bit at the zero-based offset to bitValue. Rendered as set_bit(cast(value as tinyint), cast(offset as int), cast(bitValue as bit)) behind a typed-NULL guard over all three arguments (a NULL yields cast(null as tinyint); an explicitly NULL bitValue yields SQL NULL rather than the native default). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public byte? set_bit(byte? value, int? offset, bool? bitValue)

Parameters

value byte?

The tinyint operand.

offset int?

The zero-based bit position.

bitValue bool?

The bit value to set; SQL NULL when either argument is NULL.

Returns

byte?

The value with the bit set, or SQL NULL when any argument is NULL.

set_bit(short?, int?)

set_bit(value, offset) for a short operand: sets the bit at the zero-based offset to the native default value 1. Rendered as set_bit(cast(value as smallint), cast(offset as int)) behind a typed-NULL guard (a NULL yields cast(null as smallint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public short? set_bit(short? value, int? offset)

Parameters

value short?

The smallint operand.

offset int?

The zero-based bit position.

Returns

short?

The value with the bit set, or SQL NULL when either argument is NULL.

set_bit(short?, int?, bool?)

set_bit(value, offset, value_to_set) for a short operand. Rendered as set_bit(cast(value as smallint), cast(offset as int), cast(bitValue as bit)) behind a typed-NULL guard over all three arguments (a NULL yields cast(null as smallint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public short? set_bit(short? value, int? offset, bool? bitValue)

Parameters

value short?

The smallint operand.

offset int?

The zero-based bit position.

bitValue bool?

The bit value to set; SQL NULL when either argument is NULL.

Returns

short?

The value with the bit set, or SQL NULL when any argument is NULL.

set_bit(int?, int?)

set_bit(value, offset) for an int operand: sets the bit at the zero-based offset to the native default value 1. Rendered as set_bit(cast(value as int), cast(offset as int)) behind a typed-NULL guard (a NULL yields cast(null as int)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public int? set_bit(int? value, int? offset)

Parameters

value int?

The int operand.

offset int?

The zero-based bit position.

Returns

int?

The value with the bit set, or SQL NULL when either argument is NULL.

set_bit(int?, int?, bool?)

set_bit(value, offset, value_to_set) for an int operand. Rendered as set_bit(cast(value as int), cast(offset as int), cast(bitValue as bit)) behind a typed-NULL guard over all three arguments (a NULL yields cast(null as int)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public int? set_bit(int? value, int? offset, bool? bitValue)

Parameters

value int?

The int operand.

offset int?

The zero-based bit position.

bitValue bool?

The bit value to set; SQL NULL when either argument is NULL.

Returns

int?

The value with the bit set, or SQL NULL when any argument is NULL.

set_bit(long?, int?)

set_bit(value, offset) for a long operand: sets the bit at the zero-based offset to the native default value 1. Rendered as set_bit(cast(value as bigint), cast(offset as int)) behind a typed-NULL guard (a NULL yields cast(null as bigint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? set_bit(long? value, int? offset)

Parameters

value long?

The bigint operand.

offset int?

The zero-based bit position.

Returns

long?

The value with the bit set, or SQL NULL when either argument is NULL.

set_bit(long?, int?, bool?)

set_bit(value, offset, value_to_set) for a long operand. Rendered as set_bit(cast(value as bigint), cast(offset as int), cast(bitValue as bit)) behind a typed-NULL guard over all three arguments (a NULL yields cast(null as bigint)). Requires SQL Server 2022 (major version 16) and the version-bound SupportsSqlServer2022Functions gate.

public long? set_bit(long? value, int? offset, bool? bitValue)

Parameters

value long?

The bigint operand.

offset int?

The zero-based bit position.

bitValue bool?

The bit value to set; SQL NULL when either argument is NULL.

Returns

long?

The value with the bit set, or SQL NULL when any argument is NULL.

smalldatetimefromparts(int?, int?, int?, int?, int?)

smalldatetimefromparts(year, month, day, hour, minute): builds a smalldatetime. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTime? smalldatetimefromparts(int? year, int? month, int? day, int? hour, int? minute)

Parameters

year int?
month int?
day int?
hour int?
minute int?

Returns

DateTime?

soundex(string?)

soundex(value): the four-character Soundex code of value. The result is collation-sensitive. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? soundex(string? value)

Parameters

value string

Returns

string

square(double?)

square(value): the square of value. The portable alternative is value * value or Pow(double, double). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public double? square(double? value)

Parameters

value double?

Returns

double?

stats_date(int?, int?)

stats_date(table_id, stats_id): the last-updated date of the statistics (NULL when the statistics do not exist). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTime? stats_date(int? tableId, int? statsId)

Parameters

tableId int?
statsId int?

Returns

DateTime?

str(double?)

str(float_expression): the string form of a floating-point number (length 10, 0 decimals). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? str(double? value)

Parameters

value double?

Returns

string

str(double?, int?)

str(float_expression, length): the string form of a floating-point number with the given total length. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? str(double? value, int? length)

Parameters

value double?
length int?

Returns

string

str(double?, int?, int?)

str(float_expression, length, decimal): as str(double?, int?) with an explicit number of decimalPlaces. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? str(double? value, int? length, int? decimalPlaces)

Parameters

value double?
length int?
decimalPlaces int?

Returns

string

string_escape(string?, string?)

string_escape(value, type): escapes the characters of value that are special in the target format named by type (for example "json" or "xml"; both must be literals). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? string_escape(string? value, string? type)

Parameters

value string
type string

Returns

string

string_split(string?, string?)

string_split(value, separator) as a FROM source (SQL Server 2016+); select Value. Use through ctx.From(() => ...). The fragments are not guaranteed to be ordered; add an order by on the caller side if the input order matters.

public TvfEntityBuilder<SqlFunctions.IStringSplitRow> string_split(string? value, string? separator)

Parameters

value string
separator string

Returns

TvfEntityBuilder<SqlFunctions.IStringSplitRow>

stuff(string?, int, int, string?)

stuff(value, start, length, new_value): replaces length characters of value from 1-based start with newValue. Character data only: SQL Server's T-SQL STUFF implicitly converts binary arguments to varchar, so there is deliberately no byte[] overload (it could never return binary). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? stuff(string? value, int start, int length, string? newValue)

Parameters

value string
start int
length int
newValue string

Returns

string

suser_name()

The login name of the current security context (SUSER_NAME()), resolved from the security token rather than from the session string. Requires a provider that exposes the SQL Server identity surface (see SupportsSessionIdentity; SQL Server).

public string? suser_name()

Returns

string

switchoffset(DateTimeOffset?, int)

switchoffset(value, time_zone_minutes): the signed-integer-minutes form of switchoffset(DateTimeOffset?, string?). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTimeOffset? switchoffset(DateTimeOffset? value, int timeZoneMinutes)

Parameters

value DateTimeOffset?
timeZoneMinutes int

Returns

DateTimeOffset?

switchoffset(DateTimeOffset?, string?)

switchoffset(value, time_zone): changes the time-zone offset of a datetimeoffset to timeZone, expressed as a string (for example "-08:00"). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTimeOffset? switchoffset(DateTimeOffset? value, string? timeZone)

Parameters

value DateTimeOffset?
timeZone string

Returns

DateTimeOffset?

sysdatetime()

sysdatetime(): the current system date/time as datetime2 (server-local). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTime? sysdatetime()

Returns

DateTime?

sysdatetimeoffset()

sysdatetimeoffset(): the current system date/time with the server's UTC offset as datetimeoffset. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTimeOffset? sysdatetimeoffset()

Returns

DateTimeOffset?

sysutcdatetime()

sysutcdatetime(): the current UTC date/time as datetime2. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTime? sysutcdatetime()

Returns

DateTime?

timefromparts(int?, int?, int?, int?, int?)

timefromparts(hour, minute, seconds, fractions, precision): builds a time from its parts. precision must be a constant between 0 and 7. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public TimeSpan? timefromparts(int? hour, int? minute, int? seconds, int? fractions, int? precision)

Parameters

hour int?
minute int?
seconds int?
fractions int?
precision int?

Returns

TimeSpan?

todatetimeoffset(DateTime?, int)

todatetimeoffset(value, time_zone_minutes): the signed-integer-minutes form of todatetimeoffset(DateTime?, string?). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTimeOffset? todatetimeoffset(DateTime? value, int timeZoneMinutes)

Parameters

value DateTime?
timeZoneMinutes int

Returns

DateTimeOffset?

todatetimeoffset(DateTime?, string?)

todatetimeoffset(value, time_zone): attaches timeZone (a string such as "+02:00") to a datetime, yielding a datetimeoffset. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public DateTimeOffset? todatetimeoffset(DateTime? value, string? timeZone)

Parameters

value DateTime?
timeZone string

Returns

DateTimeOffset?

try_cast(object?)

TRY_CAST(value AS nvarchar(4000)): the string-target overload of try_cast<T>(object?). A value longer than 4000 characters is truncated by the server, exactly like the native cast; an invalid conversion is SQL NULL.

public string? try_cast(object? value)

Parameters

value object

The value to convert.

Returns

string

The converted string, or SQL NULL when the conversion is invalid.

try_cast<T>(object?)

TRY_CAST(value AS <type>): converts value to the SQL Server type closed-mapped from T and returns SQL NULL when the conversion is invalid (malformed text, overflow, an out-of-range value) instead of raising an error. The target map is fixed: byte→tinyint, short→smallint, int→int, long→bigint, float→real, double→float(53), decimal→decimal(38,10), bool→bit, Guid→uniqueidentifier, DateTime→datetime2(7), DateTimeOffset→datetimeoffset(7), TimeSpan→time(7); any other target is refused before the command is prepared. A target conversion SQL Server itself prohibits is not suppressed and surfaces as the engine error. SQL Server only (declared 16, 17 and unversioned contexts); other providers reject the call.

public T? try_cast<T>(object? value) where T : struct

Parameters

value object

The value to convert.

Returns

T?

The converted value, or SQL NULL when the conversion is invalid.

Type Parameters

T

The SQL Server conversion target.

try_convert(object?)

TRY_CONVERT(nvarchar(4000), value): the string-target overload without a style.

public string? try_convert(object? value)

Parameters

value object

The value to convert.

Returns

string

The converted string, or SQL NULL when the conversion is invalid.

try_convert(object?, int?)

TRY_CONVERT(nvarchar(4000), value, style): the string-target overload with a style. A NULL style keeps SQL Server's native NULL-style semantics, which the engine applies as a NULL result even for a convertible value.

public string? try_convert(object? value, int? style)

Parameters

value object

The value to convert.

style int?

The native conversion style, or SQL NULL for the engine's NULL-style result (which is SQL NULL).

Returns

string

The converted string, or SQL NULL when the conversion is invalid.

try_convert<T>(object?)

TRY_CONVERT(<type>, value): the no-style form of try_convert<T>(object?, int?). Uses the same closed target map as try_cast<T>(object?); an unmapped target is refused before the command is prepared, and a target conversion SQL Server prohibits surfaces as the engine error.

public T? try_convert<T>(object? value) where T : struct

Parameters

value object

The value to convert.

Returns

T?

The converted value, or SQL NULL when the conversion is invalid.

Type Parameters

T

The SQL Server conversion target.

try_convert<T>(object?, int?)

TRY_CONVERT(<type>, value, style): the style-taking form. Omitting the argument and passing a NULL style are separate overloads: the NULL style is emitted as a typed NULL and keeps SQL Server's native NULL-style semantics, which the engine applies as a NULL result even when the value itself is convertible (probe C4: try_convert(int, '42', cast(null as int)) is NULL). The style is a datetime/float/money style, not a target-type selector.

public T? try_convert<T>(object? value, int? style) where T : struct

Parameters

value object

The value to convert.

style int?

The native conversion style, or SQL NULL for the engine's NULL-style result (which is SQL NULL).

Returns

T?

The converted value, or SQL NULL when the conversion is invalid.

Type Parameters

T

The SQL Server conversion target.

try_parse(string?)

TRY_PARSE(value AS decimal(38,18) USING N'en-US'): as parse(string?) with the fixed en-US culture, but invalid text returns SQL NULL instead of raising. SQL NULL (including a NULL argument) yields SQL NULL. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public decimal? try_parse(string? value)

Parameters

value string

Returns

decimal?

type_id(string?)

type_id(type_name): the id of the named data type. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? type_id(string? typeName)

Parameters

typeName string

Returns

int?

type_name(int?)

type_name(type_id): the name of the data type with the given id. Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public string? type_name(int? typeId)

Parameters

typeId int?

Returns

string

unicode(string?)

unicode(value): the UTF-16 code unit of the first character of value. The Unicode counterpart of ascii(string?). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public int? unicode(string? value)

Parameters

value string

Returns

int?

xact_state()

xact_state(): the state of the current transaction in the native T-SQL encoding 0 (no active transaction), 1 (active and committable) or -1 (active but uncommittable, so the batch must be rolled back). Requires a provider that supports it (see SqlServerFunctions; SQL Server).

public short xact_state()

Returns

short

xml_exist(string?, string?)

xml.exist(xquery): true when the XQuery selects at least one node (the XQuery must be a string literal). Rendered in the postfix form xmlcol.exist('/path'), which yields bit; in a predicate context the dialect compares it with 1. Requires a provider that supports the XML data-type methods (see Supports(string); SQL Server).

public bool xml_exist(string? xml, string? xpath)

Parameters

xml string
xpath string

Returns

bool

xml_nodes(string?, string?)

xml.nodes(xquery) as a composable rowset source (SQL Server): unfolds the XML value into one row per node selected by xpath (the XQuery must be a string literal). Use only as the source of CrossApply<TJoinEntity>(Expression<Func<TEntity, QueryCommand<TJoinEntity>>>, Action<JoinOptions>?) (or OuterApply); rendered as <xml>.nodes('xpath') as [alias]([value]), and the unfolded Value is projected further with xml_value<T>(string?, string?, string?)/xml_query(string?, string?)/ xml_exist(string?, string?). Requires a provider that supports the XML data-type methods (see Supports(string); SQL Server).

public QueryCommand<SqlFunctions.IXmlNodesRow> xml_nodes(string? xml, string? xpath)

Parameters

xml string
xpath string

Returns

QueryCommand<SqlFunctions.IXmlNodesRow>

Remarks

Unlike the runtime table-function factories (string_split/openjson) this is a correlated source over the left-hand row's column, not a standalone table function, so it is expressed through CrossApply/OuterApply rather than ctx.From(...).

xml_query(string?, string?)

xml.query(xquery): the XML fragment selected by the XQuery (the XQuery must be a string literal). Rendered in the postfix form xmlcol.query('/path'). Requires a provider that supports the XML data-type methods (see Supports(string); SQL Server).

public string? xml_query(string? xml, string? xpath)

Parameters

xml string
xpath string

Returns

string

xml_value<T>(string?, string?, string?)

xml.value(xquery, sqltype): the scalar value selected by the XQuery, cast to the T-SQL type named by sqlType (both arguments must be string literals). Rendered in the postfix form xmlcol.value('(path)[1]', 'int'); T must match the requested SQL type. Requires a provider that supports the XML data-type methods (see Supports(string); SQL Server).

public T? xml_value<T>(string? xml, string? xpath, string? sqlType)

Parameters

xml string
xpath string
sqlType string

Returns

T

Type Parameters

T
Edit this page