/// <summary>
/// Applies filters to the given where clause and joins them together in OR logic.
/// </summary>
/// <param name="where"></param>
/// <param name="whereClause"></param>
/// <returns></returns>
public static IDbQueryWhereClause Any(this Csg.Data.IDbQueryWhereClause where, Action <IDbQueryWhereClause> whereClause)
{
var orWhere = new Csg.Data.DbQueryWhereClause(where.Root, SqlLogic.Or);
whereClause(orWhere);
where.AddFilter(orWhere.Filters);
return(where);
}
/// <summary>
/// Adds a (SELECT COUNT({countFieldName}) FROM {sqlText} WHERE {subQueryConditions}) {subQueryOperator} {countValue} condition to the query;
/// </summary>
/// <param name="where"></param>
/// <param name="sqlText"></param>
/// <param name="countColumnName"></param>
/// <param name="operator"></param>
/// <param name="countValue"></param>
/// <param name="subQueryWhere"></param>
/// <returns></returns>
public static IDbQueryWhereClause SubQueryCount(this IDbQueryWhereClause where, string sqlText, string countColumnName, SqlOperator @operator, int countValue, Action <IDbQueryWhereClause> subQueryWhere)
{
var sqf = new SqlCountFilter(where.Root, SqlTable.Create(sqlText), countColumnName, @operator, countValue);
var sqfWhere = new DbQueryWhereClause(sqf.SubQueryTable, SqlLogic.And);
subQueryWhere(sqfWhere);
sqfWhere.ApplyTo(sqf.SubQueryFilters);
where.AddFilter(sqf);
return(where);
}
/// <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>
/// Adds an EXISTS(selectStatement) filter critera.
/// </summary>
/// <param name="where"></param>
/// <param name="innerQuery">The value to render as the inner SELECT statement</param>
/// <returns></returns>
public static IDbQueryWhereClause Exists(this IDbQueryWhereClause where, string sqlText, Action <IDbQueryWhereClause> subQueryFilters)
{
var innerTable = SqlTable.Create(sqlText);
var innerQuery = new SqlSelectBuilder(innerTable);
var innerWhere = new DbQueryWhereClause(innerTable, SqlLogic.And);
innerQuery.Columns.Add(new SqlRawColumn("1"));
subQueryFilters(innerWhere);
innerWhere.ApplyTo(innerQuery.Filters);
where.AddFilter(new Sql.SqlExistFilter(innerQuery));
return(where);
}
/// <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 a {columnName} IN | NOT IN (SELECT {subQueryColumnName} FROM {sqlText} WHERE {SubQueryConditions})
/// </summary>
/// <param name="where"></param>
/// <param name="columnName"></param>
/// <param name="sqlText"></param>
/// <param name="subQueryColumnName"></param>
/// <param name="condition"></param>
/// <param name="subQueryFilters"></param>
/// <returns></returns>
internal static IDbQueryWhereClause FieldSubQuery(IDbQueryWhereClause where, string columnName, string sqlText, string subQueryColumnName, SubQueryMode condition, Action <IDbQueryWhereClause> subQueryFilters)
{
var subQueryFilter = new Csg.Data.Sql.SqlSubQueryFilter(where.Root, SqlTable.Create(sqlText))
{
ColumnName = columnName,
Condition = condition,
SubQueryColumn = subQueryColumnName,
};
var subQueryWhere = new DbQueryWhereClause(subQueryFilter.SubQueryTable, SqlLogic.And);
subQueryFilters(subQueryWhere);
subQueryWhere.ApplyTo(subQueryFilter.SubQueryFilters);
where.AddFilter(subQueryFilter);
return(where);
}
