static SqlFunction ConvertCase(bool canBeNull, Type systemType, ISqlExpression[] parameters, int start) { var len = parameters.Length - start; var name = start == 0 ? "IIF" : "CASE"; var cond = parameters[start]; if (start == 0 && SqlExpression.NeedsEqual(cond)) { cond = new SqlSearchCondition( new SqlCondition( false, new SqlPredicate.ExprExpr(cond, SqlPredicate.Operator.Equal, new SqlValue(1), null))); } if (len == 3) { return new SqlFunction(systemType, name, cond, parameters[start + 1], parameters[start + 2]) { CanBeNull = canBeNull } } ; return(new SqlFunction(systemType, name, cond, parameters[start + 1], ConvertCase(canBeNull, systemType, parameters, start + 2)) { CanBeNull = canBeNull }); } }
void AddSearchConditions(SqlSearchCondition search, IEnumerable <SqlCondition> conditions) { if (_additionalFilter == null) { _additionalFilter = new Dictionary <SqlSearchCondition, SqlSearchCondition>(); } if (!_additionalFilter.TryGetValue(search, out var value)) { if (search.Conditions.Count > 0 && search.Precedence < Precedence.LogicalConjunction) { value = new SqlSearchCondition(); var prev = new SqlSearchCondition(); prev.Conditions.AddRange(search.Conditions); search.Conditions.Clear(); search.Conditions.Add(new SqlCondition(false, value, false)); search.Conditions.Add(new SqlCondition(false, prev, false)); } else { value = search; } _additionalFilter.Add(search, value); } value.Conditions.AddRange(conditions); }
//this is for Tests.Linq.Common.CoalesceLike test static SqlFunction ConvertCase(SqlFunction?func, Type systemType, ISqlExpression[] parameters, int start) { var len = parameters.Length - start; var cond = parameters[start]; if (start == 0 && SqlExpression.NeedsEqual(cond)) { cond = new SqlSearchCondition( new SqlCondition( false, new SqlPredicate.ExprExpr(cond, SqlPredicate.Operator.Equal, new SqlValue(1), null))); } const string name = "CASE"; if (len == 3) { if (func != null && start == 0 && ReferenceEquals(parameters[start], cond)) { return(func); } return(new SqlFunction(systemType, name, cond, parameters[start + 1], parameters[start + 2])); } return(new SqlFunction(systemType, name, cond, parameters[start + 1], ConvertCase(null, systemType, parameters, start + 2))); }
void OptimizeSearchCondition(SqlSearchCondition searchCondition) { var items = searchCondition.Conditions; if (items.Any(c => c.IsOr)) { return; } for (var i1 = 0; i1 < items.Count; i1++) { var c1 = items[i1]; var cmp = EvaluateLogical(c1); if (cmp != null) { if (cmp.Value) { items.RemoveAt(i1); --i1; continue; } } switch (c1.ElementType) { case QueryElementType.Condition: case QueryElementType.SearchCondition: { var search = c1.Predicate as SqlSearchCondition; if (search != null) { OptimizeSearchCondition(search); if (search.Conditions.Count == 0) { items.RemoveAt(i1); --i1; continue; } } break; } } for (var i2 = i1 + 1; i2 < items.Count; i2++) { var c2 = items[i2]; if (CompareConditions(c2, c1)) { searchCondition.Conditions.RemoveAt(i2); --i2; } } } }
protected override bool BuildJoinType(JoinType joinType, SqlSearchCondition condition) { switch (joinType) { case JoinType.CrossApply: StringBuilder.Append("INNER JOIN LATERAL "); return(true); case JoinType.OuterApply: StringBuilder.Append("LEFT JOIN LATERAL "); return(true); } return(base.BuildJoinType(joinType, condition)); }
public virtual void Visiting(SqlSearchCondition searchCondition) { if (searchCondition.Negative) { sqlBuilder.Append("Not "); } if (searchCondition.PrevSqlSearchCondition == null) { sqlBuilder.Append("("); } }
public override ISqlPredicate ConvertPredicateImpl(ISqlPredicate predicate, ConvertVisitor <RunOptimizationContext> visitor) { switch (predicate.ElementType) { case QueryElementType.ExprExprPredicate: { var expr = (SqlPredicate.ExprExpr)predicate; // Oracle saves empty string as null to database, so we need predicate modification before sending query // if (expr.WithNull == true && (expr.Operator == SqlPredicate.Operator.Equal || expr.Operator == SqlPredicate.Operator.NotEqual || expr.Operator == SqlPredicate.Operator.GreaterOrEqual || expr.Operator == SqlPredicate.Operator.LessOrEqual)) { if (expr.Expr1.SystemType == typeof(string) && expr.Expr1.TryEvaluateExpression(visitor.Context.OptimizationContext.Context, out var value1) && value1 is string string1) { if (string1 == "") { var sc = new SqlSearchCondition(); sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.ExprExpr(expr.Expr1, expr.Operator, expr.Expr2, null), true)); sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(expr.Expr2, false), true)); return(sc); } } if (expr.Expr2.SystemType == typeof(string) && expr.Expr2.TryEvaluateExpression(visitor.Context.OptimizationContext.Context, out var value2) && value2 is string string2) { if (string2 == "") { var sc = new SqlSearchCondition(); sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.ExprExpr(expr.Expr1, expr.Operator, expr.Expr2, null), true)); sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(expr.Expr1, false), true)); return(sc); } } } break; } } predicate = base.ConvertPredicateImpl(predicate, visitor); return(predicate); }
protected ISqlExpression AlternativeExists(SqlFunction func) { var query = (SelectQuery)func.Parameters[0]; if (query.Select.Columns.Count == 0) { query.Select.Columns.Add(new SqlColumn(query, new SqlExpression("'.'"))); } query.Select.Take(1, null); var sc = new SqlSearchCondition(); sc.Conditions.Add( new SqlCondition(false, new SqlPredicate.IsNull(query, true))); return(sc); }
protected override ISqlExpression ConvertFunction(SqlFunction func) { func = ConvertFunctionParameters(func, false); switch (func.Name) { case "CASE": if (func.Parameters.Length <= 5) { func = ConvertCase(func.CanBeNull, func.SystemType, func.Parameters, 0); } break; case "Coalesce": if (func.Parameters.Length > 2) { var parms = new ISqlExpression[func.Parameters.Length - 1]; Array.Copy(func.Parameters, 1, parms, 0, parms.Length); func = new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms)); break; } var sc = new SqlSearchCondition(); sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(func.Parameters[0], false))); func = new SqlFunction(func.SystemType, "IIF", sc, func.Parameters[1], func.Parameters[0]) { CanBeNull = func.CanBeNull }; break; } return(base.ConvertFunction(func)); }
public virtual void GetListPropertySubselectAndAlias(IPropertyMap propertyMap, object hash, Hashtable columns, ArrayList order, string propPath, string suggestion) { if (hash == null) { hash = propertyMap; } ITableMap listTableMap = propertyMap.MustGetTableMap(); ITableMap parentTableMap = propertyMap.ClassMap.MustGetTableMap(); SqlTableAlias parentTable = sqlEmitter.GetTableAlias(parentTableMap, hash); SqlSelectStatement subSelect = new SqlSelectStatement(parentTableMap.SourceMap); //Hmmm....can an alias be redefined in a subselect? //SqlTableAlias listTable = subSelect.GetSqlTableAlias(listTableMap, "cnt" + subSelect.GetNextTableAliasIndex()); SqlTableAlias listTable = subSelect.GetSqlTableAlias(listTableMap, "cnt" + sqlEmitter.Select.GetNextTableAliasIndex()); SqlCountFunction count = new SqlCountFunction(); subSelect.SqlSelectClause.AddSqlAliasSelectListItem(count); subSelect.SqlFromClause.AddSqlAliasTableSource(listTable); foreach (IColumnMap fkIdColumnMap in propertyMap.GetAllIdColumnMaps()) { IColumnMap idColumnMap = fkIdColumnMap.MustGetPrimaryKeyColumnMap(); SqlColumnAlias fkIdColumn = listTable.GetSqlColumnAlias(fkIdColumnMap); SqlColumnAlias idColumn = parentTable.GetSqlColumnAlias(idColumnMap); SqlSearchCondition search = subSelect.SqlWhereClause.GetNextSqlSearchCondition(); search.GetSqlComparePredicate(fkIdColumn, SqlCompareOperatorType.Equals, idColumn); } if (suggestion == "") { suggestion = propPath; } SqlAliasSelectListItem countAlias = this.sqlEmitter.Select.SqlSelectClause.AddSqlAliasSelectListItem(subSelect, suggestion); this.sqlEmitter.PropertyColumnMap[propPath] = countAlias.SqlExpressionAlias.Alias; }
public virtual void Visited(SqlSearchCondition searchCondition) { if (searchCondition.NextSqlSearchCondition != null) { if (searchCondition.SqlPredicate != null || searchCondition.SubSqlSearchCondition != null) { if (searchCondition.OrNext) { sqlBuilder.Append(" Or "); } else { sqlBuilder.Append(" And "); } } } else { sqlBuilder.Append(")"); } }
internal static void BuildJoin( ExpressionBuilder builder, SqlSearchCondition condition, IBuildContext outerKeyContext, Expression outerKeySelector, IBuildContext innerKeyContext, Expression innerKeySelector) { var predicate = builder.ConvertObjectComparison( ExpressionType.Equal, outerKeyContext, outerKeySelector, innerKeyContext, innerKeySelector); if (predicate == null) { predicate = new SqlPredicate.ExprExpr( builder.ConvertToSql(outerKeyContext, outerKeySelector), SqlPredicate.Operator.Equal, builder.ConvertToSql(innerKeyContext, innerKeySelector), Common.Configuration.Linq.CompareNullsAsValues ? true : null); } condition.Conditions.Add(new SqlCondition(false, predicate)); }
private void JoinNonPrimary(IPropertyMap iPropertyMap) { foreach (IColumnMap idColumn in iPropertyMap.GetAllIdColumnMaps()) { SqlTableAlias thisTableAlias = this.sqlEmitter.Select.GetSqlTableAlias(idColumn.TableMap.Name); SqlColumnAlias thisColAlias = thisTableAlias.GetSqlColumnAlias(idColumn.Name); SqlTableAlias parentTableAlias = this.sqlEmitter.Select.GetSqlTableAlias(idColumn.PrimaryKeyTable); SqlColumnAlias parentColAlias = parentTableAlias.GetSqlColumnAlias(idColumn.PrimaryKeyColumn); if (!joinedNonPrimaries.ContainsKey(thisColAlias)) { if (!(joinedNonPrimaries[thisColAlias] == parentColAlias)) { SqlSearchCondition search = this.sqlEmitter.Select.SqlWhereClause.GetNextSqlSearchCondition(); search.GetSqlComparePredicate(parentColAlias, SqlCompareOperatorType.Equals, thisColAlias); joinedNonPrimaries[thisColAlias] = parentColAlias; } } } }
protected override void BuildWhereSearchCondition(SelectQuery selectQuery, SqlSearchCondition condition) { if (NeedTake(selectQuery) && !NeedSkip(selectQuery) && selectQuery.OrderBy.IsEmpty && selectQuery.Having.IsEmpty) { BuildPredicate( Precedence.LogicalConjunction, new SqlPredicate.ExprExpr( new SqlExpression(null, "ROWNUM", Precedence.Primary), SqlPredicate.Operator.LessOrEqual, selectQuery.Select.TakeValue)); if (base.BuildWhere(selectQuery)) { StringBuilder.Append(" AND "); BuildSearchCondition(Precedence.LogicalConjunction, condition); } } else { BuildSearchCondition(Precedence.Unknown, condition); } }
private void AddConditions(SqlWhereClause where, ISqlTableSource table) { var keys = table.GetKeys(true); if (keys == null) { return; } foreach (var key in keys.OfType <SqlField>()) { var maxValue = GetMaxValue(key.DataType); if (maxValue == null) { continue; } var cond = new SqlSearchCondition(); cond = cond.Expr(key).IsNull.Or; if (maxValue is string) { cond.Expr(key).GreaterOrEqual.Expr(new SqlValue(maxValue)); } else { cond.Expr(key).LessOrEqual.Expr(new SqlValue(maxValue)); } where.ConcatSearchCondition(cond); // only one field is enough break; } }
public override ISqlPredicate ConvertSearchStringPredicate(SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext> visitor) { var like = base.ConvertSearchStringPredicate(predicate, visitor); if (predicate.CaseSensitive.EvaluateBoolExpression(visitor.Context.OptimizationContext.Context) == true) { SqlPredicate.ExprExpr?subStrPredicate = null; switch (predicate.Kind) { case SqlPredicate.SearchString.SearchKind.StartsWith: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction( typeof(string), "LEFT", predicate.Expr1, new SqlFunction(typeof(int), "Length", predicate.Expr2))), SqlPredicate.Operator.Equal, new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2), null ); break; } case SqlPredicate.SearchString.SearchKind.EndsWith: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction( typeof(string), "RIGHT", predicate.Expr1, new SqlFunction(typeof(int), "Length", predicate.Expr2))), SqlPredicate.Operator.Equal, new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2), null ); break; } case SqlPredicate.SearchString.SearchKind.Contains: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(int), "CHARINDEX", new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2), new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr1)), SqlPredicate.Operator.Greater, new SqlValue(0), null); break; } } if (subStrPredicate != null) { var result = new SqlSearchCondition( new SqlCondition(false, like, predicate.IsNot), new SqlCondition(predicate.IsNot, subStrPredicate)); return(result); } } return(like); }
void AddSearchCondition(SqlSearchCondition search, SqlCondition condition) { AddSearchConditions(search, new[] { condition }); }
protected override void BuildFunction(SqlFunction func) { switch (func.Name) { case "Coalesce": if (func.Parameters.Length > 2) { var parms = new ISqlExpression[func.Parameters.Length - 1]; Array.Copy(func.Parameters, 1, parms, 0, parms.Length); BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms))); return; } var sc = new SqlSearchCondition(); sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(func.Parameters[0], false))); func = new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]); break; case "CASE": func = ConvertCase(func.SystemType, func.Parameters, 0); break; case "CharIndex": func = func.Parameters.Length == 2? new SqlFunction(func.SystemType, "InStr", new SqlValue(1), func.Parameters[1], func.Parameters[0], new SqlValue(1)): new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1)); break; case "Convert": switch (func.SystemType.ToUnderlying().GetTypeCodeEx()) { case TypeCode.String: func = new SqlFunction(func.SystemType, "CStr", func.Parameters[1]); break; case TypeCode.DateTime: if (IsDateDataType(func.Parameters[0], "Date")) { func = new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]); } else if (IsTimeDataType(func.Parameters[0])) { func = new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]); } else { func = new SqlFunction(func.SystemType, "CDate", func.Parameters[1]); } break; default: if (func.SystemType == typeof(DateTime)) { goto case TypeCode.DateTime; } BuildExpression(func.Parameters[1]); return; } break; } base.BuildFunction(func); }
public override ISqlPredicate ConvertSearchStringPredicate <TContext>(MappingSchema mappingSchema, SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext <TContext> > visitor, OptimizationContext optimizationContext) { var like = ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor, optimizationContext); if (predicate.CaseSensitive.EvaluateBoolExpression(optimizationContext.Context) == true) { SqlPredicate.ExprExpr?subStrPredicate = null; switch (predicate.Kind) { case SqlPredicate.SearchString.SearchKind.StartsWith: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(string), "Substr", predicate.Expr1, new SqlValue(1), new SqlFunction(typeof(int), "Length", predicate.Expr2)), SqlPredicate.Operator.Equal, predicate.Expr2, null); break; } case SqlPredicate.SearchString.SearchKind.EndsWith: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(string), "Substr", predicate.Expr1, new SqlBinaryExpression(typeof(int), new SqlFunction(typeof(int), "Length", predicate.Expr2), "*", new SqlValue(-1), Precedence.Multiplicative) ), SqlPredicate.Operator.Equal, predicate.Expr2, null); break; } case SqlPredicate.SearchString.SearchKind.Contains: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(int), "InStr", predicate.Expr1, predicate.Expr2), SqlPredicate.Operator.Greater, new SqlValue(0), null); break; } } if (subStrPredicate != null) { var result = new SqlSearchCondition( new SqlCondition(false, like, predicate.IsNot), new SqlCondition(predicate.IsNot, subStrPredicate)); return(result); } } return(like); }
public override ISqlPredicate ConvertSearchStringPredicate <TContext>(MappingSchema mappingSchema, SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext <TContext> > visitor, OptimizationContext optimizationContext) { var like = ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor, optimizationContext); if (predicate.CaseSensitive.EvaluateBoolExpression(optimizationContext.Context) == true) { SqlPredicate.ExprExpr?subStrPredicate = null; switch (predicate.Kind) { case SqlPredicate.SearchString.SearchKind.StartsWith: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction(typeof(string), "SUBSTRING", predicate.Expr1, new SqlValue(1), new SqlFunction(typeof(int), "Length", predicate.Expr2))), SqlPredicate.Operator.Equal, new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2), null ); break; } case SqlPredicate.SearchString.SearchKind.EndsWith: { var indexExpression = new SqlBinaryExpression(typeof(int), new SqlBinaryExpression(typeof(int), new SqlFunction(typeof(int), "Length", predicate.Expr1), "-", new SqlFunction(typeof(int), "Length", predicate.Expr2)), "+", new SqlValue(1)); subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction(typeof(string), "SUBSTRING", predicate.Expr1, indexExpression, new SqlFunction(typeof(int), "Length", predicate.Expr2))), SqlPredicate.Operator.Equal, new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2), null ); break; } case SqlPredicate.SearchString.SearchKind.Contains: { subStrPredicate = new SqlPredicate.ExprExpr( new SqlFunction(typeof(int), "CHARINDEX", new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2), new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr1)), SqlPredicate.Operator.Greater, new SqlValue(0), null); break; } } if (subStrPredicate != null) { var result = new SqlSearchCondition( new SqlCondition(false, like, predicate.IsNot), new SqlCondition(predicate.IsNot, subStrPredicate)); return(result); } } return(like); }
private void BuildAssociationCondition(ExpressionBuilder builder, TableContext parent, AssociationDescriptor association, SqlSearchCondition condition) { for (var i = 0; i < association.ThisKey.Length; i++) { if (!parent.SqlTable.Fields.TryGetValue(association.ThisKey[i], out var field1)) { throw new LinqException("Association key '{0}' not found for type '{1}.", association.ThisKey[i], parent.ObjectType); } if (!SqlTable.Fields.TryGetValue(association.OtherKey[i], out var field2)) { throw new LinqException("Association key '{0}' not found for type '{1}.", association.OtherKey[i], ObjectType); } ISqlPredicate predicate = new SqlPredicate.ExprExpr( field1, SqlPredicate.Operator.Equal, field2); predicate = builder.Convert(parent, predicate); condition.Conditions.Add(new SqlCondition(false, predicate)); } if (ObjectType != OriginalType) { var predicate = Builder.MakeIsPredicate(this, OriginalType); if (predicate.GetType() != typeof(SqlPredicate.Expr)) { condition.Conditions.Add(new SqlCondition(false, predicate)); } } RegularConditionCount = condition.Conditions.Count; ExpressionPredicate = Association.GetPredicate(parent.ObjectType, ObjectType); if (ExpressionPredicate != null) { ExpressionPredicate = (LambdaExpression)Builder.ConvertExpressionTree(ExpressionPredicate); var expr = Builder.ConvertExpression(ExpressionPredicate.Body.Unwrap()); Builder.BuildSearchCondition( new ExpressionContext(parent.Parent, new IBuildContext[] { parent, this }, ExpressionPredicate), expr, condition.Conditions, false); } }