internal override SqlExpression VisitSimpleCase(SqlSimpleCase c) { this.depth++; int num = 0; Debug.Assert(c.Whens.Count > 1); SqlWhen elseNode = c.Whens[c.Whens.Count - 1]; int count = c.Whens.Count - 1; this.NewLine(); while (num < count) { SqlWhen when = c.Whens[num]; this.sb.Append("IIF("); Visit(c.Expression); sb.Append(" = "); this.Visit(when.Match); this.sb.Append(" , "); this.Visit(when.Value); this.sb.Append(" , "); num++; } Debug.Assert(elseNode != null); this.Visit(elseNode.Value); for (int i = 0; i < count; i++) { sb.Append(")"); } this.NewLine(); this.depth--; return(c); }
internal virtual SqlExpression VisitSearchedCase(SqlSearchedCase c) { for (int i = 0, n = c.Whens.Count; i < n; i++) { SqlWhen when = c.Whens[i]; when.Match = this.VisitExpression(when.Match); when.Value = this.VisitExpression(when.Value); } c.Else = this.VisitExpression(c.Else); return c; }
internal virtual SqlExpression VisitSimpleCase(SqlSimpleCase c) { c.Expression = this.VisitExpression(c.Expression); for (int i = 0, n = c.Whens.Count; i < n; i++) { SqlWhen when = c.Whens[i]; when.Match = this.VisitExpression(when.Match); when.Value = this.VisitExpression(when.Value); } return c; }
protected override SqlExpression VisitWhen(SqlWhen expr) { _builder.Append("WHEN "); Visit(expr.Match); _builder.Append(" THEN "); Visit(expr.Value); return(expr); }
internal override SqlExpression VisitSearchedCase(SqlSearchedCase c) { for (int i = 0, n = c.Whens.Count; i < n; i++) { SqlWhen when = c.Whens[i]; when.Match = this.VisitPredicate(when.Match); when.Value = this.VisitExpression(when.Value); } c.Else = this.VisitExpression(c.Else); return(c); }
internal override SqlExpression VisitSimpleCase(SqlSimpleCase c) { SqlExpression expr = this.VisitExpression(c.Expression); SqlWhen[] whens = new SqlWhen[c.Whens.Count]; for (int i = 0, n = whens.Length; i < n; i++) { SqlWhen when = c.Whens[i]; whens[i] = new SqlWhen(this.VisitExpression(when.Match), this.VisitExpression(when.Value)); } return(new SqlSimpleCase(c.ClrType, expr, whens, c.SourceExpression)); }
internal override SqlExpression VisitSearchedCase(SqlSearchedCase c) { int num = 0; int count = c.Whens.Count; while (num < count) { SqlWhen when = c.Whens[num]; when.Match = this.VisitPredicate(when.Match); when.Value = this.VisitExpression(when.Value); num++; } c.Else = this.VisitExpression(c.Else); return(c); }
internal override SqlExpression String_IndexOf(SqlMethodCall mc, Expression sourceExpression) { if (mc.Arguments.Count == 1) { if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } //var when = new SqlWhen(Binary(SqlNodeType.EQ, CLRLENGTH(mc.Arguments[0]), ValueFromObject(0, sourceExpression)), this.ValueFromObject(0, sourceExpression)); var funInStr = Subtract(FunctionCall(typeof(int), "InStr", new[] { mc.Object, mc.Arguments[0] }, sourceExpression), 1); //return SearchedCase(new[] { when }, expression9, sourceExpression); return(funInStr); } if (mc.Arguments.Count == 2) { if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } if (mc.Arguments[1].ClrType == typeof(StringComparison)) { throw SqlClient.Error.IndexOfWithStringComparisonArgNotSupported(); } #region Delete //SqlExpression expression10 = Binary(SqlNodeType.EQ, CLRLENGTH(mc.Arguments[0]), ValueFromObject(0, sourceExpression)); //var when2 = new SqlWhen(AndAccumulate(expression10, Binary(SqlNodeType.LE, Add(mc.Arguments[1], 1), CLRLENGTH(mc.Object))), mc.Arguments[1]); //SqlExpression expression11 = this.Subtract(this.FunctionCall(typeof(int), "LOCATE", new[] { mc.Arguments[0], mc.Object, Add(mc.Arguments[1], 1) }, sourceExpression), 1); //return this.SearchedCase(new[] { when2 }, expression11, sourceExpression); #endregion return(Subtract(FunctionCall(typeof(int), "LOCATE", new[] { mc.Arguments[0], mc.Object, Add(mc.Arguments[1], 1) }, sourceExpression), 1)); } if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } if (mc.Arguments[2].ClrType == typeof(StringComparison)) { throw SqlClient.Error.IndexOfWithStringComparisonArgNotSupported(); } SqlExpression left = Binary(SqlNodeType.EQ, CLRLENGTH(mc.Arguments[0]), ValueFromObject(0, sourceExpression)); var when3 = new SqlWhen(this.AndAccumulate(left, Binary(SqlNodeType.LE, Add(mc.Arguments[1], 1), CLRLENGTH(mc.Object))), mc.Arguments[1]); SqlExpression expression13 = this.FunctionCall(typeof(string), "SUBSTRING", new[] { mc.Object, ValueFromObject(1, false, sourceExpression), this.Add(new SqlExpression[] { mc.Arguments[1], mc.Arguments[2] }) }, sourceExpression); SqlExpression @else = this.Subtract(this.FunctionCall(typeof(int), "LOCATE", new[] { mc.Arguments[0], expression13, Add(mc.Arguments[1], 1) }, sourceExpression), 1); return(this.SearchedCase(new SqlWhen[] { when3 }, @else, sourceExpression)); //var args = new[] { mc.Arguments[0], mc.Object, Add(mc.Arguments[1], 1), mc.Arguments[2] }; //return Subtract(FunctionCall(typeof(int), "LOCATE", args, sourceExpression), 1); }
internal override SqlExpression String_IndexOf(SqlMethodCall mc, System.Linq.Expressions.Expression sourceExpression) { if (mc.Arguments.Count == 1) { if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } var when = new SqlWhen(Binary(SqlNodeType.EQ, String_Length(mc.Arguments[0]), ValueFromObject(0, sourceExpression)), ValueFromObject(0, sourceExpression)); SqlExpression expression9 = Subtract(FunctionCall(typeof(int), "POSITION", new[] { mc.Arguments[0], mc.Object }, sourceExpression), 1); return(SearchedCase(new[] { when }, expression9, sourceExpression)); } if (mc.Arguments.Count == 2) { if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } if (mc.Arguments[1].ClrType == typeof(StringComparison)) { throw SqlClient.Error.IndexOfWithStringComparisonArgNotSupported(); } SqlExpression expression10 = Binary(SqlNodeType.EQ, CLRLENGTH(mc.Arguments[0]), ValueFromObject(0, sourceExpression)); SqlWhen when2 = new SqlWhen(AndAccumulate(expression10, Binary(SqlNodeType.LE, Add(mc.Arguments[1], 1), CLRLENGTH(mc.Object))), mc.Arguments[1]); SqlExpression expression11 = Subtract(FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { mc.Arguments[0], mc.Object, Add(mc.Arguments[1], 1) }, sourceExpression), 1); return(SearchedCase(new SqlWhen[] { when2 }, expression11, sourceExpression)); } if (mc.Arguments.Count != 3) { //throw GetMethodSupportException(mc); SqlClient.Error.MethodHasNoSupportConversionToSql(mc); //goto Label_1B30; } if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } if (mc.Arguments[2].ClrType == typeof(StringComparison)) { throw SqlClient.Error.IndexOfWithStringComparisonArgNotSupported(); } SqlExpression left = Binary(SqlNodeType.EQ, CLRLENGTH(mc.Arguments[0]), ValueFromObject(0, sourceExpression)); SqlWhen when3 = new SqlWhen(AndAccumulate(left, Binary(SqlNodeType.LE, Add(mc.Arguments[1], 1), CLRLENGTH(mc.Object))), mc.Arguments[1]); SqlExpression expression13 = FunctionCall(typeof(string), "SUBSTRING", new SqlExpression[] { mc.Object, ValueFromObject(1, false, sourceExpression), Add(new SqlExpression[] { mc.Arguments[1], mc.Arguments[2] }) }, sourceExpression); SqlExpression @else = Subtract(FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { mc.Arguments[0], expression13, Add(mc.Arguments[1], 1) }, sourceExpression), 1); return(SearchedCase(new SqlWhen[] { when3 }, @else, sourceExpression)); }
internal override SqlExpression String_IndexOf(SqlMethodCall mc, Expression sourceExpression) { if (mc.Arguments.Count == 1) { if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } var when = new SqlWhen(this.Binary(SqlNodeType.EQ, this.CLRLENGTH(mc.Arguments[0]), this.ValueFromObject(0, sourceExpression)), this.ValueFromObject(0, sourceExpression)); SqlExpression expression9 = this.Subtract(this.FunctionCall(typeof(int), "CHARINDEX", new[] { mc.Arguments[0], mc.Object }, sourceExpression), 1); return(this.SearchedCase(new[] { when }, expression9, sourceExpression)); } if (mc.Arguments.Count == 2) { if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } if (mc.Arguments[1].ClrType == typeof(StringComparison)) { throw SqlClient.Error.IndexOfWithStringComparisonArgNotSupported(); } SqlExpression expression10 = this.Binary(SqlNodeType.EQ, this.CLRLENGTH(mc.Arguments[0]), this.ValueFromObject(0, sourceExpression)); var when2 = new SqlWhen(this.AndAccumulate(expression10, this.Binary(SqlNodeType.LE, this.Add(mc.Arguments[1], 1), this.CLRLENGTH(mc.Object))), mc.Arguments[1]); SqlExpression expression11 = this.Subtract(this.FunctionCall(typeof(int), "CHARINDEX", new[] { mc.Arguments[0], mc.Object, this.Add(mc.Arguments[1], 1) }, sourceExpression), 1); return(this.SearchedCase(new[] { when2 }, expression11, sourceExpression)); } if ((mc.Arguments[0] is SqlValue) && (((SqlValue)mc.Arguments[0]).Value == null)) { throw Error.ArgumentNull("value"); } if (mc.Arguments[2].ClrType == typeof(StringComparison)) { throw SqlClient.Error.IndexOfWithStringComparisonArgNotSupported(); } SqlExpression left = this.Binary(SqlNodeType.EQ, this.CLRLENGTH(mc.Arguments[0]), this.ValueFromObject(0, sourceExpression)); var when3 = new SqlWhen(this.AndAccumulate(left, this.Binary(SqlNodeType.LE, this.Add(mc.Arguments[1], 1), this.CLRLENGTH(mc.Object))), mc.Arguments[1]); SqlExpression expression13 = this.FunctionCall(typeof(string), "SUBSTRING", new[] { mc.Object, this.ValueFromObject(1, false, sourceExpression), this.Add(new SqlExpression[] { mc.Arguments[1], mc.Arguments[2] }) }, sourceExpression); SqlExpression @else = this.Subtract(this.FunctionCall(typeof(int), "CHARINDEX", new[] { mc.Arguments[0], expression13, this.Add(mc.Arguments[1], 1) }, sourceExpression), 1); return(this.SearchedCase(new[] { when3 }, @else, sourceExpression)); }
internal override SqlExpression VisitSearchedCase(SqlSearchedCase c) { this.depth++; int num = 0; int count; SqlNode elseNode; if (c.Else != null) { elseNode = c.Else; count = c.Whens.Count; } else { elseNode = c.Whens[c.Whens.Count - 1].Match; count = c.Whens.Count - 1; } this.NewLine(); while (num < count) { SqlWhen when = c.Whens[num]; this.sb.Append("IIF("); this.Visit(when.Match); this.sb.Append(" , "); this.Visit(when.Value); this.sb.Append(" , "); num++; } if (c.Else != null) { this.Visit(elseNode); } for (int i = 0; i < count; i++) { sb.Append(")"); } this.NewLine(); this.depth--; return(c); }
internal override SqlExpression VisitSimpleCase(SqlSimpleCase c) { c.Expression = this.VisitExpression(c.Expression); int compareWhen = 0; // Find the ELSE if it exists. for (int i = 0, n = c.Whens.Count; i < n; i++) { if (c.Whens[i].Match == null) { compareWhen = i; break; } } c.Whens[compareWhen].Match = VisitExpression(c.Whens[compareWhen].Match); c.Whens[compareWhen].Value = VisitExpression(c.Whens[compareWhen].Value); // Compare each other when value to the compare when List <SqlWhen> newWhens = new List <SqlWhen>(); bool allValuesLiteral = true; for (int i = 0, n = c.Whens.Count; i < n; i++) { if (compareWhen != i) { SqlWhen when = c.Whens[i]; when.Match = this.VisitExpression(when.Match); when.Value = this.VisitExpression(when.Value); if (!SqlComparer.AreEqual(c.Whens[compareWhen].Value, when.Value)) { newWhens.Add(when); } allValuesLiteral = allValuesLiteral && when.Value.NodeType == SqlNodeType.Value; } } newWhens.Add(c.Whens[compareWhen]); // Did everything reduce to a single CASE? SqlExpression rewrite = TryToConsolidateAllValueExpressions(newWhens.Count, c.Whens[compareWhen].Value); if (rewrite != null) { return(rewrite); } // Can it be a conjuction (or disjunction) of clauses? rewrite = TryToWriteAsSimpleBooleanExpression(c.ClrType, c.Expression, newWhens, allValuesLiteral); if (rewrite != null) { return(rewrite); } // Can any WHEN clauses be reduced to fall into the ELSE clause? rewrite = TryToWriteAsReducedCase(c.ClrType, c.Expression, newWhens, c.Whens[compareWhen].Match, c.Whens.Count); if (rewrite != null) { return(rewrite); } return(c); }
private SqlExpression TranslateStringMethod(SqlMethodCall mc) { Expression source = mc.SourceExpression; switch (mc.Method.Name) { case "Contains": if (mc.Arguments.Count == 1) { SqlExpression pattern = mc.Arguments[0]; SqlExpression escape = null; bool needsEscape = true; if (pattern.NodeType == SqlNodeType.Value) { string unescapedText = (string)((SqlValue)pattern).Value; string patternText = SqlHelpers.GetStringContainsPattern(unescapedText, '~', out needsEscape); pattern = sql.ValueFromObject(patternText, true, pattern.SourceExpression); } else if (pattern.NodeType == SqlNodeType.ClientParameter) { SqlClientParameter cp = (SqlClientParameter)pattern; Func<string, char, string> getStringContainsPatternForced = SqlHelpers.GetStringContainsPatternForced; pattern = new SqlClientParameter( cp.ClrType, cp.SqlType, Expression.Lambda( Expression.Invoke(Expression.Constant(getStringContainsPatternForced), cp.Accessor.Body, Expression.Constant('~')), cp.Accessor.Parameters[0] ), cp.SourceExpression ); } else { throw Error.NonConstantExpressionsNotSupportedFor("String.Contains"); } if (needsEscape) { escape = sql.ValueFromObject("~", false, source); } return sql.Like(mc.Object, pattern, escape, source); } break; case "StartsWith": if (mc.Arguments.Count == 1) { SqlExpression pattern = mc.Arguments[0]; SqlExpression escape = null; bool needsEscape = true; if (pattern.NodeType == SqlNodeType.Value) { string unescapedText = (string)((SqlValue)pattern).Value; string patternText = SqlHelpers.GetStringStartsWithPattern(unescapedText, '~', out needsEscape); pattern = sql.ValueFromObject(patternText, true, pattern.SourceExpression); } else if (pattern.NodeType == SqlNodeType.ClientParameter) { SqlClientParameter cp = (SqlClientParameter)pattern; Func<string, char, string> getStringStartsWithPatternForced = SqlHelpers.GetStringStartsWithPatternForced; pattern = new SqlClientParameter( cp.ClrType, cp.SqlType, Expression.Lambda( Expression.Invoke(Expression.Constant(getStringStartsWithPatternForced), cp.Accessor.Body, Expression.Constant('~')), cp.Accessor.Parameters[0] ), cp.SourceExpression ); } else { throw Error.NonConstantExpressionsNotSupportedFor("String.StartsWith"); } if (needsEscape) { escape = sql.ValueFromObject("~", false, source); } return sql.Like(mc.Object, pattern, escape, source); } break; case "EndsWith": if (mc.Arguments.Count == 1) { SqlExpression pattern = mc.Arguments[0]; SqlExpression escape = null; bool needsEscape = true; if (pattern.NodeType == SqlNodeType.Value) { string unescapedText = (string)((SqlValue)pattern).Value; string patternText = SqlHelpers.GetStringEndsWithPattern(unescapedText, '~', out needsEscape); pattern = sql.ValueFromObject(patternText, true, pattern.SourceExpression); } else if (pattern.NodeType == SqlNodeType.ClientParameter) { SqlClientParameter cp = (SqlClientParameter)pattern; Func<string, char, string> getStringEndsWithPatternForced = SqlHelpers.GetStringEndsWithPatternForced; pattern = new SqlClientParameter( cp.ClrType, cp.SqlType, Expression.Lambda( Expression.Invoke(Expression.Constant(getStringEndsWithPatternForced), cp.Accessor.Body, Expression.Constant('~')), cp.Accessor.Parameters[0] ), cp.SourceExpression ); } else { throw Error.NonConstantExpressionsNotSupportedFor("String.EndsWith"); } if (needsEscape) { escape = sql.ValueFromObject("~", false, source); } return sql.Like(mc.Object, pattern, escape, source); } break; case "IndexOf": if (mc.Arguments.Count == 1) { if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { throw Error.ArgumentNull("value"); } // if the search string is empty, return zero SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); SqlWhen when = new SqlWhen(lenZeroExpr, sql.ValueFromObject(0, source)); SqlExpression @else = sql.Subtract(sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { mc.Arguments[0], mc.Object }, source), 1); return sql.SearchedCase(new SqlWhen[] { when }, @else, source); } else if (mc.Arguments.Count == 2) { if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { throw Error.ArgumentNull("value"); } if (mc.Arguments[1].ClrType == typeof(StringComparison)) { throw Error.IndexOfWithStringComparisonArgNotSupported(); } // if the search string is empty and the start index is in bounds, // return the start index SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(mc.Object))); SqlWhen when = new SqlWhen(lenZeroExpr, mc.Arguments[1]); SqlExpression @else = sql.Subtract(sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { mc.Arguments[0], mc.Object, sql.Add(mc.Arguments[1], 1) }, source), 1); return sql.SearchedCase(new SqlWhen[] { when }, @else, source); } else if (mc.Arguments.Count == 3) { if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { throw Error.ArgumentNull("value"); } if (mc.Arguments[2].ClrType == typeof(StringComparison)) { throw Error.IndexOfWithStringComparisonArgNotSupported(); } // s1.IndexOf(s2, start, count) -> CHARINDEX(@s2, SUBSTRING(@s1, 1, @start + @count), @start + 1) // if the search string is empty and the start index is in bounds, // return the start index SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(mc.Object))); SqlWhen when = new SqlWhen(lenZeroExpr, mc.Arguments[1]); SqlExpression substring = sql.FunctionCall( typeof(string), "SUBSTRING", new SqlExpression[] { mc.Object, sql.ValueFromObject(1, false, source), sql.Add(mc.Arguments[1], mc.Arguments[2]) }, source); SqlExpression @else = sql.Subtract(sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { mc.Arguments[0], substring, sql.Add(mc.Arguments[1], 1) }, source), 1); return sql.SearchedCase(new SqlWhen[] { when }, @else, source); } break; case "LastIndexOf": if (mc.Arguments.Count == 1) { // s.LastIndexOf(part) --> // CASE WHEN CHARINDEX(@part, @s) = 0 THEN -1 // ELSE 1 + CLRLENGTH(@s) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@s)) // END SqlExpression exprPart = mc.Arguments[0]; if (exprPart is SqlValue && ((SqlValue)exprPart).Value == null) { throw Error.ArgumentNull("value"); } SqlExpression exprS = mc.Object; SqlExpression reverseS = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { exprS }, source); SqlExpression reversePart = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { exprPart }, source); SqlExpression charIndex = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { exprPart, exprS }, source); SqlExpression charIndexOfReverse = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { reversePart, reverseS }, source); SqlExpression notContained = sql.Binary(SqlNodeType.EQ, charIndex, sql.ValueFromObject(0, false, source)); SqlExpression len1 = sql.CLRLENGTH(exprS); SqlExpression len2 = sql.CLRLENGTH(exprPart); SqlExpression elseCase = sql.Add(sql.ValueFromObject(1, false, source), sql.Subtract(len1, sql.Add(len2, charIndexOfReverse))); SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source)); // if the search string is empty, return zero SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); SqlWhen whenLenZero = new SqlWhen(lenZeroExpr, sql.Subtract(sql.CLRLENGTH(exprS), 1)); return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained }, elseCase, source); } else if (mc.Arguments.Count == 2) { // s.LastIndexOf(part,i) --> // set @first = LEFT(@s, @i+1) // CASE WHEN CHARINDEX(@part, @first) = 0 THEN -1 // ELSE 1 + CLRLENGTH(@first) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@first)) // END if (mc.Arguments[1].ClrType == typeof(StringComparison)) { throw Error.LastIndexOfWithStringComparisonArgNotSupported(); } SqlExpression s = mc.Object; SqlExpression part = mc.Arguments[0]; if (part is SqlValue && ((SqlValue)part).Value == null) { throw Error.ArgumentNull("value"); } SqlExpression i = mc.Arguments[1]; SqlExpression first = sql.FunctionCall(typeof(string), "LEFT", new SqlExpression[] { s, sql.Add(i, 1) }, source); SqlExpression reverseFirst = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { first }, source); SqlExpression reversePart = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { part }, source); SqlExpression charIndex = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { part, first }, source); SqlExpression charIndexOfReverse = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { reversePart, reverseFirst }, source); SqlExpression notContained = sql.Binary(SqlNodeType.EQ, charIndex, sql.ValueFromObject(0, false, source)); SqlExpression len1 = sql.CLRLENGTH(first); SqlExpression len2 = sql.CLRLENGTH(part); SqlExpression elseCase = sql.Add(sql.ValueFromObject(1, false, source), sql.Subtract(len1, sql.Add(len2, charIndexOfReverse))); SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source)); // if the search string is empty and the start index is in bounds, // return the start index SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(s))); SqlWhen whenLenZero = new SqlWhen(lenZeroExpr, mc.Arguments[1]); return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained }, elseCase, source); } else if (mc.Arguments.Count == 3) { // s.LastIndexOf(part, i, count) --> // set @first = LEFT(@s, @i+1) // CASE WHEN (CHARINDEX(@part, @first) = 0) OR (1 + CLRLENGTH(@first) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@first))) < (@i - @count) THEN -1 // ELSE 1 + CLRLENGTH(@first) - CLRLENGTH(@part) - CHARINDEX(REVERSE(@part),REVERSE(@first)) // END if (mc.Arguments[2].ClrType == typeof(StringComparison)) { throw Error.LastIndexOfWithStringComparisonArgNotSupported(); } SqlExpression s = mc.Object; SqlExpression part = mc.Arguments[0]; if (part is SqlValue && ((SqlValue)part).Value == null) { throw Error.ArgumentNull("value"); } SqlExpression i = mc.Arguments[1]; SqlExpression count = mc.Arguments[2]; SqlExpression first = sql.FunctionCall(typeof(string), "LEFT", new SqlExpression[] { s, sql.Add(i, 1) }, source); SqlExpression reverseFirst = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { first }, source); SqlExpression reversePart = sql.FunctionCall(typeof(string), "REVERSE", new SqlExpression[] { part }, source); SqlExpression charIndex = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { part, first }, source); SqlExpression charIndexOfReverse = sql.FunctionCall(typeof(int), "CHARINDEX", new SqlExpression[] { reversePart, reverseFirst }, source); SqlExpression len1 = sql.CLRLENGTH(first); SqlExpression len2 = sql.CLRLENGTH(part); SqlExpression elseCase = sql.Add(sql.ValueFromObject(1, false, source), sql.Subtract(len1, sql.Add(len2, charIndexOfReverse))); SqlExpression notContained = sql.Binary(SqlNodeType.EQ, charIndex, sql.ValueFromObject(0, false, source)); notContained = sql.OrAccumulate(notContained, sql.Binary(SqlNodeType.LE, elseCase, sql.Subtract(i, count))); SqlWhen whenNotContained = new SqlWhen(notContained, sql.ValueFromObject(-1, false, source)); // if the search string is empty and the start index is in bounds, // return the start index SqlExpression lenZeroExpr = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.CLRLENGTH(s))); SqlWhen whenLenZero = new SqlWhen(lenZeroExpr, mc.Arguments[1]); return sql.SearchedCase(new SqlWhen[] { whenLenZero, whenNotContained }, elseCase, source); } break; case "Insert": // Create STUFF(str, insertPos + 1, 0, strToInsert) if (mc.Arguments.Count == 2) { if (mc.Arguments[1] is SqlValue && ((SqlValue)mc.Arguments[1]).Value == null) { throw Error.ArgumentNull("value"); } SqlFunctionCall stuffCall = sql.FunctionCall( typeof(string), "STUFF", new SqlExpression[] { mc.Object, sql.Add(mc.Arguments[0], 1), sql.ValueFromObject(0, false, source), mc.Arguments[1] }, source); // We construct SQL to handle the special case of when the length of the string // to modify is equal to the insert position. This occurs if the string is empty and // the insert pos is 0, or when the string is not empty, and the insert pos indicates // the end of the string. // CASE WHEN (CLRLENGTH(str) = insertPos) THEN str + strToInsert ELSE STUFF(...) SqlExpression insertingAtEnd = sql.Binary(SqlNodeType.EQ, sql.CLRLENGTH(mc.Object), mc.Arguments[0]); SqlExpression stringConcat = sql.Concat(mc.Object, mc.Arguments[1]); return sql.SearchedCase(new SqlWhen[] { new SqlWhen(insertingAtEnd, stringConcat) }, stuffCall, source); } break; case "PadLeft": if (mc.Arguments.Count == 1) { // s.PadLeft(i) --> // CASE WHEN CLRLENGTH(@s)>= @i THEN @s // ELSE SPACE(@i-CLRLENGTH(@s)) + @s // END SqlExpression exprS = mc.Object; SqlExpression exprI = mc.Arguments[0]; SqlExpression len2 = sql.CLRLENGTH(exprS); SqlExpression dontChange = sql.Binary(SqlNodeType.GE, len2, exprI); SqlExpression numSpaces = sql.Subtract(exprI, len2); SqlExpression padding = sql.FunctionCall(typeof(string), "SPACE", new SqlExpression[] { numSpaces }, source); SqlExpression elseCase = sql.Concat(padding, exprS); return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); } else if (mc.Arguments.Count == 2) { // s.PadLeft(i,c) --> // CASE WHEN CLRLENGTH(@s) >= @i THEN @s // ELSE REPLICATE(@c, @i - CLRLENGTH(@s)) + @s // END SqlExpression exprS = mc.Object; SqlExpression exprI = mc.Arguments[0]; SqlExpression exprC = mc.Arguments[1]; SqlExpression dontChange = sql.Binary(SqlNodeType.GE, sql.CLRLENGTH(exprS), exprI); SqlExpression len2 = sql.CLRLENGTH(exprS); SqlExpression numSpaces = sql.Subtract(exprI, len2); SqlExpression padding = sql.FunctionCall(typeof(string), "REPLICATE", new SqlExpression[] { exprC, numSpaces }, source); SqlExpression elseCase = sql.Concat(padding, exprS); return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); } break; case "PadRight": if (mc.Arguments.Count == 1) { // s.PadRight(i) --> // CASE WHEN CLRLENGTH(@s) >= @i THEN @s // ELSE @s + SPACE(@i - CLRLENGTH(@s)) // END SqlExpression exprS = mc.Object; SqlExpression exprI = mc.Arguments[0]; SqlExpression dontChange = sql.Binary(SqlNodeType.GE, sql.CLRLENGTH(exprS), exprI); SqlExpression len2 = sql.CLRLENGTH(exprS); SqlExpression numSpaces = sql.Subtract(exprI, len2); SqlExpression padding = sql.FunctionCall(typeof(string), "SPACE", new SqlExpression[] { numSpaces }, source); SqlExpression elseCase = sql.Concat(exprS, padding); return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); } else if (mc.Arguments.Count == 2) { // s.PadRight(i,c) --> // CASE WHEN CLRLENGTH(@s) >= @i THEN @s // ELSE @s + REPLICATE(@c, @i - CLRLENGTH(@s)) // END SqlExpression exprS = mc.Object; SqlExpression exprI = mc.Arguments[0]; SqlExpression exprC = mc.Arguments[1]; SqlExpression dontChange = sql.Binary(SqlNodeType.GE, sql.CLRLENGTH(exprS), exprI); SqlExpression len2 = sql.CLRLENGTH(exprS); SqlExpression numSpaces = sql.Subtract(exprI, len2); SqlExpression padding = sql.FunctionCall(typeof(string), "REPLICATE", new SqlExpression[] { exprC, numSpaces }, source); SqlExpression elseCase = sql.Concat(exprS, padding); return sql.SearchedCase(new SqlWhen[] { new SqlWhen(dontChange, exprS) }, elseCase, source); } break; case "Remove": if (mc.Arguments.Count == 1) { return sql.FunctionCall( typeof(string), "STUFF", new SqlExpression[] { mc.Object, sql.Add(mc.Arguments[0], 1), sql.CLRLENGTH(mc.Object), sql.ValueFromObject("", false, source) }, source); } else if (mc.Arguments.Count == 2) { return sql.FunctionCall( typeof(string), "STUFF", new SqlExpression[] { mc.Object, sql.Add(mc.Arguments[0], 1), mc.Arguments[1], sql.ValueFromObject("", false, source) }, source); } break; case "Replace": if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { throw Error.ArgumentNull("old"); } if (mc.Arguments[1] is SqlValue && ((SqlValue)mc.Arguments[1]).Value == null) { throw Error.ArgumentNull("new"); } return sql.FunctionCall( typeof(string), "REPLACE", new SqlExpression[] { mc.Object, mc.Arguments[0], mc.Arguments[1] }, source); case "Substring": if (mc.Arguments.Count == 1) { return sql.FunctionCall( typeof(string), "SUBSTRING", new SqlExpression[] { mc.Object, sql.Add(mc.Arguments[0], 1), sql.CLRLENGTH(mc.Object) }, source); } else if (mc.Arguments.Count == 2) { return sql.FunctionCall( typeof(string), "SUBSTRING", new SqlExpression[] { mc.Object, sql.Add(mc.Arguments[0], 1), mc.Arguments[1] }, source); } break; case "Trim": if (mc.Arguments.Count == 0) { return sql.FunctionCall( typeof(string), "LTRIM", new SqlExpression[] { sql.FunctionCall(typeof(string), "RTRIM", new SqlExpression[] { mc.Object }, source) }, source); } break; case "ToLower": if (mc.Arguments.Count == 0) { return sql.FunctionCall(typeof(string), "LOWER", new SqlExpression[] { mc.Object }, source); } break; case "ToUpper": if (mc.Arguments.Count == 0) { return sql.FunctionCall(typeof(string), "UPPER", new SqlExpression[] { mc.Object }, source); } break; case "get_Chars": // s[i] --> SUBSTRING(@s, @i+1, 1) if (mc.Arguments.Count == 1) { return sql.FunctionCall(typeof(char), "SUBSTRING", new SqlExpression[] {mc.Object, sql.Add( mc.Arguments[0], 1), sql.ValueFromObject(1, false, source) }, source); } break; case "CompareTo": if (mc.Arguments.Count == 1) { if (mc.Arguments[0] is SqlValue && ((SqlValue)mc.Arguments[0]).Value == null) { throw Error.ArgumentNull("value"); } return CreateComparison(mc.Object, mc.Arguments[0], source); } break; } throw GetMethodSupportException(mc); }
internal override SqlExpression VisitSimpleCase(SqlSimpleCase c) { SqlExpression expr = this.VisitExpression(c.Expression); SqlWhen[] whens = new SqlWhen[c.Whens.Count]; for (int i = 0, n = whens.Length; i < n; i++) { SqlWhen when = c.Whens[i]; whens[i] = new SqlWhen(this.VisitExpression(when.Match), this.VisitExpression(when.Value)); } return new SqlSimpleCase(c.ClrType, expr, whens, c.SourceExpression); }
protected override SqlExpression VisitWhen(SqlWhen expr) { _builder.Append("WHEN "); Visit(expr.Match); _builder.Append(" THEN "); Visit(expr.Value); return expr; }
protected virtual SqlExpression VisitWhen(SqlWhen expr) { return(expr); }
protected virtual SqlExpression VisitWhen(SqlWhen expr) { return expr; }
internal SqlSearchedCase SearchedCase(SqlWhen[] whens, SqlExpression @else, Expression sourceExpression) { return new SqlSearchedCase(whens[0].Value.ClrType, whens, @else, sourceExpression); }