|
//---------------------------------------------------------------------
// <copyright file="SqlFunctionCallHandler.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation. All rights reserved.
// </copyright>
//
// @owner Microsoft
// @backupOwner Microsoft
//---------------------------------------------------------------------
namespace System.Data.SqlClient.SqlGen
{
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Common.CommandTrees;
using System.Data.Common.CommandTrees.ExpressionBuilder;
using System.Data.Common.Utils;
using System.Data.Metadata.Edm;
using System.Data.Spatial;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
/// <summary>
/// Enacapsulates the logic required to translate function calls represented as instances of DbFunctionExpression into SQL.
/// There are several special cases that modify how the translation should proceed. These include:
/// - 'Special' canonical functions, for which the function name or arguments differ between the EDM canonical function and the SQL function
/// - 'Special' server functions, which are similar to the 'special' canonical functions but sourced by the SQL Server provider manifest
/// - Niladic functions, which require the parentheses that would usually follow the function name to be omitted
/// - Spatial canonical functions, which must translate to a static method call, instance method call, or instance property access against
/// one of the built-in spatial CLR UDTs (geography/geometry).
/// </summary>
internal static class SqlFunctionCallHandler
{
#region Static fields, include dictionaries used to dispatch function handling
static private readonly Dictionary<string, FunctionHandler> _storeFunctionHandlers = InitializeStoreFunctionHandlers();
static private readonly Dictionary<string, FunctionHandler> _canonicalFunctionHandlers = InitializeCanonicalFunctionHandlers();
static private readonly Dictionary<string, string> _functionNameToOperatorDictionary = InitializeFunctionNameToOperatorDictionary();
static private readonly Dictionary<string, string> _dateAddFunctionNameToDatepartDictionary = InitializeDateAddFunctionNameToDatepartDictionary();
static private readonly Dictionary<string, string> _dateDiffFunctionNameToDatepartDictionary = InitializeDateDiffFunctionNameToDatepartDictionary();
static private readonly Dictionary<string, FunctionHandler> _geographyFunctionNameToStaticMethodHandlerDictionary = InitializeGeographyStaticMethodFunctionsDictionary();
static private readonly Dictionary<string, string> _geographyFunctionNameToInstancePropertyNameDictionary = InitializeGeographyInstancePropertyFunctionsDictionary();
static private readonly Dictionary<string, string> _geographyRenamedInstanceMethodFunctionDictionary = InitializeRenamedGeographyInstanceMethodFunctions();
static private readonly Dictionary<string, FunctionHandler> _geometryFunctionNameToStaticMethodHandlerDictionary = InitializeGeometryStaticMethodFunctionsDictionary();
static private readonly Dictionary<string, string> _geometryFunctionNameToInstancePropertyNameDictionary = InitializeGeometryInstancePropertyFunctionsDictionary();
static private readonly Dictionary<string, string> _geometryRenamedInstanceMethodFunctionDictionary = InitializeRenamedGeometryInstanceMethodFunctions();
static private readonly Set<string> _datepartKeywords = new Set<string>(new string[] { "year", "yy", "yyyy",
"quarter", "qq", "q",
"month", "mm", "m",
"dayofyear", "dy", "y",
"day", "dd", "d",
"week", "wk", "ww",
"weekday", "dw", "w",
"hour", "hh",
"minute", "mi", "n",
"second", "ss", "s",
"millisecond", "ms",
"microsecond", "mcs",
"nanosecond", "ns",
"tzoffset", "tz",
"iso_week", "isoww", "isowk"},
StringComparer.OrdinalIgnoreCase).MakeReadOnly();
static private readonly Set<string> _functionRequiresReturnTypeCastToInt64 = new Set<string>(new string[] { "SqlServer.CHARINDEX" },
StringComparer.Ordinal).MakeReadOnly();
static private readonly Set<string> _functionRequiresReturnTypeCastToInt32 = new Set<string>(new string[] { "SqlServer.LEN" ,
"SqlServer.PATINDEX" ,
"SqlServer.DATALENGTH" ,
"SqlServer.CHARINDEX" ,
"Edm.IndexOf" ,
"Edm.Length" },
StringComparer.Ordinal).MakeReadOnly();
static private readonly Set<string> _functionRequiresReturnTypeCastToInt16 = new Set<string>(new string[] { "Edm.Abs" },
StringComparer.Ordinal).MakeReadOnly();
static private readonly Set<string> _functionRequiresReturnTypeCastToSingle = new Set<string>(new string[] { "Edm.Abs" ,
"Edm.Round" ,
"Edm.Floor" ,
"Edm.Ceiling" },
StringComparer.Ordinal).MakeReadOnly();
static private readonly Set<string> _maxTypeNames = new Set<string>(new string[] { "varchar(max)" ,
"nvarchar(max)" ,
"text" ,
"ntext" ,
"varbinary(max)" ,
"image" ,
"xml" },
StringComparer.Ordinal).MakeReadOnly();
#endregion
#region Static dictionary initialization
private delegate ISqlFragment FunctionHandler(SqlGenerator sqlgen, DbFunctionExpression functionExpr);
/// <summary>
/// All special store functions and their handlers
/// </summary>
/// <returns></returns>
private static Dictionary<string, FunctionHandler> InitializeStoreFunctionHandlers()
{
Dictionary<string, FunctionHandler> functionHandlers = new Dictionary<string, FunctionHandler>(15, StringComparer.Ordinal);
functionHandlers.Add("CONCAT", HandleConcatFunction);
functionHandlers.Add("DATEADD", HandleDatepartDateFunction);
functionHandlers.Add("DATEDIFF", HandleDatepartDateFunction);
functionHandlers.Add("DATENAME", HandleDatepartDateFunction);
functionHandlers.Add("DATEPART", HandleDatepartDateFunction);
// Spatial functions are mapped to static or instance members of geography/geometry
// Geography Static functions
functionHandlers.Add("POINTGEOGRAPHY", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::Point"));
// Geometry Static functions
functionHandlers.Add("POINTGEOMETRY", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::Point"));
// Spatial Instance functions (shared)
functionHandlers.Add("ASTEXTZM", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "AsTextZM", functionExpression, isPropertyAccess: false));
functionHandlers.Add("BUFFERWITHTOLERANCE", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "BufferWithTolerance", functionExpression, isPropertyAccess: false));
functionHandlers.Add("ENVELOPEANGLE", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "EnvelopeAngle", functionExpression, isPropertyAccess: false));
functionHandlers.Add("ENVELOPECENTER", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "EnvelopeCenter", functionExpression, isPropertyAccess: false));
functionHandlers.Add("INSTANCEOF", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "InstanceOf", functionExpression, isPropertyAccess: false));
functionHandlers.Add("FILTER", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "Filter", functionExpression, isPropertyAccess: false));
functionHandlers.Add("MAKEVALID", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "MakeValid", functionExpression, isPropertyAccess: false));
functionHandlers.Add("REDUCE", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "Reduce", functionExpression, isPropertyAccess: false));
functionHandlers.Add("NUMRINGS", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "NumRings", functionExpression, isPropertyAccess: false));
functionHandlers.Add("RINGN", (sqlgen, functionExpression) => WriteInstanceFunctionCall(sqlgen, "RingN", functionExpression, isPropertyAccess: false));
return functionHandlers;
}
/// <summary>
/// All special non-aggregate canonical functions and their handlers
/// </summary>
/// <returns></returns>
private static Dictionary<string, FunctionHandler> InitializeCanonicalFunctionHandlers()
{
Dictionary<string, FunctionHandler> functionHandlers = new Dictionary<string, FunctionHandler>(16, StringComparer.Ordinal);
functionHandlers.Add("IndexOf", HandleCanonicalFunctionIndexOf);
functionHandlers.Add("Length", HandleCanonicalFunctionLength);
functionHandlers.Add("NewGuid", HandleCanonicalFunctionNewGuid);
functionHandlers.Add("Round", HandleCanonicalFunctionRound);
functionHandlers.Add("Truncate", HandleCanonicalFunctionTruncate);
functionHandlers.Add("Abs", HandleCanonicalFunctionAbs);
functionHandlers.Add("ToLower", HandleCanonicalFunctionToLower);
functionHandlers.Add("ToUpper", HandleCanonicalFunctionToUpper);
functionHandlers.Add("Trim", HandleCanonicalFunctionTrim);
functionHandlers.Add("Contains", HandleCanonicalFunctionContains);
functionHandlers.Add("StartsWith", HandleCanonicalFunctionStartsWith);
functionHandlers.Add("EndsWith", HandleCanonicalFunctionEndsWith);
//DateTime Functions
functionHandlers.Add("Year", HandleCanonicalFunctionDatepart);
functionHandlers.Add("Month", HandleCanonicalFunctionDatepart);
functionHandlers.Add("Day", HandleCanonicalFunctionDatepart);
functionHandlers.Add("Hour", HandleCanonicalFunctionDatepart);
functionHandlers.Add("Minute", HandleCanonicalFunctionDatepart);
functionHandlers.Add("Second", HandleCanonicalFunctionDatepart);
functionHandlers.Add("Millisecond", HandleCanonicalFunctionDatepart);
functionHandlers.Add("DayOfYear", HandleCanonicalFunctionDatepart);
functionHandlers.Add("CurrentDateTime", HandleCanonicalFunctionCurrentDateTime);
functionHandlers.Add("CurrentUtcDateTime", HandleCanonicalFunctionCurrentUtcDateTime);
functionHandlers.Add("CurrentDateTimeOffset", HandleCanonicalFunctionCurrentDateTimeOffset);
functionHandlers.Add("GetTotalOffsetMinutes", HandleCanonicalFunctionGetTotalOffsetMinutes);
functionHandlers.Add("TruncateTime", HandleCanonicalFunctionTruncateTime);
functionHandlers.Add("CreateDateTime", HandleCanonicalFunctionCreateDateTime);
functionHandlers.Add("CreateDateTimeOffset", HandleCanonicalFunctionCreateDateTimeOffset);
functionHandlers.Add("CreateTime", HandleCanonicalFunctionCreateTime);
functionHandlers.Add("AddYears", HandleCanonicalFunctionDateAdd);
functionHandlers.Add("AddMonths", HandleCanonicalFunctionDateAdd);
functionHandlers.Add("AddDays", HandleCanonicalFunctionDateAdd);
functionHandlers.Add("AddHours", HandleCanonicalFunctionDateAdd);
functionHandlers.Add("AddMinutes", HandleCanonicalFunctionDateAdd);
functionHandlers.Add("AddSeconds", HandleCanonicalFunctionDateAdd);
functionHandlers.Add("AddMilliseconds", HandleCanonicalFunctionDateAdd);
functionHandlers.Add("AddMicroseconds", HandleCanonicalFunctionDateAddKatmaiOrNewer);
functionHandlers.Add("AddNanoseconds", HandleCanonicalFunctionDateAddKatmaiOrNewer);
functionHandlers.Add("DiffYears", HandleCanonicalFunctionDateDiff);
functionHandlers.Add("DiffMonths", HandleCanonicalFunctionDateDiff);
functionHandlers.Add("DiffDays", HandleCanonicalFunctionDateDiff);
functionHandlers.Add("DiffHours", HandleCanonicalFunctionDateDiff);
functionHandlers.Add("DiffMinutes", HandleCanonicalFunctionDateDiff);
functionHandlers.Add("DiffSeconds", HandleCanonicalFunctionDateDiff);
functionHandlers.Add("DiffMilliseconds", HandleCanonicalFunctionDateDiff);
functionHandlers.Add("DiffMicroseconds", HandleCanonicalFunctionDateDiffKatmaiOrNewer);
functionHandlers.Add("DiffNanoseconds", HandleCanonicalFunctionDateDiffKatmaiOrNewer);
//Functions that translate to operators
functionHandlers.Add("Concat", HandleConcatFunction);
functionHandlers.Add("BitwiseAnd", HandleCanonicalFunctionBitwise);
functionHandlers.Add("BitwiseNot", HandleCanonicalFunctionBitwise);
functionHandlers.Add("BitwiseOr", HandleCanonicalFunctionBitwise);
functionHandlers.Add("BitwiseXor", HandleCanonicalFunctionBitwise);
return functionHandlers;
}
/// <summary>
/// Initalizes the mapping from functions to TSql operators
/// for all functions that translate to TSql operators
/// </summary>
/// <returns></returns>
private static Dictionary<string, string> InitializeFunctionNameToOperatorDictionary()
{
Dictionary<string, string> functionNameToOperatorDictionary = new Dictionary<string, string>(5, StringComparer.Ordinal);
functionNameToOperatorDictionary.Add("Concat", "+"); //canonical
functionNameToOperatorDictionary.Add("CONCAT", "+"); //store
functionNameToOperatorDictionary.Add("BitwiseAnd", "&");
functionNameToOperatorDictionary.Add("BitwiseNot", "~");
functionNameToOperatorDictionary.Add("BitwiseOr", "|");
functionNameToOperatorDictionary.Add("BitwiseXor", "^");
return functionNameToOperatorDictionary;
}
/// <summary>
/// Initalizes the mapping from names of canonical function for date/time addition
/// to corresponding dateparts
/// </summary>
/// <returns></returns>
private static Dictionary<string, string> InitializeDateAddFunctionNameToDatepartDictionary()
{
Dictionary<string, string> dateAddFunctionNameToDatepartDictionary = new Dictionary<string, string>(5, StringComparer.Ordinal);
dateAddFunctionNameToDatepartDictionary.Add("AddYears", "year");
dateAddFunctionNameToDatepartDictionary.Add("AddMonths", "month");
dateAddFunctionNameToDatepartDictionary.Add("AddDays", "day");
dateAddFunctionNameToDatepartDictionary.Add("AddHours", "hour");
dateAddFunctionNameToDatepartDictionary.Add("AddMinutes", "minute");
dateAddFunctionNameToDatepartDictionary.Add("AddSeconds", "second");
dateAddFunctionNameToDatepartDictionary.Add("AddMilliseconds", "millisecond");
dateAddFunctionNameToDatepartDictionary.Add("AddMicroseconds", "microsecond");
dateAddFunctionNameToDatepartDictionary.Add("AddNanoseconds", "nanosecond");
return dateAddFunctionNameToDatepartDictionary;
}
/// <summary>
/// Initalizes the mapping from names of canonical function for date/time difference
/// to corresponding dateparts
/// </summary>
/// <returns></returns>
private static Dictionary<string, string> InitializeDateDiffFunctionNameToDatepartDictionary()
{
Dictionary<string, string> dateDiffFunctionNameToDatepartDictionary = new Dictionary<string, string>(5, StringComparer.Ordinal);
dateDiffFunctionNameToDatepartDictionary.Add("DiffYears", "year");
dateDiffFunctionNameToDatepartDictionary.Add("DiffMonths", "month");
dateDiffFunctionNameToDatepartDictionary.Add("DiffDays", "day");
dateDiffFunctionNameToDatepartDictionary.Add("DiffHours", "hour");
dateDiffFunctionNameToDatepartDictionary.Add("DiffMinutes", "minute");
dateDiffFunctionNameToDatepartDictionary.Add("DiffSeconds", "second");
dateDiffFunctionNameToDatepartDictionary.Add("DiffMilliseconds", "millisecond");
dateDiffFunctionNameToDatepartDictionary.Add("DiffMicroseconds", "microsecond");
dateDiffFunctionNameToDatepartDictionary.Add("DiffNanoseconds", "nanosecond");
return dateDiffFunctionNameToDatepartDictionary;
}
/// <summary>
/// Initalizes the mapping from names of canonical function that represent static geography methods to their corresponding
/// static method name, qualified with the 'geography::' prefix.
/// </summary>
/// <returns></returns>
private static Dictionary<string, FunctionHandler> InitializeGeographyStaticMethodFunctionsDictionary()
{
Dictionary<string, FunctionHandler> staticGeographyFunctions = new Dictionary<string, FunctionHandler>();
// Well Known Text constructors
staticGeographyFunctions.Add("GeographyFromText", HandleSpatialFromTextFunction);
staticGeographyFunctions.Add("GeographyPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPointFromText"));
staticGeographyFunctions.Add("GeographyLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STLineFromText"));
staticGeographyFunctions.Add("GeographyPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPolyFromText"));
staticGeographyFunctions.Add("GeographyMultiPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPointFromText"));
staticGeographyFunctions.Add("GeographyMultiLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMLineFromText"));
staticGeographyFunctions.Add("GeographyMultiPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPolyFromText"));
staticGeographyFunctions.Add("GeographyCollectionFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STGeomCollFromText"));
// Well Known Binary constructors
staticGeographyFunctions.Add("GeographyFromBinary", HandleSpatialFromBinaryFunction);
staticGeographyFunctions.Add("GeographyPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPointFromWKB"));
staticGeographyFunctions.Add("GeographyLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STLineFromWKB"));
staticGeographyFunctions.Add("GeographyPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STPolyFromWKB"));
staticGeographyFunctions.Add("GeographyMultiPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPointFromWKB"));
staticGeographyFunctions.Add("GeographyMultiLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMLineFromWKB"));
staticGeographyFunctions.Add("GeographyMultiPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STMPolyFromWKB"));
staticGeographyFunctions.Add("GeographyCollectionFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geography::STGeomCollFromWKB"));
// GML constructor (non-OGC)
staticGeographyFunctions.Add("GeographyFromGml", HandleSpatialFromGmlFunction);
return staticGeographyFunctions;
}
/// <summary>
/// Initalizes the mapping from names of canonical function that represent geography instance properties to their corresponding
/// store property name.
/// </summary>
/// <returns></returns>
private static Dictionary<string, string> InitializeGeographyInstancePropertyFunctionsDictionary()
{
Dictionary<string, string> instancePropGeographyFunctions = new Dictionary<string, string>();
instancePropGeographyFunctions.Add("CoordinateSystemId", "STSrid");
instancePropGeographyFunctions.Add("Latitude", "Lat");
instancePropGeographyFunctions.Add("Longitude", "Long");
instancePropGeographyFunctions.Add("Measure", "M");
instancePropGeographyFunctions.Add("Elevation", "Z");
return instancePropGeographyFunctions;
}
/// <summary>
/// Initalizes the mapping of canonical function name to instance method name for geography instance functions that differ in name from the sql server equivalent.
/// </summary>
/// <returns></returns>
private static Dictionary<string, string> InitializeRenamedGeographyInstanceMethodFunctions()
{
Dictionary<string, string> renamedInstanceMethodFunctions = new Dictionary<string, string>();
renamedInstanceMethodFunctions.Add("AsText", "STAsText");
renamedInstanceMethodFunctions.Add("AsBinary", "STAsBinary");
renamedInstanceMethodFunctions.Add("SpatialTypeName", "STGeometryType");
renamedInstanceMethodFunctions.Add("SpatialDimension", "STDimension");
renamedInstanceMethodFunctions.Add("IsEmptySpatial", "STIsEmpty");
renamedInstanceMethodFunctions.Add("SpatialEquals", "STEquals");
renamedInstanceMethodFunctions.Add("SpatialDisjoint", "STDisjoint");
renamedInstanceMethodFunctions.Add("SpatialIntersects", "STIntersects");
renamedInstanceMethodFunctions.Add("SpatialBuffer", "STBuffer");
renamedInstanceMethodFunctions.Add("Distance", "STDistance");
renamedInstanceMethodFunctions.Add("SpatialUnion", "STUnion");
renamedInstanceMethodFunctions.Add("SpatialIntersection", "STIntersection");
renamedInstanceMethodFunctions.Add("SpatialDifference", "STDifference");
renamedInstanceMethodFunctions.Add("SpatialSymmetricDifference", "STSymDifference");
renamedInstanceMethodFunctions.Add("SpatialElementCount", "STNumGeometries");
renamedInstanceMethodFunctions.Add("SpatialElementAt", "STGeometryN");
renamedInstanceMethodFunctions.Add("SpatialLength", "STLength");
renamedInstanceMethodFunctions.Add("StartPoint", "STStartPoint");
renamedInstanceMethodFunctions.Add("EndPoint", "STEndPoint");
renamedInstanceMethodFunctions.Add("IsClosedSpatial", "STIsClosed");
renamedInstanceMethodFunctions.Add("PointCount", "STNumPoints");
renamedInstanceMethodFunctions.Add("PointAt", "STPointN");
renamedInstanceMethodFunctions.Add("Area", "STArea");
return renamedInstanceMethodFunctions;
}
/// <summary>
/// Initalizes the mapping from names of canonical function that represent static geometry methods to their corresponding
/// static method name, qualified with the 'geometry::' prefix.
/// </summary>
/// <returns></returns>
private static Dictionary<string, FunctionHandler> InitializeGeometryStaticMethodFunctionsDictionary()
{
Dictionary<string, FunctionHandler> staticGeometryFunctions = new Dictionary<string, FunctionHandler>();
// Well Known Text constructors
staticGeometryFunctions.Add("GeometryFromText", HandleSpatialFromTextFunction);
staticGeometryFunctions.Add("GeometryPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPointFromText"));
staticGeometryFunctions.Add("GeometryLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STLineFromText"));
staticGeometryFunctions.Add("GeometryPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPolyFromText"));
staticGeometryFunctions.Add("GeometryMultiPointFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPointFromText"));
staticGeometryFunctions.Add("GeometryMultiLineFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMLineFromText"));
staticGeometryFunctions.Add("GeometryMultiPolygonFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPolyFromText"));
staticGeometryFunctions.Add("GeometryCollectionFromText", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STGeomCollFromText"));
// Well Known Binary constructors
staticGeometryFunctions.Add("GeometryFromBinary", HandleSpatialFromBinaryFunction);
staticGeometryFunctions.Add("GeometryPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPointFromWKB"));
staticGeometryFunctions.Add("GeometryLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STLineFromWKB"));
staticGeometryFunctions.Add("GeometryPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STPolyFromWKB"));
staticGeometryFunctions.Add("GeometryMultiPointFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPointFromWKB"));
staticGeometryFunctions.Add("GeometryMultiLineFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMLineFromWKB"));
staticGeometryFunctions.Add("GeometryMultiPolygonFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STMPolyFromWKB"));
staticGeometryFunctions.Add("GeometryCollectionFromBinary", (sqlgen, functionExpression) => HandleFunctionDefaultGivenName(sqlgen, functionExpression, "geometry::STGeomCollFromWKB"));
// GML constructor (non-OGC)
staticGeometryFunctions.Add("GeometryFromGml", HandleSpatialFromGmlFunction);
return staticGeometryFunctions;
}
/// <summary>
/// Initalizes the mapping from names of canonical function that represent geometry instance properties to their corresponding
/// store property name.
/// </summary>
/// <returns></returns>
private static Dictionary<string, string> InitializeGeometryInstancePropertyFunctionsDictionary()
{
Dictionary<string, string> instancePropGeometryFunctions = new Dictionary<string, string>();
instancePropGeometryFunctions.Add("CoordinateSystemId", "STSrid");
instancePropGeometryFunctions.Add("Measure", "M");
instancePropGeometryFunctions.Add("XCoordinate", "STX");
instancePropGeometryFunctions.Add("YCoordinate", "STY");
instancePropGeometryFunctions.Add("Elevation", "Z");
return instancePropGeometryFunctions;
}
/// <summary>
/// Initalizes the mapping of canonical function name to instance method name for geometry instance functions that differ in name from the sql server equivalent.
/// </summary>
/// <returns></returns>
private static Dictionary<string, string> InitializeRenamedGeometryInstanceMethodFunctions()
{
Dictionary<string, string> renamedInstanceMethodFunctions = new Dictionary<string, string>();
renamedInstanceMethodFunctions.Add("AsText", "STAsText");
renamedInstanceMethodFunctions.Add("AsBinary", "STAsBinary");
renamedInstanceMethodFunctions.Add("SpatialTypeName", "STGeometryType");
renamedInstanceMethodFunctions.Add("SpatialDimension", "STDimension");
renamedInstanceMethodFunctions.Add("IsEmptySpatial", "STIsEmpty");
renamedInstanceMethodFunctions.Add("IsSimpleGeometry", "STIsSimple");
renamedInstanceMethodFunctions.Add("IsValidGeometry", "STIsValid");
renamedInstanceMethodFunctions.Add("SpatialBoundary", "STBoundary");
renamedInstanceMethodFunctions.Add("SpatialEnvelope", "STEnvelope");
renamedInstanceMethodFunctions.Add("SpatialEquals", "STEquals");
renamedInstanceMethodFunctions.Add("SpatialDisjoint", "STDisjoint");
renamedInstanceMethodFunctions.Add("SpatialIntersects", "STIntersects");
renamedInstanceMethodFunctions.Add("SpatialTouches", "STTouches");
renamedInstanceMethodFunctions.Add("SpatialCrosses", "STCrosses");
renamedInstanceMethodFunctions.Add("SpatialWithin", "STWithin");
renamedInstanceMethodFunctions.Add("SpatialContains", "STContains");
renamedInstanceMethodFunctions.Add("SpatialOverlaps", "STOverlaps");
renamedInstanceMethodFunctions.Add("SpatialRelate", "STRelate");
renamedInstanceMethodFunctions.Add("SpatialBuffer", "STBuffer");
renamedInstanceMethodFunctions.Add("SpatialConvexHull", "STConvexHull");
renamedInstanceMethodFunctions.Add("Distance", "STDistance");
renamedInstanceMethodFunctions.Add("SpatialUnion", "STUnion");
renamedInstanceMethodFunctions.Add("SpatialIntersection", "STIntersection");
renamedInstanceMethodFunctions.Add("SpatialDifference", "STDifference");
renamedInstanceMethodFunctions.Add("SpatialSymmetricDifference", "STSymDifference");
renamedInstanceMethodFunctions.Add("SpatialElementCount", "STNumGeometries");
renamedInstanceMethodFunctions.Add("SpatialElementAt", "STGeometryN");
renamedInstanceMethodFunctions.Add("SpatialLength", "STLength");
renamedInstanceMethodFunctions.Add("StartPoint", "STStartPoint");
renamedInstanceMethodFunctions.Add("EndPoint", "STEndPoint");
renamedInstanceMethodFunctions.Add("IsClosedSpatial", "STIsClosed");
renamedInstanceMethodFunctions.Add("IsRing", "STIsRing");
renamedInstanceMethodFunctions.Add("PointCount", "STNumPoints");
renamedInstanceMethodFunctions.Add("PointAt", "STPointN");
renamedInstanceMethodFunctions.Add("Area", "STArea");
renamedInstanceMethodFunctions.Add("Centroid", "STCentroid");
renamedInstanceMethodFunctions.Add("PointOnSurface", "STPointOnSurface");
renamedInstanceMethodFunctions.Add("ExteriorRing", "STExteriorRing");
renamedInstanceMethodFunctions.Add("InteriorRingCount", "STNumInteriorRing");
renamedInstanceMethodFunctions.Add("InteriorRingAt", "STInteriorRingN");
return renamedInstanceMethodFunctions;
}
private static ISqlFragment HandleSpatialFromTextFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
{
string functionNameWithSrid = (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::STGeomFromText" : "geography::STGeomFromText");
string functionNameWithoutSrid = (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::Parse" : "geography::Parse");
if (functionExpression.Arguments.Count == 2)
{
return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionNameWithSrid);
}
else
{
Debug.Assert(functionExpression.Arguments.Count == 1, "FromText function should have text or text + srid arguments only");
return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionNameWithoutSrid);
}
}
private static ISqlFragment HandleSpatialFromGmlFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
{
return HandleSpatialStaticMethodFunctionAppendSrid(sqlgen, functionExpression, (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::GeomFromGml" : "geography::GeomFromGml"));
}
private static ISqlFragment HandleSpatialFromBinaryFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
{
return HandleSpatialStaticMethodFunctionAppendSrid(sqlgen, functionExpression, (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? "geometry::STGeomFromWKB" : "geography::STGeomFromWKB"));
}
private static readonly DbExpression defaultGeographySridExpression = DbExpressionBuilder.Constant(DbGeography.DefaultCoordinateSystemId);
private static readonly DbExpression defaultGeometrySridExpression = DbExpressionBuilder.Constant(DbGeometry.DefaultCoordinateSystemId);
private static ISqlFragment HandleSpatialStaticMethodFunctionAppendSrid(SqlGenerator sqlgen, DbFunctionExpression functionExpression, string functionName)
{
if (functionExpression.Arguments.Count == 2)
{
return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionName);
}
else
{
DbExpression sridExpression = (TypeSemantics.IsPrimitiveType(functionExpression.ResultType, PrimitiveTypeKind.Geometry) ? defaultGeometrySridExpression : defaultGeographySridExpression);
SqlBuilder result = new SqlBuilder();
result.Append(functionName);
WriteFunctionArguments(sqlgen, functionExpression.Arguments.Concat(new[] { sridExpression }), result);
return result;
}
}
#endregion
internal static ISqlFragment GenerateFunctionCallSql(SqlGenerator sqlgen, DbFunctionExpression functionExpression)
{
//
// check if function requires special case processing, if so, delegates to it
//
if (IsSpecialCanonicalFunction(functionExpression))
{
return HandleSpecialCanonicalFunction(sqlgen, functionExpression);
}
if (IsSpecialStoreFunction(functionExpression))
{
return HandleSpecialStoreFunction(sqlgen, functionExpression);
}
PrimitiveTypeKind spatialTypeKind;
if(IsSpatialCanonicalFunction(functionExpression, out spatialTypeKind))
{
return HandleSpatialCanonicalFunction(sqlgen, functionExpression, spatialTypeKind);
}
return HandleFunctionDefault(sqlgen, functionExpression);
}
/// <summary>
/// Determines whether the given function is a store function that
/// requires special handling
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static bool IsSpecialStoreFunction(DbFunctionExpression e)
{
return IsStoreFunction(e.Function)
&& _storeFunctionHandlers.ContainsKey(e.Function.Name);
}
/// <summary>
/// Determines whether the given function is a canonical function that
/// requires special handling
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static bool IsSpecialCanonicalFunction(DbFunctionExpression e)
{
return TypeHelpers.IsCanonicalFunction(e.Function)
&& _canonicalFunctionHandlers.ContainsKey(e.Function.Name);
}
/// <summary>
/// Determines whether the given function is a canonical function the translates
/// to a spatial (geography/geometry) property access or method call.
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static bool IsSpatialCanonicalFunction(DbFunctionExpression e, out PrimitiveTypeKind spatialTypeKind)
{
if (TypeHelpers.IsCanonicalFunction(e.Function))
{
if (Helper.IsSpatialType(e.ResultType, out spatialTypeKind))
{
return true;
}
foreach (FunctionParameter functionParameter in e.Function.Parameters)
{
if (Helper.IsSpatialType(functionParameter.TypeUsage, out spatialTypeKind))
{
return true;
}
}
}
spatialTypeKind = default(PrimitiveTypeKind);
return false;
}
/// <summary>
/// Default handling for functions.
/// Translates them to FunctionName(arg1, arg2, ..., argn)
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleFunctionDefault(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleFunctionDefaultGivenName(sqlgen, e, null);
}
/// <summary>
/// Default handling for functions with a given name.
/// Translates them to FunctionName(arg1, arg2, ..., argn)
/// </summary>
/// <param name="e"></param>
/// <param name="functionName"></param>
/// <returns></returns>
private static ISqlFragment HandleFunctionDefaultGivenName(SqlGenerator sqlgen, DbFunctionExpression e, string functionName)
{
// NOTE: The order of checks is important in case of CHARINDEX.
if (CastReturnTypeToInt64(e))
{
return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "bigint");
}
else if (CastReturnTypeToInt32(sqlgen, e))
{
return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "int");
}
else if (CastReturnTypeToInt16(e))
{
return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "smallint");
}
else if (CastReturnTypeToSingle(e))
{
return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, "real");
}
else
{
return HandleFunctionDefaultCastReturnValue(sqlgen, e, functionName, null);
}
}
/// <summary>
/// Default handling for functions with a given name and given return value cast.
/// Translates them to CAST(FunctionName(arg1, arg2, ..., argn) AS returnType)
/// </summary>
/// <param name="e"></param>
/// <param name="functionName"></param>
/// <param name="returnType"></param>
/// <returns></returns>
private static ISqlFragment HandleFunctionDefaultCastReturnValue(SqlGenerator sqlgen, DbFunctionExpression e, string functionName, string returnType)
{
return WrapWithCast(returnType, result =>
{
if (functionName == null)
{
WriteFunctionName(result, e.Function);
}
else
{
result.Append(functionName);
}
HandleFunctionArgumentsDefault(sqlgen, e, result);
});
}
private static ISqlFragment WrapWithCast(string returnType, Action<SqlBuilder> toWrap)
{
SqlBuilder result = new SqlBuilder();
if (returnType != null)
{
result.Append(" CAST(");
}
toWrap(result);
if (returnType != null)
{
result.Append(" AS ");
result.Append(returnType);
result.Append(")");
}
return result;
}
/// <summary>
/// Default handling on function arguments.
/// Appends the list of arguments to the given result
/// If the function is niladic it does not append anything,
/// otherwise it appends (arg1, arg2, .., argn)
/// </summary>
/// <param name="e"></param>
/// <param name="result"></param>
private static void HandleFunctionArgumentsDefault(SqlGenerator sqlgen, DbFunctionExpression e, SqlBuilder result)
{
bool isNiladicFunction = e.Function.NiladicFunctionAttribute;
Debug.Assert(!(isNiladicFunction && (0 < e.Arguments.Count)), "function attributed as NiladicFunction='true' in the provider manifest cannot have arguments");
if (isNiladicFunction && e.Arguments.Count > 0)
{
EntityUtil.Metadata(System.Data.Entity.Strings.SqlGen_NiladicFunctionsCannotHaveParameters);
}
if (!isNiladicFunction)
{
WriteFunctionArguments(sqlgen, e.Arguments, result);
}
}
private static void WriteFunctionArguments(SqlGenerator sqlgen, IEnumerable<DbExpression> functionArguments, SqlBuilder result)
{
result.Append("(");
string separator = "";
foreach (DbExpression arg in functionArguments)
{
result.Append(separator);
result.Append(arg.Accept(sqlgen));
separator = ", ";
}
result.Append(")");
}
/// <summary>
/// Handler for functions that need to be translated to different store function based on version
/// </summary>
/// <param name="e"></param>
/// <param name="preKatmaiName"></param>
/// <param name="katmaiName"></param>
/// <returns></returns>
private static ISqlFragment HandleFunctionGivenNameBasedOnVersion(SqlGenerator sqlgen, DbFunctionExpression e, string preKatmaiName, string katmaiName)
{
if (sqlgen.IsPreKatmai)
{
return HandleFunctionDefaultGivenName(sqlgen, e, preKatmaiName);
}
return HandleFunctionDefaultGivenName(sqlgen, e, katmaiName);
}
/// <summary>
/// Handler for special build in functions
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleSpecialStoreFunction(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleSpecialFunction(_storeFunctionHandlers, sqlgen, e);
}
/// <summary>
/// Handler for special canonical functions
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleSpecialCanonicalFunction(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleSpecialFunction(_canonicalFunctionHandlers, sqlgen, e);
}
/// <summary>
/// Dispatches the special function processing to the appropriate handler
/// </summary>
/// <param name="handlers"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleSpecialFunction(Dictionary<string, FunctionHandler> handlers, SqlGenerator sqlgen, DbFunctionExpression e)
{
Debug.Assert(handlers.ContainsKey(e.Function.Name), "Special handling should be called only for functions in the list of special functions");
return handlers[e.Function.Name](sqlgen, e);
}
private static ISqlFragment HandleSpatialCanonicalFunction(SqlGenerator sqlgen, DbFunctionExpression functionExpression, PrimitiveTypeKind spatialTypeKind)
{
Debug.Assert(spatialTypeKind == PrimitiveTypeKind.Geography || spatialTypeKind == PrimitiveTypeKind.Geometry, "Spatial function does not refer to a valid spatial primitive type kind?");
if (spatialTypeKind == PrimitiveTypeKind.Geography)
{
return HandleSpatialCanonicalFunction(sqlgen, functionExpression, _geographyFunctionNameToStaticMethodHandlerDictionary, _geographyFunctionNameToInstancePropertyNameDictionary, _geographyRenamedInstanceMethodFunctionDictionary);
}
else
{
return HandleSpatialCanonicalFunction(sqlgen, functionExpression, _geometryFunctionNameToStaticMethodHandlerDictionary, _geometryFunctionNameToInstancePropertyNameDictionary, _geometryRenamedInstanceMethodFunctionDictionary);
}
}
private static ISqlFragment HandleSpatialCanonicalFunction(SqlGenerator sqlgen,
DbFunctionExpression functionExpression,
Dictionary<string, FunctionHandler> staticMethodsMap,
Dictionary<string, string> instancePropertiesMap,
Dictionary<string, string> renamedInstanceMethodsMap)
{
FunctionHandler staticFunctionHandler;
string instancePropertyName;
if (staticMethodsMap.TryGetValue(functionExpression.Function.Name, out staticFunctionHandler))
{
return staticFunctionHandler(sqlgen, functionExpression);
}
else if (instancePropertiesMap.TryGetValue(functionExpression.Function.Name, out instancePropertyName))
{
Debug.Assert(functionExpression.Function.Parameters.Count > 0 && Helper.IsSpatialType(functionExpression.Function.Parameters[0].TypeUsage), "Instance property function does not have instance parameter?");
return WriteInstanceFunctionCall(sqlgen, instancePropertyName, functionExpression, isPropertyAccess: true, castReturnTypeTo: null);
}
else
{
// Default translation pattern is instance method; the instance method name may differ from that of the spatial canonical function
Debug.Assert(functionExpression.Function.Parameters.Count > 0 && Helper.IsSpatialType(functionExpression.Function.Parameters[0].TypeUsage), "Instance method function does not have instance parameter?");
string effectiveFunctionName;
if (!renamedInstanceMethodsMap.TryGetValue(functionExpression.Function.Name, out effectiveFunctionName))
{
effectiveFunctionName = functionExpression.Function.Name;
}
// For AsGml() calls, the XML result must be cast to string to match the declared function result type.
string castResultType = null;
if (effectiveFunctionName == "AsGml")
{
castResultType = sqlgen.DefaultStringTypeName;
}
return WriteInstanceFunctionCall(sqlgen, effectiveFunctionName, functionExpression, isPropertyAccess: false, castReturnTypeTo: castResultType);
}
}
private static ISqlFragment WriteInstanceFunctionCall(SqlGenerator sqlgen, string functionName, DbFunctionExpression functionExpression, bool isPropertyAccess)
{
return WriteInstanceFunctionCall(sqlgen, functionName, functionExpression, isPropertyAccess, null);
}
private static ISqlFragment WriteInstanceFunctionCall(SqlGenerator sqlgen, string functionName, DbFunctionExpression functionExpression, bool isPropertyAccess, string castReturnTypeTo)
{
Debug.Assert(!isPropertyAccess || functionExpression.Arguments.Count == 1, "Property accessor instance functions should have only the single instance argument");
return WrapWithCast(castReturnTypeTo, result =>
{
DbExpression instanceExpression = functionExpression.Arguments[0];
// Write the instance - if this is another function call, it need not be enclosed in parentheses.
if (instanceExpression.ExpressionKind != DbExpressionKind.Function)
{
sqlgen.ParenthesizeExpressionIfNeeded(instanceExpression, result);
}
else
{
result.Append(instanceExpression.Accept(sqlgen));
}
result.Append(".");
result.Append(functionName);
if (!isPropertyAccess)
{
WriteFunctionArguments(sqlgen, functionExpression.Arguments.Skip(1), result);
}
});
}
/// <summary>
/// Handles functions that are translated into TSQL operators.
/// The given function should have one or two arguments.
/// Functions with one arguemnt are translated into
/// op arg
/// Functions with two arguments are translated into
/// arg0 op arg1
/// Also, the arguments can be optionaly enclosed in parethesis
/// </summary>
/// <param name="e"></param>
/// <param name="parenthesiseArguments">Whether the arguments should be enclosed in parethesis</param>
/// <returns></returns>
private static ISqlFragment HandleSpecialFunctionToOperator(SqlGenerator sqlgen, DbFunctionExpression e, bool parenthesiseArguments)
{
SqlBuilder result = new SqlBuilder();
Debug.Assert(e.Arguments.Count > 0 && e.Arguments.Count <= 2, "There should be 1 or 2 arguments for operator");
if (e.Arguments.Count > 1)
{
if (parenthesiseArguments)
{
result.Append("(");
}
result.Append(e.Arguments[0].Accept(sqlgen));
if (parenthesiseArguments)
{
result.Append(")");
}
}
result.Append(" ");
Debug.Assert(_functionNameToOperatorDictionary.ContainsKey(e.Function.Name), "The function can not be mapped to an operator");
result.Append(_functionNameToOperatorDictionary[e.Function.Name]);
result.Append(" ");
if (parenthesiseArguments)
{
result.Append("(");
}
result.Append(e.Arguments[e.Arguments.Count - 1].Accept(sqlgen));
if (parenthesiseArguments)
{
result.Append(")");
}
return result;
}
/// <summary>
/// <see cref="HandleSpecialFunctionToOperator"></see>
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleConcatFunction(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleSpecialFunctionToOperator(sqlgen, e, false);
}
/// <summary>
/// <see cref="HandleSpecialFunctionToOperator"></see>
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionBitwise(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleSpecialFunctionToOperator(sqlgen, e, true);
}
/// <summary>
/// Handles special case in which datapart 'type' parameter is present. all the functions
/// handles here have *only* the 1st parameter as datepart. datepart value is passed along
/// the QP as string and has to be expanded as TSQL keyword.
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleDatepartDateFunction(SqlGenerator sqlgen, DbFunctionExpression e)
{
Debug.Assert(e.Arguments.Count > 0, "e.Arguments.Count > 0");
DbConstantExpression constExpr = e.Arguments[0] as DbConstantExpression;
if (null == constExpr)
{
throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
}
string datepart = constExpr.Value as string;
if (null == datepart)
{
throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
}
SqlBuilder result = new SqlBuilder();
//
// check if datepart value is valid
//
if (!_datepartKeywords.Contains(datepart))
{
throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.SqlGen_InvalidDatePartArgumentValue(datepart, e.Function.NamespaceName, e.Function.Name));
}
//
// finaly, expand the function name
//
WriteFunctionName(result, e.Function);
result.Append("(");
// expand the datepart literal as tsql kword
result.Append(datepart);
string separator = ", ";
// expand remaining arguments
for (int i = 1; i < e.Arguments.Count; i++)
{
result.Append(separator);
result.Append(e.Arguments[i].Accept(sqlgen));
}
result.Append(")");
return result;
}
/// <summary>
/// Handler for canonical functions for extracting date parts.
/// For example:
/// Year(date) -> DATEPART( year, date)
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1308:NormalizeStringsToUppercase")]
private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleCanonicalFunctionDatepart(sqlgen, e.Function.Name.ToLowerInvariant(), e);
}
/// <summary>
/// Handler for canonical funcitons for GetTotalOffsetMinutes.
/// GetTotalOffsetMinutes(e) --> Datepart(tzoffset, e)
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionGetTotalOffsetMinutes(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleCanonicalFunctionDatepart(sqlgen, "tzoffset", e);
}
/// <summary>
/// Handler for turning a canonical function into DATEPART
/// Results in DATEPART(datepart, e)
/// </summary>
/// <param name="datepart"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, string datepart, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("DATEPART (");
result.Append(datepart);
result.Append(", ");
Debug.Assert(e.Arguments.Count == 1, "Canonical datepart functions should have exactly one argument");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(")");
return result;
}
/// <summary>
/// Handler for the canonical function CurrentDateTime
/// For Sql8 and Sql9: CurrentDateTime() -> GetDate()
/// For Sql10: CurrentDateTime() -> SysDateTime()
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionCurrentDateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleFunctionGivenNameBasedOnVersion(sqlgen, e, "GetDate", "SysDateTime");
}
/// <summary>
/// Handler for the canonical function CurrentUtcDateTime
/// For Sql8 and Sql9: CurrentUtcDateTime() -> GetUtcDate()
/// For Sql10: CurrentUtcDateTime() -> SysUtcDateTime()
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionCurrentUtcDateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleFunctionGivenNameBasedOnVersion(sqlgen, e, "GetUtcDate", "SysUtcDateTime");
}
/// <summary>
/// Handler for the canonical function CurrentDateTimeOffset
/// For Sql8 and Sql9: throw
/// For Sql10: CurrentDateTimeOffset() -> SysDateTimeOffset()
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionCurrentDateTimeOffset(SqlGenerator sqlgen, DbFunctionExpression e)
{
sqlgen.AssertKatmaiOrNewer(e);
return HandleFunctionDefaultGivenName(sqlgen, e, "SysDateTimeOffset");
}
/// <summary>
/// See <see cref="HandleCanonicalFunctionDateTimeTypeCreation"/> for exact translation
/// Pre Katmai creates datetime.
/// On Katmai creates datetime2.
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionCreateDateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{
string typeName = (sqlgen.IsPreKatmai) ? "datetime" : "datetime2";
return HandleCanonicalFunctionDateTimeTypeCreation(sqlgen, typeName, e.Arguments, true, false);
}
/// <summary>
/// See <see cref="HandleCanonicalFunctionDateTimeTypeCreation"/> for exact translation
/// Pre Katmai not supported.
/// On Katmai creates datetimeoffset.
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionCreateDateTimeOffset(SqlGenerator sqlgen, DbFunctionExpression e)
{
sqlgen.AssertKatmaiOrNewer(e);
return HandleCanonicalFunctionDateTimeTypeCreation(sqlgen, "datetimeoffset", e.Arguments, true, true);
}
/// <summary>
/// See <see cref="HandleCanonicalFunctionDateTimeTypeCreation"/> for exact translation
/// Pre Katmai not supported.
/// On Katmai creates time.
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionCreateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{
sqlgen.AssertKatmaiOrNewer(e);
return HandleCanonicalFunctionDateTimeTypeCreation(sqlgen, "time", e.Arguments, false, false);
}
/// <summary>
/// Helper for all date and time types creating functions.
///
/// The given expression is in general trainslated into:
///
/// CONVERT(@typename, [datePart] + [timePart] + [timeZonePart], 121), where the datePart and the timeZonePart are optional
///
/// Only on Katmai, if a date part is present it is wrapped with a call for adding years as shown below.
/// The individual parts are translated as:
///
/// Date part:
/// PRE KATMAI: convert(varchar(255), @year) + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day)
/// KATMAI: DateAdd(year, @year-1, covert(@typename, '0001' + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day) + [possibly time ], 121)
///
/// Time part:
/// PRE KATMAI: convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 6, 3)
/// KATMAI: convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 10, 7)
///
/// Time zone part:
/// (case when @tzoffset >= 0 then '+' else '-' end) + convert(varchar(255), ABS(@tzoffset)/60) + ':' + convert(varchar(255), ABS(@tzoffset)%60)
///
/// </summary>
/// <param name="typeName"></param>
/// <param name="args"></param>
/// <param name="hasDatePart"></param>
/// <param name="hasTimeZonePart"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDateTimeTypeCreation(SqlGenerator sqlgen, string typeName, IList<DbExpression> args, bool hasDatePart, bool hasTimeZonePart)
{
Debug.Assert(args.Count == (hasDatePart ? 3 : 0) + 3 + (hasTimeZonePart ? 1 : 0), "Invalid number of parameters for a date time creating function");
SqlBuilder result = new SqlBuilder();
int currentArgumentIndex = 0;
if (!sqlgen.IsPreKatmai && hasDatePart)
{
result.Append("DATEADD(year, ");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex++], result);
result.Append(" - 1, ");
}
result.Append("convert (");
result.Append(typeName);
result.Append(",");
//Building the string representation
if (hasDatePart)
{
// YEAR: PREKATMAI: CONVERT(VARCHAR, @YEAR)
// KATMAI : '0001'
if (!sqlgen.IsPreKatmai)
{
result.Append("'0001'");
}
else
{
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
}
// MONTH
result.Append(" + '-' + ");
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
// DAY
result.Append(" + '-' + ");
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
result.Append(" + ' ' + ");
}
// HOUR
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
// MINUTE
result.Append(" + ':' + ");
AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]);
// SECOND
result.Append(" + ':' + str(");
result.Append(args[currentArgumentIndex++].Accept(sqlgen));
if (sqlgen.IsPreKatmai)
{
result.Append(", 6, 3)");
}
else
{
result.Append(", 10, 7)");
}
// TZOFFSET
if (hasTimeZonePart)
{
result.Append(" + (CASE WHEN ");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
result.Append(" >= 0 THEN '+' ELSE '-' END) + convert(varchar(255), ABS(");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
result.Append("/60)) + ':' + convert(varchar(255), ABS(");
sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result);
result.Append("%60))");
}
result.Append(", 121)");
if (!sqlgen.IsPreKatmai && hasDatePart)
{
result.Append(")");
}
return result;
}
/// <summary>
/// Helper method that wrapps the given expession with a conver to varchar(255)
/// </summary>
/// <param name="result"></param>
/// <param name="e"></param>
private static void AppendConvertToVarchar(SqlGenerator sqlgen, SqlBuilder result, DbExpression e)
{
result.Append("convert(varchar(255), ");
result.Append(e.Accept(sqlgen));
result.Append(")");
}
/// <summary>
/// TruncateTime(DateTime X)
/// PreKatmai: TRUNCATETIME(X) => CONVERT(DATETIME, CONVERT(VARCHAR(255), expression, 102), 102)
/// Katmai: TRUNCATETIME(X) => CONVERT(DATETIME2, CONVERT(VARCHAR(255), expression, 102), 102)
///
/// TruncateTime(DateTimeOffset X)
/// TRUNCATETIME(X) => CONVERT(datetimeoffset, CONVERT(VARCHAR(255), expression, 102)
/// + ' 00:00:00 ' + Right(convert(varchar(255), @arg, 121), 6), 102)
///
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionTruncateTime(SqlGenerator sqlgen, DbFunctionExpression e)
{
//The type that we need to return is based on the argument type.
string typeName = null;
bool isDateTimeOffset = false;
PrimitiveTypeKind typeKind;
bool isPrimitiveType = TypeHelpers.TryGetPrimitiveTypeKind(e.Arguments[0].ResultType, out typeKind);
Debug.Assert(isPrimitiveType, "Expecting primitive type as input parameter to TruncateTime");
if (typeKind == PrimitiveTypeKind.DateTime)
{
typeName = sqlgen.IsPreKatmai ? "datetime" : "datetime2";
}
else if (typeKind == PrimitiveTypeKind.DateTimeOffset)
{
typeName = "datetimeoffset";
isDateTimeOffset = true;
}
else
{
Debug.Assert(true, "Unexpected type to TruncateTime" + typeKind.ToString());
}
SqlBuilder result = new SqlBuilder();
result.Append("convert (");
result.Append(typeName);
result.Append(", convert(varchar(255), ");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", 102) ");
if (isDateTimeOffset)
{
result.Append("+ ' 00:00:00 ' + Right(convert(varchar(255), ");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", 121), 6) ");
}
result.Append(", 102)");
return result;
}
/// <summary>
/// Handler for date addition functions supported only starting from Katmai
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDateAddKatmaiOrNewer(SqlGenerator sqlgen, DbFunctionExpression e)
{
sqlgen.AssertKatmaiOrNewer(e);
return HandleCanonicalFunctionDateAdd(sqlgen, e);
}
/// <summary>
/// Handler for all date/time addition canonical functions.
/// Translation, e.g.
/// AddYears(datetime, number) => DATEADD(year, number, datetime)
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDateAdd(SqlGenerator sqlgen, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("DATEADD (");
result.Append(_dateAddFunctionNameToDatepartDictionary[e.Function.Name]);
result.Append(", ");
result.Append(e.Arguments[1].Accept(sqlgen));
result.Append(", ");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(")");
return result;
}
/// <summary>
/// Hanndler for date differencing functions supported only starting from Katmai
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDateDiffKatmaiOrNewer(SqlGenerator sqlgen, DbFunctionExpression e)
{
sqlgen.AssertKatmaiOrNewer(e);
return HandleCanonicalFunctionDateDiff(sqlgen, e);
}
/// <summary>
/// Handler for all date/time addition canonical functions.
/// Translation, e.g.
/// DiffYears(datetime, number) => DATEDIFF(year, number, datetime)
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionDateDiff(SqlGenerator sqlgen, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("DATEDIFF (");
result.Append(_dateDiffFunctionNameToDatepartDictionary[e.Function.Name]);
result.Append(", ");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", ");
result.Append(e.Arguments[1].Accept(sqlgen));
result.Append(")");
return result;
}
/// <summary>
/// Function rename IndexOf -> CHARINDEX
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionIndexOf(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleFunctionDefaultGivenName(sqlgen, e, "CHARINDEX");
}
/// <summary>
/// Function rename NewGuid -> NEWID
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionNewGuid(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleFunctionDefaultGivenName(sqlgen, e, "NEWID");
}
/// <summary>
/// Function rename Length -> LEN
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionLength(SqlGenerator sqlgen, DbFunctionExpression e)
{
// We are aware of SQL Server's trimming of trailing spaces. We disclaim that behavior in general.
// It's up to the user to decide whether to trim them explicitly or to append a non-blank space char explicitly.
// Once SQL Server implements a function that computes Length correctly, we'll use it here instead of LEN,
// and we'll drop the disclaimer.
return HandleFunctionDefaultGivenName(sqlgen, e, "LEN");
}
/// <summary>
/// Round(numericExpression) -> Round(numericExpression, 0);
/// Round(numericExpression, digits) -> Round(numericExpression, digits);
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionRound(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleCanonicalFunctionRoundOrTruncate(sqlgen, e, true);
}
/// <summary>
/// Truncate(numericExpression) -> Round(numericExpression, 0, 1); (does not exist as canonical function yet)
/// Truncate(numericExpression, digits) -> Round(numericExpression, digits, 1);
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionTruncate(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleCanonicalFunctionRoundOrTruncate(sqlgen, e, false);
}
/// <summary>
/// Common handler for the canonical functions ROUND and TRUNCATE
/// </summary>
/// <param name="e"></param>
/// <param name="round"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionRoundOrTruncate(SqlGenerator sqlgen, DbFunctionExpression e, bool round)
{
SqlBuilder result = new SqlBuilder();
// Do not add the cast for the Round() overload having two arguments.
// Round(Single,Int32) maps to Round(Double,Int32)due to implicit casting.
// We don't need to cast in that case, since the server returned type is same
// as the expected type. Cast is only required for the overload - Round(Single)
bool requiresCastToSingle = false;
if (e.Arguments.Count == 1)
{
requiresCastToSingle = CastReturnTypeToSingle(e);
if (requiresCastToSingle)
{
result.Append(" CAST(");
}
}
result.Append("ROUND(");
Debug.Assert(e.Arguments.Count <= 2, "Round or truncate should have at most 2 arguments");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append(", ");
if (e.Arguments.Count > 1)
{
result.Append(e.Arguments[1].Accept(sqlgen));
}
else
{
result.Append("0");
}
if (!round)
{
result.Append(", 1");
}
result.Append(")");
if (requiresCastToSingle)
{
result.Append(" AS real)");
}
return result;
}
/// <summary>
/// Handle the canonical function Abs().
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionAbs(SqlGenerator sqlgen, DbFunctionExpression e)
{
// Convert the call to Abs(Byte) to a no-op, since Byte is an unsigned type.
if (TypeSemantics.IsPrimitiveType(e.Arguments[0].ResultType, PrimitiveTypeKind.Byte))
{
SqlBuilder result = new SqlBuilder();
result.Append(e.Arguments[0].Accept(sqlgen));
return result;
}
else
{
return HandleFunctionDefault(sqlgen, e);
}
}
/// <summary>
/// TRIM(string) -> LTRIM(RTRIM(string))
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionTrim(SqlGenerator sqlgen, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("LTRIM(RTRIM(");
Debug.Assert(e.Arguments.Count == 1, "Trim should have one argument");
result.Append(e.Arguments[0].Accept(sqlgen));
result.Append("))");
return result;
}
/// <summary>
/// Function rename ToLower -> LOWER
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionToLower(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleFunctionDefaultGivenName(sqlgen, e, "LOWER");
}
/// <summary>
/// Function rename ToUpper -> UPPER
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionToUpper(SqlGenerator sqlgen, DbFunctionExpression e)
{
return HandleFunctionDefaultGivenName(sqlgen, e, "UPPER");
}
/// <summary>
/// Function to translate the StartsWith, EndsWith and Contains canonical functions to LIKE expression in T-SQL
/// and also add the trailing ESCAPE '~' when escaping of the search string for the LIKE expression has occurred
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="targetExpression"></param>
/// <param name="constSearchParamExpression"></param>
/// <param name="result"></param>
/// <param name="insertPercentStart"></param>
/// <param name="insertPercentEnd"></param>
private static void TranslateConstantParameterForLike(SqlGenerator sqlgen, DbExpression targetExpression, DbConstantExpression constSearchParamExpression, SqlBuilder result, bool insertPercentStart, bool insertPercentEnd)
{
result.Append(targetExpression.Accept(sqlgen));
result.Append(" LIKE ");
// If it's a DbConstantExpression then escape the search parameter if necessary.
bool escapingOccurred;
StringBuilder searchParamBuilder = new StringBuilder();
if (insertPercentStart == true)
searchParamBuilder.Append("%");
searchParamBuilder.Append(SqlProviderManifest.EscapeLikeText(constSearchParamExpression.Value as string, false, out escapingOccurred));
if (insertPercentEnd == true)
searchParamBuilder.Append("%");
DbConstantExpression escapedSearchParamExpression = new DbConstantExpression(constSearchParamExpression.ResultType, searchParamBuilder.ToString());
result.Append(escapedSearchParamExpression.Accept(sqlgen));
// If escaping did occur (special characters were found), then append the escape character used.
if (escapingOccurred)
result.Append(" ESCAPE '" + SqlProviderManifest.LikeEscapeChar + "'");
}
/// <summary>
/// Handler for Contains. Wraps the normal translation with a case statement
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionContains(SqlGenerator sqlgen, DbFunctionExpression e)
{
return WrapPredicate( HandleCanonicalFunctionContains, sqlgen, e);
}
/// <summary>
/// CONTAINS(arg0, arg1) => arg0 LIKE '%arg1%'
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="args"></param>
/// <param name="result"></param>
/// <returns></returns>
private static SqlBuilder HandleCanonicalFunctionContains(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
{
Debug.Assert(args.Count == 2, "Contains should have two arguments");
// Check if args[1] is a DbConstantExpression
DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
if ((constSearchParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
{
TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, true, true);
}
else
{
// We use CHARINDEX when the search param is a DbNullExpression because all of SQL Server 2008, 2005 and 2000
// consistently return NULL as the result.
// However, if instead we use the optimized LIKE translation when the search param is a DbNullExpression,
// only SQL Server 2005 yields a True instead of a DbNull as compared to SQL Server 2008 and 2000. This is
// tracked in SQLBUDT #32315 in LIKE in SQL Server 2005.
result.Append("CHARINDEX( ");
result.Append(args[1].Accept(sqlgen));
result.Append(", ");
result.Append(args[0].Accept(sqlgen));
result.Append(") > 0");
}
return result;
}
/// <summary>
/// Handler for StartsWith. Wraps the normal translation with a case statement
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionStartsWith(SqlGenerator sqlgen, DbFunctionExpression e)
{
return WrapPredicate(HandleCanonicalFunctionStartsWith, sqlgen, e);
}
/// <summary>
/// STARTSWITH(arg0, arg1) => arg0 LIKE 'arg1%'
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="args"></param>
/// <param name="result"></param>
/// <returns></returns>
private static SqlBuilder HandleCanonicalFunctionStartsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
{
Debug.Assert(args.Count == 2, "StartsWith should have two arguments");
// Check if args[1] is a DbConstantExpression
DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
if ((constSearchParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
{
TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, false, true);
}
else
{
// We use CHARINDEX when the search param is a DbNullExpression because all of SQL Server 2008, 2005 and 2000
// consistently return NULL as the result.
// However, if instead we use the optimized LIKE translation when the search param is a DbNullExpression,
// only SQL Server 2005 yields a True instead of a DbNull as compared to SQL Server 2008 and 2000. This is
// bug 32315 in LIKE in SQL Server 2005.
result.Append("CHARINDEX( ");
result.Append(args[1].Accept(sqlgen));
result.Append(", ");
result.Append(args[0].Accept(sqlgen));
result.Append(") = 1");
}
return result;
}
/// <summary>
/// Handler for EndsWith. Wraps the normal translation with a case statement
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment HandleCanonicalFunctionEndsWith(SqlGenerator sqlgen, DbFunctionExpression e)
{
return WrapPredicate(HandleCanonicalFunctionEndsWith, sqlgen, e);
}
/// <summary>
/// ENDSWITH(arg0, arg1) => arg0 LIKE '%arg1'
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="args"></param>
/// <param name="result"></param>
/// <returns></returns>
private static SqlBuilder HandleCanonicalFunctionEndsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result)
{
Debug.Assert(args.Count == 2, "EndsWith should have two arguments");
// Check if args[1] is a DbConstantExpression and if args [0] is a DbPropertyExpression
DbConstantExpression constSearchParamExpression = args[1] as DbConstantExpression;
DbPropertyExpression targetParamExpression = args[0] as DbPropertyExpression;
if ((constSearchParamExpression != null) && (targetParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false))
{
// The LIKE optimization for EndsWith can only be used when the target is a column in table and
// the search string is a constant. This is because SQL Server ignores a trailing space in a query like:
// EndsWith('abcd ', 'cd'), which translates to:
// SELECT
// CASE WHEN ('abcd ' LIKE '%cd') THEN cast(1 as bit) WHEN ( NOT ('abcd ' LIKE '%cd')) THEN cast(0 as bit) END AS [C1]
// FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
// and "incorrectly" returns 1 (true), but the CLR would expect a 0 (false) back.
TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, true, false);
}
else
{
result.Append("CHARINDEX( REVERSE(");
result.Append(args[1].Accept(sqlgen));
result.Append("), REVERSE(");
result.Append(args[0].Accept(sqlgen));
result.Append(")) = 1");
}
return result;
}
/// <summary>
/// Turns a predicate into a statement returning a bit
/// PREDICATE => CASE WHEN (PREDICATE) THEN CAST(1 AS BIT) WHEN (NOT (PREDICATE)) CAST (O AS BIT) END
/// The predicate is produced by the given predicateTranslator.
/// </summary>
/// <param name="predicateTranslator"></param>
/// <param name="e"></param>
/// <returns></returns>
private static ISqlFragment WrapPredicate(Func<SqlGenerator, IList<DbExpression>, SqlBuilder, SqlBuilder> predicateTranslator, SqlGenerator sqlgen, DbFunctionExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append("CASE WHEN (");
predicateTranslator(sqlgen, e.Arguments, result);
result.Append(") THEN cast(1 as bit) WHEN ( NOT (");
predicateTranslator(sqlgen, e.Arguments, result);
result.Append(")) THEN cast(0 as bit) END");
return result;
}
/// <summary>
/// Writes the function name to the given SqlBuilder.
/// </summary>
/// <param name="function"></param>
/// <param name="result"></param>
internal static void WriteFunctionName(SqlBuilder result, EdmFunction function)
{
string storeFunctionName;
if (null != function.StoreFunctionNameAttribute)
{
storeFunctionName = function.StoreFunctionNameAttribute;
}
else
{
storeFunctionName = function.Name;
}
// If the function is a builtin (i.e. the BuiltIn attribute has been
// specified, both store and canonical functions have this attribute),
// then the function name should not be quoted;
// additionally, no namespace should be used.
if (TypeHelpers.IsCanonicalFunction(function))
{
result.Append(storeFunctionName.ToUpperInvariant());
}
else if (IsStoreFunction(function))
{
result.Append(storeFunctionName);
}
else
{
// Should we actually support this?
if (String.IsNullOrEmpty(function.Schema))
{
result.Append(SqlGenerator.QuoteIdentifier(function.NamespaceName));
}
else
{
result.Append(SqlGenerator.QuoteIdentifier(function.Schema));
}
result.Append(".");
result.Append(SqlGenerator.QuoteIdentifier(storeFunctionName));
}
}
/// <summary>
/// Is this a Store function (ie) does it have the builtinAttribute specified and it is not a canonical function?
/// </summary>
/// <param name="function"></param>
/// <returns></returns>
internal static bool IsStoreFunction(EdmFunction function)
{
return function.BuiltInAttribute && !TypeHelpers.IsCanonicalFunction(function);
}
/// <summary>
/// determines if the function requires the return type be enforeced by use of a cast expression
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static bool CastReturnTypeToInt64(DbFunctionExpression e)
{
return CastReturnTypeToGivenType(e, _functionRequiresReturnTypeCastToInt64, PrimitiveTypeKind.Int64);
}
/// <summary>
/// determines if the function requires the return type be enforeced by use of a cast expression
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static bool CastReturnTypeToInt32(SqlGenerator sqlgen, DbFunctionExpression e)
{
if (!_functionRequiresReturnTypeCastToInt32.Contains(e.Function.FullName))
{
return false;
}
for (int i = 0; i < e.Arguments.Count; i++)
{
TypeUsage storeType = sqlgen.StoreItemCollection.StoreProviderManifest.GetStoreType(e.Arguments[i].ResultType);
if (_maxTypeNames.Contains(storeType.EdmType.Name))
{
return true;
}
}
return false;
}
/// <summary>
/// determines if the function requires the return type be enforeced by use of a cast expression
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static bool CastReturnTypeToInt16(DbFunctionExpression e)
{
return CastReturnTypeToGivenType(e, _functionRequiresReturnTypeCastToInt16, PrimitiveTypeKind.Int16);
}
/// <summary>
/// determines if the function requires the return type be enforeced by use of a cast expression
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
private static bool CastReturnTypeToSingle(DbFunctionExpression e)
{
//Do not add the cast for the Round() overload having 2 arguments.
//Round(Single,Int32) maps to Round(Double,Int32)due to implicit casting.
//We don't need to cast in that case, since we expect a Double as return type there anyways.
return CastReturnTypeToGivenType(e, _functionRequiresReturnTypeCastToSingle, PrimitiveTypeKind.Single);
}
/// <summary>
/// Determines if the function requires the return type be enforced by use of a cast expression
/// </summary>
/// <param name="e"></param>
/// <param name="functionsRequiringReturnTypeCast"></param>
/// <param name="type"></param>
/// <returns></returns>
private static bool CastReturnTypeToGivenType(DbFunctionExpression e, Set<string> functionsRequiringReturnTypeCast, PrimitiveTypeKind type)
{
if (!functionsRequiringReturnTypeCast.Contains(e.Function.FullName))
{
return false;
}
for (int i = 0; i < e.Arguments.Count; i++)
{
if (TypeSemantics.IsPrimitiveType(e.Arguments[i].ResultType, type))
{
return true;
}
}
return false;
}
}
}
|