mcs/class/referencesource/System.Data.Entity/System/Data/SqlClient/SqlGen/SqlFunctionCallHandler.cs

   1 //---------------------------------------------------------------------
   2 // <copyright file="SqlFunctionCallHandler.cs" company="Microsoft">
   3 //      Copyright (c) Microsoft Corporation.  All rights reserved.
   4 // </copyright>
   5 //
   6 // @owner  Microsoft
   7 // @backupOwner Microsoft
   8 //---------------------------------------------------------------------
   9
  10 namespace System.Data.SqlClient.SqlGen
  11 {
  12     using System;
  13     using System.Collections.Generic;
  14     using System.Data.Common;
  15     using System.Data.Common.CommandTrees;
  16     using System.Data.Common.CommandTrees.ExpressionBuilder;
  17     using System.Data.Common.Utils;
  18     using System.Data.Metadata.Edm;
  19     using System.Data.Spatial;
  20     using System.Data.SqlClient;
  21     using System.Diagnostics;
  22     using System.Linq;
  23     using System.Text;
  24
  25     /// <summary>
  26     /// Enacapsulates the logic required to translate function calls represented as instances of DbFunctionExpression into SQL.
  27     /// There are several special cases that modify how the translation should proceed. These include:
  28     /// - 'Special' canonical functions, for which the function name or arguments differ between the EDM canonical function and the SQL function
  29     /// - 'Special' server functions, which are similar to the 'special' canonical functions but sourced by the SQL Server provider manifest
  30     /// - Niladic functions, which require the parentheses that would usually follow the function name to be omitted
  31     /// - Spatial canonical functions, which must translate to a static method call, instance method call, or instance property access against
  32     ///   one of the built-in spatial CLR UDTs (geography/geometry).
  33     /// </summary>
  34     internal static class SqlFunctionCallHandler
  35     {
  36         #region Static fields, include dictionaries used to dispatch function handling
  37
  38         static private readonly Dictionary<string, FunctionHandler> _storeFunctionHandlers = InitializeStoreFunctionHandlers();
  39         static private readonly Dictionary<string, FunctionHandler> _canonicalFunctionHandlers = InitializeCanonicalFunctionHandlers();
  40         static private readonly Dictionary<string, string> _functionNameToOperatorDictionary = InitializeFunctionNameToOperatorDictionary();
  41         static private readonly Dictionary<string, string> _dateAddFunctionNameToDatepartDictionary = InitializeDateAddFunctionNameToDatepartDictionary();
  42         static private readonly Dictionary<string, string> _dateDiffFunctionNameToDatepartDictionary = InitializeDateDiffFunctionNameToDatepartDictionary();
  43         static private readonly Dictionary<string, FunctionHandler> _geographyFunctionNameToStaticMethodHandlerDictionary = InitializeGeographyStaticMethodFunctionsDictionary();
  44         static private readonly Dictionary<string, string> _geographyFunctionNameToInstancePropertyNameDictionary = InitializeGeographyInstancePropertyFunctionsDictionary();
  45         static private readonly Dictionary<string, string> _geographyRenamedInstanceMethodFunctionDictionary = InitializeRenamedGeographyInstanceMethodFunctions();
  46         static private readonly Dictionary<string, FunctionHandler> _geometryFunctionNameToStaticMethodHandlerDictionary = InitializeGeometryStaticMethodFunctionsDictionary();
  47         static private readonly Dictionary<string, string> _geometryFunctionNameToInstancePropertyNameDictionary = InitializeGeometryInstancePropertyFunctionsDictionary();
  48         static private readonly Dictionary<string, string> _geometryRenamedInstanceMethodFunctionDictionary = InitializeRenamedGeometryInstanceMethodFunctions();
  49         static private readonly Set<string> _datepartKeywords = new Set<string>(new string[] {  "year", "yy", "yyyy",
  50                                                                                                  "quarter", "qq", "q",
  51                                                                                                  "month", "mm", "m",
  52                                                                                                  "dayofyear", "dy", "y",
  53                                                                                                  "day", "dd", "d",
  54                                                                                                  "week", "wk", "ww",
  55                                                                                                  "weekday", "dw", "w",
  56                                                                                                  "hour", "hh",
  57                                                                                                  "minute", "mi", "n",
  58                                                                                                  "second", "ss", "s",
  59                                                                                                  "millisecond", "ms",
  60                                                                                                  "microsecond", "mcs",
  61                                                                                                  "nanosecond", "ns",
  62                                                                                                  "tzoffset", "tz",
  63                                                                                                  "iso_week", "isoww", "isowk"},
  64                                                                                         StringComparer.OrdinalIgnoreCase).MakeReadOnly();
  65         static private readonly Set<string> _functionRequiresReturnTypeCastToInt64 = new Set<string>(new string[] { "SqlServer.CHARINDEX" },
  66                                                                                               StringComparer.Ordinal).MakeReadOnly();
  67         static private readonly Set<string> _functionRequiresReturnTypeCastToInt32 = new Set<string>(new string[] { "SqlServer.LEN"      ,
  68                                                                                                                  "SqlServer.PATINDEX"    ,
  69                                                                                                                  "SqlServer.DATALENGTH"  ,
  70                                                                                                                  "SqlServer.CHARINDEX"   ,
  71                                                                                                                  "Edm.IndexOf"           ,
  72                                                                                                                  "Edm.Length"            },
  73                                                                                                       StringComparer.Ordinal).MakeReadOnly();
  74         static private readonly Set<string> _functionRequiresReturnTypeCastToInt16 = new Set<string>(new string[] { "Edm.Abs"            },
  75                                                                                                       StringComparer.Ordinal).MakeReadOnly();
  76         static private readonly Set<string> _functionRequiresReturnTypeCastToSingle = new Set<string>(new string[] { "Edm.Abs"           ,
  77                                                                                                                      "Edm.Round"         ,
  78                                                                                                                      "Edm.Floor"         ,
  79                                                                                                                      "Edm.Ceiling"       },
  80                                                                                                       StringComparer.Ordinal).MakeReadOnly();
  81         static private readonly Set<string> _maxTypeNames = new Set<string>(new string[] { "varchar(max)"    ,
  82                                                                                            "nvarchar(max)"   ,
  83                                                                                            "text"            ,
  84                                                                                            "ntext"           ,
  85                                                                                            "varbinary(max)"  ,
  86                                                                                            "image"           ,
  87                                                                                            "xml"             },
  88                                                                                 StringComparer.Ordinal).MakeReadOnly();
  89
  90         #endregion
  91
  92         #region Static dictionary initialization
  93
  94         private delegate ISqlFragment FunctionHandler(SqlGenerator sqlgen, DbFunctionExpression functionExpr);
  95
  96         /// <summary>
  97         /// All special store functions and their handlers
  98         /// </summary>
  99         /// <returns></returns>
 100         private static Dictionary<string, FunctionHandler> InitializeStoreFunctionHandlers()
 101         {
 102             Dictionary<string, FunctionHandler> functionHandlers = new Dictionary<string, FunctionHandler>(15, StringComparer.Ordinal);
 103             functionHandlers.Add("CONCAT", HandleConcatFunction);
 104             functionHandlers.Add("DATEADD", HandleDatepartDateFunction);
 105             functionHandlers.Add("DATEDIFF", HandleDatepartDateFunction);
 106             functionHandlers.Add("DATENAME", HandleDatepartDateFunction);
 107             functionHandlers.Add("DATEPART", HandleDatepartDateFunction);
 108
 109             // Spatial functions are mapped to static or instance members of geography/geometry
 110             // Geography Static functions
 111             functionHandlers.Add("POINTGEOGRAPHY", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::Point"));
 112
 113             // Geometry Static functions
 114             functionHandlers.Add("POINTGEOMETRY", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::Point"));
 115
 116             // Spatial Instance functions (shared)
 117             functionHandlers.Add("ASTEXTZM", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "AsTextZM", functionExpression, isPropertyAccess: false));
 118             functionHandlers.Add("BUFFERWITHTOLERANCE", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "BufferWithTolerance", functionExpression, isPropertyAccess: false));
 119             functionHandlers.Add("ENVELOPEANGLE", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "EnvelopeAngle", functionExpression, isPropertyAccess: false));
 120             functionHandlers.Add("ENVELOPECENTER", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "EnvelopeCenter", functionExpression, isPropertyAccess: false));
 121             functionHandlers.Add("INSTANCEOF", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "InstanceOf", functionExpression, isPropertyAccess: false));
 122             functionHandlers.Add("FILTER", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "Filter", functionExpression, isPropertyAccess: false));
 123             functionHandlers.Add("MAKEVALID", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "MakeValid", functionExpression, isPropertyAccess: false));
 124             functionHandlers.Add("REDUCE", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "Reduce", functionExpression, isPropertyAccess: false));
 125             functionHandlers.Add("NUMRINGS", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "NumRings", functionExpression, isPropertyAccess: false));
 126             functionHandlers.Add("RINGN", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "RingN", functionExpression, isPropertyAccess: false));
 127
 128             return functionHandlers;
 129         }
 130
 131         /// <summary>
 132         /// All special non-aggregate canonical functions and their handlers
 133         /// </summary>
 134         /// <returns></returns>
 135         private static Dictionary<string, FunctionHandler> InitializeCanonicalFunctionHandlers()
 136         {
 137             Dictionary<string, FunctionHandler> functionHandlers = new Dictionary<string, FunctionHandler>(16, StringComparer.Ordinal);
 138             functionHandlers.Add("IndexOf", HandleCanonicalFunctionIndexOf);
 139             functionHandlers.Add("Length", HandleCanonicalFunctionLength);
 140             functionHandlers.Add("NewGuid", HandleCanonicalFunctionNewGuid);
 141             functionHandlers.Add("Round", HandleCanonicalFunctionRound);
 142             functionHandlers.Add("Truncate", HandleCanonicalFunctionTruncate);
 143             functionHandlers.Add("Abs", HandleCanonicalFunctionAbs);
 144             functionHandlers.Add("ToLower", HandleCanonicalFunctionToLower);
 145             functionHandlers.Add("ToUpper", HandleCanonicalFunctionToUpper);
 146             functionHandlers.Add("Trim", HandleCanonicalFunctionTrim);
 147             functionHandlers.Add("Contains", HandleCanonicalFunctionContains);
 148             functionHandlers.Add("StartsWith", HandleCanonicalFunctionStartsWith);
 149             functionHandlers.Add("EndsWith", HandleCanonicalFunctionEndsWith);
 150
 151             //DateTime Functions
 152             functionHandlers.Add("Year", HandleCanonicalFunctionDatepart);
 153             functionHandlers.Add("Month", HandleCanonicalFunctionDatepart);
 154             functionHandlers.Add("Day", HandleCanonicalFunctionDatepart);
 155             functionHandlers.Add("Hour", HandleCanonicalFunctionDatepart);
 156             functionHandlers.Add("Minute", HandleCanonicalFunctionDatepart);
 157             functionHandlers.Add("Second", HandleCanonicalFunctionDatepart);
 158             functionHandlers.Add("Millisecond", HandleCanonicalFunctionDatepart);
 159             functionHandlers.Add("DayOfYear", HandleCanonicalFunctionDatepart);
 160             functionHandlers.Add("CurrentDateTime", HandleCanonicalFunctionCurrentDateTime);
 161             functionHandlers.Add("CurrentUtcDateTime", HandleCanonicalFunctionCurrentUtcDateTime);
 162             functionHandlers.Add("CurrentDateTimeOffset", HandleCanonicalFunctionCurrentDateTimeOffset);
 163             functionHandlers.Add("GetTotalOffsetMinutes", HandleCanonicalFunctionGetTotalOffsetMinutes);
 164             functionHandlers.Add("TruncateTime", HandleCanonicalFunctionTruncateTime);
 165             functionHandlers.Add("CreateDateTime", HandleCanonicalFunctionCreateDateTime);
 166             functionHandlers.Add("CreateDateTimeOffset", HandleCanonicalFunctionCreateDateTimeOffset);
 167             functionHandlers.Add("CreateTime", HandleCanonicalFunctionCreateTime);
 168             functionHandlers.Add("AddYears", HandleCanonicalFunctionDateAdd);
 169             functionHandlers.Add("AddMonths", HandleCanonicalFunctionDateAdd);
 170             functionHandlers.Add("AddDays", HandleCanonicalFunctionDateAdd);
 171             functionHandlers.Add("AddHours", HandleCanonicalFunctionDateAdd);
 172             functionHandlers.Add("AddMinutes", HandleCanonicalFunctionDateAdd);
 173             functionHandlers.Add("AddSeconds", HandleCanonicalFunctionDateAdd);
 174             functionHandlers.Add("AddMilliseconds", HandleCanonicalFunctionDateAdd);
 175             functionHandlers.Add("AddMicroseconds", HandleCanonicalFunctionDateAddKatmaiOrNewer);
 176             functionHandlers.Add("AddNanoseconds", HandleCanonicalFunctionDateAddKatmaiOrNewer);
 177             functionHandlers.Add("DiffYears", HandleCanonicalFunctionDateDiff);
 178             functionHandlers.Add("DiffMonths", HandleCanonicalFunctionDateDiff);
 179             functionHandlers.Add("DiffDays", HandleCanonicalFunctionDateDiff);
 180             functionHandlers.Add("DiffHours", HandleCanonicalFunctionDateDiff);
 181             functionHandlers.Add("DiffMinutes", HandleCanonicalFunctionDateDiff);
 182             functionHandlers.Add("DiffSeconds", HandleCanonicalFunctionDateDiff);
 183             functionHandlers.Add("DiffMilliseconds", HandleCanonicalFunctionDateDiff);
 184             functionHandlers.Add("DiffMicroseconds", HandleCanonicalFunctionDateDiffKatmaiOrNewer);
 185             functionHandlers.Add("DiffNanoseconds", HandleCanonicalFunctionDateDiffKatmaiOrNewer);
 186
 187             //Functions that translate to operators
 188             functionHandlers.Add("Concat", HandleConcatFunction);
 189             functionHandlers.Add("BitwiseAnd", HandleCanonicalFunctionBitwise);
 190             functionHandlers.Add("BitwiseNot", HandleCanonicalFunctionBitwise);
 191             functionHandlers.Add("BitwiseOr", HandleCanonicalFunctionBitwise);
 192             functionHandlers.Add("BitwiseXor", HandleCanonicalFunctionBitwise);
 193
 194             return functionHandlers;
 195         }
 196
 197         /// <summary>
 198         /// Initalizes the mapping from functions to TSql operators
 199         /// for all functions that translate to TSql operators
 200         /// </summary>
 201         /// <returns></returns>
 202         private static Dictionary<string, string> InitializeFunctionNameToOperatorDictionary()
 203         {
 204             Dictionary<string, string> functionNameToOperatorDictionary = new Dictionary<string, string>(5, StringComparer.Ordinal);
 205             functionNameToOperatorDictionary.Add("Concat", "+");    //canonical
 206             functionNameToOperatorDictionary.Add("CONCAT", "+");    //store
 207             functionNameToOperatorDictionary.Add("BitwiseAnd", "&");
 208             functionNameToOperatorDictionary.Add("BitwiseNot", "~");
 209             functionNameToOperatorDictionary.Add("BitwiseOr", "|");
 210             functionNameToOperatorDictionary.Add("BitwiseXor", "^");
 211             return functionNameToOperatorDictionary;
 212         }
 213
 214         /// <summary>
 215         /// Initalizes the mapping from names of canonical function for date/time addition
 216         /// to corresponding dateparts
 217         /// </summary>
 218         /// <returns></returns>
 219         private static Dictionary<string, string> InitializeDateAddFunctionNameToDatepartDictionary()
 220         {
 221             Dictionary<string, string> dateAddFunctionNameToDatepartDictionary = new Dictionary<string, string>(5, StringComparer.Ordinal);
 222             dateAddFunctionNameToDatepartDictionary.Add("AddYears", "year");
 223             dateAddFunctionNameToDatepartDictionary.Add("AddMonths", "month");
 224             dateAddFunctionNameToDatepartDictionary.Add("AddDays", "day");
 225             dateAddFunctionNameToDatepartDictionary.Add("AddHours", "hour");
 226             dateAddFunctionNameToDatepartDictionary.Add("AddMinutes", "minute");
 227             dateAddFunctionNameToDatepartDictionary.Add("AddSeconds", "second");
 228             dateAddFunctionNameToDatepartDictionary.Add("AddMilliseconds", "millisecond");
 229             dateAddFunctionNameToDatepartDictionary.Add("AddMicroseconds", "microsecond");
 230             dateAddFunctionNameToDatepartDictionary.Add("AddNanoseconds", "nanosecond");
 231             return dateAddFunctionNameToDatepartDictionary;
 232         }
 233
 234         /// <summary>
 235         /// Initalizes the mapping from names of canonical function for date/time difference
 236         /// to corresponding dateparts
 237         /// </summary>
 238         /// <returns></returns>
 239         private static Dictionary<string, string> InitializeDateDiffFunctionNameToDatepartDictionary()
 240         {
 241             Dictionary<string, string> dateDiffFunctionNameToDatepartDictionary = new Dictionary<string, string>(5, StringComparer.Ordinal);
 242             dateDiffFunctionNameToDatepartDictionary.Add("DiffYears", "year");
 243             dateDiffFunctionNameToDatepartDictionary.Add("DiffMonths", "month");
 244             dateDiffFunctionNameToDatepartDictionary.Add("DiffDays", "day");
 245             dateDiffFunctionNameToDatepartDictionary.Add("DiffHours", "hour");
 246             dateDiffFunctionNameToDatepartDictionary.Add("DiffMinutes", "minute");
 247             dateDiffFunctionNameToDatepartDictionary.Add("DiffSeconds", "second");
 248             dateDiffFunctionNameToDatepartDictionary.Add("DiffMilliseconds", "millisecond");
 249             dateDiffFunctionNameToDatepartDictionary.Add("DiffMicroseconds", "microsecond");
 250             dateDiffFunctionNameToDatepartDictionary.Add("DiffNanoseconds", "nanosecond");
 251             return dateDiffFunctionNameToDatepartDictionary;
 252         }
 253
 254         /// <summary>
 255         /// Initalizes the mapping from names of canonical function that represent static geography methods to their corresponding
 256         /// static method name, qualified with the 'geography::' prefix.
 257         /// </summary>
 258         /// <returns></returns>
 259         private static Dictionary<string, FunctionHandler> InitializeGeographyStaticMethodFunctionsDictionary()
 260         {
 261             Dictionary<string, FunctionHandler> staticGeographyFunctions = new Dictionary<string, FunctionHandler>();
 262
 263             // Well Known Text constructors
 264             staticGeographyFunctions.Add("GeographyFromText", HandleSpatialFromTextFunction);
 265             staticGeographyFunctions.Add("GeographyPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPointFromText"));
 266             staticGeographyFunctions.Add("GeographyLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STLineFromText"));
 267             staticGeographyFunctions.Add("GeographyPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPolyFromText"));
 268             staticGeographyFunctions.Add("GeographyMultiPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPointFromText"));
 269             staticGeographyFunctions.Add("GeographyMultiLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMLineFromText"));
 270             staticGeographyFunctions.Add("GeographyMultiPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPolyFromText"));
 271             staticGeographyFunctions.Add("GeographyCollectionFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STGeomCollFromText"));
 272
 273             // Well Known Binary constructors
 274             staticGeographyFunctions.Add("GeographyFromBinary", HandleSpatialFromBinaryFunction);
 275             staticGeographyFunctions.Add("GeographyPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPointFromWKB"));
 276             staticGeographyFunctions.Add("GeographyLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STLineFromWKB"));
 277             staticGeographyFunctions.Add("GeographyPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPolyFromWKB"));
 278             staticGeographyFunctions.Add("GeographyMultiPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPointFromWKB"));
 279             staticGeographyFunctions.Add("GeographyMultiLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMLineFromWKB"));
 280             staticGeographyFunctions.Add("GeographyMultiPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPolyFromWKB"));
 281             staticGeographyFunctions.Add("GeographyCollectionFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STGeomCollFromWKB"));
 282
 283             // GML constructor (non-OGC)
 284             staticGeographyFunctions.Add("GeographyFromGml", HandleSpatialFromGmlFunction);
 285
 286             return staticGeographyFunctions;
 287         }
 288
 289         /// <summary>
 290         /// Initalizes the mapping from names of canonical function that represent geography instance properties to their corresponding
 291         /// store property name.
 292         /// </summary>
 293         /// <returns></returns>
 294         private static Dictionary<string, string> InitializeGeographyInstancePropertyFunctionsDictionary()
 295         {
 296             Dictionary<string, string> instancePropGeographyFunctions = new Dictionary<string, string>();
 297
 298             instancePropGeographyFunctions.Add("CoordinateSystemId", "STSrid");
 299             instancePropGeographyFunctions.Add("Latitude", "Lat");
 300             instancePropGeographyFunctions.Add("Longitude", "Long");
 301             instancePropGeographyFunctions.Add("Measure", "M");
 302             instancePropGeographyFunctions.Add("Elevation", "Z");
 303
 304             return instancePropGeographyFunctions;
 305         }
 306
 307         /// <summary>
 308         /// Initalizes the mapping of canonical function name to instance method name for geography instance functions that differ in name from the sql server equivalent.
 309         /// </summary>
 310         /// <returns></returns>
 311         private static Dictionary<string, string> InitializeRenamedGeographyInstanceMethodFunctions()
 312         {
 313             Dictionary<string, string> renamedInstanceMethodFunctions = new Dictionary<string, string>();
 314
 315             renamedInstanceMethodFunctions.Add("AsText", "STAsText");
 316             renamedInstanceMethodFunctions.Add("AsBinary", "STAsBinary");
 317             renamedInstanceMethodFunctions.Add("SpatialTypeName", "STGeometryType");
 318             renamedInstanceMethodFunctions.Add("SpatialDimension", "STDimension");
 319             renamedInstanceMethodFunctions.Add("IsEmptySpatial", "STIsEmpty");
 320             renamedInstanceMethodFunctions.Add("SpatialEquals", "STEquals");
 321             renamedInstanceMethodFunctions.Add("SpatialDisjoint", "STDisjoint");
 322             renamedInstanceMethodFunctions.Add("SpatialIntersects", "STIntersects");
 323             renamedInstanceMethodFunctions.Add("SpatialBuffer", "STBuffer");
 324             renamedInstanceMethodFunctions.Add("Distance", "STDistance");
 325             renamedInstanceMethodFunctions.Add("SpatialUnion", "STUnion");
 326             renamedInstanceMethodFunctions.Add("SpatialIntersection", "STIntersection");
 327             renamedInstanceMethodFunctions.Add("SpatialDifference", "STDifference");
 328             renamedInstanceMethodFunctions.Add("SpatialSymmetricDifference", "STSymDifference");
 329             renamedInstanceMethodFunctions.Add("SpatialElementCount", "STNumGeometries");
 330             renamedInstanceMethodFunctions.Add("SpatialElementAt", "STGeometryN");
 331             renamedInstanceMethodFunctions.Add("SpatialLength", "STLength");
 332             renamedInstanceMethodFunctions.Add("StartPoint", "STStartPoint");
 333             renamedInstanceMethodFunctions.Add("EndPoint", "STEndPoint");
 334             renamedInstanceMethodFunctions.Add("IsClosedSpatial", "STIsClosed");
 335             renamedInstanceMethodFunctions.Add("PointCount", "STNumPoints");
 336             renamedInstanceMethodFunctions.Add("PointAt", "STPointN");
 337             renamedInstanceMethodFunctions.Add("Area", "STArea");
 338
 339             return renamedInstanceMethodFunctions;
 340         }
 341
 342         /// <summary>
 343         /// Initalizes the mapping from names of canonical function that represent static geometry methods to their corresponding
 344         /// static method name, qualified with the 'geometry::' prefix.
 345         /// </summary>
 346         /// <returns></returns>
 347         private static Dictionary<string, FunctionHandler> InitializeGeometryStaticMethodFunctionsDictionary()
 348         {
 349             Dictionary<string, FunctionHandler> staticGeometryFunctions = new Dictionary<string, FunctionHandler>();
 350
 351             // Well Known Text constructors
 352             staticGeometryFunctions.Add("GeometryFromText", HandleSpatialFromTextFunction);
 353             staticGeometryFunctions.Add("GeometryPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPointFromText"));
 354             staticGeometryFunctions.Add("GeometryLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STLineFromText"));
 355             staticGeometryFunctions.Add("GeometryPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPolyFromText"));
 356             staticGeometryFunctions.Add("GeometryMultiPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPointFromText"));
 357             staticGeometryFunctions.Add("GeometryMultiLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMLineFromText"));
 358             staticGeometryFunctions.Add("GeometryMultiPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPolyFromText"));
 359             staticGeometryFunctions.Add("GeometryCollectionFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STGeomCollFromText"));
 360
 361             // Well Known Binary constructors
 362             staticGeometryFunctions.Add("GeometryFromBinary", HandleSpatialFromBinaryFunction);
 363             staticGeometryFunctions.Add("GeometryPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPointFromWKB"));
 364             staticGeometryFunctions.Add("GeometryLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STLineFromWKB"));
 365             staticGeometryFunctions.Add("GeometryPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPolyFromWKB"));
 366             staticGeometryFunctions.Add("GeometryMultiPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPointFromWKB"));
 367             staticGeometryFunctions.Add("GeometryMultiLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMLineFromWKB"));
 368             staticGeometryFunctions.Add("GeometryMultiPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPolyFromWKB"));
 369             staticGeometryFunctions.Add("GeometryCollectionFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STGeomCollFromWKB"));
 370
 371             // GML constructor (non-OGC)
 372             staticGeometryFunctions.Add("GeometryFromGml", HandleSpatialFromGmlFunction);
 373
 374             return staticGeometryFunctions;
 375         }
 376
 377         /// <summary>
 378         /// Initalizes the mapping from names of canonical function that represent geometry instance properties to their corresponding
 379         /// store property name.
 380         /// </summary>
 381         /// <returns></returns>
 382         private static Dictionary<string, string> InitializeGeometryInstancePropertyFunctionsDictionary()
 383         {
 384             Dictionary<string, string> instancePropGeometryFunctions = new Dictionary<string, string>();
 385
 386             instancePropGeometryFunctions.Add("CoordinateSystemId", "STSrid");
 387             instancePropGeometryFunctions.Add("Measure", "M");
 388             instancePropGeometryFunctions.Add("XCoordinate", "STX");
 389             instancePropGeometryFunctions.Add("YCoordinate", "STY");
 390             instancePropGeometryFunctions.Add("Elevation", "Z");
 391
 392             return instancePropGeometryFunctions;
 393         }
 394
 395         /// <summary>
 396         /// Initalizes the mapping of canonical function name to instance method name for geometry instance functions that differ in name from the sql server equivalent.
 397         /// </summary>
 398         /// <returns></returns>
 399         private static Dictionary<string, string> InitializeRenamedGeometryInstanceMethodFunctions()
 400         {
 401             Dictionary<string, string> renamedInstanceMethodFunctions = new Dictionary<string, string>();
 402
 403             renamedInstanceMethodFunctions.Add("AsText", "STAsText");
 404             renamedInstanceMethodFunctions.Add("AsBinary", "STAsBinary");
 405             renamedInstanceMethodFunctions.Add("SpatialTypeName", "STGeometryType");
 406             renamedInstanceMethodFunctions.Add("SpatialDimension", "STDimension");
 407             renamedInstanceMethodFunctions.Add("IsEmptySpatial", "STIsEmpty");
 408             renamedInstanceMethodFunctions.Add("IsSimpleGeometry", "STIsSimple");
 409             renamedInstanceMethodFunctions.Add("IsValidGeometry", "STIsValid");
 410             renamedInstanceMethodFunctions.Add("SpatialBoundary", "STBoundary");
 411             renamedInstanceMethodFunctions.Add("SpatialEnvelope", "STEnvelope");
 412             renamedInstanceMethodFunctions.Add("SpatialEquals", "STEquals");
 413             renamedInstanceMethodFunctions.Add("SpatialDisjoint", "STDisjoint");
 414             renamedInstanceMethodFunctions.Add("SpatialIntersects", "STIntersects");
 415             renamedInstanceMethodFunctions.Add("SpatialTouches", "STTouches");
 416             renamedInstanceMethodFunctions.Add("SpatialCrosses", "STCrosses");
 417             renamedInstanceMethodFunctions.Add("SpatialWithin", "STWithin");
 418             renamedInstanceMethodFunctions.Add("SpatialContains", "STContains");
 419             renamedInstanceMethodFunctions.Add("SpatialOverlaps", "STOverlaps");
 420             renamedInstanceMethodFunctions.Add("SpatialRelate", "STRelate");
 421             renamedInstanceMethodFunctions.Add("SpatialBuffer", "STBuffer");
 422             renamedInstanceMethodFunctions.Add("SpatialConvexHull", "STConvexHull");
 423             renamedInstanceMethodFunctions.Add("Distance", "STDistance");
 424             renamedInstanceMethodFunctions.Add("SpatialUnion", "STUnion");
 425             renamedInstanceMethodFunctions.Add("SpatialIntersection", "STIntersection");
 426             renamedInstanceMethodFunctions.Add("SpatialDifference", "STDifference");
 427             renamedInstanceMethodFunctions.Add("SpatialSymmetricDifference", "STSymDifference");
 428             renamedInstanceMethodFunctions.Add("SpatialElementCount", "STNumGeometries");
 429             renamedInstanceMethodFunctions.Add("SpatialElementAt", "STGeometryN");
 430             renamedInstanceMethodFunctions.Add("SpatialLength", "STLength");
 431             renamedInstanceMethodFunctions.Add("StartPoint", "STStartPoint");
 432             renamedInstanceMethodFunctions.Add("EndPoint", "STEndPoint");
 433             renamedInstanceMethodFunctions.Add("IsClosedSpatial", "STIsClosed");
 434             renamedInstanceMethodFunctions.Add("IsRing", "STIsRing");
 435             renamedInstanceMethodFunctions.Add("PointCount", "STNumPoints");
 436             renamedInstanceMethodFunctions.Add("PointAt", "STPointN");
 437             renamedInstanceMethodFunctions.Add("Area", "STArea");
 438             renamedInstanceMethodFunctions.Add("Centroid", "STCentroid");
 439             renamedInstanceMethodFunctions.Add("PointOnSurface", "STPointOnSurface");
 440             renamedInstanceMethodFunctions.Add("ExteriorRing", "STExteriorRing");
 441             renamedInstanceMethodFunctions.Add("InteriorRingCount", "STNumInteriorRing");
 442             renamedInstanceMethodFunctions.Add("InteriorRingAt", "STInteriorRingN");
 443
 444             return renamedInstanceMethodFunctions;
 445         }
 446
 447         private static ISqlFragment HandleSpatialFromTextFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
 448         {
 449             string functionNameWithSrid = (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::STGeomFromText" : "geography::STGeomFromText");
 450             string functionNameWithoutSrid = (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::Parse" : "geography::Parse");
 451
 452             if (functionExpression.Arguments.Count == 2)
 453             {
 454                 return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionNameWithSrid);
 455             }
 456             else
 457             {
 458                 Debug.Assert(functionExpression.Arguments.Count == 1, "FromText function should have text or text + srid arguments only");
 459                 return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionNameWithoutSrid);
 460             }
 461         }
 462
 463
 464         private static ISqlFragment HandleSpatialFromGmlFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
 465         {
 466             return HandleSpatialStaticMethodFunctionAppendSrid(sqlgen, functionExpression, (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::GeomFromGml" : "geography::GeomFromGml"));
 467         }
 468
 469         private static ISqlFragment HandleSpatialFromBinaryFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
 470         {
 471             return HandleSpatialStaticMethodFunctionAppendSrid(sqlgen, functionExpression, (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::STGeomFromWKB" : "geography::STGeomFromWKB"));
 472         }
 473
 474         private static readonly DbExpression defaultGeographySridExpression = DbExpressionBuilder.Constant(DbGeography.DefaultCoordinateSystemId);
 475         private static readonly DbExpression defaultGeometrySridExpression = DbExpressionBuilder.Constant(DbGeometry.DefaultCoordinateSystemId);
 476
 477         private static ISqlFragment HandleSpatialStaticMethodFunctionAppendSrid(SqlGenerator sqlgen, DbFunctionExpression functionExpression, string functionName)
 478         {
 479             if (functionExpression.Arguments.Count == 2)
 480             {
 481                 return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionName);
 482             }
 483             else
 484             {
 485                 DbExpression sridExpression = (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? defaultGeometrySridExpression : defaultGeographySridExpression);
 486                 SqlBuilder result = new SqlBuilder();
 487                 result.Append(functionName);
 488                 WriteFunctionArguments(sqlgen, functionExpression.Arguments.Concat(new[] { sridExpression }), result);
 489                 return result;
 490             }
 491         }
 492
 493         #endregion
 494
 495         internal static ISqlFragment GenerateFunctionCallSql(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
 496         {
 497             //
 498             // check if function requires special case processing, if so, delegates to it
 499             //
 500             if (IsSpecialCanonicalFunction(functionExpression))
 501             {
 502                 return HandleSpecialCanonicalFunction(sqlgen, functionExpression);
 503             }
 504
 505             if (IsSpecialStoreFunction(functionExpression))
 506             {
 507                 return HandleSpecialStoreFunction(sqlgen, functionExpression);
 508             }
 509
 510             PrimitiveTypeKind spatialTypeKind;
 511             if(IsSpatialCanonicalFunction(functionExpression, out spatialTypeKind))
 512             {
 513                 return HandleSpatialCanonicalFunction(sqlgen, functionExpression, spatialTypeKind);
 514             }
 515
 516             return HandleFunctionDefault(sqlgen, functionExpression);
 517         }
 518
 519         /// <summary>
 520         /// Determines whether the given function is a store function that
 521         /// requires special handling
 522         /// </summary>
 523         /// <param name="e"></param>
 524         /// <returns></returns>
 525         private static bool IsSpecialStoreFunction(DbFunctionExpression e)
 526         {
 527             return IsStoreFunction(e.Function)
 528                 && _storeFunctionHandlers.ContainsKey(e.Function.Name);
 529         }
 530
 531         /// <summary>
 532         /// Determines whether the given function is a canonical function that
 533         /// requires special handling
 534         /// </summary>
 535         /// <param name="e"></param>
 536         /// <returns></returns>
 537         private static bool IsSpecialCanonicalFunction(DbFunctionExpression e)
 538         {
 539             return TypeHelpers.IsCanonicalFunction(e.Function)
 540             && _canonicalFunctionHandlers.ContainsKey(e.Function.Name);
 541         }
 542
 543         /// <summary>
 544         /// Determines whether the given function is a canonical function the translates
 545         /// to a spatial (geography/geometry) property access or method call.
 546         /// </summary>
 547         /// <param name="e"></param>
 548         /// <returns></returns>
 549         private static bool IsSpatialCanonicalFunction(DbFunctionExpression e, out PrimitiveTypeKind spatialTypeKind)
 550         {
 551             if (TypeHelpers.IsCanonicalFunction(e.Function))
 552             {
 553                 if (Helper.IsSpatialType(e.ResultType, out spatialTypeKind))
 554                 {
 555                     return true;
 556                 }
 557
 558                 foreach (FunctionParameter functionParameter in e.Function.Parameters)
 559                 {
 560                     if (Helper.IsSpatialType(functionParameter.TypeUsage, out spatialTypeKind))
 561                     {
 562                         return true;
 563                     }
 564                 }
 565             }
 566
 567             spatialTypeKind = default(PrimitiveTypeKind);
 568             return false;
 569         }
 570
 571         /// <summary>
 572         /// Default handling for functions.
 573         /// Translates them to FunctionName(arg1, arg2, ..., argn)
 574         /// </summary>
 575         /// <param name="e"></param>
 576         /// <returns></returns>
 577         private static ISqlFragment HandleFunctionDefault(SqlGenerator sqlgen, DbFunctionExpression e)
 578         {
 579             return HandleFunctionDefaultGivenName(sqlgen, e, null);
 580         }
 581
 582         /// <summary>
 583         /// Default handling for functions with a given name.
 584         /// Translates them to FunctionName(arg1, arg2, ..., argn)
 585         /// </summary>
 586         /// <param name="e"></param>
 587         /// <param name="functionName"></param>
 588         /// <returns></returns>
 589         private static ISqlFragment HandleFunctionDefaultGivenName(SqlGenerator sqlgen, DbFunctionExpression e, string functionName)
 590         {
 591             // NOTE: The order of checks is important in case of CHARINDEX.
 592             if (CastReturnTypeToInt64(e))
 593             {
 594                 return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "bigint");
 595             }
 596             else if (CastReturnTypeToInt32(sqlgen, e))
 597             {
 598                 return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "int");
 599             }
 600             else if (CastReturnTypeToInt16(e))
 601             {
 602                 return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "smallint");
 603             }
 604             else if (CastReturnTypeToSingle(e))
 605             {
 606                 return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "real");
 607             }
 608             else
 609             {
 610                 return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, null);
 611             }
 612         }
 613
 614         /// <summary>
 615         /// Default handling for functions with a given name and given return value cast.
 616         /// Translates them to CAST(FunctionName(arg1, arg2, ..., argn) AS returnType)
 617         /// </summary>
 618         /// <param name="e"></param>
 619         /// <param name="functionName"></param>
 620         /// <param name="returnType"></param>
 621         /// <returns></returns>
 622         private static ISqlFragment HandleFunctionDefaultCastReturnValue(SqlGenerator sqlgen, DbFunctionExpression e, string functionName, string returnType)
 623         {
 624             return WrapWithCast(returnType, result =>
 625             {
 626                 if (functionName == null)
 627                 {
 628                     WriteFunctionName(result, e.Function);
 629                 }
 630                 else
 631                 {
 632                     result.Append(functionName);
 633                 }
 634
 635                 HandleFunctionArgumentsDefault(sqlgen, e, result);
 636             });
 637         }
 638
 639         private static ISqlFragment WrapWithCast(string returnType, Action<SqlBuilder> toWrap)
 640         {
 641             SqlBuilder result = new SqlBuilder();
 642             if (returnType != null)
 643             {
 644                 result.Append(" CAST(");
 645             }
 646
 647             toWrap(result);
 648
 649             if (returnType != null)
 650             {
 651                 result.Append(" AS ");
 652                 result.Append(returnType);
 653                 result.Append(")");
 654             }
 655             return result;
 656         }
 657
 658         /// <summary>
 659         /// Default handling on function arguments.
 660         /// Appends the list of arguments to the given result
 661         /// If the function is niladic it does not append anything,
 662         /// otherwise it appends (arg1, arg2, .., argn)
 663         /// </summary>
 664         /// <param name="e"></param>
 665         /// <param name="result"></param>
 666         private static void HandleFunctionArgumentsDefault(SqlGenerator sqlgen, DbFunctionExpression e, SqlBuilder result)
 667         {
 668             bool isNiladicFunction = e.Function.NiladicFunctionAttribute;
 669             Debug.Assert(!(isNiladicFunction && (0 < e.Arguments.Count)), "function attributed as NiladicFunction='true' in the provider manifest cannot have arguments");
 670             if (isNiladicFunction && e.Arguments.Count > 0)
 671             {
 672                 EntityUtil.Metadata(System.Data.Entity.Strings.SqlGen_NiladicFunctionsCannotHaveParameters);
 673             }
 674
 675             if (!isNiladicFunction)
 676             {
 677                 WriteFunctionArguments(sqlgen, e.Arguments, result);
 678             }
 679         }
 680
 681         private static void WriteFunctionArguments(SqlGenerator sqlgen, IEnumerable<DbExpression> functionArguments, SqlBuilder result)
 682         {
 683             result.Append("(");
 684             string separator = "";
 685             foreach (DbExpression arg in functionArguments)
 686             {
 687                 result.Append(separator);
 688                 result.Append(arg.Accept(sqlgen));
 689                 separator = ", ";
 690             }
 691             result.Append(")");
 692         }
 693
 694         /// <summary>
 695         /// Handler for functions that need to be translated to different store function based on version
 696         /// </summary>
 697         /// <param name="e"></param>
 698         /// <param name="preKatmaiName"></param>
 699         /// <param name="katmaiName"></param>
 700         /// <returns></returns>
 701         private static ISqlFragment HandleFunctionGivenNameBasedOnVersion(SqlGenerator sqlgen, DbFunctionExpression e, string preKatmaiName, string katmaiName)
 702         {
 703             if (sqlgen.IsPreKatmai)
 704             {
 705                 return HandleFunctionDefaultGivenName(sqlgen, e, preKatmaiName);
 706             }
 707             return HandleFunctionDefaultGivenName(sqlgen, e, katmaiName);
 708         }
 709
 710         /// <summary>
 711         /// Handler for special build in functions
 712         /// </summary>
 713         /// <param name="e"></param>
 714         /// <returns></returns>
 715         private static ISqlFragment HandleSpecialStoreFunction(SqlGenerator sqlgen, DbFunctionExpression e)
 716         {
 717             return HandleSpecialFunction(_storeFunctionHandlers, sqlgen, e);
 718         }
 719
 720         /// <summary>
 721         /// Handler for special canonical functions
 722         /// </summary>
 723         /// <param name="e"></param>
 724         /// <returns></returns>
 725         private static ISqlFragment HandleSpecialCanonicalFunction(SqlGenerator sqlgen, DbFunctionExpression e)
 726         {
 727             return HandleSpecialFunction(_canonicalFunctionHandlers, sqlgen, e);
 728         }
 729
 730         /// <summary>
 731         /// Dispatches the special function processing to the appropriate handler
 732         /// </summary>
 733         /// <param name="handlers"></param>
 734         /// <param name="e"></param>
 735         /// <returns></returns>
 736         private static ISqlFragment HandleSpecialFunction(Dictionary<string, FunctionHandler> handlers, SqlGenerator sqlgen, DbFunctionExpression e)
 737         {
 738             Debug.Assert(handlers.ContainsKey(e.Function.Name), "Special handling should be called only for functions in the list of special functions");
 739             return handlers[e.Function.Name](sqlgen, e);
 740         }
 741
 742         private static ISqlFragment HandleSpatialCanonicalFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression, PrimitiveTypeKind spatialTypeKind)
 743         {
 744             Debug.Assert(spatialTypeKind == PrimitiveTypeKind.Geography || spatialTypeKind == PrimitiveTypeKind.Geometry, "Spatial function does not refer to a valid spatial primitive type kind?");
 745             if (spatialTypeKind == PrimitiveTypeKind.Geography)
 746             {
 747                 return HandleSpatialCanonicalFunction(sqlgen, functionExpression, _geographyFunctionNameToStaticMethodHandlerDictionary, _geographyFunctionNameToInstancePropertyNameDictionary, _geographyRenamedInstanceMethodFunctionDictionary);
 748             }
 749             else
 750             {
 751                 return HandleSpatialCanonicalFunction(sqlgen, functionExpression, _geometryFunctionNameToStaticMethodHandlerDictionary, _geometryFunctionNameToInstancePropertyNameDictionary, _geometryRenamedInstanceMethodFunctionDictionary);
 752             }
 753         }
 754
 755         private static ISqlFragment HandleSpatialCanonicalFunction(SqlGenerator sqlgen,
 756                                                                    DbFunctionExpression functionExpression,
 757                                                                    Dictionary<string, FunctionHandler> staticMethodsMap,
 758                                                                    Dictionary<string, string> instancePropertiesMap,
 759                                                                    Dictionary<string, string> renamedInstanceMethodsMap)
 760         {
 761             FunctionHandler staticFunctionHandler;
 762             string instancePropertyName;
 763             if (staticMethodsMap.TryGetValue(functionExpression.Function.Name, out staticFunctionHandler))
 764             {
 765                 return staticFunctionHandler(sqlgen, functionExpression);
 766             }
 767             else if (instancePropertiesMap.TryGetValue(functionExpression.Function.Name, out instancePropertyName))
 768             {
 769                 Debug.Assert(functionExpression.Function.Parameters.Count > 0 && Helper.IsSpatialType(functionExpression.Function.Parameters[0].TypeUsage), "Instance property function does not have instance parameter?");
 770                 return WriteInstanceFunctionCall(sqlgen, instancePropertyName, functionExpression, isPropertyAccess: true, castReturnTypeTo: null);
 771             }
 772             else
 773             {
 774                 // Default translation pattern is instance method; the instance method name may differ from that of the spatial canonical function
 775                 Debug.Assert(functionExpression.Function.Parameters.Count > 0 && Helper.IsSpatialType(functionExpression.Function.Parameters[0].TypeUsage), "Instance method function does not have instance parameter?");
 776                 string effectiveFunctionName;
 777                 if (!renamedInstanceMethodsMap.TryGetValue(functionExpression.Function.Name, out effectiveFunctionName))
 778                 {
 779                     effectiveFunctionName = functionExpression.Function.Name;
 780                 }
 781
 782                 // For AsGml() calls, the XML result must be cast to string to match the declared function result type.
 783                 string castResultType = null;
 784                 if (effectiveFunctionName == "AsGml")
 785                 {
 786                     castResultType = sqlgen.DefaultStringTypeName;
 787                 }
 788                 return WriteInstanceFunctionCall(sqlgen, effectiveFunctionName, functionExpression, isPropertyAccess: false, castReturnTypeTo: castResultType);
 789             }
 790         }
 791
 792         private static ISqlFragment WriteInstanceFunctionCall(SqlGenerator sqlgen, string functionName, DbFunctionExpression functionExpression, bool isPropertyAccess)
 793         {
 794             return WriteInstanceFunctionCall(sqlgen, functionName, functionExpression, isPropertyAccess, null);
 795         }
 796
 797         private static ISqlFragment WriteInstanceFunctionCall(SqlGenerator sqlgen, string functionName, DbFunctionExpression functionExpression, bool isPropertyAccess, string castReturnTypeTo)
 798         {
 799             Debug.Assert(!isPropertyAccess || functionExpression.Arguments.Count == 1, "Property accessor instance functions should have only the single instance argument");
 800
 801             return WrapWithCast(castReturnTypeTo, result =>
 802             {
 803                 DbExpression instanceExpression = functionExpression.Arguments[0];
 804
 805                 // Write the instance - if this is another function call, it need not be enclosed in parentheses.
 806                 if (instanceExpression.ExpressionKind != DbExpressionKind.Function)
 807                 {
 808                     sqlgen.ParenthesizeExpressionIfNeeded(instanceExpression, result);
 809                 }
 810                 else
 811                 {
 812                     result.Append(instanceExpression.Accept(sqlgen));
 813                 }
 814                 result.Append(".");
 815                 result.Append(functionName);
 816
 817                 if (!isPropertyAccess)
 818                 {
 819                     WriteFunctionArguments(sqlgen, functionExpression.Arguments.Skip(1), result);
 820                 }
 821
 822             });
 823         }
 824
 825         /// <summary>
 826         /// Handles functions that are translated into TSQL operators.
 827         /// The given function should have one or two arguments.
 828         /// Functions with one arguemnt are translated into
 829         ///     op arg
 830         /// Functions with two arguments are translated into
 831         ///     arg0 op arg1
 832         /// Also, the arguments can be optionaly enclosed in parethesis
 833         /// </summary>
 834         /// <param name="e"></param>
 835         /// <param name="parenthesiseArguments">Whether the arguments should be enclosed in parethesis</param>
 836         /// <returns></returns>
 837         private static ISqlFragment HandleSpecialFunctionToOperator(SqlGenerator sqlgen, DbFunctionExpression e, bool parenthesiseArguments)
 838         {
 839             SqlBuilder result = new SqlBuilder();
 840             Debug.Assert(e.Arguments.Count > 0 && e.Arguments.Count <= 2, "There should be 1 or 2 arguments for operator");
 841
 842             if (e.Arguments.Count > 1)
 843             {
 844                 if (parenthesiseArguments)
 845                 {
 846                     result.Append("(");
 847                 }
 848                 result.Append(e.Arguments[0].Accept(sqlgen));
 849                 if (parenthesiseArguments)
 850                 {
 851                     result.Append(")");
 852                 }
 853             }
 854             result.Append(" ");
 855             Debug.Assert(_functionNameToOperatorDictionary.ContainsKey(e.Function.Name), "The function can not be mapped to an operator");
 856             result.Append(_functionNameToOperatorDictionary[e.Function.Name]);
 857             result.Append(" ");
 858
 859             if (parenthesiseArguments)
 860             {
 861                 result.Append("(");
 862             }
 863             result.Append(e.Arguments[e.Arguments.Count - 1].Accept(sqlgen));
 864             if (parenthesiseArguments)
 865             {
 866                 result.Append(")");
 867             }
 868             return result;
 869         }
 870
 871         /// <summary>
 872         /// <see cref="HandleSpecialFunctionToOperator"></see>
 873         /// </summary>
 874         /// <param name="sqlgen"></param>
 875         /// <param name="e"></param>
 876         /// <returns></returns>
 877         private static ISqlFragment HandleConcatFunction(SqlGenerator sqlgen, DbFunctionExpression e)
 878         {
 879             return HandleSpecialFunctionToOperator(sqlgen, e, false);
 880         }
 881
 882         /// <summary>
 883         /// <see cref="HandleSpecialFunctionToOperator"></see>
 884         /// </summary>
 885         /// <param name="sqlgen"></param>
 886         /// <param name="e"></param>
 887         /// <returns></returns>
 888         private static ISqlFragment HandleCanonicalFunctionBitwise(SqlGenerator sqlgen, DbFunctionExpression e)
 889         {
 890             return HandleSpecialFunctionToOperator(sqlgen, e, true);
 891         }
 892
 893         /// <summary>
 894         /// Handles special case in which datapart 'type' parameter is present. all the functions
 895         /// handles here have *only* the 1st parameter as datepart. datepart value is passed along
 896         /// the QP as string and has to be expanded as TSQL keyword.
 897         /// </summary>
 898         /// <param name="sqlgen"></param>
 899         /// <param name="e"></param>
 900         /// <returns></returns>
 901         private static ISqlFragment HandleDatepartDateFunction(SqlGenerator sqlgen, DbFunctionExpression e)
 902         {
 903             Debug.Assert(e.Arguments.Count > 0, "e.Arguments.Count > 0");
 904
 905             DbConstantExpression constExpr = e.Arguments[0] as DbConstantExpression;
 906             if (null == constExpr)
 907             {
 908                 throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
 909             }
 910
 911             string datepart = constExpr.Value as string;
 912             if (null == datepart)
 913             {
 914                 throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
 915             }
 916
 917             SqlBuilder result = new SqlBuilder();
 918
 919             //
 920             // check if datepart value is valid
 921             //
 922             if (!_datepartKeywords.Contains(datepart))
 923             {
 924                 throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentValue(datepart, e.Function.NamespaceName, e.Function.Name));
 925             }
 926
 927             //
 928             // finaly, expand the function name
 929             //
 930             WriteFunctionName(result, e.Function);
 931             result.Append("(");
 932
 933             // expand the datepart literal as tsql kword
 934             result.Append(datepart);
 935             string separator = ", ";
 936
 937             // expand remaining arguments
 938             for (int i = 1; i < e.Arguments.Count; i++)
 939             {
 940                 result.Append(separator);
 941                 result.Append(e.Arguments[i].Accept(sqlgen));
 942             }
 943
 944             result.Append(")");
 945
 946             return result;
 947         }
 948
 949         /// <summary>
 950         /// Handler for canonical functions for extracting date parts.
 951         /// For example:
 952         ///     Year(date) -> DATEPART( year, date)
 953         /// </summary>
 954         /// <param name="sqlgen"></param>
 955         /// <param name="e"></param>
 956         /// <returns></returns>
 957         [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1308:NormalizeStringsToUppercase")]
 958         private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, DbFunctionExpression e)
 959         {
 960             return HandleCanonicalFunctionDatepart(sqlgen, e.Function.Name.ToLowerInvariant(), e);
 961         }
 962
 963         /// <summary>
 964         /// Handler for canonical funcitons for GetTotalOffsetMinutes.
 965         /// GetTotalOffsetMinutes(e) --> Datepart(tzoffset, e)
 966         /// </summary>
 967         /// <param name="sqlgen"></param>
 968         /// <param name="e"></param>
 969         /// <returns></returns>
 970         private static ISqlFragment HandleCanonicalFunctionGetTotalOffsetMinutes(SqlGenerator sqlgen, DbFunctionExpression e)
 971         {
 972             return HandleCanonicalFunctionDatepart(sqlgen, "tzoffset", e);
 973         }
 974
 975         /// <summary>
 976         /// Handler for turning a canonical function into DATEPART
 977         /// Results in DATEPART(datepart, e)
 978         /// </summary>
 979         /// <param name="datepart"></param>
 980         /// <param name="e"></param>
 981         /// <returns></returns>
 982         private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, string datepart, DbFunctionExpression e)
 983         {
 984             SqlBuilder result = new SqlBuilder();
 985             result.Append("DATEPART (");
 986             result.Append(datepart);
 987             result.Append(", ");
 988
 989             Debug.Assert(e.Arguments.Count == 1, "Canonical datepart functions should have exactly one argument");
 990             result.Append(e.Arguments[0].Accept(sqlgen));
 991
 992             result.Append(")");
 993
 994             return result;
 995         }
 996
 997         /// <summary>
 998         /// Handler for the canonical function CurrentDateTime
 999         /// For Sql8 and Sql9:  CurrentDateTime() -> GetDate()
1000         /// For Sql10:          CurrentDateTime() -> SysDateTime()
1001         /// </summary>
1002         /// <param name="sqlgen"></param>
1003         /// <param name="e"></param>
1004         /// <returns></returns>
1005         private static ISqlFragment HandleCanonicalFunctionCurrentDateTime(SqlGenerator sqlgen, DbFunctionExpression e)
1006         {
1007             return HandleFunctionGivenNameBasedOnVersion(sqlgen, e, "GetDate", "SysDateTime");
1008         }
1009
1010         /// <summary>
1011         /// Handler for the canonical function CurrentUtcDateTime
1012         /// For Sql8 and Sql9:  CurrentUtcDateTime() -> GetUtcDate()
1013         /// For Sql10:          CurrentUtcDateTime() -> SysUtcDateTime()
1014         /// </summary>
1015         /// <param name="sqlgen"></param>
1016         /// <param name="e"></param>
1017         /// <returns></returns>
1018         private static ISqlFragment HandleCanonicalFunctionCurrentUtcDateTime(SqlGenerator sqlgen, DbFunctionExpression e)
1019         {
1020             return HandleFunctionGivenNameBasedOnVersion(sqlgen, e, "GetUtcDate", "SysUtcDateTime");
1021         }
1022
1023         /// <summary>
1024         /// Handler for the canonical function CurrentDateTimeOffset
1025         /// For Sql8 and Sql9:  throw
1026         /// For Sql10: CurrentDateTimeOffset() -> SysDateTimeOffset()
1027         /// </summary>
1028         /// <param name="sqlgen"></param>
1029         /// <param name="e"></param>
1030         /// <returns></returns>
1031         private static ISqlFragment HandleCanonicalFunctionCurrentDateTimeOffset(SqlGenerator sqlgen, DbFunctionExpression e)
1032         {
1033             sqlgen.AssertKatmaiOrNewer(e);
1034             return HandleFunctionDefaultGivenName(sqlgen, e, "SysDateTimeOffset");
1035         }
1036
1037         /// <summary>
1038         /// See <see cref="HandleCanonicalFunctionDateTimeTypeCreation"/> for exact translation
1039         /// Pre Katmai creates datetime.
1040         /// On Katmai creates datetime2.
1041         /// </summary>
1042         /// <param name="sqlgen"></param>
1043         /// <param name="e"></param>
1044         /// <returns></returns>
1045         private static ISqlFragment HandleCanonicalFunctionCreateDateTime(SqlGenerator sqlgen, DbFunctionExpression e)
1046         {
1047             string typeName = (sqlgen.IsPreKatmai) ? "datetime" : "datetime2";
1048             return HandleCanonicalFunctionDateTimeTypeCreation(sqlgen, typeName, e.Arguments, true, false);
1049         }
1050
1051         /// <summary>
1052         /// See <see cref="HandleCanonicalFunctionDateTimeTypeCreation"/> for exact translation
1053         /// Pre Katmai not supported.
1054         /// On Katmai creates datetimeoffset.
1055         /// </summary>
1056         /// <param name="sqlgen"></param>
1057         /// <param name="e"></param>
1058         /// <returns></returns>
1059         private static ISqlFragment HandleCanonicalFunctionCreateDateTimeOffset(SqlGenerator sqlgen, DbFunctionExpression e)
1060         {
1061             sqlgen.AssertKatmaiOrNewer(e);
1062             return HandleCanonicalFunctionDateTimeTypeCreation(sqlgen, "datetimeoffset", e.Arguments, true, true);
1063         }
1064
1065         /// <summary>
1066         /// See <see cref="HandleCanonicalFunctionDateTimeTypeCreation"/> for exact translation
1067         /// Pre Katmai not supported.
1068         /// On Katmai creates time.
1069         /// </summary>
1070         /// <param name="sqlgen"></param>
1071         /// <param name="e"></param>
1072         /// <returns></returns>
1073         private static ISqlFragment HandleCanonicalFunctionCreateTime(SqlGenerator sqlgen, DbFunctionExpression e)
1074         {
1075             sqlgen.AssertKatmaiOrNewer(e);
1076             return HandleCanonicalFunctionDateTimeTypeCreation(sqlgen, "time", e.Arguments, false, false);
1077         }
1078
1079         /// <summary>
1080         /// Helper for all date and time types creating functions.
1081         ///
1082         /// The given expression is in general trainslated into:
1083         ///
1084         /// CONVERT(@typename, [datePart] + [timePart] + [timeZonePart], 121), where the datePart and the timeZonePart are optional
1085         ///
1086         /// Only on Katmai, if a date part is present it is wrapped with a call for adding years as shown below.
1087         /// The individual parts are translated as:
1088         ///
1089         /// Date part:
1090         ///     PRE KATMAI: convert(varchar(255), @year) + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day)
1091         ///         KATMAI: DateAdd(year, @year-1, covert(@typename, '0001' + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day)  + [possibly time ], 121)
1092         ///
1093         /// Time part:
1094         /// PRE KATMAI:  convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 6, 3)
1095         ///     KATMAI:  convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 10, 7)
1096         ///
1097         /// Time zone part:
1098         ///     (case when @tzoffset >= 0 then '+' else '-' end) + convert(varchar(255), ABS(@tzoffset)/60) + ':' + convert(varchar(255), ABS(@tzoffset)%60)
1099         ///
1100         /// </summary>
1101         /// <param name="typeName"></param>
1102         /// <param name="args"></param>
1103         /// <param name="hasDatePart"></param>
1104         /// <param name="hasTimeZonePart"></param>
1105         /// <returns></returns>
1106         private static ISqlFragment HandleCanonicalFunctionDateTimeTypeCreation(SqlGenerator sqlgen, string typeName, IList<DbExpression> args, bool hasDatePart, bool hasTimeZonePart)
1107         {
1108             Debug.Assert(args.Count == (hasDatePart ? 3 : 0) + 3 + (hasTimeZonePart ? 1 : 0), "Invalid number of parameters for a date time creating function");
1109
1110             SqlBuilder result = new SqlBuilder();
1111             int currentArgumentIndex = 0;
1112
1113             if (!sqlgen.IsPreKatmai && hasDatePart)
1114             {
1115                 result.Append("DATEADD(year, ");
1116                 sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex++], result);
1117                 result.Append(" - 1, ");
1118             }
1119
1120             result.Append("convert (");
1121             result.Append(typeName);
1122             result.Append(",");
1123
1124             //Building the string representation
1125             if (hasDatePart)
1126             {
1127                 //  YEAR:   PREKATMAI:               CONVERT(VARCHAR, @YEAR)
1128                 //          KATMAI   :              '0001'
1129                 if (!sqlgen.IsPreKatmai)
1130                 {
1131                     result.Append("'0001'");
1132                 }
1133                 else
1134                 {
1135                     AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
1136                 }
1137
1138                 //  MONTH
1139                 result.Append(" + '-' + ");
1140                 AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
1141
1142                 //  DAY
1143                 result.Append(" + '-' + ");
1144                 AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
1145                 result.Append(" + ' ' + ");
1146             }
1147
1148             //  HOUR
1149             AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
1150
1151             // MINUTE
1152             result.Append(" + ':' + ");
1153             AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
1154
1155             // SECOND
1156             result.Append(" + ':' + str(");
1157             result.Append(args[currentArgumentIndex++].Accept(sqlgen));
1158
1159             if (sqlgen.IsPreKatmai)
1160             {
1161                 result.Append(", 6, 3)");
1162             }
1163             else
1164             {
1165                 result.Append(", 10, 7)");
1166             }
1167
1168             //  TZOFFSET
1169             if (hasTimeZonePart)
1170             {
1171                 result.Append(" + (CASE WHEN ");
1172                 sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
1173                 result.Append(" >= 0 THEN '+' ELSE '-' END) + convert(varchar(255), ABS(");
1174                 sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
1175                 result.Append("/60)) + ':' + convert(varchar(255), ABS(");
1176                 sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
1177                 result.Append("%60))");
1178             }
1179
1180             result.Append(", 121)");
1181
1182             if (!sqlgen.IsPreKatmai && hasDatePart)
1183             {
1184                 result.Append(")");
1185             }
1186             return result;
1187         }
1188
1189         /// <summary>
1190         /// Helper method that wrapps the given expession with a conver to varchar(255)
1191         /// </summary>
1192         /// <param name="result"></param>
1193         /// <param name="e"></param>
1194         private static void AppendConvertToVarchar(SqlGenerator sqlgen, SqlBuilder result, DbExpression e)
1195         {
1196             result.Append("convert(varchar(255), ");
1197             result.Append(e.Accept(sqlgen));
1198             result.Append(")");
1199         }
1200
1201         /// <summary>
1202         /// TruncateTime(DateTime X)
1203         ///   PreKatmai:    TRUNCATETIME(X) => CONVERT(DATETIME, CONVERT(VARCHAR(255), expression, 102),  102)
1204         ///      Katmai:    TRUNCATETIME(X) => CONVERT(DATETIME2, CONVERT(VARCHAR(255), expression, 102),  102)
1205         ///
1206         /// TruncateTime(DateTimeOffset X)
1207         ///                 TRUNCATETIME(X) => CONVERT(datetimeoffset, CONVERT(VARCHAR(255), expression,  102)
1208         ///                                     + ' 00:00:00 ' +  Right(convert(varchar(255), @arg, 121), 6),  102)
1209         ///
1210         /// </summary>
1211         /// <param name="sqlgen"></param>
1212         /// <param name="e"></param>
1213         /// <returns></returns>
1214         private static ISqlFragment HandleCanonicalFunctionTruncateTime(SqlGenerator sqlgen, DbFunctionExpression e)
1215         {
1216             //The type that we need to return is based on the argument type.
1217             string typeName = null;
1218             bool isDateTimeOffset = false;
1219
1220             PrimitiveTypeKind typeKind;
1221             bool isPrimitiveType = TypeHelpers.TryGetPrimitiveTypeKind(e.Arguments[0].ResultType, out typeKind);
1222             Debug.Assert(isPrimitiveType, "Expecting primitive type as input parameter to TruncateTime");
1223
1224             if (typeKind == PrimitiveTypeKind.DateTime)
1225             {
1226                 typeName = sqlgen.IsPreKatmai ? "datetime" : "datetime2";
1227             }
1228             else if (typeKind == PrimitiveTypeKind.DateTimeOffset)
1229             {
1230                 typeName = "datetimeoffset";
1231                 isDateTimeOffset = true;
1232             }
1233             else
1234             {
1235                 Debug.Assert(true, "Unexpected type to TruncateTime" + typeKind.ToString());
1236             }
1237
1238             SqlBuilder result = new SqlBuilder();
1239             result.Append("convert (");
1240             result.Append(typeName);
1241             result.Append(", convert(varchar(255), ");
1242             result.Append(e.Arguments[0].Accept(sqlgen));
1243             result.Append(", 102) ");
1244
1245             if (isDateTimeOffset)
1246             {
1247                 result.Append("+ ' 00:00:00 ' +  Right(convert(varchar(255), ");
1248                 result.Append(e.Arguments[0].Accept(sqlgen));
1249                 result.Append(", 121), 6)  ");
1250             }
1251
1252             result.Append(",  102)");
1253             return result;
1254         }
1255
1256         /// <summary>
1257         /// Handler for date addition functions supported only starting from Katmai
1258         /// </summary>
1259         /// <param name="sqlgen"></param>
1260         /// <param name="e"></param>
1261         /// <returns></returns>
1262         private static ISqlFragment HandleCanonicalFunctionDateAddKatmaiOrNewer(SqlGenerator sqlgen, DbFunctionExpression e)
1263         {
1264             sqlgen.AssertKatmaiOrNewer(e);
1265             return HandleCanonicalFunctionDateAdd(sqlgen, e);
1266         }
1267
1268         /// <summary>
1269         /// Handler for all date/time addition canonical functions.
1270         /// Translation, e.g.
1271         /// AddYears(datetime, number) =>  DATEADD(year, number, datetime)
1272         /// </summary>
1273         /// <param name="sqlgen"></param>
1274         /// <param name="e"></param>
1275         /// <returns></returns>
1276         private static ISqlFragment HandleCanonicalFunctionDateAdd(SqlGenerator sqlgen, DbFunctionExpression e)
1277         {
1278             SqlBuilder result = new SqlBuilder();
1279
1280             result.Append("DATEADD (");
1281             result.Append(_dateAddFunctionNameToDatepartDictionary[e.Function.Name]);
1282             result.Append(", ");
1283             result.Append(e.Arguments[1].Accept(sqlgen));
1284             result.Append(", ");
1285             result.Append(e.Arguments[0].Accept(sqlgen));
1286             result.Append(")");
1287
1288             return result;
1289         }
1290
1291         /// <summary>
1292         /// Hanndler for date differencing functions supported only starting from Katmai
1293         /// </summary>
1294         /// <param name="sqlgen"></param>
1295         /// <param name="e"></param>
1296         /// <returns></returns>
1297         private static ISqlFragment HandleCanonicalFunctionDateDiffKatmaiOrNewer(SqlGenerator sqlgen, DbFunctionExpression e)
1298         {
1299             sqlgen.AssertKatmaiOrNewer(e);
1300             return HandleCanonicalFunctionDateDiff(sqlgen, e);
1301         }
1302
1303         /// <summary>
1304         /// Handler for all date/time addition canonical functions.
1305         /// Translation, e.g.
1306         /// DiffYears(datetime, number) =>  DATEDIFF(year, number, datetime)
1307         /// </summary>
1308         /// <param name="sqlgen"></param>
1309         /// <param name="e"></param>
1310         /// <returns></returns>
1311         private static ISqlFragment HandleCanonicalFunctionDateDiff(SqlGenerator sqlgen, DbFunctionExpression e)
1312         {
1313             SqlBuilder result = new SqlBuilder();
1314
1315             result.Append("DATEDIFF (");
1316             result.Append(_dateDiffFunctionNameToDatepartDictionary[e.Function.Name]);
1317             result.Append(", ");
1318             result.Append(e.Arguments[0].Accept(sqlgen));
1319             result.Append(", ");
1320             result.Append(e.Arguments[1].Accept(sqlgen));
1321             result.Append(")");
1322
1323             return result;
1324         }
1325
1326         /// <summary>
1327         ///  Function rename IndexOf -> CHARINDEX
1328         /// </summary>
1329         /// <param name="sqlgen"></param>
1330         /// <param name="e"></param>
1331         /// <returns></returns>
1332         private static ISqlFragment HandleCanonicalFunctionIndexOf(SqlGenerator sqlgen, DbFunctionExpression e)
1333         {
1334             return HandleFunctionDefaultGivenName(sqlgen, e, "CHARINDEX");
1335         }
1336
1337         /// <summary>
1338         ///  Function rename NewGuid -> NEWID
1339         /// </summary>
1340         /// <param name="sqlgen"></param>
1341         /// <param name="e"></param>
1342         /// <returns></returns>
1343         private static ISqlFragment HandleCanonicalFunctionNewGuid(SqlGenerator sqlgen, DbFunctionExpression e)
1344         {
1345             return HandleFunctionDefaultGivenName(sqlgen, e, "NEWID");
1346         }
1347
1348         /// <summary>
1349         ///  Function rename Length -> LEN
1350         /// </summary>
1351         /// <param name="sqlgen"></param>
1352         /// <param name="e"></param>
1353         /// <returns></returns>
1354         private static ISqlFragment HandleCanonicalFunctionLength(SqlGenerator sqlgen, DbFunctionExpression e)
1355         {
1356             // We are aware of SQL Server's trimming of trailing spaces. We disclaim that behavior in general.
1357             // It's up to the user to decide whether to trim them explicitly or to append a non-blank space char explicitly.
1358             // Once SQL Server implements a function that computes Length correctly, we'll use it here instead of LEN,
1359             // and we'll drop the disclaimer.
1360             return HandleFunctionDefaultGivenName(sqlgen, e, "LEN");
1361         }
1362
1363         /// <summary>
1364         /// Round(numericExpression) -> Round(numericExpression, 0);
1365         /// Round(numericExpression, digits) -> Round(numericExpression, digits);
1366         /// </summary>
1367         /// <param name="sqlgen"></param>
1368         /// <param name="e"></param>
1369         /// <returns></returns>
1370         private static ISqlFragment HandleCanonicalFunctionRound(SqlGenerator sqlgen, DbFunctionExpression e)
1371         {
1372             return HandleCanonicalFunctionRoundOrTruncate(sqlgen, e, true);
1373         }
1374
1375         /// <summary>
1376         /// Truncate(numericExpression) -> Round(numericExpression, 0, 1); (does not exist as canonical function yet)
1377         /// Truncate(numericExpression, digits) -> Round(numericExpression, digits, 1);
1378         /// </summary>
1379         /// <param name="sqlgen"></param>
1380         /// <param name="e"></param>
1381         /// <returns></returns>
1382         private static ISqlFragment HandleCanonicalFunctionTruncate(SqlGenerator sqlgen, DbFunctionExpression e)
1383         {
1384             return HandleCanonicalFunctionRoundOrTruncate(sqlgen, e, false);
1385         }
1386
1387         /// <summary>
1388         /// Common handler for the canonical functions ROUND and TRUNCATE
1389         /// </summary>
1390         /// <param name="e"></param>
1391         /// <param name="round"></param>
1392         /// <returns></returns>
1393         private static ISqlFragment HandleCanonicalFunctionRoundOrTruncate(SqlGenerator sqlgen, DbFunctionExpression e, bool round)
1394         {
1395             SqlBuilder result = new SqlBuilder();
1396
1397             // Do not add the cast for the Round() overload having two arguments.
1398             // Round(Single,Int32) maps to Round(Double,Int32)due to implicit casting.
1399             // We don't need to cast in that case, since the server returned type is same
1400             // as the expected  type. Cast is only required for the overload - Round(Single)
1401             bool requiresCastToSingle = false;
1402             if (e.Arguments.Count == 1)
1403             {
1404                 requiresCastToSingle = CastReturnTypeToSingle(e);
1405                 if (requiresCastToSingle)
1406                 {
1407                     result.Append(" CAST(");
1408                 }
1409             }
1410             result.Append("ROUND(");
1411
1412             Debug.Assert(e.Arguments.Count <= 2, "Round or truncate should have at most 2 arguments");
1413             result.Append(e.Arguments[0].Accept(sqlgen));
1414             result.Append(", ");
1415
1416             if (e.Arguments.Count > 1)
1417             {
1418                 result.Append(e.Arguments[1].Accept(sqlgen));
1419             }
1420             else
1421             {
1422                 result.Append("0");
1423             }
1424
1425             if (!round)
1426             {
1427                 result.Append(", 1");
1428             }
1429
1430             result.Append(")");
1431
1432             if (requiresCastToSingle)
1433             {
1434                 result.Append(" AS real)");
1435             }
1436             return result;
1437         }
1438
1439         /// <summary>
1440         /// Handle the canonical function Abs().
1441         /// </summary>
1442         /// <param name="sqlgen"></param>
1443         /// <param name="e"></param>
1444         /// <returns></returns>
1445         private static ISqlFragment HandleCanonicalFunctionAbs(SqlGenerator sqlgen, DbFunctionExpression e)
1446         {
1447             // Convert the call to Abs(Byte) to a no-op, since Byte is an unsigned type.
1448             if (TypeSemantics.IsPrimitiveType(e.Arguments[0].ResultType, PrimitiveTypeKind.Byte))
1449             {
1450                 SqlBuilder result = new SqlBuilder();
1451                 result.Append(e.Arguments[0].Accept(sqlgen));
1452                 return result;
1453             }
1454             else
1455             {
1456                 return HandleFunctionDefault(sqlgen, e);
1457             }
1458         }
1459
1460         /// <summary>
1461         /// TRIM(string) -> LTRIM(RTRIM(string))
1462         /// </summary>
1463         /// <param name="sqlgen"></param>
1464         /// <param name="e"></param>
1465         /// <returns></returns>
1466         private static ISqlFragment HandleCanonicalFunctionTrim(SqlGenerator sqlgen, DbFunctionExpression e)
1467         {
1468             SqlBuilder result = new SqlBuilder();
1469
1470             result.Append("LTRIM(RTRIM(");
1471
1472             Debug.Assert(e.Arguments.Count == 1, "Trim should have one argument");
1473             result.Append(e.Arguments[0].Accept(sqlgen));
1474
1475             result.Append("))");
1476
1477             return result;
1478         }
1479
1480         /// <summary>
1481         ///  Function rename ToLower -> LOWER
1482         /// </summary>
1483         /// <param name="sqlgen"></param>
1484         /// <param name="e"></param>
1485         /// <returns></returns>
1486         private static ISqlFragment HandleCanonicalFunctionToLower(SqlGenerator sqlgen, DbFunctionExpression e)
1487         {
1488             return HandleFunctionDefaultGivenName(sqlgen, e, "LOWER");
1489         }
1490
1491         /// <summary>
1492         ///  Function rename ToUpper -> UPPER
1493         /// </summary>
1494         /// <param name="sqlgen"></param>
1495         /// <param name="e"></param>
1496         /// <returns></returns>
1497         private static ISqlFragment HandleCanonicalFunctionToUpper(SqlGenerator sqlgen, DbFunctionExpression e)
1498         {
1499             return HandleFunctionDefaultGivenName(sqlgen, e, "UPPER");
1500         }
1501
1502         /// <summary>
1503         /// Function to translate the StartsWith, EndsWith and Contains canonical functions to LIKE expression in T-SQL
1504         /// and also add the trailing ESCAPE '~' when escaping of the search string for the LIKE expression has occurred
1505         /// </summary>
1506         /// <param name="sqlgen"></param>
1507         /// <param name="targetExpression"></param>
1508         /// <param name="constSearchParamExpression"></param>
1509         /// <param name="result"></param>
1510         /// <param name="insertPercentStart"></param>
1511         /// <param name="insertPercentEnd"></param>
1512         private static void TranslateConstantParameterForLike(SqlGenerator sqlgen, DbExpression targetExpression, DbConstantExpression constSearchParamExpression, SqlBuilder result, bool insertPercentStart, bool insertPercentEnd)
1513         {
1514             result.Append(targetExpression.Accept(sqlgen));
1515             result.Append(" LIKE ");
1516
1517             // If it's a DbConstantExpression then escape the search parameter if necessary.
1518             bool escapingOccurred;
1519
1520             StringBuilder searchParamBuilder = new StringBuilder();
1521             if (insertPercentStart == true)
1522                 searchParamBuilder.Append("%");
1523             searchParamBuilder.Append(SqlProviderManifest.EscapeLikeText(constSearchParamExpression.Value as string, false,  out escapingOccurred));
1524             if (insertPercentEnd == true)
1525                 searchParamBuilder.Append("%");
1526
1527             DbConstantExpression escapedSearchParamExpression = new DbConstantExpression(constSearchParamExpression.ResultType, searchParamBuilder.ToString());
1528             result.Append(escapedSearchParamExpression.Accept(sqlgen));
1529
1530             // If escaping did occur (special characters were found), then append the escape character used.
1531             if (escapingOccurred)
1532                 result.Append(" ESCAPE '" + SqlProviderManifest.LikeEscapeChar + "'");
1533         }
1534
1535         /// <summary>
1536         /// Handler for Contains. Wraps the normal translation with a case statement
1537         /// </summary>
1538         /// <param name="sqlgen"></param>
1539         /// <param name="e"></param>
1540         /// <returns></returns>
1541         private static ISqlFragment HandleCanonicalFunctionContains(SqlGenerator sqlgen, DbFunctionExpression e)
1542         {
1543             return WrapPredicate( HandleCanonicalFunctionContains, sqlgen, e);
1544         }
1545
1546         /// <summary>
1547         /// CONTAINS(arg0, arg1) => arg0 LIKE '%arg1%'
1548         /// </summary>
1549         /// <param name="sqlgen"></param>
1550         /// <param name="args"></param>
1551         /// <param name="result"></param>
1552         /// <returns></returns>
1553         private static SqlBuilder HandleCanonicalFunctionContains(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
1554         {
1555             Debug.Assert(args.Count == 2, "Contains should have two arguments");
1556             // Check if args[1] is a DbConstantExpression
1557             DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
1558             if ((constSearchParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
1559             {
1560                 TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, true, true);
1561             }
1562             else
1563             {
1564                 // We use CHARINDEX when the search param is a DbNullExpression because all of SQL Server 2008, 2005 and 2000
1565                 // consistently return NULL as the result.
1566                 //  However, if instead we use the optimized LIKE translation when the search param is a DbNullExpression,
1567                 //  only SQL Server 2005 yields a True instead of a DbNull as compared to SQL Server 2008 and 2000. This is
1568                 //  tracked in SQLBUDT #32315 in LIKE in SQL Server 2005.
1569                 result.Append("CHARINDEX( ");
1570                 result.Append(args[1].Accept(sqlgen));
1571                 result.Append(", ");
1572                 result.Append(args[0].Accept(sqlgen));
1573                 result.Append(") > 0");
1574             }
1575             return result;
1576         }
1577
1578         /// <summary>
1579         /// Handler for StartsWith. Wraps the normal translation with a case statement
1580         /// </summary>
1581         /// <param name="sqlgen"></param>
1582         /// <param name="e"></param>
1583         /// <returns></returns>
1584         private static ISqlFragment HandleCanonicalFunctionStartsWith(SqlGenerator sqlgen, DbFunctionExpression e)
1585         {
1586             return WrapPredicate(HandleCanonicalFunctionStartsWith, sqlgen, e);
1587         }
1588
1589         /// <summary>
1590         /// STARTSWITH(arg0, arg1) => arg0 LIKE 'arg1%'
1591         /// </summary>
1592         /// <param name="sqlgen"></param>
1593         /// <param name="args"></param>
1594         /// <param name="result"></param>
1595         /// <returns></returns>
1596         private static SqlBuilder HandleCanonicalFunctionStartsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
1597         {
1598             Debug.Assert(args.Count == 2, "StartsWith should have two arguments");
1599             // Check if args[1] is a DbConstantExpression
1600             DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
1601             if ((constSearchParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
1602             {
1603                 TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, false, true);
1604             }
1605             else
1606             {
1607                 // We use CHARINDEX when the search param is a DbNullExpression because all of SQL Server 2008, 2005 and 2000
1608                 // consistently return NULL as the result.
1609                 //      However, if instead we use the optimized LIKE translation when the search param is a DbNullExpression,
1610                 //      only SQL Server 2005 yields a True instead of a DbNull as compared to SQL Server 2008 and 2000. This is
1611                 //
1612                 result.Append("CHARINDEX( ");
1613                 result.Append(args[1].Accept(sqlgen));
1614                 result.Append(", ");
1615                 result.Append(args[0].Accept(sqlgen));
1616                 result.Append(") = 1");
1617             }
1618
1619             return result;
1620         }
1621
1622         /// <summary>
1623         /// Handler for EndsWith. Wraps the normal translation with a case statement
1624         /// </summary>
1625         /// <param name="sqlgen"></param>
1626         /// <param name="e"></param>
1627         /// <returns></returns>
1628         private static ISqlFragment HandleCanonicalFunctionEndsWith(SqlGenerator sqlgen, DbFunctionExpression e)
1629         {
1630             return WrapPredicate(HandleCanonicalFunctionEndsWith, sqlgen, e);
1631         }
1632
1633         /// <summary>
1634         /// ENDSWITH(arg0, arg1) => arg0 LIKE '%arg1'
1635         /// </summary>
1636         /// <param name="sqlgen"></param>
1637         /// <param name="args"></param>
1638         /// <param name="result"></param>
1639         /// <returns></returns>
1640         private static SqlBuilder HandleCanonicalFunctionEndsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
1641         {
1642             Debug.Assert(args.Count == 2, "EndsWith should have two arguments");
1643
1644             // Check if args[1] is a DbConstantExpression and if args [0] is a DbPropertyExpression
1645             DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
1646             DbPropertyExpression targetParamExpression = args[0] as DbPropertyExpression;
1647             if ((constSearchParamExpression != null) && (targetParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
1648             {
1649                 // The LIKE optimization for EndsWith can only be used when the target is a column in table and
1650                 // the search string is a constant. This is because SQL Server ignores a trailing space in a query like:
1651                 // EndsWith('abcd ', 'cd'), which translates to:
1652                 //      SELECT
1653                 //      CASE WHEN ('abcd ' LIKE '%cd') THEN cast(1 as bit) WHEN ( NOT ('abcd ' LIKE '%cd')) THEN cast(0 as bit) END AS [C1]
1654                 //      FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
1655                 // and "incorrectly" returns 1 (true), but the CLR would expect a 0 (false) back.
1656
1657                 TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, true, false);
1658             }
1659             else
1660             {
1661                 result.Append("CHARINDEX( REVERSE(");
1662                 result.Append(args[1].Accept(sqlgen));
1663                 result.Append("), REVERSE(");
1664                 result.Append(args[0].Accept(sqlgen));
1665                 result.Append(")) = 1");
1666             }
1667             return result;
1668         }
1669
1670         /// <summary>
1671         /// Turns a predicate into a statement returning a bit
1672         /// PREDICATE => CASE WHEN (PREDICATE) THEN CAST(1 AS BIT) WHEN (NOT (PREDICATE)) CAST (O AS BIT) END
1673         /// The predicate is produced by the given predicateTranslator.
1674         /// </summary>
1675         /// <param name="predicateTranslator"></param>
1676         /// <param name="e"></param>
1677         /// <returns></returns>
1678         private static ISqlFragment WrapPredicate(Func<SqlGenerator, IList<DbExpression>, SqlBuilder, SqlBuilder> predicateTranslator, SqlGenerator sqlgen, DbFunctionExpression e)
1679         {
1680             SqlBuilder result = new SqlBuilder();
1681             result.Append("CASE WHEN (");
1682             predicateTranslator(sqlgen, e.Arguments, result);
1683             result.Append(") THEN cast(1 as bit) WHEN ( NOT (");
1684             predicateTranslator(sqlgen, e.Arguments, result);
1685             result.Append(")) THEN cast(0 as bit) END");
1686             return result;
1687         }
1688
1689         /// <summary>
1690         /// Writes the function name to the given SqlBuilder.
1691         /// </summary>
1692         /// <param name="function"></param>
1693         /// <param name="result"></param>
1694         internal static void WriteFunctionName(SqlBuilder result, EdmFunction function)
1695         {
1696             string storeFunctionName;
1697
1698             if (null != function.StoreFunctionNameAttribute)
1699             {
1700                 storeFunctionName = function.StoreFunctionNameAttribute;
1701             }
1702             else
1703             {
1704                 storeFunctionName = function.Name;
1705             }
1706
1707             // If the function is a builtin (i.e. the BuiltIn attribute has been
1708             // specified, both store and canonical functions have this attribute),
1709             // then the function name should not be quoted;
1710             // additionally, no namespace should be used.
1711             if (TypeHelpers.IsCanonicalFunction(function))
1712             {
1713                 result.Append(storeFunctionName.ToUpperInvariant());
1714             }
1715             else if (IsStoreFunction(function))
1716             {
1717                 result.Append(storeFunctionName);
1718             }
1719             else
1720             {
1721                 // Should we actually support this?
1722                 if (String.IsNullOrEmpty(function.Schema))
1723                 {
1724                     result.Append(SqlGenerator.QuoteIdentifier(function.NamespaceName));
1725                 }
1726                 else
1727                 {
1728                     result.Append(SqlGenerator.QuoteIdentifier(function.Schema));
1729                 }
1730                 result.Append(".");
1731                 result.Append(SqlGenerator.QuoteIdentifier(storeFunctionName));
1732             }
1733         }
1734
1735
1736         /// <summary>
1737         /// Is this a Store function (ie) does it have the builtinAttribute specified and it is not a canonical function?
1738         /// </summary>
1739         /// <param name="function"></param>
1740         /// <returns></returns>
1741         internal static bool IsStoreFunction(EdmFunction function)
1742         {
1743             return function.BuiltInAttribute && !TypeHelpers.IsCanonicalFunction(function);
1744         }
1745
1746         /// <summary>
1747         /// determines if the function requires the return type be enforeced by use of a cast expression
1748         /// </summary>
1749         /// <param name="e"></param>
1750         /// <returns></returns>
1751         private static bool CastReturnTypeToInt64(DbFunctionExpression e)
1752         {
1753             return CastReturnTypeToGivenType(e, _functionRequiresReturnTypeCastToInt64, PrimitiveTypeKind.Int64);
1754         }
1755
1756         /// <summary>
1757         /// determines if the function requires the return type be enforeced by use of a cast expression
1758         /// </summary>
1759         /// <param name="e"></param>
1760         /// <returns></returns>
1761         private static bool CastReturnTypeToInt32(SqlGenerator sqlgen, DbFunctionExpression e)
1762         {
1763             if (!_functionRequiresReturnTypeCastToInt32.Contains(e.Function.FullName))
1764             {
1765                 return false;
1766             }
1767
1768             for (int i = 0; i < e.Arguments.Count; i++)
1769             {
1770                 TypeUsage storeType = sqlgen.StoreItemCollection.StoreProviderManifest.GetStoreType(e.Arguments[i].ResultType);
1771                 if (_maxTypeNames.Contains(storeType.EdmType.Name))
1772                 {
1773                     return true;
1774                 }
1775             }
1776             return false;
1777         }
1778
1779         /// <summary>
1780         /// determines if the function requires the return type be enforeced by use of a cast expression
1781         /// </summary>
1782         /// <param name="e"></param>
1783         /// <returns></returns>
1784         private static bool CastReturnTypeToInt16(DbFunctionExpression e)
1785         {
1786             return CastReturnTypeToGivenType(e, _functionRequiresReturnTypeCastToInt16, PrimitiveTypeKind.Int16);
1787         }
1788
1789         /// <summary>
1790         /// determines if the function requires the return type be enforeced by use of a cast expression
1791         /// </summary>
1792         /// <param name="e"></param>
1793         /// <returns></returns>
1794         private static bool CastReturnTypeToSingle(DbFunctionExpression e)
1795         {
1796             //Do not add the cast for the Round() overload having 2 arguments.
1797             //Round(Single,Int32) maps to Round(Double,Int32)due to implicit casting.
1798             //We don't need to cast in that case, since we expect a Double as return type there anyways.
1799             return CastReturnTypeToGivenType(e, _functionRequiresReturnTypeCastToSingle, PrimitiveTypeKind.Single);
1800         }
1801
1802         /// <summary>
1803         /// Determines if the function requires the return type be enforced by use of a cast expression
1804         /// </summary>
1805         /// <param name="e"></param>
1806         /// <param name="functionsRequiringReturnTypeCast"></param>
1807         /// <param name="type"></param>
1808         /// <returns></returns>
1809         private static bool CastReturnTypeToGivenType(DbFunctionExpression e, Set<string> functionsRequiringReturnTypeCast, PrimitiveTypeKind type)
1810         {
1811             if (!functionsRequiringReturnTypeCast.Contains(e.Function.FullName))
1812             {
1813                 return false;
1814             }
1815
1816             for (int i = 0; i < e.Arguments.Count; i++)
1817             {
1818                 if (TypeSemantics.IsPrimitiveType(e.Arguments[i].ResultType, type))
1819                 {
1820                     return true;
1821                 }
1822             }
1823             return false;
1824         }
1825     }
1826 }
1827