Class NPocoSqlExtensions
Inheritance
Namespace: Umbraco.Extensions
Assembly: Umbraco.Infrastructure.dll
Syntax
public static class NPocoSqlExtensionsMethods
View SourceAndBy<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expressions specifying the fields. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
AndBy<TDto>(Sql<ISqlContext>, String, Expression<Func<TDto, Object>>[])
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 | |
| System.String | tableAlias | |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Type Parameters
| Name | Description |
|---|---|
| TDto |
AndByDescending<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expressions specifying the fields. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
AndSelect(Sql<ISqlContext>, 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. |
| System.String[] | fields | Columns to select. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
AndSelect<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
AndSelect<TDto>(Sql<ISqlContext>, String, 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. |
| System.String | tableAlias | A table alias. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
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. |
| System.String | alias | An optional alias. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
AndSelectCount<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
AndSelectCount<TDto>(Sql<ISqlContext>, String, 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. |
| System.String | alias | An alias. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
AppendForUpdateHint(Sql<ISqlContext>)
Declaration
public static Sql<ISqlContext> AppendForUpdateHint(this Sql<ISqlContext> sql)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Columns<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expressions specifying the fields. |
Returns
| Type | Description |
|---|---|
| System.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.
Columns<TDto>(Sql<ISqlContext>, String, 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. |
| System.String | alias | The Dto table alias. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expressions specifying the fields. |
Returns
| Type | Description |
|---|---|
| System.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.
ColumnsForInsert<TDto>(Sql<ISqlContext>, 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 | |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields |
Returns
| Type | Description |
|---|---|
| System.String |
Type Parameters
| Name | Description |
|---|---|
| TDto |
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. |
| System.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. |
Delete(Sql<ISqlContext>)
Declaration
public static Sql<ISqlContext> Delete(this Sql<ISqlContext> sql)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Delete<TDto>(Sql<ISqlContext>)
Declaration
public static Sql<ISqlContext> Delete<TDto>(this Sql<ISqlContext> sql)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Type Parameters
| Name | Description |
|---|---|
| TDto |
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!
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. |
| System.String | alias | An optional table alias |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
GetTableName(Type)
Declaration
public static string GetTableName(this Type type)Parameters
| Type | Name | Description |
|---|---|---|
| Type | type |
Returns
| Type | Description |
|---|---|
| System.String |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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. |
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, params Expression<Func<TDto, object>>[] fields)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql | The Sql statement. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expression specifying the fields. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
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. |
| System.String | alias | An optional alias for the joined table. |
Returns
| Type | Description |
|---|---|
| Sql.SqlJoinClause<ISqlContext> | A SqlJoin statement. |
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. |
| System.String | alias | An optional alias for the joined table. |
Returns
| Type | Description |
|---|---|
| Sql.SqlJoinClause<ISqlContext> | A SqlJoin statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
InnerJoinNested(Sql<ISqlContext>, Sql<ISqlContext>, String)
Declaration
public static Sql<ISqlContext>.SqlJoinClause<ISqlContext> InnerJoinNested(this Sql<ISqlContext> sql, Sql<ISqlContext> nestedQuery, string alias)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql | |
| Sql<ISqlContext> | nestedQuery | |
| System.String | alias |
Returns
| Type | Description |
|---|---|
| Sql.SqlJoinClause<ISqlContext> |
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. |
| System.String | alias | An optional alias for the joined table. |
Returns
| Type | Description |
|---|---|
| Sql.SqlJoinClause<ISqlContext> | A SqlJoin statement. |
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. |
| System.String | alias | An optional alias for the joined table. |
Returns
| Type | Description |
|---|---|
| Sql.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 ...
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. |
| System.String | alias | An optional alias for the joined table. |
Returns
| Type | Description |
|---|---|
| Sql.SqlJoinClause<ISqlContext> | A SqlJoin statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
On(Sql<ISqlContext>.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 |
|---|---|---|
| Sql.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. |
On<TDto1, TDto2>(Sql<ISqlContext>.SqlJoinClause<ISqlContext>, Expression<Func<TDto1, TDto2, Boolean>>, 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 |
|---|---|---|
| Sql.SqlJoinClause<ISqlContext> | sqlJoin | The SqlJoin statement. |
| System.Linq.Expressions.Expression<Func<TDto1, TDto2, System.Boolean>> | predicate | A predicate to transform and use as the ON clause body. |
| System.String | aliasLeft | An optional alias for Dto 1 table. |
| System.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. |
On<TLeft, TRight>(Sql<ISqlContext>.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 |
|---|---|---|
| Sql.SqlJoinClause<ISqlContext> | sqlJoin | The Sql join statement. |
| System.Linq.Expressions.Expression<Func<TLeft, System.Object>> | leftField | An expression specifying the left field. |
| System.Linq.Expressions.Expression<Func<TRight, System.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. |
On<TDto1, TDto2, TDto3>(Sql<ISqlContext>.SqlJoinClause<ISqlContext>, Expression<Func<TDto1, TDto2, TDto3, Boolean>>, 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 |
|---|---|---|
| Sql.SqlJoinClause<ISqlContext> | sqlJoin | The SqlJoin statement. |
| System.Linq.Expressions.Expression<Func<TDto1, TDto2, TDto3, System.Boolean>> | predicate | A predicate to transform and use as the ON clause body. |
| System.String | aliasLeft | An optional alias for Dto 1 table. |
| System.String | aliasRight | An optional alias for Dto 2 table. |
| System.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. |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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. |
OrderBy<TDto>(Sql<ISqlContext>, Expression<Func<TDto, Object>>, String)
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 | |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | field | |
| System.String | alias |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Type Parameters
| Name | Description |
|---|---|
| TDto |
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, params Expression<Func<TDto, object>>[] fields)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql | The Sql statement. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expression specifying the fields. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
OrderByDescending(Sql<ISqlContext>, 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. |
| System.String[] | fields | Fields. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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. |
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, params Expression<Func<TDto, object>>[] fields)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql | The Sql statement. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expression specifying the fields. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
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. |
| System.String | alias | An optional alias for the joined table. |
Returns
| Type | Description |
|---|---|
| Sql.SqlJoinClause<ISqlContext> | A SqlJoin statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
Select<TDto>(Sql<ISqlContext>, Func<NPocoSqlExtensions.SqlRef<TDto>, NPocoSqlExtensions.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. |
Select<TDto>(Sql<ISqlContext>, Func<NPocoSqlExtensions.SqlRef<TDto>, NPocoSqlExtensions.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.
Select<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
Select<TDto>(Sql<ISqlContext>, String, 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. |
| System.String | tableAlias | A table alias. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
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. |
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. |
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. |
| System.String | alias | An optional alias. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
SelectCount<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
SelectCount<TDto>(Sql<ISqlContext>, String, 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. |
| System.String | alias | An alias. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
SelectDistinct(Sql<ISqlContext>, Object[])
Declaration
public static Sql<ISqlContext> SelectDistinct(this Sql<ISqlContext> sql, params object[] columns)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql | |
| System.Object[] | columns |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
SelectDistinct<TDto>(Sql<ISqlContext>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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.
SelectTop(Sql<ISqlContext>, Int32)
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. |
| System.Int32 | count | The maximum number of rows to return. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
ToText(Sql)
Declaration
public static string ToText(this Sql sql)Parameters
| Type | Name | Description |
|---|---|---|
| Sql | sql |
Returns
| Type | Description |
|---|---|
| System.String |
ToText(Sql, StringBuilder)
Declaration
public static void ToText(this Sql sql, StringBuilder text)Parameters
| Type | Name | Description |
|---|---|---|
| Sql | sql | |
| System.Text.StringBuilder | text |
ToText(String, Object[], StringBuilder)
Declaration
public static void ToText(string sql, object[] arguments, StringBuilder text)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | sql | |
| System.Object[] | arguments | |
| System.Text.StringBuilder | text |
Union(Sql<ISqlContext>, Sql<ISqlContext>)
Declaration
public static Sql<ISqlContext> Union(this Sql<ISqlContext> sql, Sql<ISqlContext> sql2)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql | |
| Sql<ISqlContext> | sql2 |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Update(Sql<ISqlContext>)
Declaration
public static Sql<ISqlContext> Update(this Sql<ISqlContext> sql)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Update<TDto>(Sql<ISqlContext>)
Declaration
public static Sql<ISqlContext> Update<TDto>(this Sql<ISqlContext> sql)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Type Parameters
| Name | Description |
|---|---|
| TDto |
Update<TDto>(Sql<ISqlContext>, Func<NPocoSqlExtensions.SqlUpd<TDto>, NPocoSqlExtensions.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 |
Where<TDto>(Sql<ISqlContext>, Expression<Func<TDto, Boolean>>, 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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Boolean>> | predicate | A predicate to transform and append to the Sql statement. |
| System.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. |
Where<TDto1, TDto2>(Sql<ISqlContext>, Expression<Func<TDto1, TDto2, Boolean>>, 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. |
| System.Linq.Expressions.Expression<Func<TDto1, TDto2, System.Boolean>> | predicate | A predicate to transform and append to the Sql statement. |
| System.String | alias1 | An optional alias for Dto 1 table. |
| System.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. |
WhereAny(Sql<ISqlContext>, 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. |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>>[] | fields | Expressions specifying the fields. |
| System.Collections.IEnumerable | values | The values. |
Returns
| Type | Description |
|---|---|
| Sql | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | field | An expression specifying the field. |
| System.Collections.IEnumerable | values | The values. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
WhereIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, Object>>, Nullable<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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | field | An expression specifying the field. |
| System.Nullable<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. |
WhereIn<TDto>(Sql<ISqlContext>, Expression<Func<TDto, Object>>, Nullable<Sql<ISqlContext>>, String)
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 | |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | field | |
| System.Nullable<Sql<ISqlContext>> | values | |
| System.String | tableAlias |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Type Parameters
| Name | Description |
|---|---|
| TDto |
WhereLike<TDto>(Sql<ISqlContext>, Expression<Func<TDto, Object>>, Nullable<Sql<ISqlContext>>)
Declaration
public static Sql<ISqlContext> WhereLike<TDto>(this Sql<ISqlContext> sql, Expression<Func<TDto, object>> fieldSelector, Sql<ISqlContext>? valuesSql)Parameters
| Type | Name | Description |
|---|---|---|
| Sql<ISqlContext> | sql | |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | fieldSelector | |
| System.Nullable<Sql<ISqlContext>> | valuesSql |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Type Parameters
| Name | Description |
|---|---|
| TDto |
WhereLike<TDto>(Sql<ISqlContext>, Expression<Func<TDto, Object>>, String)
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 | |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | fieldSelector | |
| System.String | likeValue |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> |
Type Parameters
| Name | Description |
|---|---|
| TDto |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.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. |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | field | An expression specifying the field. |
| System.Collections.IEnumerable | values | The values. |
Returns
| Type | Description |
|---|---|
| Sql<ISqlContext> | The Sql statement. |
Type Parameters
| Name | Description |
|---|---|
| TDto | The type of the Dto. |
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | field | Expression specifying the field. |
| System.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. |
WhereNull<TDto>(Sql<ISqlContext>, Expression<Func<TDto, Object>>, String, Boolean)
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. |
| System.Linq.Expressions.Expression<Func<TDto, System.Object>> | field | Expression specifying the field. |
| System.String | tableAlias | An optional alias for the table. |
| System.Boolean | 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. |