/// <summary>Операция NotIn</summary> //public static Cond NotIn(Expr expr, params Constant[] values) //{ // List<Constant> list = new List<Constant>(); // if (values != null) // list.AddRange(values); // return Cond.NotIn(expr, list); //} /// <summary>Операция NotIn</summary> //public static Cond NotIn(string field, From alias, List<Constant> values) //{ // return Cond.NotIn(Expr.Field(field, alias), values); //} /// <summary>Операция NotIn</summary> //public static Cond NotIn(string field, List<Constant> values) //{ // return NotIn(field, null, values); //} /// <summary>Операция NotIn</summary> //public static Cond NotIn(string field, params Constant[] values) //{ // return Cond.NotIn(field, null, values); //} /// <summary>Операция NotIn</summary> //public static Cond NotIn(string field, From alias, params Constant[] values) //{ // return Cond.NotIn(Expr.Field(field, alias), values); //} /// <summary>Операция NotIn</summary> public static Cond NotIn(Expr expr, Select subQuery) { Cond oper = new Cond(); oper.Term = WhereTerm.CreateNotIn(expr.Expression, subQuery.Query); return(oper); }
public static Cond NotIn(Expr expr, List <Constant> values) { Cond oper = new Cond(); OmConstantCollection col = new OmConstantCollection(); for (int i = 0; i < values.Count; i++) { col.Add(values[i].Const); } oper.Term = WhereTerm.CreateNotIn(expr.Expression, col); return(oper); }
public virtual void ComplicatedQuery() { FromTerm tCustomers = FromTerm.Table("customers", "c"); FromTerm tProducts = FromTerm.Table("products", "p"); FromTerm tOrders = FromTerm.Table("orders", "o"); SelectQuery query = new SelectQuery(); query.Columns.Add(new SelectColumn("name", tCustomers)); query.Columns.Add(new SelectColumn(SqlExpression.IfNull(SqlExpression.Field("name", tCustomers), SqlExpression.Constant(SqlConstant.String("name"))), "notNull")); query.Columns.Add(new SelectColumn(SqlExpression.Null(), "nullValue")); query.Columns.Add(new SelectColumn("name", tProducts, "productName", SqlAggregationFunction.None)); query.Columns.Add(new SelectColumn("price", tProducts)); query.FromClause.BaseTable = tCustomers; query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId"); query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId"); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal)); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.String("Dohe"), SqlExpression.Field("name", tCustomers), CompareOperator.NotEqual)); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("J%"), CompareOperator.Like)); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Date(DateTime.Now), SqlExpression.Field("date", tOrders), CompareOperator.Greater)); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.Greater)); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.GreaterOrEqual)); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(11.5), CompareOperator.LessOrEqual)); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.BitwiseAnd)); WhereClause group = new WhereClause(WhereClauseRelationship.Or); group.Terms.Add(WhereTerm.CreateBetween(SqlExpression.Field("price", tProducts), SqlExpression.Number(1), SqlExpression.Number(10))); group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Nail", "Hamer", "Skrewdriver" }))); group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), "select name from products")); group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Unkown" }))); group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), "select name from products")); group.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("name", tProducts))); group.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field("name", tProducts))); group.Terms.Add(WhereTerm.CreateExists("select productId from products")); group.Terms.Add(WhereTerm.CreateNotExists("select productId from products")); query.WherePhrase.SubClauses.Add(group); query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending)); query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending)); query.Distinct = true; query.Top = 10; RenderSelect(query); }
/// <summary> /// Creates a WHERE clause for a single-valued attribute /// </summary> /// <param name="parameterNames">The parameters to evaluate against</param> /// <returns>The WhereClause object for this query</returns> private WhereClause CreateWhereClauseforSVTarget(IList <string> parameterNames) { FromTerm fromTable; fromTable = this.SearchAttribute.DBTable; WhereClause clause = new WhereClause(WhereClauseRelationship.And); if (this.Operator == ValueOperator.IsPresent) { clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable))); } else if (this.Operator == ValueOperator.NotPresent) { clause.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable))); } else if (parameterNames.Count == 0) { throw new ArgumentNullException("parameterNames"); } else if (parameterNames.Count > 1) { WhereClause subClause = new WhereClause(WhereClauseRelationship.Or); switch (this.Operator) { case ValueOperator.And: case ValueOperator.Contains: case ValueOperator.StartsWith: case ValueOperator.EndsWith: case ValueOperator.GreaterThan: case ValueOperator.GreaterThanOrEq: case ValueOperator.LessThan: case ValueOperator.LessThanOrEq: foreach (string parameterName in parameterNames) { subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator)); } break; case ValueOperator.Equals: subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString())); break; case ValueOperator.NotEquals: subClause.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString())); break; default: throw new InvalidOperationException(); } clause.SubClauses.Add(subClause); } else { clause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterNames[0]), this.CompareOperator)); } return(clause); }