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);
}