View Source

Class NPocoSqlExtensions

Inheritance

System.Object

Namespace: Umbraco.Extensions

Assembly: Umbraco.Infrastructure.dll

Syntax

public static class NPocoSqlExtensions

Methods

AndBy<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.

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.