internal SqlSearchedCase SearchedCase(SqlWhen[] whens, SqlExpression @else, Expression sourceExpression) { return new SqlSearchedCase(whens[0].Value.ClrType, whens, @else, sourceExpression); }
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); }
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.FunctionCallChrLength(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.FunctionCallChrLength(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.FunctionCallChrLength(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.FunctionCallChrLength(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.FunctionCallChrLength(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.FunctionCallChrLength(exprS); SqlExpression len2 = sql.FunctionCallChrLength(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.FunctionCallChrLength(mc.Arguments[0]), sql.ValueFromObject(0, source)); SqlWhen whenLenZero = new SqlWhen(lenZeroExpr, sql.Subtract(sql.FunctionCallChrLength(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.FunctionCallChrLength(first); SqlExpression len2 = sql.FunctionCallChrLength(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.FunctionCallChrLength(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.FunctionCallChrLength(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.FunctionCallChrLength(first); SqlExpression len2 = sql.FunctionCallChrLength(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.FunctionCallChrLength(mc.Arguments[0]), sql.ValueFromObject(0, source)); lenZeroExpr = sql.AndAccumulate(lenZeroExpr, sql.Binary(SqlNodeType.LE, sql.Add(mc.Arguments[1], 1), sql.FunctionCallChrLength(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.FunctionCallChrLength(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.FunctionCallChrLength(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.FunctionCallChrLength(exprS), exprI); SqlExpression len2 = sql.FunctionCallChrLength(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.FunctionCallChrLength(exprS), exprI); SqlExpression len2 = sql.FunctionCallChrLength(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.FunctionCallChrLength(exprS), exprI); SqlExpression len2 = sql.FunctionCallChrLength(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.FunctionCallChrLength(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.FunctionCallChrLength(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); }