Class SqlServerEntityBuilderExtensions

Namespace

NextORM.SqlServer

Assembly

nextorm.sqlserver.dll

SQL Server-only fluent members of EntityBuilder<TEntity>, removed from the common nextorm package and re-exposed here as extension methods. Add using NextORM.SqlServer; to keep the fluent chain unchanged on a SQL Server provider. The render-time dialect gating (and the thrown exceptions) is unchanged.

public static class SqlServerEntityBuilderExtensions

Inheritance

object

SqlServerEntityBuilderExtensions

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Methods

Pivot<TEntity>(EntityBuilder<TEntity>, PivotAggregate, Expression<Func<TEntity, object?>>, Expression<Func<TEntity, object?>>, params PivotValue[])

Reshapes this query's source into columns with the native PIVOT operator: for every distinct forColumn value in values a result column is produced from aggregate of aggregateColumn. The result is an untyped source (TableAlias): select the grouping columns and the pivoted columns by name. Each result column is named by its pivot value; the value string is a raw name that is always safely quoted, so read it through SqlFunctions.Column<T> in the projection (Q1 = SqlFunctions.Column<decimal?>(t, "1")) rather than pre-quoting it. Requires a dialect that supports it (see ISqlDialect.Pivot). The source may be a plain table/entity, a table-valued function or a derived query (From(query)); filters and other modifiers belong to the reshaped result (or, for a derived source, inside the derived query).

public static EntityBuilder<TableAlias> Pivot<TEntity>(this EntityBuilder<TEntity> builder, PivotAggregate aggregate, Expression<Func<TEntity, object?>> aggregateColumn, Expression<Func<TEntity, object?>> forColumn, params PivotValue[] values)

Parameters

builder EntityBuilder<TEntity>

The query builder to extend.

aggregate PivotAggregate

The aggregate applied to each cell (SUM/COUNT/AVG/MIN/MAX).

aggregateColumn Expression<Func<TEntity, object>>

The column expression aggregated into each cell.

forColumn Expression<Func<TEntity, object>>

The column whose values become the result columns.

values PivotValue[]

The pivot values; each value names its result column and is a raw name, safely quoted independently of the command's QuoteIdentifiers option.

Returns

EntityBuilder<TableAlias>

Type Parameters

TEntity

Pivot<TEntity, TShape>(EntityBuilder<TEntity>, PivotAggregate, Expression<Func<TEntity, object?>>, Expression<Func<TEntity, object?>>, Expression<Func<TEntity, PivotValues, TShape>>)

Reshapes this query's source into columns with the native PIVOT operator and projects the typed result shape in one declaration: projection declares the result members (a grouping member is a direct member of the inner source) and the CLR type of every pivoted cell (pivot.Value<decimal?>("1")). The declared value order defines the IN (...) list, so each value is declared exactly once.

Each value is the data value matched by the pivoted column (the same string PivotValue.Create takes), not its SQL identifier: the identifier is derived from it the way the renderer escapes the IN list. The value string is a runtime identifier with no compile-time membership guarantee — it is never validated against the data. A value absent from the data still renders its column but materializes as SQL NULL for every row, so declare the cell nullable when that is possible; an invalid name or an incompatible explicit cell type can fail during query preparation or materialization. A cell whose type is non-nullable against a SQL NULL behaves like the matching TableAlias accessor; the nullability is never inferred. A truly dynamic PIVOT whose value list is known only at runtime is outside this typed overload and stays with the untyped TableAlias path.

The returned builder is a full source over the typed projection: its members are usable in Where/OrderBy/Select and it can be consumed as a derived source or CTE. Like every derived source, materializing it requires an explicit Select (an anonymous/record shape has no entity mapping to fall back on).

Requires a dialect that supports PIVOT (SQL Server); other providers reject the query when it renders, exactly like the untyped overload. The projection is fail-closed: a duplicate value, a projection without values, a value name that is not a compile-time or captured constant, and a grouping member that is also the aggregate or FOR column all throw ArgumentException instead of emitting wrong SQL. The source rules are those of the untyped overload (a plain table/entity, a table-valued function or a derived query; filters and other modifiers belong to the reshaped result or inside the derived query).

public static EntityBuilder<TShape> Pivot<TEntity, TShape>(this EntityBuilder<TEntity> builder, PivotAggregate aggregate, Expression<Func<TEntity, object?>> aggregateColumn, Expression<Func<TEntity, object?>> forColumn, Expression<Func<TEntity, PivotValues, TShape>> projection)

Parameters

builder EntityBuilder<TEntity>

The query builder to extend.

aggregate PivotAggregate

The aggregate applied to each cell (SUM/COUNT/AVG/MIN/MAX).

aggregateColumn Expression<Func<TEntity, object>>

The column expression aggregated into each cell.

forColumn Expression<Func<TEntity, object>>

The column whose values become the result columns.

projection Expression<Func<TEntity, PivotValues, TShape>>

The typed result projection; unused inner members (other than the aggregate/FOR column) remain grouping inputs.

Returns

EntityBuilder<TShape>

A builder over the typed PIVOT result.

Type Parameters

TEntity

The inner entity/projection type whose members act as grouping columns.

TShape

The typed result shape declared by projection.

Exceptions

ArgumentNullException

A required argument is null.

ArgumentException

The projection declares no value, an empty shape, a duplicate value, a non-constant/empty value name, or reads the aggregate/FOR column as a grouping member.

NotSupportedException

The query is rendered by a provider without PIVOT support, or the projection reads the inner source as a whole.

BuildSqlCommandException

A grouping member has no registered column name (the inner mapping was never registered).

Unpivot<TEntity>(EntityBuilder<TEntity>, string, string, params UnpivotColumn[])

Reshapes this query's source with the native UNPIVOT operator: the listed columns are stacked into two result columns (valueColumnName holding the value and nameColumnName holding the originating column name). Requires a dialect that supports it (see ISqlDialect.Pivot). The source may be a plain table/entity or a derived query (From(query)).

public static EntityBuilder<TableAlias> Unpivot<TEntity>(this EntityBuilder<TEntity> builder, string valueColumnName, string nameColumnName, params UnpivotColumn[] columns)

Parameters

builder EntityBuilder<TEntity>

The query builder to extend.

valueColumnName string

The raw name of the output column that receives the values; safely quoted independently of the command's QuoteIdentifiers option.

nameColumnName string

The raw name of the output column that receives the source column name; safely quoted independently of the command's QuoteIdentifiers option.

columns UnpivotColumn[]

The source columns to unpivot; each column's raw name becomes its name-column value and is safely quoted.

Returns

EntityBuilder<TableAlias>

Type Parameters

TEntity

Unpivot<TEntity, TShape>(EntityBuilder<TEntity>, Expression<Func<TEntity, UnpivotValues, TShape>>, params UnpivotColumn[])

Reshapes this query's source by stacking the listed columns with the native UNPIVOT operator and projects the typed result shape in one declaration. The two output roles are declared in projection: unpivot.Value<decimal?>("val") reads the value column and unpivot.Name<string>("qtr") the name column, using the same role identifiers the UNPIVOT ([val] for [qtr] in (...)) clause takes. A direct member of the inner source is a grouping member.

The role identifiers passed to Value/Name are runtime values: they are matched against the UNPIVOT ([value] for [name] in (...)) clause at runtime, so an incompatible explicit cell type or a malformed role name is not rejected at compile time. SQL Server excludes a source cell whose value is NULL, so the stacked result contains only non-null cells and can have fewer rows than columns × source rows; declaring the value cell nullable models that exclusion.

The returned builder is a full source over the typed projection (see the typed Pivot overload); like every derived source it is materialized with an explicit Select. Requires a dialect that supports UNPIVOT (SQL Server); other providers reject the query when it renders. The projection is fail-closed: zero columns, a missing role, and conflicting role identifiers throw ArgumentException; a repeated consistent reference reuses the role.

public static EntityBuilder<TShape> Unpivot<TEntity, TShape>(this EntityBuilder<TEntity> builder, Expression<Func<TEntity, UnpivotValues, TShape>> projection, params UnpivotColumn[] columns)

Parameters

builder EntityBuilder<TEntity>

The query builder to extend.

projection Expression<Func<TEntity, UnpivotValues, TShape>>

The typed result projection declaring the value and name column roles.

columns UnpivotColumn[]

The source columns to unpivot; each column's name becomes its name-column value.

Returns

EntityBuilder<TShape>

A builder over the typed UNPIVOT result.

Type Parameters

TEntity

The inner entity/projection type whose members act as grouping columns.

TShape

The typed result shape declared by projection.

Exceptions

ArgumentNullException

A required argument is null.

ArgumentException

No column is supplied, a role is missing, or a role is declared with conflicting identifiers.

NotSupportedException

The query is rendered by a provider without UNPIVOT support, or the projection reads the inner source as a whole.

BuildSqlCommandException

A grouping member has no registered column name (the inner mapping was never registered).