Scalar functions
Translate
string,MathandDateTimemembers,??coalescing, boolean predicates and numeric conversions into provider-specific SQL.
Prerequisites: Entities and metadata · Querying and projections · Filtering (WHERE)
Overview
nextorm recognises a fixed set of CLR members and rewrites them to SQL inside any query expression.
The dispatch lives in BaseExpressionVisitor: string methods, Math methods, DateTime members,
SqlFunctions.Sql.like, the ?? operator and numeric conversions. Everything provider-specific is delegated to
ISqlDialect, so the same C# code renders the correct function on every provider.
Two rules apply throughout:
- a captured value (a local or parameter) becomes a query parameter, not a literal;
- a constant is inlined in the provider's own string-literal form. For
Contains/StartsWith/EndsWiththat also means%,_and\in a constant are escaped before rendering.
The ESCAPE clause of the rendered LIKE pattern is provider-dependent: ClickHouse emits none (and fails
closed on a custom escape character), MySQL/MariaDB emit the operand as a mode-independent hex token, and
the remaining providers keep the clause.
The built-in translations are attempted before any [SqlFunction]
mapping, so a user-defined attribute cannot change the behaviour of string/Math/DateTime members.
Cross-provider helpers live on SqlFunctions.Sql. Functions that only one provider supports are grouped
under a provider-specific surface: Postgres (PostgreSQL: native arrays, native JSON, the extended
scalar library, the PostgreSQL-only aggregates and the generate_series/unnest table functions),
SqlServer (SQL Server: the JSON-as-text functions, the version-gated 2022/2025 scalars such as left_shift and regexp_count, the product aggregate, and string_split/openjson/regexp_split_to_table) and
ClickHouse (ClickHouse: arg_min/arg_max, the -If combinator, the string-JSON
JSONExtract* family, the flat-JSON visitParamExtract* fast path, the JSONPath scalars
json_value/json_query/json_exists and the dictionary functions
dict_get/dict_get_or_default/dict_has/dict_get_hierarchy/dict_get_children/dict_is_in), and
Sqlite (SQLite: the core scalars, JSON1 and the total aggregate,
the date/math helpers and the full-text query surface). Calling one of them on a
provider that does not opt in throws NotSupportedException.
See also
- Filtering (WHERE) -
Contains/in,??and conditional expressions in predicates. - Grouping and aggregates - aggregate functions (
count,sum, ...). - User-defined functions - when a scalar function is not built in.
- Provider overview - capability flags and quoting.
Source: src/nextorm.core/Visitors/Core/BaseExpressionVisitor.cs:541, :1157, :1204, :1752;
src/nextorm.core/Visitors/Functions/BuiltinFunctionTranslator.cs, src/nextorm.core/Visitors/Aggregates/AggregateFilter.cs;
src/nextorm.core/Query/SqlFunctions/SqlFunctions.cs;
src/nextorm.core/DataContext/Dialect/SqlDialectBase.cs:57;
tests/nextorm.integration.tests/CommonTestSuite/Functions/CommonTestSuite.Functions.cs:8, :19, :43, :54, :65, :76, :87, :98, :117;
generated SQL: tests/nextorm.sqlite.tests/SqlGeneration/SqlGenerationTests.cs:695, :775, :801, :811, :832, :852, :861, :872, :882, :892, :912, :921, :931, :940, :950, :960, :974, :985;
tests/nextorm.sqlserver.tests/SqlGeneration/SqlGenerationTests.cs:457, :512, :522, :533, :543, :552, :563, :573, :583, :593, :602, :613, :624, :633, :643;
tests/nextorm.postgres.tests/SqlGeneration/SqlGenerationTests.cs:390, :445, :465, :475, :484, :495, :505, :515, :525, :534, :544, :554, :563, :573.