Class NPocoSqlExtensions

Provides extension methods to enhance or simplify NPoco SQL operations.

Inheritance

object

Namespace: Umbraco.Extensions

Assembly: Umbraco.Infrastructure.dll

Syntax

public static class NPocoSqlExtensions

Methods

View Source

AndByDescending<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Appends more ORDER BY DESC fields to the Sql statement.

Declaration

public static Sql<ISqlContext> AndByDescending<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>[]	fields	Expressions specifying the fields.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

AndBy<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Appends more ORDER BY or GROUP BY fields to the Sql statement.

Declaration

public static Sql<ISqlContext> AndBy<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>[]	fields	Expressions specifying the fields.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

AndBy<TDto>(Sql<ISqlContext>, string, params Expression<Func<TDto, object?>>[])

Appends additional fields to an existing ORDER BY or GROUP BY clause in the SQL statement, using the specified table alias.

Declaration

public static Sql<ISqlContext> AndBy<TDto>(this Sql<ISqlContext> sql, string tableAlias, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL statement to append fields to.
string	tableAlias	The alias of the table to use for the fields.
Expression<Func<TDto, object>>[]	fields	Expressions specifying the fields to append.

Returns

Type	Description
Sql<ISqlContext>	A NPoco.Sql<> statement with the additional fields appended to the ORDER BY or GROUP BY clause.

Type Parameters

Name	Description
TDto	The type of the DTO (data transfer object).

View Source

AndSelect(Sql<ISqlContext>, params string[])

Adds columns to a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> AndSelect(this Sql<ISqlContext> sql, params string[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string[]	fields	Columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

AndSelectCount(Sql<ISqlContext>, string?)

Adds a COUNT(*) to a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> AndSelectCount(this Sql<ISqlContext> sql, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	alias	An optional alias.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

AndSelectCount<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Adds a COUNT to a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> AndSelectCount<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to count.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to count.

Remarks

If fields is empty, all columns are counted.

View Source

AndSelectCount<TDto>(Sql<ISqlContext>, string?, params Expression<Func<TDto, object?>>[])

Adds a COUNT to a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> AndSelectCount<TDto>(this Sql<ISqlContext> sql, string? alias = null, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	alias	An alias.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to count.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to count.

Remarks

If fields is empty, all columns are counted.

View Source

AndSelect<TDto>(Sql<ISqlContext>, bool, params Expression<Func<TDto, object?>>[])

Adds columns to a SELECT Sql statement, optionally including aliases for each field.

Declaration

public static Sql<ISqlContext> AndSelect<TDto>(this Sql<ISqlContext> sql, bool withAlias = true, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
bool	withAlias	Indicates whether to include aliases for the selected fields. The default value is true.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to select.

Remarks

If fields is empty, all columns are selected.

Exceptions

Type	Condition
ArgumentNullException	Thrown when `sql` is null.

View Source

AndSelect<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Adds columns to a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> AndSelect<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to select.

Remarks

If fields is empty, all columns are selected.

View Source

AndSelect<TDto>(Sql<ISqlContext>, string, params Expression<Func<TDto, object?>>[])

Adds columns to a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> AndSelect<TDto>(this Sql<ISqlContext> sql, string tableAlias, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	tableAlias	A table alias.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to select.

Remarks

If fields is empty, all columns are selected.

View Source

AppendForUpdateHint(Sql<ISqlContext>)

Appends an update hint to the specified NPoco.Sql<> query, indicating that the query is intended for updating records.

Declaration

public static Sql<ISqlContext> AppendForUpdateHint(this Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL query object to which the update hint will be appended.

Returns

Type	Description
Sql<ISqlContext>	A new NPoco.Sql<> instance with the update hint applied.

View Source

AppendSubQuery(Sql<ISqlContext>, Sql<ISqlContext>, string)

Appends a subquery as a derived table with the specified alias to the given SQL query.

Declaration

public static Sql<ISqlContext> AppendSubQuery(this Sql<ISqlContext> sql, Sql<ISqlContext> subQuery, string alias)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The original SQL query to append to.
Sql<ISqlContext>	subQuery	The subquery to append as a derived table.
string	alias	The alias to use for the derived table.

Returns

Type	Description
Sql<ISqlContext>	The modified SQL query with the appended subquery.

View Source

ColumnsForInsert<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[]?)

Gets fields for a Dto.

Declaration

public static string ColumnsForInsert<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[]? fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql
Expression<Func<TDto, object>>[]	fields

Returns

Type	Description
string

Type Parameters

Name	Description
TDto

View Source

Columns<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Gets fields for a Dto.

Declaration

public static string Columns<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
Expression<Func<TDto, object>>[]	fields	Expressions specifying the fields.

Returns

Type	Description
string	The comma-separated list of fields.

Type Parameters

Name	Description
TDto	The type of the Dto.

Remarks

If fields is empty, all fields are selected.

View Source

Columns<TDto>(Sql<ISqlContext>, string, params Expression<Func<TDto, object?>>[])

Gets fields for a Dto.

Declaration

public static string Columns<TDto>(this Sql<ISqlContext> sql, string alias, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	alias	The Dto table alias.
Expression<Func<TDto, object>>[]	fields	Expressions specifying the fields.

Returns

Type	Description
string	The comma-separated list of fields.

Type Parameters

Name	Description
TDto	The type of the Dto.

Remarks

If fields is empty, all fields are selected.

View Source

CrossJoin<TDto>(Sql<ISqlContext>, string?)

Appends a CROSS JOIN clause to the Sql statement.

Declaration

public static Sql<ISqlContext> CrossJoin<TDto>(this Sql<ISqlContext> sql, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
string	alias	An optional alias for the joined table.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

Delete(Sql<ISqlContext>)

Appends a DELETE statement to the specified SQL query.

Declaration

public static Sql<ISqlContext> Delete(this Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The NPoco.Sql<> instance to append the `DELETE` statement to.

Returns

Type	Description
Sql<ISqlContext>	The same NPoco.Sql<> instance with the `DELETE` statement appended.

View Source

Delete<TDto>(Sql<ISqlContext>)

Appends a DELETE statement to the specified SQL query.

Declaration

public static Sql<ISqlContext> Delete<TDto>(this Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The NPoco.Sql<> instance to append the `DELETE` statement to.

Returns

Type	Description
Sql<ISqlContext>	The same NPoco.Sql<> instance with the `DELETE` statement appended.

Type Parameters

Name	Description
TDto

View Source

Delete<TDto>(Sql<ISqlContext>, Expression<Func<TDto, bool>>)

Deletes records from a table based on a predicate.

Declaration

public static Sql<ISqlContext> Delete<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, bool>> predicate)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	SqlConext
Expression<Func<TDto, bool>>	predicate	A predicate to transform and append to the Sql statement (WHERE clause).

Returns

Type	Description
Sql<ISqlContext>

Type Parameters

Name	Description
TDto	Table definition.

View Source

ForUpdate(Sql<ISqlContext>)

Appends the relevant ForUpdate hint.

Declaration

public static Sql<ISqlContext> ForUpdate(this Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Remarks

NOTE: This method will not work for all queries, only simple ones!

View Source

From<TDto>(Sql<ISqlContext>, string?)

Appends a FROM clause to the Sql statement.

Declaration

public static Sql<ISqlContext> From<TDto>(this Sql<ISqlContext> sql, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
string	alias	An optional table alias

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

GetTableName(Type)

Returns the table name specified by the NPoco.TableNameAttribute on the given System.Type.

Declaration

public static string GetTableName(this Type type)

Parameters

Type	Name	Description
Type	type	The System.Type whose table name is to be retrieved.

Returns

Type	Description
string	The table name as a string, or `string.Empty` if the attribute is not present or its value is null or whitespace.

View Source

GroupBy<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>)

Appends a GROUP BY clause to the Sql statement.

Declaration

public static Sql<ISqlContext> GroupBy<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

GroupBy<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Appends a GROUP BY clause to the Sql statement.

Declaration

public static Sql<ISqlContext> GroupBy<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>[]	fields	Expression specifying the fields.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

GroupBy<TDto>(Sql<ISqlContext>, string, params Expression<Func<TDto, object?>>[])

Appends a GROUP BY clause to the Sql statement.

Declaration

public static Sql<ISqlContext> GroupBy<TDto>(this Sql<ISqlContext> sql, string tableAlias, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
string	tableAlias	A table alias.
Expression<Func<TDto, object>>[]	fields	Expression specifying the fields.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

InnerJoin(Sql<ISqlContext>, Sql<ISqlContext>, string?)

Appends an INNER JOIN clause using a nested query.

Declaration

public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> InnerJoin(this Sql<ISqlContext> sql, Sql<ISqlContext> nestedSelect, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL statement.
Sql<ISqlContext>	nestedSelect	The nested sql query.
string	alias	An optional alias for the joined table.

Returns

Type	Description
SqlJoinClause<ISqlContext>	A SqlJoin statement.

View Source

InnerJoinNested(Sql<ISqlContext>, Sql<ISqlContext>, string)

Appends an INNER JOIN clause to the current SQL query, joining with a nested SQL subquery and assigning it an alias.

Declaration

public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> InnerJoinNested(this Sql<ISqlContext> sql, Sql<ISqlContext> nestedQuery, string alias)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The base NPoco.Sql<> instance to which the join will be appended.
Sql<ISqlContext>	nestedQuery	The nested NPoco.Sql<> subquery to join.
string	alias	The alias to assign to the nested subquery in the join clause. The alias will be quoted according to the SQL syntax provider.

Returns

Type	Description
SqlJoinClause<ISqlContext>	A SqlJoinClause<ISqlContext> representing the INNER JOIN with the nested subquery and alias.

View Source

InnerJoin<TDto>(Sql<ISqlContext>, string?)

Appends an INNER JOIN clause to the Sql statement.

Declaration

public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> InnerJoin<TDto>(this Sql<ISqlContext> sql, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
string	alias	An optional alias for the joined table.

Returns

Type	Description
SqlJoinClause<ISqlContext>	A SqlJoin statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

LeftJoin(Sql<ISqlContext>, Sql<ISqlContext>, string?)

Appends an LEFT JOIN clause using a nested query.

Declaration

public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> LeftJoin(this Sql<ISqlContext> sql, Sql<ISqlContext> nestedSelect, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL statement.
Sql<ISqlContext>	nestedSelect	The nested sql query.
string	alias	An optional alias for the joined table.

Returns

Type	Description
SqlJoinClause<ISqlContext>	A SqlJoin statement.

View Source

LeftJoin<TDto>(Sql<ISqlContext>, Func<Sql<ISqlContext>, Sql<ISqlContext>>, string?)

Appends a LEFT JOIN clause to the Sql statement.

Declaration

public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> LeftJoin<TDto>(this Sql<ISqlContext> sql, Func<Sql<ISqlContext>, Sql<ISqlContext>> nestedJoin, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Func<Sql<ISqlContext>, Sql<ISqlContext>>	nestedJoin	A nested join statement.
string	alias	An optional alias for the joined table.

Returns

Type	Description
SqlJoinClause<ISqlContext>	A SqlJoin statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

Remarks

Nested statement produces LEFT JOIN xxx JOIN yyy ON ... ON ...

View Source

LeftJoin<TDto>(Sql<ISqlContext>, string?)

Appends a LEFT JOIN clause to the Sql statement.

Declaration

public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> LeftJoin<TDto>(this Sql<ISqlContext> sql, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
string	alias	An optional alias for the joined table.

Returns

Type	Description
SqlJoinClause<ISqlContext>	A SqlJoin statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

On(SqlJoinClause<ISqlContext>, Func<Sql<ISqlContext>, Sql<ISqlContext>>)

Appends an ON clause to a SqlJoin statement.

Declaration

public static Sql<ISqlContext> On(this Sql<ISqlContext>.SqlJoinClause<ISqlContext> sqlJoin, Func<Sql<ISqlContext>, Sql<ISqlContext>> on)

Parameters

Type	Name	Description
SqlJoinClause<ISqlContext>	sqlJoin	The Sql join statement.
Func<Sql<ISqlContext>, Sql<ISqlContext>>	on	A Sql fragment to use as the ON clause body.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

On<TLeft, TRight>(SqlJoinClause<ISqlContext>, Expression<Func<TLeft, object?>>, Expression<Func<TRight, object?>>)

Appends an ON clause to a SqlJoin statement.

Declaration

public static Sql<ISqlContext> On<TLeft, TRight>(this Sql<ISqlContext>.SqlJoinClause<ISqlContext> sqlJoin, Expression<Func<TLeft, object?>> leftField, Expression<Func<TRight, object?>> rightField)

Parameters

Type	Name	Description
SqlJoinClause<ISqlContext>	sqlJoin	The Sql join statement.
Expression<Func<TLeft, object>>	leftField	An expression specifying the left field.
Expression<Func<TRight, object>>	rightField	An expression specifying the right field.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TLeft	The type of the left Dto.
TRight	The type of the right Dto.

View Source

On<TDto1, TDto2>(SqlJoinClause<ISqlContext>, Expression<Func<TDto1, TDto2, bool>>, string?, string?)

Appends an ON clause to a SqlJoin statement.

Declaration

public static Sql<ISqlContext> On<TDto1, TDto2>(this Sql<ISqlContext>.SqlJoinClause<ISqlContext> sqlJoin, Expression<Func<TDto1, TDto2, bool>> predicate, string? aliasLeft = null, string? aliasRight = null)

Parameters

Type	Name	Description
SqlJoinClause<ISqlContext>	sqlJoin	The SqlJoin statement.
Expression<Func<TDto1, TDto2, bool>>	predicate	A predicate to transform and use as the ON clause body.
string	aliasLeft	An optional alias for Dto 1 table.
string	aliasRight	An optional alias for Dto 2 table.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto1	The type of Dto 1.
TDto2	The type of Dto 2.

View Source

On<TDto1, TDto2, TDto3>(SqlJoinClause<ISqlContext>, Expression<Func<TDto1, TDto2, TDto3, bool>>, string?, string?, string?)

Appends an ON clause to a SqlJoin statement.

Declaration

public static Sql<ISqlContext> On<TDto1, TDto2, TDto3>(this Sql<ISqlContext>.SqlJoinClause<ISqlContext> sqlJoin, Expression<Func<TDto1, TDto2, TDto3, bool>> predicate, string? aliasLeft = null, string? aliasRight = null, string? aliasOther = null)

Parameters

Type	Name	Description
SqlJoinClause<ISqlContext>	sqlJoin	The SqlJoin statement.
Expression<Func<TDto1, TDto2, TDto3, bool>>	predicate	A predicate to transform and use as the ON clause body.
string	aliasLeft	An optional alias for Dto 1 table.
string	aliasRight	An optional alias for Dto 2 table.
string	aliasOther	An optional alias for Dto 3 table.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto1	The type of Dto 1.
TDto2	The type of Dto 2.
TDto3	The type of Dto 3.

View Source

OrderByDescending(Sql<ISqlContext>, params string?[])

Appends an ORDER BY DESC clause to the Sql statement.

Declaration

public static Sql<ISqlContext> OrderByDescending(this Sql<ISqlContext> sql, params string?[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
string[]	fields	Fields.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

OrderByDescending<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>)

Appends an ORDER BY DESC clause to the Sql statement.

Declaration

public static Sql<ISqlContext> OrderByDescending<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

OrderByDescending<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Appends an ORDER BY DESC clause to the Sql statement.

Declaration

public static Sql<ISqlContext> OrderByDescending<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>[]	fields	Expression specifying the fields.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

OrderBy<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>)

Appends an ORDER BY clause to the Sql statement.

Declaration

public static Sql<ISqlContext> OrderBy<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

OrderBy<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, string)

Appends an ORDER BY clause to the Sql statement using the specified field and alias.

Declaration

public static Sql<ISqlContext> OrderBy<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, string alias)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.
string	alias	The alias to use for the field.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

OrderBy<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Appends an ORDER BY clause to the Sql statement.

Declaration

public static Sql<ISqlContext> OrderBy<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>[]	fields	Expression specifying the fields.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

RightJoin<TDto>(Sql<ISqlContext>, string?)

Appends a RIGHT JOIN clause to the Sql statement.

Declaration

public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> RightJoin<TDto>(this Sql<ISqlContext> sql, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
string	alias	An optional alias for the joined table.

Returns

Type	Description
SqlJoinClause<ISqlContext>	A SqlJoin statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

SelectAll(Sql<ISqlContext>)

Creates a SELECT * Sql statement.

Declaration

public static Sql<ISqlContext> SelectAll(this Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

SelectAnyIfExists(Sql<ISqlContext>, Sql<ISqlContext>)

Creates a SELECT CASE WHEN EXISTS query, which returns 1 if the sub query returns any results, and 0 if not.

Declaration

public static Sql<ISqlContext> SelectAnyIfExists(this Sql<ISqlContext> sql, Sql<ISqlContext> nestedSelect)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The original SQL.
Sql<ISqlContext>	nestedSelect	The nested select to run the query against.

Returns

Type	Description
Sql<ISqlContext>	The updated Sql statement.

View Source

SelectClosure<TDto>(Sql<ISqlContext>, Func<SqlConvert<TDto>, SqlConvert<TDto>>)

Adds a SELECT clause to the SQL query based on the specified conversion function and optional alias, and prepends an opening parenthesis to the query. This is typically used for subqueries within "WHERE [column] IN (SELECT ...)" statements.

Declaration

public static Sql<ISqlContext> SelectClosure<TDto>(this Sql<ISqlContext> sql, Func<NPocoSqlExtensions.SqlConvert<TDto>, NPocoSqlExtensions.SqlConvert<TDto>> converts)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL query to modify by adding a SELECT clause and opening parenthesis.
Func<NPocoSqlExtensions.SqlConvert<TDto>, NPocoSqlExtensions.SqlConvert<TDto>>	converts	A function that configures the columns or expressions to be selected in the subquery.

Returns

Type	Description
Sql<ISqlContext>	The modified SQL query with the prepended SELECT clause and opening parenthesis.

Type Parameters

Name	Description
TDto

View Source

SelectCount(Sql<ISqlContext>, string?)

Creates a SELECT COUNT(*) Sql statement.

Declaration

public static Sql<ISqlContext> SelectCount(this Sql<ISqlContext> sql, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	alias	An optional alias.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

SelectCount<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Creates a SELECT COUNT Sql statement.

Declaration

public static Sql<ISqlContext> SelectCount<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to count.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to count.

Remarks

If fields is empty, all columns are counted.

View Source

SelectCount<TDto>(Sql<ISqlContext>, string?, params Expression<Func<TDto, object?>>[])

Creates a SELECT COUNT Sql statement.

Declaration

public static Sql<ISqlContext> SelectCount<TDto>(this Sql<ISqlContext> sql, string? alias, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	alias	An alias.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to count.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to count.

Remarks

If fields is empty, all columns are counted.

View Source

SelectDistinct(Sql<ISqlContext>, params object[])

Creates a SELECT DISTINCT SQL statement for the specified columns.

Declaration

public static Sql<ISqlContext> SelectDistinct(this Sql<ISqlContext> sql, params object[] columns)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The original SQL statement to extend.
object[]	columns	The columns to select distinct values from.

Returns

Type	Description
Sql<ISqlContext>	A new NPoco.Sql<> statement with the `SELECT DISTINCT` clause applied.

Remarks

If columns is empty, all columns are selected.

View Source

SelectDistinct<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Creates a SELECT DISTINCT Sql statement.

Declaration

public static Sql<ISqlContext> SelectDistinct<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to select.

Remarks

If fields is empty, all columns are selected.

View Source

SelectDistinct<TDto>(Sql<ISqlContext>, string, params Expression<Func<TDto, object?>>[])

Creates a SELECT DISTINCT Sql statement.

Declaration

public static Sql<ISqlContext> SelectDistinct<TDto>(this Sql<ISqlContext> sql, string tableAlias, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	tableAlias	A table alias.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to select.

Remarks

If fields is empty, all columns are selected.

View Source

SelectMax<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>)

Adds a SQL SELECT statement to retrieve the maximum value of the specified field from the table associated with the specified DTO type.

Declaration

public static Sql<ISqlContext> SelectMax<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL query builder to which the SELECT statement will be appended. Cannot be null.
Expression<Func<TDto, object>>	field	An expression specifying the field for which the maximum value will be calculated. Cannot be null.

Returns

Type	Description
Sql<ISqlContext>	A modified SQL query builder that includes the SELECT statement for the maximum value of the specified field.

Type Parameters

Name	Description
TDto	The type of the Data Transfer Object (DTO) that represents the table from which the maximum value will be selected.

View Source

SelectMax<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, int)

Adds a SQL SELECT statement to retrieve the maximum value of the specified field from the table associated with the specified DTO type.

Declaration

public static Sql<ISqlContext> SelectMax<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, int coalesceValue)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL query builder to which the SELECT statement will be appended. Cannot be null.
Expression<Func<TDto, object>>	field	An expression specifying the field for which the maximum value will be calculated. Cannot be null.
int	coalesceValue	COALESCE int value.

Returns

Type	Description
Sql<ISqlContext>	A modified SQL query builder that includes the SELECT statement for the maximum value of the specified field or the coalesceValue.

Type Parameters

Name	Description
TDto	The type of the Data Transfer Object (DTO) that represents the table from which the maximum value will be selected.

View Source

SelectSum<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>)

Adds a SQL SELECT statement to retrieve the sum of the values of the specified field from the table associated with the specified DTO type.

Declaration

public static Sql<ISqlContext> SelectSum<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL query builder to which the SELECT statement will be appended. Cannot be null.
Expression<Func<TDto, object>>	field	An expression specifying the field for which the maximum value will be calculated. Cannot be null.

Returns

Type	Description
Sql<ISqlContext>	A modified SQL query builder that includes the SELECT statement for the maximum value of the specified field.

Type Parameters

Name	Description
TDto	The type of the Data Transfer Object (DTO) that represents the table from which the maximum value will be selected.

View Source

SelectTop(Sql<ISqlContext>, int)

Alters a Sql statement to return a maximum amount of rows.

Declaration

public static Sql<ISqlContext> SelectTop(this Sql<ISqlContext> sql, int count)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
int	count	The maximum number of rows to return.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

Select<TDto>(Sql<ISqlContext>, Func<SqlRef<TDto>, SqlRef<TDto>>)

Creates a SELECT Sql statement with a referenced Dto.

Declaration

public static Sql<ISqlContext> Select<TDto>(this Sql<ISqlContext> sql, Func<NPocoSqlExtensions.SqlRef<TDto>, NPocoSqlExtensions.SqlRef<TDto>> reference)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin Sql.
Func<NPocoSqlExtensions.SqlRef<TDto>, NPocoSqlExtensions.SqlRef<TDto>>	reference	An expression specifying the reference.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto to select.

View Source

Select<TDto>(Sql<ISqlContext>, Func<SqlRef<TDto>, SqlRef<TDto>>, Func<Sql<ISqlContext>, Sql<ISqlContext>>)

Creates a SELECT Sql statement with a referenced Dto.

Declaration

public static Sql<ISqlContext> Select<TDto>(this Sql<ISqlContext> sql, Func<NPocoSqlExtensions.SqlRef<TDto>, NPocoSqlExtensions.SqlRef<TDto>> reference, Func<Sql<ISqlContext>, Sql<ISqlContext>> sqlexpr)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin Sql.
Func<NPocoSqlExtensions.SqlRef<TDto>, NPocoSqlExtensions.SqlRef<TDto>>	reference	An expression specifying the reference.
Func<Sql<ISqlContext>, Sql<ISqlContext>>	sqlexpr	An expression to apply to the Sql statement before adding the reference selection.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto to select.

Remarks

The sqlexpr expression applies to the Sql statement before the reference selection is added, so that it is possible to add (e.g. calculated) columns to the referencing Dto.

View Source

Select<TDto>(Sql<ISqlContext>, params Expression<Func<TDto, object?>>[])

Creates a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> Select<TDto>(this Sql<ISqlContext> sql, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to select.

Remarks

If fields is empty, all columns are selected.

View Source

Select<TDto>(Sql<ISqlContext>, string, params Expression<Func<TDto, object?>>[])

Creates a SELECT Sql statement.

Declaration

public static Sql<ISqlContext> Select<TDto>(this Sql<ISqlContext> sql, string tableAlias, params Expression<Func<TDto, object?>>[] fields)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The origin sql.
string	tableAlias	A table alias.
Expression<Func<TDto, object>>[]	fields	Expressions indicating the columns to select.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the DTO to select.

Remarks

If fields is empty, all columns are selected.

View Source

ToText(Sql)

Returns the SQL query string representation of the specified NPoco.Sql object.

Declaration

public static string ToText(this Sql sql)

Parameters

Type	Name	Description
Sql	sql	The NPoco.Sql instance to convert to a SQL string.

Returns

Type	Description
string	A string containing the textual SQL query.

View Source

ToText(Sql, StringBuilder)

Appends the SQL query text represented by the specified NPoco.Sql object to the given System.Text.StringBuilder instance. This method does not return the SQL text; instead, it appends the generated SQL to the provided text parameter.

Declaration

public static void ToText(this Sql sql, StringBuilder text)

Parameters

Type	Name	Description
Sql	sql	The NPoco.Sql object containing the SQL query to convert to text.
StringBuilder	text	The System.Text.StringBuilder instance to which the SQL text will be appended.

View Source

ToText(string?, object[]?, StringBuilder)

Appends a textual representation of the specified SQL query and its arguments to the provided System.Text.StringBuilder.

Declaration

public static void ToText(string? sql, object[]? arguments, StringBuilder text)

Parameters

Type	Name	Description
string	sql	The SQL query string to be represented as text.
object[]	arguments	The arguments to be included in the textual representation of the SQL query.
StringBuilder	text	The System.Text.StringBuilder instance to which the textual representation will be appended.

View Source

Union(Sql<ISqlContext>, Sql<ISqlContext>)

Returns a new SQL query that combines the results of two specified SQL queries using the UNION operator.

Declaration

public static Sql<ISqlContext> Union(this Sql<ISqlContext> sql, Sql<ISqlContext> sql2)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The first SQL query.
Sql<ISqlContext>	sql2	The second SQL query to combine with the first using UNION.

Returns

Type	Description
Sql<ISqlContext>	A NPoco.Sql<> representing the union of the two queries.

View Source

Update(Sql<ISqlContext>)

Appends the UPDATE keyword to the current SQL query.

Declaration

public static Sql<ISqlContext> Update(this Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The NPoco.Sql<> instance to append the `UPDATE` keyword to.

Returns

Type	Description
Sql<ISqlContext>	The same NPoco.Sql<> instance with the `UPDATE` keyword appended.

View Source

Update<TDto>(Sql<ISqlContext>)

Appends an UPDATE statement to the specified SQL query.

Declaration

public static Sql<ISqlContext> Update<TDto>(this Sql<ISqlContext> sql)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL query to which the `UPDATE` statement will be appended.

Returns

Type	Description
Sql<ISqlContext>	The NPoco.Sql<> instance with the appended `UPDATE` statement.

Type Parameters

Name	Description
TDto	The type representing the data transfer object (DTO) for the update operation.

View Source

Update<TDto>(Sql<ISqlContext>, Func<SqlUpd<TDto>, SqlUpd<TDto>>)

Declaration

public static Sql<ISqlContext> Update<TDto>(this Sql<ISqlContext> sql, Func<NPocoSqlExtensions.SqlUpd<TDto>, NPocoSqlExtensions.SqlUpd<TDto>> updates)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql
Func<NPocoSqlExtensions.SqlUpd<TDto>, NPocoSqlExtensions.SqlUpd<TDto>>	updates

Returns

Type	Description
Sql<ISqlContext>

Type Parameters

Name	Description
TDto

View Source

WhereAny(Sql<ISqlContext>, params Func<Sql<ISqlContext>, Sql<ISqlContext>>[])

Appends multiple OR WHERE clauses to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereAny(this Sql<ISqlContext> sql, params Func<Sql<ISqlContext>, Sql<ISqlContext>>[] predicates)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Func<Sql<ISqlContext>, Sql<ISqlContext>>[]	predicates	The WHERE predicates.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

View Source

WhereAnyIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>[], IEnumerable)

Appends multiple OR WHERE IN clauses to the Sql statement.

Declaration

public static Sql WhereAnyIn<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>>[] fields, IEnumerable values)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>[]	fields	Expressions specifying the fields.
IEnumerable	values	The values.

Returns

Type	Description
Sql	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereClosure<TDto>(Sql<ISqlContext>, Expression<Func<TDto, bool>>, string?)

Adds a WHERE clause to the SQL query based on the specified predicate and optional alias, and appends a closing parenthesis to the query. This is used for selects within "WHERE [column] IN (SELECT ...)" statements.

Declaration

public static Sql<ISqlContext> WhereClosure<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, bool>> predicate, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL query to which the WHERE clause will be added.
Expression<Func<TDto, bool>>	predicate	An expression that defines the condition for the WHERE clause.
string	alias	An optional alias to qualify the table or entity in the query. If null, no alias is used.

Returns

Type	Description
Sql<ISqlContext>	The modified SQL query with the appended WHERE clause and closing parenthesis.

Type Parameters

Name	Description
TDto	The type of the data transfer object (DTO) used to define the predicate.

View Source

WhereInOr<TDto, TDto2>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, Expression<Func<TDto2, object?>>, IEnumerable?, IEnumerable?)

Appends a OR IN clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereInOr<TDto, TDto2>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, Expression<Func<TDto2, object?>> field2, IEnumerable? values, IEnumerable? values2)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the first field.
Expression<Func<TDto2, object>>	field2	An expression specifying the second field.
IEnumerable	values	First values.
IEnumerable	values2	Second values.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the first Dto.
TDto2	The type of the second Dto.

View Source

WhereIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, Sql<ISqlContext>?)

Appends a WHERE IN clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereIn<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, Sql<ISqlContext>? values)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.
Sql<ISqlContext>	values	A subquery returning the value.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, Sql<ISqlContext>?, string)

Appends a WHERE IN clause to the specified sql statement, filtering the results where the given field matches any of the provided values.

Declaration

public static Sql<ISqlContext> WhereIn<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, Sql<ISqlContext>? values, string tableAlias)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL statement to which the WHERE IN clause will be appended.
Expression<Func<TDto, object>>	field	An expression specifying the field to filter on.
Sql<ISqlContext>	values	The collection of values to include in the IN clause.
string	tableAlias	The alias of the table to use when qualifying the field in the WHERE IN clause.

Returns

Type	Description
Sql<ISqlContext>	The modified SQL statement with the appended WHERE IN clause.

Type Parameters

Name	Description
TDto	The type of the DTO (data transfer object) being queried.

View Source

WhereIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, IEnumerable?)

Appends a WHERE IN clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereIn<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, IEnumerable? values)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.
IEnumerable	values	The values.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, IEnumerable?, string)

Appends a WHERE IN clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereIn<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, IEnumerable? values, string alias)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.
IEnumerable	values	The values.
string	alias	The table alias for the field.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereLike<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, Sql<ISqlContext>?, string)

This builds a WHERE clause with a LIKE statement where the value is built from a subquery. E.g. for SQL Server: WHERE [field] LIKE CONCAT((SELECT [value] FROM ...), '%'). Or SQLite : WHERE [field] LIKE ((SELECT [value] FROM ...) || '%').

Declaration

public static Sql<ISqlContext> WhereLike<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> fieldSelector, Sql<ISqlContext>? valuesSql, string concatDefault = "")

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	fieldSelector	An expression specifying the field.
Sql<ISqlContext>	valuesSql	The sql object to select the values.
string	concatDefault	If ommitted or empty the specific wildcard char is used as suffix for the resulting values from valueSql query.

Returns

Type	Description
Sql<ISqlContext>

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereLike<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, string)

Adds a WHERE clause to the SQL query that filters results based on a LIKE comparison for the specified field.

Declaration

public static Sql<ISqlContext> WhereLike<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> fieldSelector, string likeValue)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The SQL builder instance to which the WHERE clause will be appended.
Expression<Func<TDto, object>>	fieldSelector	An expression that selects the field of the entity to apply the LIKE filter to.
string	likeValue	The value to use in the LIKE comparison. This can include SQL wildcard characters such as '%' or '_'.

Returns

Type	Description
Sql<ISqlContext>	The updated SQL builder instance with the appended WHERE LIKE clause.

Type Parameters

Name	Description
TDto	The type of the data transfer object representing the table or entity being queried.

Remarks

Use this method to perform pattern matching queries, such as searching for records where a field contains, starts with, or ends with a specified substring. The method does not automatically add wildcard characters; include them in the likeValue parameter as needed.

View Source

WhereNotIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, Sql<ISqlContext>)

Appends a WHERE NOT IN clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereNotIn<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, Sql<ISqlContext> values)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.
Sql<ISqlContext>	values	A subquery returning the value.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereNotIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, IEnumerable)

Appends a WHERE NOT IN clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereNotIn<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, IEnumerable values)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	An expression specifying the field.
IEnumerable	values	The values.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereNotNull<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, string?)

Appends a WHERE NOT NULL clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereNotNull<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, string? tableAlias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	Expression specifying the field.
string	tableAlias	An optional alias for the table.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

WhereNull<TDto>(Sql<ISqlContext>, Expression<Func<TDto, object?>>, string?, bool)

Appends a WHERE [NOT] NULL clause to the Sql statement.

Declaration

public static Sql<ISqlContext> WhereNull<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object?>> field, string? tableAlias = null, bool not = false)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, object>>	field	Expression specifying the field.
string	tableAlias	An optional alias for the table.
bool	not	A value indicating whether to NOT NULL.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

Where<TDto>(Sql<ISqlContext>, Expression<Func<TDto, bool>>, string?)

Appends a WHERE clause to the Sql statement.

Declaration

public static Sql<ISqlContext> Where<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, bool>> predicate, string? alias = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto, bool>>	predicate	A predicate to transform and append to the Sql statement.
string	alias	An optional alias for the table.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto	The type of the Dto.

View Source

Where<TDto1, TDto2>(Sql<ISqlContext>, Expression<Func<TDto1, TDto2, bool>>, string?, string?)

Appends a WHERE clause to the Sql statement.

Declaration

public static Sql<ISqlContext> Where<TDto1, TDto2>(this Sql<ISqlContext> sql, Expression<Func<TDto1, TDto2, bool>> predicate, string? alias1 = null, string? alias2 = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto1, TDto2, bool>>	predicate	A predicate to transform and append to the Sql statement.
string	alias1	An optional alias for Dto 1 table.
string	alias2	An optional alias for Dto 2 table.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto1	The type of Dto 1.
TDto2	The type of Dto 2.

View Source

Where<TDto1, TDto2, TDto3>(Sql<ISqlContext>, Expression<Func<TDto1, TDto2, TDto3, bool>>, string?, string?, string?)

Appends a WHERE clause to the Sql statement.

Declaration

public static Sql<ISqlContext> Where<TDto1, TDto2, TDto3>(this Sql<ISqlContext> sql, Expression<Func<TDto1, TDto2, TDto3, bool>> predicate, string? alias1 = null, string? alias2 = null, string? alias3 = null)

Parameters

Type	Name	Description
Sql<ISqlContext>	sql	The Sql statement.
Expression<Func<TDto1, TDto2, TDto3, bool>>	predicate	A predicate to transform and append to the Sql statement.
string	alias1	An optional alias for Dto 1 table.
string	alias2	An optional alias for Dto 2 table.
string	alias3	An optional alias for Dto 3 table.

Returns

Type	Description
Sql<ISqlContext>	The Sql statement.

Type Parameters

Name	Description
TDto1	The type of Dto 1.
TDto2	The type of Dto 2.
TDto3	The type of Dto 3.