/// <summary> /// Creates the WHERE clause for this query /// </summary> /// <param name="builder">The builder used by this query</param> /// <param name="hologram">The hologram used as the source of query values</param> /// <returns>A WhereClause object containing the terms of the query</returns> public WhereClause CreateWhereClause(DBQueryBuilder builder, MAObjectHologram hologram) { if (this.DBQueries.Count == 0) { throw new InvalidOperationException("The query group cannot generate a query when no child queries are specified"); } WhereClause parentClause; WhereClause querySubClause; if (this.Operator == GroupOperator.None) { // Workaround to stop SqlOm from dropping the operator when there is only a single term in the where clause parentClause = new WhereClause(WhereClauseRelationship.Not); parentClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.Equal)); querySubClause = new WhereClause(WhereClauseRelationship.Or); parentClause.SubClauses.Add(querySubClause); } else { parentClause = new WhereClause(this.GetWhereClauseRelationship()); querySubClause = parentClause; } IList <DBQueryObject> queryObjects = this.ProcessChildDBQueryObjects(querySubClause, builder, hologram); if (queryObjects.Count == 0) { throw new QueryValueNullException("The query group contained no valid queries to execute"); } else { return(parentClause); } }
public void Delete() { DeleteQuery query = new DeleteQuery("products"); query.WhereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId"), SqlExpression.Number(999), CompareOperator.Equal)); RenderDelete(query); }
WhereTerm CreateRangeTerm(PivotColumn pivotCol, PivotColumnValue pivotColValue) { Range step = pivotColValue.Range; SqlExpression fieldExpr = SqlExpression.Field(pivotCol.ColumnField); if (step.HighBound == null && step.LowBound == null) { throw new PivotTableException("At least one bound of a Range must be set."); } SqlExpression lowBoundExpr = (step.LowBound != null) ? SqlExpression.Constant(pivotCol.DataType, pivotColValue.Range.LowBound) : null; SqlExpression highBoundExpr = (step.HighBound != null) ? SqlExpression.Constant(pivotCol.DataType, pivotColValue.Range.HighBound) : null; WhereTerm term; if (step.HighBound == null) { term = WhereTerm.CreateCompare(fieldExpr, lowBoundExpr, CompareOperator.GreaterOrEqual); } else if (step.LowBound == null) { term = WhereTerm.CreateCompare(fieldExpr, highBoundExpr, CompareOperator.Less); } else { term = WhereTerm.CreateBetween(fieldExpr, lowBoundExpr, highBoundExpr); } return(term); }
static Cond Compare(Expr expr1, Expr expr2, CompCond op) { Cond oper = new Cond(); oper.Term = WhereTerm.CreateCompare(expr1.Expression, expr2.Expression, op); return(oper); }
private string RenderSelect(SelectQuery query, int limitRows) { query.Validate(); StringBuilder selectBuilder = new StringBuilder(); //Start the select statement Select(selectBuilder, query.Distinct); //Render select columns SelectColumns(selectBuilder, query.Columns); FromClause(selectBuilder, query.FromClause, query.TableSpace); WhereClause fullWhereClause = new WhereClause(WhereClauseRelationship.And); fullWhereClause.SubClauses.Add(query.WherePhrase); if (limitRows > -1) { fullWhereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.PseudoField("rownum"), SqlExpression.Number(limitRows), CompareOperator.LessOrEqual)); } Where(selectBuilder, fullWhereClause); WhereClause(selectBuilder, fullWhereClause); OrderBy(selectBuilder, query.OrderByTerms); OrderByTerms(selectBuilder, query.OrderByTerms); return(selectBuilder.ToString()); }
/// <summary> /// Create a sub-select statement for a query condition where the operator specifies that the specified multivalued attribute value must not exist /// </summary> /// <param name="builder">The builder for this query</param> /// <param name="parameterNames">The parameters to evaluate against</param> /// <returns>An SQL SELECT statement</returns> private string CreateSubSelectStatementForMVNotEquals(DBQueryBuilder builder, IList <string> parameterNames) { string paramNameAttribute = builder.AddParameter(this.SearchAttribute.Name); WhereClause subClause = new WhereClause(WhereClauseRelationship.Or); WhereClause existWhere = new WhereClause(WhereClauseRelationship.And); AcmaSchemaAttribute objectIdAttribute = ActiveConfig.DB.GetAttribute("objectId"); existWhere.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("objectId", this.SearchAttribute.DBTable), SqlExpression.Field(objectIdAttribute.Name, objectIdAttribute.DBTable), Reeb.SqlOM.CompareOperator.Equal)); existWhere.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("attributeName", this.SearchAttribute.DBTable), SqlExpression.Parameter(paramNameAttribute), Reeb.SqlOM.CompareOperator.Equal)); foreach (string paramNameValue in parameterNames) { subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, this.SearchAttribute.DBTable), SqlExpression.Parameter(paramNameValue), Reeb.SqlOM.CompareOperator.Equal)); } existWhere.SubClauses.Add(subClause); SelectQuery subQuery = new SelectQuery(); subQuery.Columns.Add(new SelectColumn("objectId")); subQuery.Top = 1; subQuery.FromClause.BaseTable = this.SearchAttribute.DBTable; subQuery.WherePhrase.SubClauses.Add(existWhere); Reeb.SqlOM.Render.SqlServerRenderer render = new Reeb.SqlOM.Render.SqlServerRenderer(); string sql = render.RenderSelect(subQuery); return(sql); }
public void Update() { UpdateQuery query = new UpdateQuery("products"); query.Terms.Add(new UpdateTerm("price", SqlExpression.Number(12.1))); query.Terms.Add(new UpdateTerm("quantaty", SqlExpression.Field("quantaty"))); query.WhereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId"), SqlExpression.Number(1), CompareOperator.Equal)); RenderUpdate(query); }
string RenderSelect(SelectQuery query, int limitRows) { query.Validate(); StringBuilder selectBuilder = new StringBuilder(); //start the select this.Select(selectBuilder, query.IndexHints); //Render the Distinct statement this.Select(selectBuilder, query.Distinct); //Render select columns this.SelectColumns(selectBuilder, query.Columns); this.FromClause(selectBuilder, query.FromClause, query.TableSpace); WhereClause fullWhereClause = new WhereClause(FilterCompositionLogicalOperator.And); if (!query.WherePhrase.IsEmpty) { fullWhereClause.SubClauses.Add(query.WherePhrase); } if (limitRows > -1) { fullWhereClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.PseudoField("rownum"), SqlExpression.Number(limitRows), FilterOperator.IsLessThanOrEqualTo)); } this.Where(selectBuilder, fullWhereClause); this.WhereClause(selectBuilder, fullWhereClause); this.GroupBy(selectBuilder, query.GroupByTerms); if (query.GroupByWithCube) { selectBuilder.Append(" cube ("); } else if (query.GroupByWithRollup) { selectBuilder.Append(" rollup ("); } this.GroupByTerms(selectBuilder, query.GroupByTerms); if (query.GroupByWithCube || query.GroupByWithRollup) { selectBuilder.Append(" )"); } this.Having(selectBuilder, query.HavingPhrase); this.WhereClause(selectBuilder, query.HavingPhrase); this.OrderBy(selectBuilder, query.OrderByTerms); this.OrderByTerms(selectBuilder, query.OrderByTerms); return(selectBuilder.ToString()); }
WhereTerm CreateColumnValueCondition(PivotColumn col, PivotColumnValue val) { if (val.ValueType == PivotColumnValueType.Scalar) { return(WhereTerm.CreateCompare(SqlExpression.Field(col.ColumnField), SqlExpression.Constant(new SqlConstant(col.DataType, val.Value)), CompareOperator.Equal)); } else { return(CreateRangeTerm(col, val)); } }
public override void HandleQuickFilters(poSelectQuery query, List <powFilterField> filterFields) { base.HandleQuickFilters(query, filterFields); foreach (var field in filterFields) { if (field.FieldName == "heName") { var value = $"%{field.FieldValue}%"; query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heName", query.BaseTable), query.ParameterValue(value), CompareOperator.Like)); } } }
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); }
private void AppendLikeExpression(string[] words, SelectQuery selectQuery, WhereClause group, string columnName) { foreach (string word in words) { if (word.Length == 0) { continue; } var parameter = new Parameter("%" + word + "%"); group.Terms.Add( WhereTerm.CreateCompare(SqlExpression.Field(columnName, selectQuery.FromClause.BaseTable), SqlExpression.Parameter(), CompareOperator.Like)); selectQuery.Parameters.Add(parameter); } }
/// <summary> /// Builds the query /// </summary> /// <param name="whereClause">The WHERE conditions</param> /// <param name="maxResults">The number of results to return</param> /// <returns>The constructed SQL query</returns> private string BuildQuery(WhereClause whereClause, int maxResults) { SelectQuery query = new SelectQuery(); AcmaSchemaAttribute objectIdAttribute = ActiveConfig.DB.GetAttribute("objectId"); FromTerm objectBaseTable = objectIdAttribute.DBTable; query.Columns.Add(new SelectColumn("*", objectBaseTable)); query.Distinct = true; query.FromClause.BaseTable = objectBaseTable; foreach (FromTerm term in this.attributeTables) { query.FromClause.Join(JoinType.Left, objectBaseTable, term, new JoinCondition("objectId", "objectId")); } if (maxResults > 0) { query.Top = maxResults; } //if (this.parameters.Count == 0) //{ // return null; //} // Prevents returning the querying object as a search result if (Guid.Empty != this.SourceObjectId) { string param = this.AddParameter(this.SourceObjectId); query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(objectIdAttribute.Name, objectIdAttribute.DBTable), SqlExpression.Parameter(param), CompareOperator.NotEqual)); } if (this.OrderByTerms != null) { query.OrderByTerms.AddRange(this.OrderByTerms); } query.WherePhrase.SubClauses.Add(whereClause); SqlServerRenderer renderer = new SqlServerRenderer(); return(renderer.RenderSelect(query)); }
public virtual void SubQuery() { FromTerm tCustomers = FromTerm.Table("customers"); SelectQuery subQuery = new SelectQuery(); subQuery.Top = 1; subQuery.Columns.Add(new SelectColumn("name", tCustomers)); subQuery.FromClause.BaseTable = FromTerm.Table("customers"); SelectQuery query = new SelectQuery(); query.Columns.Add(new SelectColumn("name", tCustomers)); query.Columns.Add(new SelectColumn(SqlExpression.SubQuery("select count(*) from customers"), "cnt")); query.Columns.Add(new SelectColumn(SqlExpression.SubQuery(subQuery), "subq")); query.FromClause.BaseTable = tCustomers; query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("customerId", tCustomers), SqlExpression.SubQuery("select customerId from customers where name='John'"), CompareOperator.Equal)); RenderSelect(query); }
public virtual void Join5() { FromTerm tCustomers = FromTerm.Table("customers"); 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("name", tProducts)); query.FromClause.BaseTable = tCustomers; query.FromClause.Join(JoinType.Left, tCustomers, tOrders, new JoinCondition("customerId"), new JoinCondition("customerId")); WhereClause condition = new WhereClause(WhereClauseRelationship.Or); condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal)); condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("orderId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal)); query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition); RenderSelect(query); }
public virtual void Parameter() { FromTerm tCustomers = FromTerm.Table("customers"); SelectQuery query = new SelectQuery(); query.Columns.Add(new SelectColumn("name", tCustomers)); query.FromClause.BaseTable = tCustomers; query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Parameter(GetParameterName("pName")), SqlExpression.Field("name", tCustomers), CompareOperator.Equal)); string sql = Renderer.RenderSelect(query); Console.WriteLine(sql); if (connection != null) { IDbCommand command = connection.CreateCommand(); command.CommandText = sql; command.Parameters.Add(CreateParameter("@pName", "John")); command.ExecuteNonQuery(); } }
public virtual void Having() { FromTerm tCustomers = FromTerm.Table("customers"); 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("price", tProducts, "sum", SqlAggregationFunction.Sum)); query.FromClause.BaseTable = tCustomers; query.FromClause.Join(JoinType.Inner, query.FromClause.BaseTable, tOrders, "customerId", "customerId"); query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId"); query.GroupByTerms.Add(new GroupByTerm("name", tCustomers)); query.HavingPhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal)); RenderSelect(query); }
protected override poSelectQuery BuildQuery() { var selQuery = new poSelectQuery("heItems"); selQuery.AddColumn("heID", selQuery.BaseTable, "ID"); selQuery.AddColumn("heCode", selQuery.BaseTable, "heCode"); selQuery.AddColumn("heName", selQuery.BaseTable, "heName"); selQuery.AddColumns("heFactoryCode", "heSeasID", "heAMsntID", "heAuxiliaryCode", "heDetailedDescr", "heClassification", "heKind", "heCompID", "heActive", "heNameSoundex", "heRefNumber", "heIaccID", "heProductionCatID", "heBlockSales", "heBlockPurchases", "heBlockWarehouses", "hePartInStockControl" , "heSalInvAttrSpPrice", "hePurInvAttrSpPrice", "heCat01ID", "heCat02ID", "HECALCFROMVALUE", "heRetailPrice", "heWholeSalesPrice"); selQuery.AddColumns("heSplrID", "heType"); selQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heCompID", selQuery.FromClause.BaseTable), selQuery.ParameterValue(AppContext.GetService <heSystemParams>().CurrentCompanyID), CompareOperator.Equal)); selQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("heType", selQuery.FromClause.BaseTable), selQuery.ParameterValue(Convert.ToInt16(hewItemsDataObjectProxy.ItemTypeEnum.Service)), CompareOperator.Equal)); selQuery.AddEnumColumn(AppContext, selQuery.FromClause.BaseTable, "Hercules;heItems;heKind"); selQuery.AddEnumColumn(AppContext, selQuery.FromClause.BaseTable, "Hercules;heItems;heClassification"); var AMesUnits = FromTerm.Table("heMeasurementUnits", "MU1"); selQuery.AddColumn("heName", AMesUnits, "AMUNAME"); selQuery.FromClause.Join(JoinType.Inner, selQuery.BaseTable, AMesUnits, "heAMsntID", "heID"); var vats = FromTerm.Table("heVATClasses", "Vats"); selQuery.AddColumn("heName", vats, "VTCLNAME"); selQuery.FromClause.Join(JoinType.Inner, selQuery.FromClause.BaseTable, vats, "heVtclID", "heID"); var iaccs = FromTerm.Table("heItemAccCategories", "Iacc"); selQuery.AddColumn("heName", iaccs, "IaccName"); selQuery.FromClause.Join(JoinType.Left, selQuery.FromClause.BaseTable, iaccs, "heIaccID", "heID"); return(selQuery); }
/// <summary> /// Creates a <see cref="SelectQuery"/> which produces drill-down results /// </summary> /// <param name="crossTabRowKey">Value identifying cross-tab's row</param> /// <param name="crossTabColumnName">Name of a cross-tab column</param> /// <returns>A <see cref="SelectQuery"/> which produces drill-down results of the specified cross-tab cell</returns> public SelectQuery BuildDrillDownSql(SqlConstant crossTabRowKey, string crossTabColumnName) { Validate(); SelectQuery query = new SelectQuery(); query.Columns.Add(new SelectColumn("*")); query.FromClause.BaseTable = GetBaseFromTerm(); PivotColumn pivotCol; PivotColumnValue pivotVal; if (!FindPivotColumnValue(crossTabColumnName, out pivotCol, out pivotVal)) { throw new PivotTableException(string.Format("Cross-Tab column '{0}' could not be found in pivot transformation definition.", crossTabColumnName)); } query.WherePhrase.Terms.Add(CreateColumnValueCondition(pivotCol, pivotVal)); if (crossTabRowKey != null) { query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(rowField), SqlExpression.Constant(crossTabRowKey), CompareOperator.Equal)); } return(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); }
/// <summary> /// Creates a WHERE clause for a multivalued attribute /// </summary> /// <param name="builder">The builder for this query</param> /// <param name="parameterNames">The parameters to evaluate against</param> /// <returns>The WhereClause object for this query</returns> private WhereClause CreateWhereClauseforAVPTarget(DBQueryBuilder builder, IList <string> parameterNames) { FromTerm fromTable; fromTable = builder.GetNextAVPTableReference(this.SearchAttribute.TableName); WhereClause clause = new WhereClause(WhereClauseRelationship.And); if (this.Operator != ValueOperator.NotPresent) { string paramName = builder.AddParameter(this.SearchAttribute.Name); WhereTerm attributeNameTerm = WhereTerm.CreateCompare(SqlExpression.Field("attributeName", fromTable), SqlExpression.Parameter(paramName), CompareOperator.Equal); clause.Terms.Add(attributeNameTerm); } if (this.Operator == ValueOperator.IsPresent) { clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable))); } else if (this.Operator == ValueOperator.NotPresent) { string sql = this.CreateSubSelectStatementForMVNotExists(builder); clause.Terms.Add(WhereTerm.CreateNotExists(sql)); } else { WhereClause subClause = new WhereClause(WhereClauseRelationship.Or); switch (this.Operator) { case ValueOperator.And: case ValueOperator.Contains: case ValueOperator.GreaterThan: case ValueOperator.GreaterThanOrEq: case ValueOperator.LessThan: case ValueOperator.StartsWith: case ValueOperator.EndsWith: 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: string sql = this.CreateSubSelectStatementForMVNotEquals(builder, parameterNames); clause.Terms.Add(WhereTerm.CreateNotExists(sql)); break; default: throw new InvalidOperationException(); } clause.SubClauses.Add(subClause); } return(clause); }