/// <summary>
/// Sets the resulting query to use SELECT DISTINCT.
/// </summary>
/// <param name="query">The query builder instance.</param>
/// <returns></returns>
public static IDbQueryBuilder Distinct(this IDbQueryBuilder query)
{
var fork = query.Fork();
fork.Distinct = true;
return(fork);
}
/// <summary>
/// Adds a set of WHERE clause conditions defined by the given expression, grouped by logical OR.
/// </summary>
/// <param name="query"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static IDbQueryBuilder WhereAny(this IDbQueryBuilder query, Action <IDbQueryWhereClause> expression)
{
query = query.Fork();
var group = new DbQueryWhereClause(query.Root, SqlLogic.Or);
expression.Invoke(group);
query.AddFilter(group.Filters);
return(query);
}
/// <summary>
/// Adds a set of WHERE clause conditions defined by the given expression, grouped by logical AND.
/// </summary>
/// <param name="query"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static IDbQueryBuilder Where(this IDbQueryBuilder query, Action <IDbQueryWhereClause> expression)
{
query = query.Fork();
var group = new DbQueryWhereClause(query.Root, SqlLogic.And);
expression.Invoke(group);
group.ApplyToQuery(query);
return(query);
}
/// <summary>
/// Sets the execution command timeout for the query.
/// </summary>
/// <param name="query">The query builder instance</param>
/// <param name="timeout">The timeout value in seconds.</param>
/// <returns></returns>
public static IDbQueryBuilder Timeout(this IDbQueryBuilder query, int timeout)
{
if (timeout <= 0)
{
throw new ArgumentException(ErrorMessage.TimeoutValueMustBeGreater, nameof(timeout));
}
var fork = query.Fork();
fork.CommandTimeout = timeout;
return(fork);
}
/// <summary>
/// Sets the columns that will be selected on the given query builder and replaces any existing selections.
/// </summary>
/// <param name="queryBuilder"></param>
/// <param name="columns"></param>
/// <remarks>This method replaces any existing selected columns on the query builder.</remarks>
/// <returns></returns>
public static IDbQueryBuilder SelectOnly(this IDbQueryBuilder queryBuilder, params ISqlColumn[] columns)
{
var query = queryBuilder.Fork();
query.SelectColumns.Clear();
foreach (var col in columns)
{
query.SelectColumns.Add(col);
}
return(query);
}
/// <summary>
/// Adds ORDER BY fields to a query for descending sort.
/// </summary>
/// <param name="query">The query builder instance</param>
/// <param name="fields">A set of field expressions to order the query by.</param>
/// <returns></returns>
public static IDbQueryBuilder OrderByDescending(this IDbQueryBuilder query, params string[] fields)
{
var fork = query.Fork();
foreach (var field in fields)
{
fork.OrderBy.Add(new Csg.Data.Sql.SqlOrderColumn()
{
ColumnName = field, SortDirection = Csg.Data.Sql.DbSortDirection.Descending
});
}
return(fork);
}
/// <summary>
/// Adds an arbitrary parameter to the resulting query.
/// </summary>
/// <param name="query">The query builder instance</param>
/// <param name="name">The name of the parameter.</param>
/// <param name="value">The value of the parameter.</param>
/// <param name="dbType">The data type of the parameter.</param>
/// <param name="size">The size of the parameter.</param>
/// <returns></returns>
public static IDbQueryBuilder AddParameter(this IDbQueryBuilder query, string name, object value, DbType dbType, int?size = null)
{
var fork = query.Fork();
fork.Parameters.Add(new DbParameterValue()
{
ParameterName = name,
DbType = dbType,
Value = value,
Size = size
});
return(fork);
}
/// <summary>
/// Adds limit or offset conditions to the query.
/// </summary>
/// <param name="query"></param>
/// <param name="limit">The maximum number of rows to return.</param>
/// <param name="offset">The zero-based index of the first row to return.</param>
/// <returns></returns>
public static IDbQueryBuilder Limit(this IDbQueryBuilder query, int limit = 0, int offset = 0)
{
if (query.OrderBy.Count <= 0)
{
throw new InvalidOperationException(ErrorMessage.LimitOrOffsetWithoutOrderBy);
}
query = query.Fork();
query.PagingOptions = new SqlPagingOptions()
{
Limit = limit,
Offset = offset
};
return(query);
}
/// <summary>
/// Adds a set of WHERE clause conditions by looping over the given collection, and then joining them together with the given logic.
/// </summary>
/// <param name="query"></param>
/// <param name="list"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static IDbQueryBuilder WhereAny <TItem>(this IDbQueryBuilder query, IList <TItem> list, Action <IDbQueryWhereClause, TItem, int> expression)
{
query = query.Fork();
var group = new DbQueryWhereClause(query.Root, SqlLogic.Or);
for (var i = 0; i < list.Count; i++)
{
var innerGroup = new DbQueryWhereClause(query.Root, SqlLogic.And);
expression.Invoke(innerGroup, list[i], i);
group.AddFilter(innerGroup.Filters);
}
query.AddFilter(group.Filters);
return(query);
}
/// <summary>
/// Adds a set of WHERE clause conditions by looping over the given collection, and then joining them together with the given logic.
/// </summary>
/// <param name="query"></param>
/// <param name="collection"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static IDbQueryBuilder WhereAny <TItem>(this IDbQueryBuilder query, IEnumerable <TItem> collection, Action <IDbQueryWhereClause, TItem> expression)
{
query = query.Fork();
var group = new DbQueryWhereClause(query.Root, SqlLogic.Or);
foreach (var item in collection)
{
var innerGroup = new DbQueryWhereClause(query.Root, SqlLogic.And);
expression.Invoke(innerGroup, item);
group.AddFilter(innerGroup.Filters);
}
query.AddFilter(group.Filters);
return(query);
}
/// <summary>
/// Adds the given fields to the selection list, optionally replacing existing selections.
/// </summary>
/// <param name="query">The query builder instance.</param>
/// <param name="fields">A set of field names to select.</param>
/// <param name="replace">If true, replaces any existing selections, if false, adds to existing selectoins.</param>
/// <returns></returns>
public static IDbQueryBuilder Select(this IDbQueryBuilder query, IEnumerable <ISqlColumn> fields, bool replace = false)
{
var fork = query.Fork();
if (replace)
{
query.SelectColumns.Clear();
}
foreach (var field in fields)
{
fork.SelectColumns.Add(field);
}
return(fork);
}
/// <summary>
/// Sets a SQL statment that will be appended to the end of the rendered query after a statement separaeter (semicolon).
/// </summary>
/// <param name="query"></param>
/// <param name="suffix"></param>
/// <returns></returns>
public static IDbQueryBuilder Suffix(this IDbQueryBuilder query, string suffix)
{
query = query.Fork();
query.Suffix = suffix;
return(query);
}
/// <summary>
/// Sets a SQL statement that will be prefixed to the rendered query with a statement separater afterwards. This can be used to set query options.
/// </summary>
/// <param name="query"></param>
/// <param name="prefix"></param>
/// <returns></returns>
public static IDbQueryBuilder Prefix(this IDbQueryBuilder query, string prefix)
{
query = query.Fork();
query.Prefix = prefix;
return(query);
}
