/// <summary>
/// Creates a TopClause with the given topCount and withTies.
/// </summary>
/// <param name="topCount"></param>
/// <param name="withTies"></param>
internal TopClause(int topCount, bool withTies)
{
SqlBuilder sqlBuilder = new SqlBuilder();
sqlBuilder.Append(topCount.ToString(CultureInfo.InvariantCulture));
this.topCount = sqlBuilder;
this.withTies = withTies;
}
private static void WriteFunctionArguments(SqlGenerator sqlgen, IEnumerable<DbExpression> functionArguments, SqlBuilder result)
{
result.Append("(");
string separator = "";
foreach (DbExpression arg in functionArguments)
{
result.Append(separator);
result.Append(arg.Accept(sqlgen));
separator = ", ";
}
result.Append(")");
}
/// <summary>
/// Default handling on function arguments.
/// Appends the list of arguments to the given result
/// If the function is niladic it does not append anything,
/// otherwise it appends (arg1, arg2, .., argn)
/// </summary>
/// <param name="e"></param>
/// <param name="result"></param>
private static void HandleFunctionArgumentsDefault(SqlGenerator sqlgen, DbFunctionExpression e, SqlBuilder result)
{
bool isNiladicFunction = e.Function.NiladicFunctionAttribute;
Debug.Assert(!(isNiladicFunction && (0 < e.Arguments.Count)), "function attributed as NiladicFunction='true' in the provider manifest cannot have arguments");
if (isNiladicFunction && e.Arguments.Count > 0)
{
EntityUtil.Metadata(System.Data.Entity.Strings.SqlGen_NiladicFunctionsCannotHaveParameters);
}
if (!isNiladicFunction)
{
WriteFunctionArguments(sqlgen, e.Arguments, result);
}
}
private static ISqlFragment WrapWithCast(string returnType, Action<SqlBuilder> toWrap)
{
SqlBuilder result = new SqlBuilder();
if (returnType != null)
{
result.Append(" CAST(");
}
toWrap(result);
if (returnType != null)
{
result.Append(" AS ");
result.Append(returnType);
result.Append(")");
}
return result;
}
private static ISqlFragment HandleSpatialStaticMethodFunctionAppendSrid(SqlGenerator sqlgen, DbFunctionExpression functionExpression, string functionName)
{
if (functionExpression.Arguments.Count == 2)
{
return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionName);
}
else
{
DbExpression sridExpression = (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? defaultGeometrySridExpression : defaultGeographySridExpression);
SqlBuilder result = new SqlBuilder();
result.Append(functionName);
WriteFunctionArguments(sqlgen, functionExpression.Arguments.Concat(new[] { sridExpression }), result);
return result;
}
}
/// <summary>
/// Writes the function name to the given SqlBuilder.
/// </summary>
/// <param name="function"></param>
/// <param name="result"></param>
internal static void WriteFunctionName(SqlBuilder result, EdmFunction function)
{
string storeFunctionName;
if (null != function.StoreFunctionNameAttribute)
{
storeFunctionName = function.StoreFunctionNameAttribute;
}
else
{
storeFunctionName = function.Name;
}
// If the function is a builtin (i.e. the BuiltIn attribute has been
// specified, both store and canonical functions have this attribute),
// then the function name should not be quoted;
// additionally, no namespace should be used.
if (TypeHelpers.IsCanonicalFunction(function))
{
result.Append(storeFunctionName.ToUpperInvariant());
}
else if (IsStoreFunction(function))
{
result.Append(storeFunctionName);
}
else
{
// Should we actually support this?
if (String.IsNullOrEmpty(function.Schema))
{
result.Append(SqlGenerator.QuoteIdentifier(function.NamespaceName));
}
else
{
result.Append(SqlGenerator.QuoteIdentifier(function.Schema));
}
result.Append(".");
result.Append(SqlGenerator.QuoteIdentifier(storeFunctionName));
}
}
/// <summary>
/// Helper method that wrapps the given expession with a conver to varchar(255)
/// </summary>
/// <param name="result"></param>
/// <param name="e"></param>
private static void AppendConvertToVarchar(SqlGenerator sqlgen, SqlBuilder result, DbExpression e)
{
result.Append("convert(varchar(255), ");
result.Append(e.Accept(sqlgen));
result.Append(")");
}
/// <summary>
/// Handler for turning a canonical function into DATEPART
/// Results in DATEPART(datepart, e)
/// </summary>
/// <param name="datepart"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, string datepart, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("DATEPART (");
result.Append(datepart);
result.Append(", ");
Debug.Assert(e.Arguments.Count == 1, "Canonical datepart functions should have exactly one argument");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(")");
return result;
}
/// <summary>
/// STARTSWITH(arg0, arg1) => arg0 LIKE 'arg1%'
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="args"></param>
/// <param name="result"></param>
/// <returns></returns>
private static SqlBuilder HandleCanonicalFunctionStartsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
{
Debug.Assert(args.Count == 2, "StartsWith should have two arguments");
// Check if args[1] is a DbConstantExpression
DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
if ((constSearchParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
{
TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, false, true);
}
else
{
// We use CHARINDEX when the search param is a DbNullExpression because all of SQL Server 2008, 2005 and 2000
// consistently return NULL as the result.
// However, if instead we use the optimized LIKE translation when the search param is a DbNullExpression,
// only SQL Server 2005 yields a True instead of a DbNull as compared to SQL Server 2008 and 2000. This is
// bug 32315 in LIKE in SQL Server 2005.
result.Append("CHARINDEX( ");
result.Append(args[1].Accept(sqlgen));
result.Append(", ");
result.Append(args[0].Accept(sqlgen));
result.Append(") = 1");
}
return result;
}
/// <summary>
/// Function to translate the StartsWith, EndsWith and Contains canonical functions to LIKE expression in T-SQL
/// and also add the trailing ESCAPE '~' when escaping of the search string for the LIKE expression has occurred
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="targetExpression"></param>
/// <param name="constSearchParamExpression"></param>
/// <param name="result"></param>
/// <param name="insertPercentStart"></param>
/// <param name="insertPercentEnd"></param>
private static void TranslateConstantParameterForLike(SqlGenerator sqlgen, DbExpression targetExpression, DbConstantExpression constSearchParamExpression, SqlBuilder result, bool insertPercentStart, bool insertPercentEnd)
{
result.Append(targetExpression.Accept(sqlgen));
result.Append(" LIKE ");
// If it's a DbConstantExpression then escape the search parameter if necessary.
bool escapingOccurred;
StringBuilder searchParamBuilder = new StringBuilder();
if (insertPercentStart == true)
searchParamBuilder.Append("%");
searchParamBuilder.Append(SqlProviderManifest.EscapeLikeText(constSearchParamExpression.Value as string, false, out escapingOccurred));
if (insertPercentEnd == true)
searchParamBuilder.Append("%");
DbConstantExpression escapedSearchParamExpression = new DbConstantExpression(constSearchParamExpression.ResultType, searchParamBuilder.ToString());
result.Append(escapedSearchParamExpression.Accept(sqlgen));
// If escaping did occur (special characters were found), then append the escape character used.
if (escapingOccurred)
result.Append(" ESCAPE '" + SqlProviderManifest.LikeEscapeChar + "'");
}
/// <summary>
/// TRIM(string) -> LTRIM(RTRIM(string))
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionTrim(SqlGenerator sqlgen, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("LTRIM(RTRIM(");
Debug.Assert(e.Arguments.Count == 1, "Trim should have one argument");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append("))");
return result;
}
/// <summary>
/// Handle the canonical function Abs().
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionAbs(SqlGenerator sqlgen, DbFunctionExpression e)
{
// Convert the call to Abs(Byte) to a no-op, since Byte is an unsigned type.
if (TypeSemantics.IsPrimitiveType(e.Arguments[0].ResultType, PrimitiveTypeKind.Byte))
{
SqlBuilder result = new SqlBuilder();
result.Append(e.Arguments[0].Accept(sqlgen));
return result;
}
else
{
return HandleFunctionDefault(sqlgen, e);
}
}
/// <summary>
/// Common handler for the canonical functions ROUND and TRUNCATE
/// </summary>
/// <param name="e"></param>
/// <param name="round"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionRoundOrTruncate(SqlGenerator sqlgen, DbFunctionExpression e, bool round)
{
SqlBuilder result = new SqlBuilder();
// Do not add the cast for the Round() overload having two arguments.
// Round(Single,Int32) maps to Round(Double,Int32)due to implicit casting.
// We don't need to cast in that case, since the server returned type is same
// as the expected type. Cast is only required for the overload - Round(Single)
bool requiresCastToSingle = false;
if (e.Arguments.Count == 1)
{
requiresCastToSingle = CastReturnTypeToSingle(e);
if (requiresCastToSingle)
{
result.Append(" CAST(");
}
}
result.Append("ROUND(");
Debug.Assert(e.Arguments.Count <= 2, "Round or truncate should have at most 2 arguments");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", ");
if (e.Arguments.Count > 1)
{
result.Append(e.Arguments[1].Accept(sqlgen));
}
else
{
result.Append("0");
}
if (!round)
{
result.Append(", 1");
}
result.Append(")");
if (requiresCastToSingle)
{
result.Append(" AS real)");
}
return result;
}
/// <summary>
/// Handler for all date/time addition canonical functions.
/// Translation, e.g.
/// DiffYears(datetime, number) => DATEDIFF(year, number, datetime)
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDateDiff(SqlGenerator sqlgen, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("DATEDIFF (");
result.Append(_dateDiffFunctionNameToDatepartDictionary[e.Function.Name]);
result.Append(", ");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", ");
result.Append(e.Arguments[1].Accept(sqlgen));
result.Append(")");
return result;
}
/// <summary>
/// TruncateTime(DateTime X)
/// PreKatmai: TRUNCATETIME(X) => CONVERT(DATETIME, CONVERT(VARCHAR(255), expression, 102), 102)
/// Katmai: TRUNCATETIME(X) => CONVERT(DATETIME2, CONVERT(VARCHAR(255), expression, 102), 102)
///
/// TruncateTime(DateTimeOffset X)
/// TRUNCATETIME(X) => CONVERT(datetimeoffset, CONVERT(VARCHAR(255), expression, 102)
/// + ' 00:00:00 ' + Right(convert(varchar(255), @arg, 121), 6), 102)
///
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionTruncateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{
//The type that we need to return is based on the argument type.
string typeName = null;
bool isDateTimeOffset = false;
PrimitiveTypeKind typeKind;
bool isPrimitiveType = TypeHelpers.TryGetPrimitiveTypeKind(e.Arguments[0].ResultType, out typeKind);
Debug.Assert(isPrimitiveType, "Expecting primitive type as input parameter to TruncateTime");
if (typeKind == PrimitiveTypeKind.DateTime)
{
typeName = sqlgen.IsPreKatmai ? "datetime" : "datetime2";
}
else if (typeKind == PrimitiveTypeKind.DateTimeOffset)
{
typeName = "datetimeoffset";
isDateTimeOffset = true;
}
else
{
Debug.Assert(true, "Unexpected type to TruncateTime" + typeKind.ToString());
}
SqlBuilder result = new SqlBuilder();
result.Append("convert (");
result.Append(typeName);
result.Append(", convert(varchar(255), ");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", 102) ");
if (isDateTimeOffset)
{
result.Append("+ ' 00:00:00 ' + Right(convert(varchar(255), ");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", 121), 6) ");
}
result.Append(", 102)");
return result;
}
/// <summary>
/// Handles functions that are translated into TSQL operators.
/// The given function should have one or two arguments.
/// Functions with one arguemnt are translated into
/// op arg
/// Functions with two arguments are translated into
/// arg0 op arg1
/// Also, the arguments can be optionaly enclosed in parethesis
/// </summary>
/// <param name="e"></param>
/// <param name="parenthesiseArguments">Whether the arguments should be enclosed in parethesis</param>
/// <returns></returns>
private static ISqlFragment HandleSpecialFunctionToOperator(SqlGenerator sqlgen, DbFunctionExpression e, bool parenthesiseArguments)
{
SqlBuilder result = new SqlBuilder();
Debug.Assert(e.Arguments.Count > 0 && e.Arguments.Count <= 2, "There should be 1 or 2 arguments for operator");
if (e.Arguments.Count > 1)
{
if (parenthesiseArguments)
{
result.Append("(");
}
result.Append(e.Arguments[0].Accept(sqlgen));
if (parenthesiseArguments)
{
result.Append(")");
}
}
result.Append(" ");
Debug.Assert(_functionNameToOperatorDictionary.ContainsKey(e.Function.Name), "The function can not be mapped to an operator");
result.Append(_functionNameToOperatorDictionary[e.Function.Name]);
result.Append(" ");
if (parenthesiseArguments)
{
result.Append("(");
}
result.Append(e.Arguments[e.Arguments.Count - 1].Accept(sqlgen));
if (parenthesiseArguments)
{
result.Append(")");
}
return result;
}
/// <summary>
/// Handles special case in which datapart 'type' parameter is present. all the functions
/// handles here have *only* the 1st parameter as datepart. datepart value is passed along
/// the QP as string and has to be expanded as TSQL keyword.
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleDatepartDateFunction(SqlGenerator sqlgen, DbFunctionExpression e)
{
Debug.Assert(e.Arguments.Count > 0, "e.Arguments.Count > 0");
DbConstantExpression constExpr = e.Arguments[0] as DbConstantExpression;
if (null == constExpr)
{
throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
}
string datepart = constExpr.Value as string;
if (null == datepart)
{
throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
}
SqlBuilder result = new SqlBuilder();
//
// check if datepart value is valid
//
if (!_datepartKeywords.Contains(datepart))
{
throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentValue(datepart, e.Function.NamespaceName, e.Function.Name));
}
//
// finaly, expand the function name
//
WriteFunctionName(result, e.Function);
result.Append("(");
// expand the datepart literal as tsql kword
result.Append(datepart);
string separator = ", ";
// expand remaining arguments
for (int i = 1; i < e.Arguments.Count; i++)
{
result.Append(separator);
result.Append(e.Arguments[i].Accept(sqlgen));
}
result.Append(")");
return result;
}
/// <summary>
/// ENDSWITH(arg0, arg1) => arg0 LIKE '%arg1'
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="args"></param>
/// <param name="result"></param>
/// <returns></returns>
private static SqlBuilder HandleCanonicalFunctionEndsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
{
Debug.Assert(args.Count == 2, "EndsWith should have two arguments");
// Check if args[1] is a DbConstantExpression and if args [0] is a DbPropertyExpression
DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
DbPropertyExpression targetParamExpression = args[0] as DbPropertyExpression;
if ((constSearchParamExpression != null) && (targetParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
{
// The LIKE optimization for EndsWith can only be used when the target is a column in table and
// the search string is a constant. This is because SQL Server ignores a trailing space in a query like:
// EndsWith('abcd ', 'cd'), which translates to:
// SELECT
// CASE WHEN ('abcd ' LIKE '%cd') THEN cast(1 as bit) WHEN ( NOT ('abcd ' LIKE '%cd')) THEN cast(0 as bit) END AS [C1]
// FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
// and "incorrectly" returns 1 (true), but the CLR would expect a 0 (false) back.
TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, true, false);
}
else
{
result.Append("CHARINDEX( REVERSE(");
result.Append(args[1].Accept(sqlgen));
result.Append("), REVERSE(");
result.Append(args[0].Accept(sqlgen));
result.Append(")) = 1");
}
return result;
}
/// <summary>
/// Turns a predicate into a statement returning a bit
/// PREDICATE => CASE WHEN (PREDICATE) THEN CAST(1 AS BIT) WHEN (NOT (PREDICATE)) CAST (O AS BIT) END
/// The predicate is produced by the given predicateTranslator.
/// </summary>
/// <param name="predicateTranslator"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment WrapPredicate(Func<SqlGenerator, IList<DbExpression>, SqlBuilder, SqlBuilder> predicateTranslator, SqlGenerator sqlgen, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("CASE WHEN (");
predicateTranslator(sqlgen, e.Arguments, result);
result.Append(") THEN cast(1 as bit) WHEN ( NOT (");
predicateTranslator(sqlgen, e.Arguments, result);
result.Append(")) THEN cast(0 as bit) END");
return result;
}
/// <summary>
/// Helper for all date and time types creating functions.
///
/// The given expression is in general trainslated into:
///
/// CONVERT(@typename, [datePart] + [timePart] + [timeZonePart], 121), where the datePart and the timeZonePart are optional
///
/// Only on Katmai, if a date part is present it is wrapped with a call for adding years as shown below.
/// The individual parts are translated as:
///
/// Date part:
/// PRE KATMAI: convert(varchar(255), @year) + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day)
/// KATMAI: DateAdd(year, @year-1, covert(@typename, '0001' + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day) + [possibly time ], 121)
///
/// Time part:
/// PRE KATMAI: convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 6, 3)
/// KATMAI: convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 10, 7)
///
/// Time zone part:
/// (case when @tzoffset >= 0 then '+' else '-' end) + convert(varchar(255), ABS(@tzoffset)/60) + ':' + convert(varchar(255), ABS(@tzoffset)%60)
///
/// </summary>
/// <param name="typeName"></param>
/// <param name="args"></param>
/// <param name="hasDatePart"></param>
/// <param name="hasTimeZonePart"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDateTimeTypeCreation(SqlGenerator sqlgen, string typeName, IList<DbExpression> args, bool hasDatePart, bool hasTimeZonePart)
{
Debug.Assert(args.Count == (hasDatePart ? 3 : 0) + 3 + (hasTimeZonePart ? 1 : 0), "Invalid number of parameters for a date time creating function");
SqlBuilder result = new SqlBuilder();
int currentArgumentIndex = 0;
if (!sqlgen.IsPreKatmai && hasDatePart)
{
result.Append("DATEADD(year, ");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex++], result);
result.Append(" - 1, ");
}
result.Append("convert (");
result.Append(typeName);
result.Append(",");
//Building the string representation
if (hasDatePart)
{
// YEAR: PREKATMAI: CONVERT(VARCHAR, @YEAR)
// KATMAI : '0001'
if (!sqlgen.IsPreKatmai)
{
result.Append("'0001'");
}
else
{
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
}
// MONTH
result.Append(" + '-' + ");
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
// DAY
result.Append(" + '-' + ");
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
result.Append(" + ' ' + ");
}
// HOUR
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
// MINUTE
result.Append(" + ':' + ");
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
// SECOND
result.Append(" + ':' + str(");
result.Append(args[currentArgumentIndex++].Accept(sqlgen));
if (sqlgen.IsPreKatmai)
{
result.Append(", 6, 3)");
}
else
{
result.Append(", 10, 7)");
}
// TZOFFSET
if (hasTimeZonePart)
{
result.Append(" + (CASE WHEN ");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
result.Append(" >= 0 THEN '+' ELSE '-' END) + convert(varchar(255), ABS(");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
result.Append("/60)) + ':' + convert(varchar(255), ABS(");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
result.Append("%60))");
}
result.Append(", 121)");
if (!sqlgen.IsPreKatmai && hasDatePart)
{
result.Append(")");
}
return result;
}
