/// <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); }