Interface IQuerySelectBuilder

A class used for creating and returning a SQL SELECT statement. All tables added are given unique Aliases (as the same table can be added more than once). These aliases must be used in all subsequent references to the added table.

Inherited Members

IQueryBuilderFiltered.RemoveTable(string)

IQueryBuilderFiltered.AddWhereCondition(string, string, SQLOperator, string, string)

IQueryBuilderFiltered.AddWhereCondition(string, string, SQLOperator, string)

IQueryBuilderFiltered.AddWhereIsNullCondition(string, string, string, SQLOperator, string)

IQueryBuilderFiltered.AddWhereIsNull(string, string)

IQueryBuilderFiltered.AddWhereIsNotNull(string, string)

IQueryBuilderFiltered.AddWhereExpression(string)

IQueryBuilderFiltered.RemoveWhereCondition(int)

IQueryBuilderFiltered.AddHavingCondition(AggregateFunction, string, string, SQLOperator, AggregateFunction, string, string)

IQueryBuilderFiltered.AddHavingCondition(AggregateFunction, string, string, SQLOperator, string)

IQueryBuilderFiltered.AddHavingExpression(string)

IQueryBuilderFiltered.BeginWhereGroup(GroupType)

IQueryBuilderFiltered.EndWhereGroup()

IQueryBuilderFiltered.BeginHavingGroup(GroupType)

IQueryBuilderFiltered.EndHavingGroup()

IQueryBuilderFiltered.ToString()

IQueryBuilderFiltered.CurrentWhereGroup

IQueryBuilderFiltered.CurrentHavingGroup

IQueryBuilder.GetCommandText()

IQueryBuilder.GetCommandText(string)

IQueryBuilder.MaxQueryWidth

Namespace: LemonEdge.DataAccess.Core.Abstractions.Builders

Assembly: LemonEdge.DataAccess.Core.Abstractions.dll

Syntax

public interface IQuerySelectBuilder : IQueryBuilderFiltered, IQueryBuilder

Properties

AddOptionRecompile

If true tells sql builder to add option (recompile) to end of select statement By adding 'option (recompile)' to SQL Wrapper execution we ensure that performance is optimised for the parameters passed in (can be 100s of times faster)

Declaration

bool AddOptionRecompile { get; set; }

Property Value

Type	Description
bool

ColumnNames

Returns a list of all the column names returned in this sql select statement

Declaration

IEnumerable<(string Name, string TableAlias, string ColumnSQL, bool AddAtEnd)> ColumnNames { get; }

Property Value

Type	Description
IEnumerable<(string Name, string GroupKey, string FilterName, bool Visible)>

Count

Whether to add COUNT(*).

Declaration

bool Count { get; set; }

Property Value

Type	Description
bool

Distinct

Whether this query has a DISTINCT on it.

Declaration

bool Distinct { get; set; }

Property Value

Type	Description
bool

HasGrouping

Whether any of the selected columns are aggregated.

Declaration

bool HasGrouping { get; }

Property Value

Type	Description
bool

Header

Sets the header.

Declaration

string Header { set; }

Property Value

Type	Description
string

IsWith

Whether this builder has other tables.

Declaration

bool IsWith { get; }

Property Value

Type	Description
bool

OnlyUseFrom

Indicates the sql builder will only produces the sql from and onwards. It will not produce select and any of the columns This is so it can be used for filtering other sql statements, such as update statements.

Declaration

bool OnlyUseFrom { get; set; }

Property Value

Type	Description
bool

SelectIntoTable

Optionally holds a table to select the result set into, rather than just returning it.

Declaration

string SelectIntoTable { get; }

Property Value

Type	Description
string

Methods

AddAllColumns(string)

Adds a alias.* to the select builder to return all columns from that table

Declaration

void AddAllColumns(string tableAlias)

Parameters

Type	Name	Description
string	tableAlias	The table to return all columns from

AddColumn(string, string, AggregateFunction, string, bool, int, string)

Adds a column (of the form t1.c1 As OptionalAlias, or AggFunc(t1.c1) As OptionalAlias) to the result set for the SQL select statement.

Declaration

void AddColumn(string tableAlias, string columnName, AggregateFunction aggFunc = AggregateFunction.None, string columnAlias = "", bool addAtEnd = false, int roundToDecimalPlaces = 0, string isNull = null)

Parameters

Type	Name	Description
string	tableAlias	The alias of the table containing the column to include in the results.
string	columnName	The name of the column in tableAlias to include in the results.
AggregateFunction	aggFunc	Indicates if any aggregate function should be applied to the resulting column.
string	columnAlias	Provides an DatabaseName to refer to the result of the column in the new SQL result set.
bool	addAtEnd	Indicates to add this column at the end of the statement rather than in the order added
int	roundToDecimalPlaces	Indicates the result, when decimal, should be rounded to certain number of decimal places
string	isNull

AddGroupBy(string)

Adds a group by option (of the form expr) to the result set. Mandatory if aggregate functions have been used.

Declaration

void AddGroupBy(string expr)

Parameters

Type	Name	Description
string	expr	The expression to group by.

AddGroupBy(string, string)

Adds a group by option (of the form t1.c1) to the result set. Mandatory if aggregate functions have been used.

Declaration

void AddGroupBy(string tableAlias, string columnName)

Parameters

Type	Name	Description
string	tableAlias	The alias of the table containing the column to group by.
string	columnName	The name of the column in tableAlias to group our results by.

AddJoinCondition(string, string, SQLOperator, string, string, string)

Adds a condition (of the type t1.c1 SQLOperator t2.c2) to the Join statement for a table.

Declaration

int AddJoinCondition(string tableAlias1, string column1, SQLOperator op, string tableAlias2, string column2, string comment)

Parameters

Type	Name	Description
string	tableAlias1	The alias of the table to use in the condition.
string	column1	The name of the column in tableAlias1 to Join too.
SQLOperator	op	The type of SQL comparison operation.
string	tableAlias2	The alias of the second table to use in the comparison of the condition.
string	column2	The name of the column in tableAlias2 to compare to tableAlias1.column1.
string	comment	Comment that will be placed in a comment in the final sql statement.

Returns

Type	Description
int	Returns a unique alias that can be used to refer to this join condition.

AddJoinCondition(string, string, string, SQLOperator, string, string)

Adds a condition (of the type t1.c1 SQLOperator expression) to the Join statement for a table.

Declaration

int AddJoinCondition(string tableAlias1, string tableAlias2, string column1, SQLOperator op, string expr, string comment)

Parameters

Type	Name	Description
string	tableAlias1	The alias of the table to use in the condition.
string	tableAlias2	The alias of the second table being joined too.
string	column1	The name of the column in tableAlias1 to Join too.
SQLOperator	op	The type of SQL comparison operation.
string	expr	Any valid SQL expression to use as the comparison for the Join.
string	comment	Comment that will be placed in a comment in the final sql statement.

Returns

Type	Description
int	Returns a unique alias that can be used to refer to this join condition.

AddJoinExpression(string, string, string, string)

Adds a condition (of the type expression) to the Join statement for a table.

Declaration

int AddJoinExpression(string tableAlias1, string tableAlias2, string expr, string comment)

Parameters

Type	Name	Description
string	tableAlias1	The alias of the table being joined from.
string	tableAlias2	The alias of the second table being joined too.
string	expr	Any valid SQL expression to use as the comparison for the Join.
string	comment	Comment that will be placed in a comment in the final sql statement.

Returns

Type	Description
int	Returns a unique alias that can be used to refer to this join condition.

AddOrderBy(string, Order, bool, SqlDbType)

Adds an order by option of the form (columnAlias Asc|Desc or IsNull(t1.c1, 'zzzzzzzzzz') Asc|Desc) to the result set of the SQL statement.

Declaration

void AddOrderBy(string columnAlias, Order order, bool atEndIfNull = false, SqlDbType dataTypeIfNull = SqlDbType.BigInt)

Parameters

Type	Name	Description
string	columnAlias
Order	order	The alias of the column to order by
bool	atEndIfNull	Indicates that IsNull(tablesAlias.columnName, 'zzzzzzzzzzzzz') should be used to place the result at the end if it is null.
SqlDbType	dataTypeIfNull	Indicates the type of the data to place at the end if null. text would use zzzzzz, numbers would use max numbers, etc.

AddOrderBy(string, string, Order, bool, SqlDbType)

Adds an order by option of the form (t1.c1 Asc|Desc or IsNull(t1.c1, 'zzzzzzzzzz') Asc|Desc) to the result set of the SQL statement.

Declaration

void AddOrderBy(string tableAlias, string columnName, Order order, bool atEndIfNull = false, SqlDbType dataTypeIfNull = SqlDbType.BigInt)

Parameters

Type	Name	Description
string	tableAlias	The alias of the table containg the column to order.
string	columnName	The name of the column in tableAlias to order by
Order	order	The type of SQL comparison operation.
bool	atEndIfNull	Indicates that IsNull(tablesAlias.columnName, 'zzzzzzzzzzzzz') should be used to place the result at the end if it is null.
SqlDbType	dataTypeIfNull	Indicates the type of the data to place at the end if null. text would use zzzzzz, numbers would use max numbers, etc.

AddSelectExpression(string, AggregateFunction, string, bool)

Adds a valid SQL expression (of the form expr As OptionalAlias, or AggFunc(expr) As OptionalAlias) to the result set for the SQL select statement.

Declaration

void AddSelectExpression(string expression, AggregateFunction aggFunc = AggregateFunction.None, string columnAlias = "", bool addAtEndOfColumn = false)

Parameters

Type	Name	Description
string	expression	The SQL expression to include in the results.
AggregateFunction	aggFunc	Indicates if any aggregate function should be applied to the resulting expression.
string	columnAlias	Provides an DatabaseName to refer to the result of the column in the new SQL result set.
bool	addAtEndOfColumn	Indicates the sql statement should add this column to the end of the list of columns in the select statement.

AddTable(string, RelationshipJoinType, bool, bool)

Adds the specified table name to the SQL select statement using the specified Join Type for the statement.

Declaration

string AddTable(string tableName, RelationshipJoinType joinType, bool useCrossApply, bool withNoLock)

Parameters

Type	Name	Description
string	tableName	The name of the table to be added to the SQL Select statement
RelationshipJoinType	joinType	The type of Join to be used when adding this table to the select statement. Can be anything if this is the first table added.
bool	useCrossApply	Indicate the join will be a cross join or outer cross join as opposed to an left/right join
bool	withNoLock	Adds a WITH NO LOCK

Returns

Type	Description
string	A unique alias (as the same table can be added more than once) to be used for all subsequent references to the instance of this table.

AddTable(string, RelationshipJoinType, string, bool)

Adds the specified table name to the SQL select statement using the specified Join Type for the statement.

Declaration

string AddTable(string tableName, RelationshipJoinType joinType, string beforeTableAlias = "", bool withNoLock = false)

Parameters

Type	Name	Description
string	tableName	The name of the table to be added to the SQL Select statement
RelationshipJoinType	joinType	The type of Join to be used when adding this table to the select statement. Can be anything if this is the first table added.
string	beforeTableAlias	Indicates the table alias this table should be placed before in the select builder list
bool	withNoLock

Returns

Type	Description
string	A unique alias (as the same table can be added more than once) to be used for all subsequent references to the instance of this table.

AddTable(string, string, RelationshipJoinType, bool, string, bool)

Adds the specified table name to the SQL select statement using the specified Join Type for the statement.

Declaration

void AddTable(string tableName, string uniqueTableAlias, RelationshipJoinType joinType, bool useCrossApply, string beforeTableAlias = "", bool withNoLock = false)

Parameters

Type	Name	Description
string	tableName	The name of the table to be added to the SQL Select statement
string	uniqueTableAlias	The unique table alias to be assigned to this table for referencing it
RelationshipJoinType	joinType	The type of Join to be used when adding this table to the select statement. Can be anything if this is the first table added.
bool	useCrossApply	Indicate the join will be a cross join or outer cross join as opposed to an left/right join
string	beforeTableAlias	Indicates the table alias this table should be placed before in the select builder list
bool	withNoLock

AddTable(string, string, RelationshipJoinType, string, bool)

Adds the specified table name to the SQL select statement using the specified Join Type for the statement.

Declaration

void AddTable(string tableName, string uniqueTableAlias, RelationshipJoinType joinType, string beforeTableAlias = "", bool withNoLock = false)

Parameters

Type	Name	Description
string	tableName	The name of the table to be added to the SQL Select statement
string	uniqueTableAlias	The unique table alias to be assigned to this table for referencing it
RelationshipJoinType	joinType	The type of Join to be used when adding this table to the select statement. Can be anything if this is the first table added.
string	beforeTableAlias	Indicates the table alias this table should be placed before in the select builder list
bool	withNoLock

AddUnPivot(string, string, string, IEnumerable<string>)

Adds an UNPIVOT to the SQL.

Declaration

void AddUnPivot(string alias, string valueName, string propName, IEnumerable<string> columnNames = null)

Parameters

Type	Name	Description
string	alias	The name / alias.
string	valueName	The value used.
string	propName	The property name.
IEnumerable<string>	columnNames	The list of columns to pivot.

AddUnPivotColumn(string)

Adds a column to the UNPIVOT mechanism.

Declaration

void AddUnPivotColumn(string colName)

Parameters

Type	Name	Description
string	colName	The column name.

AddWithTable(IQuerySelectBuilder, string)

Adds another select builder.

Declaration

void AddWithTable(IQuerySelectBuilder withTable, string cteName)

Parameters

Type	Name	Description
IQuerySelectBuilder	withTable	The IQuerySelectBuilder.
string	cteName	The name of the CTE.

ClearLimit()

Clears the limit set in LimitRowsTo(int, bool).

Declaration

void ClearLimit()

ClearWithTables()

Clears the list of tables in WITH.

Declaration

void ClearWithTables()

Concatenate(IQuerySelectBuilder, SetOperator)

Concatenates with another IQuerySelectBuilder, using SetOperator.

Declaration

void Concatenate(IQuerySelectBuilder sb, SetOperator op)

Parameters

Type	Name	Description
IQuerySelectBuilder	sb	The other IQuerySelectBuilder.
SetOperator	op	The way to concatenate.

GetAliases(string)

Gets the Aliases for the supplied tableName.

Declaration

IEnumerable<string> GetAliases(string tableName)

Parameters

Type	Name	Description
string	tableName	The name of the table.

Returns

Type	Description
IEnumerable<string>

GetColumnSQLFromAlias(string)

ets the column's SQL from the alias.

Declaration

string GetColumnSQLFromAlias(string colAlias)

Parameters

Type	Name	Description
string	colAlias	The alias.

Returns

Type	Description
string	The SQL.

GetColumnSQLFromAliasWithoutTablePrefix(string)

Gets the column SQL without prefix for the supplied alias.

Declaration

string GetColumnSQLFromAliasWithoutTablePrefix(string colAlias)

Parameters

Type	Name	Description
string	colAlias	The alias.

Returns

Type	Description
string	The SQL

GetColumnTableAliasFromColumnAlias(string)

Searches for the alias of the containing table.

Declaration

string GetColumnTableAliasFromColumnAlias(string colAlias)

Parameters

Type	Name	Description
string	colAlias	The column alias.

Returns

Type	Description
string	The alias.

GetLastTableAlias()

Gets the previous table alias.

Declaration

string GetLastTableAlias()

Returns

Type	Description
string

GetNextTableAddedAliasAfter(string)

Gets the alias of the next table added after the table with the given alias

Declaration

string GetNextTableAddedAliasAfter(string alias)

Parameters

Type	Name	Description
string	alias

Returns

Type	Description
string

GetNextTableAlias()

Gets the next table alias.

Declaration

string GetNextTableAlias()

Returns

Type	Description
string

GetTableFromAlias(string)

Declaration

string GetTableFromAlias(string alias)

Parameters

Type	Name	Description
string	alias

Returns

Type	Description
string

GetWithTables()

Returns a list of tables in the WITH.

Declaration

IEnumerable<(IQuerySelectBuilder WithTable, string WithTableAlias)> GetWithTables()

Returns

Type	Description
IEnumerable<(IQuerySelectBuilder WithTable, string WithTableAlias)>	An IEnumerable<T>

HasJoinCondition(string, string, SQLOperator, string, string)

Declaration

bool HasJoinCondition(string tableAlias1, string column1, SQLOperator op, string tableAlias2, string column2)

Parameters

Type	Name	Description
string	tableAlias1
string	column1
SQLOperator	op
string	tableAlias2
string	column2

Returns

Type	Description
bool

LimitRowsTo(int, bool)

Limits the rows returned.

Declaration

void LimitRowsTo(int rowLimit, bool asPercentage)

Parameters

Type	Name	Description
int	rowLimit	The limit.
bool	asPercentage	Whether the rowLimit is %

LimitRowsTo(string, bool)

Limits the rows returned to a variable.

Declaration

void LimitRowsTo(string rowLimitVariable, bool asPercentage)

Parameters

Type	Name	Description
string	rowLimitVariable	The limit.
bool	asPercentage	Whether the rowLimitVariable is %

OffsetRows(int)

Skips amount rows.

Declaration

void OffsetRows(int amount)

Parameters

Type	Name	Description
int	amount	The amount.

PrefixAllTables(string)

Prefixes all table joins. Used to run sql on different database, i.e prefix with dbname would turn [dbo].[table] into [DbName].[dbo].[table]

Declaration

void PrefixAllTables(string prefix)

Parameters

Type	Name	Description
string	prefix	database name prefix, will result in dbo.tbl becoming dbname.dbo.tbl

RemoveAllSelectedColumns()

Removes all selected columns.

Declaration

void RemoveAllSelectedColumns()

RemoveSelectCol(string)

Removes the specified column from the SELECT.

Declaration

void RemoveSelectCol(string colAlias)

Parameters

Type	Name	Description
string	colAlias

ReplaceColumn(string, string, string, AggregateFunction, string, bool, int, string)

Replaces a column.

Declaration

void ReplaceColumn(string aliasOfColumnToReplace, string tableAlias, string columnName, AggregateFunction aggFunc = AggregateFunction.None, string columnAlias = "", bool addAtEnd = false, int roundToDecimalPlaces = 0, string isNull = null)

Parameters

Type	Name	Description
string	aliasOfColumnToReplace	The target column alias.
string	tableAlias	The target table alias.
string	columnName	The new column name.
AggregateFunction	aggFunc	The AggregateFunction.
string	columnAlias	The column alias.
bool	addAtEnd	Whether to move it to the end.
int	roundToDecimalPlaces	Rounding.
string	isNull	Nullability.

ReplaceSelectExpression(string, string, AggregateFunction, string, bool)

Replaces a SELECT expression.

Declaration

void ReplaceSelectExpression(string aliasOfColumnToReplace, string expression, AggregateFunction aggFunc = AggregateFunction.None, string columnAlias = "", bool addAtEndOfColumn = false)

Parameters

Type	Name	Description
string	aliasOfColumnToReplace	The target alias.
string	expression	The new expression.
AggregateFunction	aggFunc	The AggregateFunction.
string	columnAlias	The new column alias.
bool	addAtEndOfColumn	Whether to add at the end.

SetColumnCast(string, string, string)

Adds a CAST (COLUMNNAME as CASTAS) to the column.

Declaration

void SetColumnCast(string tableAlias, string columnName, string castAs)

Parameters

Type	Name	Description
string	tableAlias	The table alias.
string	columnName	The column name.
string	castAs	The cast target type.

SetSelectIntoTable(string, bool)

Sets a table to SELECT INTO.

Declaration

void SetSelectIntoTable(string value, bool createColumnsAsNullable = false)

Parameters

Type	Name	Description
string	value	The table name.
bool	createColumnsAsNullable	Whether to define the columns as nullable.

UpdateAlias(string, string)

Updates the alias to a new one - should be used immediately after addtable

Declaration

string UpdateAlias(string alias, string newAlias)

Parameters

Type	Name	Description
string	alias	The old alias
string	newAlias	The new alias

Returns

Type	Description
string

UpdateTable(string, string)

Updates a table by alias, to the supplied SQL.

Declaration

void UpdateTable(string alias, string tableSQL)

Parameters

Type	Name	Description
string	alias	The alias.
string	tableSQL	The SQL.

UpdateTables(Func<(string Name, bool WithNoLock), (string Name, bool WithNoLock)>)

Runs the provided Func<T, TResult> against all tables.

Declaration

void UpdateTables(Func<(string Name, bool WithNoLock), (string Name, bool WithNoLock)> updateTable)

Parameters

Type	Name	Description
Func<(string Name, bool WithNoLock), (string Name, bool WithNoLock)>	updateTable	The Func.

WrapLastSelectExpression(string, string)

Wraps the last SELECT expression with a pre and post fix.

Declaration

void WrapLastSelectExpression(string prefix, string postfix)

Parameters

Type	Name	Description
string	prefix	The prefix.
string	postfix	The postfix.

Extension Methods

LinqExtensions.AsArray<T>(T)

LinqExtensions.ToArrayOfOne<T>(T)

LinqExtensions.ToListOfOne<T>(T)

MiscExtensions.SetIfNotEqual<T, TP>(T, Expression<Func<T, TP>>, TP)

WeakReferenceExtensions.WeakReference(object)

SQLExtensions.ToSQLValue(object, bool)

ReflectionExtensions.ClearEventInvocations(object, string)

StringExtensions.ToCSVFormatString(object, Type)