Code:
/ Net / Net / 3.5.50727.3053 / DEVDIV / depot / DevDiv / releases / Orcas / SP / ndp / fx / src / DataEntity / System / Data / SqlClient / SqlGen / SqlGenerator.cs / 4 / SqlGenerator.cs
//----------------------------------------------------------------------
//
// Copyright (c) Microsoft Corporation. All rights reserved.
//
//
// @owner [....], [....]
//---------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text;
using System.Data.Common;
using System.Data.Common.CommandTrees;
using System.Data.Common.CommandTrees.Internal;
using System.Data.Common.Utils;
using System.Data.Metadata.Edm;
using System.Data.SqlClient;
namespace System.Data.SqlClient.SqlGen
{
///
/// Translates the command object into a SQL string that can be executed on
/// SQL Server 2000 and SQL Server 2005.
///
///
/// The translation is implemented as a visitor
/// - Select statement minimization. Multiple nodes in the query tree
/// that can be part of a single SQL select statement are merged. e.g. a
/// Filter node that is the input of a Project node can typically share the
/// same SQL statement.
/// - Alpha-renaming. As a result of the statement minimization above, there
/// could be name collisions when using correlated subqueries
///
///
/// Filter(
/// b = Project( c.x
/// c = Extent(foo)
/// )
/// exists (
/// Filter(
/// c = Extent(foo)
/// b.x = c.x
/// )
/// )
/// )
///
/// The first Filter, Project and Extent will share the same SQL select statement.
/// The alias for the Project i.e. b, will be replaced with c.
/// If the alias c for the Filter within the exists clause is not renamed,
/// we will get c.x = c.x , which is incorrect.
/// Instead, the alias c within the second filter should be renamed to c1, to give
/// c.x = c1.x i.e. b is renamed to c, and c is renamed to c1.
///
///
/// - Join flattening. In the query tree, a list of join nodes is typically
/// represented as a tree of Join nodes, each with 2 children. e.g.
///
///
/// a = Join(InnerJoin
/// b = Join(CrossJoin
/// c = Extent(foo)
/// d = Extent(foo)
/// )
/// e = Extent(foo)
/// on b.c.x = e.x
/// )
///
/// If translated directly, this will be translated to
///
/// FROM ( SELECT c.*, d.*
/// FROM foo as c
/// CROSS JOIN foo as d) as b
/// INNER JOIN foo as e on b.x' = e.x
///
/// It would be better to translate this as
///
/// FROM foo as c
/// CROSS JOIN foo as d
/// INNER JOIN foo as e on c.x = e.x
///
/// This allows the optimizer to choose an appropriate join ordering for evaluation.
///
///
/// - Select * and column renaming. In the example above, we noticed that
/// in some cases we add
SELECT * FROM ... to complete the SQL
/// statement. i.e. there is no explicit PROJECT list.
/// In this case, we enumerate all the columns available in the FROM clause
/// This is particularly problematic in the case of Join trees, since the columns
/// from the extents joined might have the same name - this is illegal. To solve
/// this problem, we will have to rename columns if they are part of a SELECT *
/// for a JOIN node - we do not need renaming in any other situation.
///
///
///
///
/// Renaming issues.
/// When rows or columns are renamed, we produce names that are unique globally
/// with respect to the query. The names are derived from the original names,
/// with an integer as a suffix. e.g. CustomerId will be renamed to CustomerId1,
/// CustomerId2 etc.
///
/// Since the names generated are globally unique, they will not conflict when the
/// columns of a JOIN SELECT statement are joined with another JOIN.
///
///
///
///
/// Record flattening.
/// SQL server does not have the concept of records. However, a join statement
/// produces records. We have to flatten the record accesses into a simple
/// alias.column form.
///
///
/// Building the SQL.
/// There are 2 phases
///
/// - Traverse the tree, producing a sql builder
/// - Write the SqlBuilder into a string, renaming the aliases and columns
/// as needed.
///
///
/// In the first phase, we traverse the tree. We cannot generate the SQL string
/// right away, since
///
/// - The WHERE clause has to be visited before the from clause.
/// - extent aliases and column aliases need to be renamed. To minimize
/// renaming collisions, all the names used must be known, before any renaming
/// choice is made.
///
/// To defer the renaming choices, we use symbols
/// - A stack for the current SQL select statement. This is needed by
///
/// - A stack for the join context. When visiting an extent,
/// we need to know whether we are inside a join or not. If we are inside
/// a join, we do not create a new SELECT statement.
///
///
///
///
/// Global state.
/// To enable renaming, we maintain
///
/// - The set of all extent aliases used.
/// - The set of all parameter names.
/// - The set of all column names that may need to be renamed.
///
///
/// Finally, we have a symbol table to lookup variable references. All references
/// to the same extent have the same symbol.
///
///
///
/// Sql select statement sharing.
///
/// Each of the relational operator nodes
///
/// - Project
/// - Filter
/// - GroupBy
/// - Sort/OrderBy
///
/// can add its non-input (e.g. project, predicate, sort order etc.) to
/// the SQL statement for the input, or create a new SQL statement.
/// If it chooses to reuse the input's SQL statement, we play the following
/// symbol table trick to accomplish renaming. The symbol table entry for
/// the alias of the current node points to the symbol for the input in
/// the input's SQL statement.
///
///
/// Project(b.x
/// b = Filter(
/// c = Extent(foo)
/// c.x = 5)
/// )
///
/// The Extent node creates a new SqlSelectStatement. This is added to the
/// symbol table by the Filter as {c, Symbol(c)}. Thus, c.x is resolved to
/// Symbol(c).x .
/// Looking at the project node, we add {b, Symbol(c)} to the symbol table if the
/// SQL statement is reused, and {b, Symbol(b)}, if there is no reuse.
///
/// Thus, b.x is resolved to Symbol(c).x if there is reuse, and to
/// Symbol(b).x if there is no reuse.
///
///
///
internal sealed class SqlGenerator : DbExpressionVisitor
{
#region Visitor parameter stacks
///
/// Every relational node has to pass its SELECT statement to its children
/// This allows them (DbVariableReferenceExpression eventually) to update the list of
/// outer extents (free variables) used by this select statement.
///
private Stack selectStatementStack;
///
/// The top of the stack
///
private SqlSelectStatement CurrentSelectStatement
{
// There is always something on the stack, so we can always Peek.
get { return selectStatementStack.Peek(); }
}
///
/// Nested joins and extents need to know whether they should create
/// a new Select statement, or reuse the parent's. This flag
/// indicates whether the parent is a join or not.
///
private Stack isParentAJoinStack;
///
/// The top of the stack
///
private bool IsParentAJoin
{
// There might be no entry on the stack if a Join node has never
// been seen, so we return false in that case.
get { return isParentAJoinStack.Count == 0 ? false : isParentAJoinStack.Peek(); }
}
#endregion
#region Global lists and state
Dictionary allExtentNames;
internal Dictionary AllExtentNames
{
get { return allExtentNames; }
}
// For each column name, we store the last integer suffix that
// was added to produce a unique column name. This speeds up
// the creation of the next unique name for this column name.
Dictionary allColumnNames;
internal Dictionary AllColumnNames
{
get { return allColumnNames; }
}
SymbolTable symbolTable = new SymbolTable();
///
/// VariableReferenceExpressions are allowed only as children of DbPropertyExpression
/// or MethodExpression. The cheapest way to ensure this is to set the following
/// property in DbVariableReferenceExpression and reset it in the allowed parent expressions.
///
private bool isVarRefSingle;
#endregion
#region Statics
static private readonly Dictionary _storeFunctionHandlers = InitializeStoreFunctionHandlers();
static private readonly Dictionary _canonicalFunctionHandlers = InitializeCanonicalFunctionHandlers();
static private readonly Dictionary _functionNameToOperatorDictionary = InitializeFunctionNameToOperatorDictionary();
static private readonly Set _datepartKeywords = new Set(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 char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
static private readonly Set _functionRequiresReturnTypeCast = new Set(new string[] { "SqlServer.LEN" ,
"SqlServer.PATINDEX" ,
"SqlServer.CHARINDEX" ,
"SqlServer.DATALENGTH" ,
"Edm.IndexOf" ,
"Edm.Length" },
StringComparer.Ordinal).MakeReadOnly();
static private readonly Set _maxTypeNames = new Set(new string[] { "varchar(max)" ,
"nvarchar(max)" ,
"text" ,
"ntext" ,
"varbinary(max)" ,
"image" ,
"xml" },
StringComparer.Ordinal).MakeReadOnly();
const byte defaultDecimalPrecision = 18;
private delegate ISqlFragment FunctionHandler(SqlGenerator sqlgen, DbFunctionExpression functionExpr);
///
/// All special store functions and their handlers
///
/// InitializeStoreFunctionHandlers()
{
Dictionary functionHandlers = new Dictionary(5, StringComparer.Ordinal);
functionHandlers.Add("CONCAT", HandleConcatFunction);
functionHandlers.Add("DATEADD", HandleDatepartDateFunction);
functionHandlers.Add("DATEDIFF", HandleDatepartDateFunction);
functionHandlers.Add("DATENAME", HandleDatepartDateFunction);
functionHandlers.Add("DATEPART", HandleDatepartDateFunction);
return functionHandlers;
}
///
/// All special non-aggregate canonical functions and their handlers
///
/// InitializeCanonicalFunctionHandlers()
{
Dictionary functionHandlers = new Dictionary(16, StringComparer.Ordinal);
functionHandlers.Add("IndexOf", HandleCanonicalFunctionIndexOf);
functionHandlers.Add("Length", HandleCanonicalFunctionLength);
functionHandlers.Add("NewGuid", HandleCanonicalFunctionNewGuid);
functionHandlers.Add("Round", HandleCanonicalFunctionRound);
functionHandlers.Add("ToLower", HandleCanonicalFunctionToLower);
functionHandlers.Add("ToUpper", HandleCanonicalFunctionToUpper);
functionHandlers.Add("Trim", HandleCanonicalFunctionTrim);
//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("CurrentDateTime", HandleCanonicalFunctionCurrentDateTime);
functionHandlers.Add("CurrentUtcDateTime", HandleCanonicalFunctionCurrentUtcDateTime);
functionHandlers.Add("CurrentDateTimeOffset", HandleCanonicalFunctionCurrentDateTimeOffset);
functionHandlers.Add("GetTotalOffsetMinutes", HandleCanonicalFunctionGetTotalOffsetMinutes);
//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;
}
///
/// Initalizes the mapping from functions to TSql operators
/// for all functions that translate to TSql operators
///
/// InitializeFunctionNameToOperatorDictionary()
{
Dictionary functionNameToOperatorDictionary = new Dictionary(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;
}
#endregion
#region SqlVersion, Metadata, ...
///
/// The current SQL Server version
///
private SqlVersion sqlVersion;
internal SqlVersion SqlVersion
{
get { return sqlVersion; }
}
internal bool IsPreKatmai
{
get { return SqlVersionUtils.IsPreKatmai(this.SqlVersion); }
}
private MetadataWorkspace metadataWorkspace;
internal MetadataWorkspace Workspace
{
get { return metadataWorkspace; }
}
private TypeUsage integerType = null;
internal TypeUsage IntegerType
{
get
{
if (integerType == null)
{
integerType = GetPrimitiveType(PrimitiveTypeKind.Int64);
}
return integerType;
}
}
private StoreItemCollection _storeItemCollection;
#endregion
#region Constructor
///
/// Basic constructor.
///
/// server version
private SqlGenerator(SqlVersion sqlVersion)
{
this.sqlVersion = sqlVersion;
}
#endregion
#region Entry points
///
/// General purpose static function that can be called from System.Data assembly
///
/// Server version
/// command tree
/// Parameters to add to the command tree corresponding
/// to constants in the command tree. Used only in ModificationCommandTrees.
/// CommandType for generated command.
/// The string representing the SQL to be executed.
internal static string GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, out List parameters, out CommandType commandType)
{
SqlGenerator sqlGen;
commandType = CommandType.Text;
parameters = null;
switch (tree.CommandTreeKind)
{
case DbCommandTreeKind.Query:
sqlGen = new SqlGenerator(sqlVersion);
return sqlGen.GenerateSql((DbQueryCommandTree)tree);
case DbCommandTreeKind.Insert:
return DmlSqlGenerator.GenerateInsertSql((DbInsertCommandTree)tree, sqlVersion, out parameters);
case DbCommandTreeKind.Delete:
return DmlSqlGenerator.GenerateDeleteSql((DbDeleteCommandTree)tree, sqlVersion, out parameters);
case DbCommandTreeKind.Update:
return DmlSqlGenerator.GenerateUpdateSql((DbUpdateCommandTree)tree, sqlVersion, out parameters);
case DbCommandTreeKind.Function:
sqlGen = new SqlGenerator(sqlVersion);
return GenerateFunctionSql((DbFunctionCommandTree)tree, out commandType);
default:
//We have covered all command tree kinds
Debug.Assert(false, "Unknown command tree kind");
parameters = null;
return null;
}
}
private static string GenerateFunctionSql(DbFunctionCommandTree tree, out CommandType commandType)
{
tree.Validate();
EdmFunction function = tree.EdmFunction;
if (String.IsNullOrEmpty(function.CommandTextAttribute))
{
// build a quoted description of the function
commandType = CommandType.StoredProcedure;
// if the schema name is not explicitly given, it is assumed to be the metadata namespace
string schemaName = String.IsNullOrEmpty(function.Schema) ?
function.NamespaceName : function.Schema;
// if the function store name is not explicitly given, it is assumed to be the metadata name
string functionName = String.IsNullOrEmpty(function.StoreFunctionNameAttribute) ?
function.Name : function.StoreFunctionNameAttribute;
// quote elements of function text
string quotedSchemaName = QuoteIdentifier(schemaName);
string quotedFunctionName = QuoteIdentifier(functionName);
// separator
const string schemaSeparator = ".";
// concatenate elements of function text
string quotedFunctionText = quotedSchemaName + schemaSeparator + quotedFunctionName;
return quotedFunctionText;
}
else
{
// if the user has specified the command text, pass it through verbatim and choose CommandType.Text
commandType = CommandType.Text;
return function.CommandTextAttribute;
}
}
#endregion
#region Driver Methods
///
/// Translate a command tree to a SQL string.
///
/// The input tree could be translated to either a SQL SELECT statement
/// or a SELECT expression. This choice is made based on the return type
/// of the expression
/// CollectionType => select statement
/// non collection type => select expression
///
///
/// The string representing the SQL to be executed.
private string GenerateSql(DbQueryCommandTree tree)
{
tree.Validate();
DbQueryCommandTree targetTree = tree;
//If we are on Sql 8.0 rewrite the tree if needed
if (this.SqlVersion == SqlVersion.Sql8)
{
if (Sql8ConformanceChecker.NeedsRewrite(tree.Query))
{
targetTree = Sql8ExpressionRewriter.Rewrite(tree);
}
}
this.metadataWorkspace = targetTree.MetadataWorkspace;
// needed in Private Type Helpers section bellow
_storeItemCollection = (StoreItemCollection)this.Workspace.GetItemCollection(DataSpace.SSpace);
selectStatementStack = new Stack();
isParentAJoinStack = new Stack();
allExtentNames = new Dictionary(StringComparer.OrdinalIgnoreCase);
allColumnNames = new Dictionary(StringComparer.OrdinalIgnoreCase);
// Literals will not be converted to parameters.
ISqlFragment result;
if (TypeSemantics.IsCollectionType(targetTree.Query.ResultType))
{
SqlSelectStatement sqlStatement = VisitExpressionEnsureSqlStatement(targetTree.Query);
Debug.Assert(sqlStatement != null, "The outer most sql statment is null");
sqlStatement.IsTopMost = true;
result = sqlStatement;
}
else
{
SqlBuilder sqlBuilder = new SqlBuilder();
sqlBuilder.Append("SELECT ");
sqlBuilder.Append(targetTree.Query.Accept(this));
result = sqlBuilder;
}
if (isVarRefSingle)
{
throw EntityUtil.NotSupported();
// A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
}
// Check that the parameter stacks are not leaking.
Debug.Assert(selectStatementStack.Count == 0);
Debug.Assert(isParentAJoinStack.Count == 0);
return WriteSql(result);
}
///
/// Convert the SQL fragments to a string.
/// We have to setup the Stream for writing.
///
///
/// A string representing the SQL to be executed.
private string WriteSql(ISqlFragment sqlStatement)
{
StringBuilder builder = new StringBuilder(1024);
using (SqlWriter writer = new SqlWriter(builder))
{
sqlStatement.WriteSql(writer, this);
}
return builder.ToString();
}
#endregion
#region IExpressionVisitor Members
///
/// Translate(left) AND Translate(right)
///
///
/// A
public override ISqlFragment Visit(DbAndExpression e)
{
return VisitBinaryExpression(" AND ", DbExpressionKind.And, e.Left, e.Right);
}
///
/// An apply is just like a join, so it shares the common join processing
/// in
///
/// A
public override ISqlFragment Visit(DbApplyExpression e)
{
Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbApplyExpression when translating for SQL Server 2000.");
List inputs = new List();
inputs.Add(e.Input);
inputs.Add(e.Apply);
string joinString;
switch (e.ExpressionKind)
{
case DbExpressionKind.CrossApply:
joinString = "CROSS APPLY";
break;
case DbExpressionKind.OuterApply:
joinString = "OUTER APPLY";
break;
default:
Debug.Assert(false);
throw EntityUtil.InvalidOperation(String.Empty);
}
// The join condition does not exist in this case, so we use null.
// WE do not have a on clause, so we use JoinType.CrossJoin.
return VisitJoinExpression(inputs, DbExpressionKind.CrossJoin, joinString, null);
}
///
/// For binary expressions, we delegate to
///
/// A
public override ISqlFragment Visit(DbArithmeticExpression e)
{
SqlBuilder result;
switch (e.ExpressionKind)
{
case DbExpressionKind.Divide:
result = VisitBinaryExpression(" / ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Minus:
result = VisitBinaryExpression(" - ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Modulo:
result = VisitBinaryExpression(" % ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Multiply:
result = VisitBinaryExpression(" * ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Plus:
result = VisitBinaryExpression(" + ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.UnaryMinus:
result = new SqlBuilder();
result.Append(" -(");
result.Append(e.Arguments[0].Accept(this));
result.Append(")");
break;
default:
Debug.Assert(false);
throw EntityUtil.InvalidOperation(String.Empty);
}
return result;
}
///
/// If the ELSE clause is null, we do not write it out.
///
///
/// A
public override ISqlFragment Visit(DbCaseExpression e)
{
SqlBuilder result = new SqlBuilder();
Debug.Assert(e.When.Count == e.Then.Count);
result.Append("CASE");
for (int i = 0; i < e.When.Count; ++i)
{
result.Append(" WHEN (");
result.Append(e.When[i].Accept(this));
result.Append(") THEN ");
result.Append(e.Then[i].Accept(this));
}
//
if (e.Else != null && !(e.Else is DbNullExpression))
{
result.Append(" ELSE ");
result.Append(e.Else.Accept(this));
}
result.Append(" END");
return result;
}
///
///
///
///
///
/// The parser generates Not(Equals(...)) for <>.
///
///
/// A
public override ISqlFragment Visit(DbComparisonExpression e)
{
SqlBuilder result;
switch (e.ExpressionKind)
{
case DbExpressionKind.Equals:
result = VisitComparisonExpression(" = ", e.Left, e.Right);
break;
case DbExpressionKind.LessThan:
result = VisitComparisonExpression(" < ", e.Left, e.Right);
break;
case DbExpressionKind.LessThanOrEquals:
result = VisitComparisonExpression(" <= ", e.Left, e.Right);
break;
case DbExpressionKind.GreaterThan:
result = VisitComparisonExpression(" > ", e.Left, e.Right);
break;
case DbExpressionKind.GreaterThanOrEquals:
result = VisitComparisonExpression(" >= ", e.Left, e.Right);
break;
// The parser does not generate the expression kind below.
case DbExpressionKind.NotEquals:
result = VisitComparisonExpression(" <> ", e.Left, e.Right);
break;
default:
Debug.Assert(false); // The constructor should have prevented this
throw EntityUtil.InvalidOperation(String.Empty);
}
return result;
}
///
/// Generate tsql for a constant. Avoid the explicit cast (if possible) when
/// the isCastOptional parameter is set
///
/// the constant expression
/// can we avoid the CAST
/// the tsql fragment
private ISqlFragment VisitConstant(DbConstantExpression e, bool isCastOptional)
{
// Constants will be send to the store as part of the generated TSQL, not as parameters
SqlBuilder result = new SqlBuilder();
PrimitiveTypeKind typeKind;
// Model Types can be (at the time of this implementation):
// Binary, Boolean, Byte, Date, DateTime, DateTimeOffset, Decimal, Double, Guid, Int16, Int32, Int64, Single, String, Time
if (TypeHelpers.TryGetPrimitiveTypeKind(e.ResultType, out typeKind))
{
switch (typeKind)
{
case PrimitiveTypeKind.Int32:
// default sql server type for integral values.
result.Append(e.Value.ToString());
break;
case PrimitiveTypeKind.Binary:
result.Append(" 0x");
result.Append(ByteArrayToBinaryString((Byte[])e.Value));
result.Append(" ");
break;
case PrimitiveTypeKind.Boolean:
// Bugs 450277, 430294: Need to preserve the boolean type-ness of
// this value for round-trippability
WrapWithCastIfNeeded(!isCastOptional, (bool)e.Value ? "1" : "0", "bit", result);
break;
case PrimitiveTypeKind.Byte:
WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "tinyint", result);
break;
case PrimitiveTypeKind.DateTime:
result.Append("convert(");
result.Append(this.IsPreKatmai ? "datetime" : "datetime2");
result.Append(", ");
result.Append(EscapeSingleQuote(((System.DateTime)e.Value).ToString(this.IsPreKatmai ? "yyyy-MM-dd HH:mm:ss.fff" : "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture), false /* IsUnicode */));
result.Append(", 121)");
break;
case PrimitiveTypeKind.Time:
AssertKatmaiOrNewer(typeKind);
result.Append("convert(");
result.Append(e.ResultType.EdmType.Name);
result.Append(", ");
result.Append(EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */));
result.Append(", 121)");
break;
case PrimitiveTypeKind.DateTimeOffset:
AssertKatmaiOrNewer(typeKind);
result.Append("convert(");
result.Append(e.ResultType.EdmType.Name);
result.Append(", ");
result.Append(EscapeSingleQuote(((System.DateTimeOffset)e.Value).ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture), false /* IsUnicode */));
result.Append(", 121)");
break;
case PrimitiveTypeKind.Decimal:
string strDecimal = ((Decimal)e.Value).ToString(CultureInfo.InvariantCulture);
// if the decimal value has no decimal part, cast as decimal to preserve type
// if the number has precision > int64 max precision, it will be handled as decimal by sql server
// and does not need cast. if precision is lest then 20, then cast using Max(literal precision, sql default precision)
bool needsCast = -1 == strDecimal.IndexOf('.') && (strDecimal.TrimStart(new char[] { '-' }).Length < 20);
byte precision = (byte)Math.Max((Byte)strDecimal.Length, defaultDecimalPrecision);
Debug.Assert(precision > 0, "Precision must be greater than zero");
string decimalType = "decimal(" + precision.ToString(CultureInfo.InvariantCulture) + ")";
WrapWithCastIfNeeded(needsCast, strDecimal, decimalType, result);
break;
case PrimitiveTypeKind.Double:
WrapWithCastIfNeeded(true, ((Double)e.Value).ToString("R", CultureInfo.InvariantCulture), "float(53)", result);
break;
case PrimitiveTypeKind.Guid:
WrapWithCastIfNeeded(true, EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */), "uniqueidentifier", result);
break;
case PrimitiveTypeKind.Int16:
WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "smallint", result);
break;
case PrimitiveTypeKind.Int64:
WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "bigint", result);
break;
case PrimitiveTypeKind.Single:
WrapWithCastIfNeeded(true, ((Single)e.Value).ToString("R", CultureInfo.InvariantCulture), "real", result);
break;
case PrimitiveTypeKind.String:
bool isUnicode;
if (!TypeHelpers.TryGetIsUnicode(e.ResultType, out isUnicode))
{
isUnicode = true;
}
result.Append(EscapeSingleQuote(e.Value as string, isUnicode));
break;
default:
// all known scalar types should been handled already.
throw EntityUtil.NotSupported();
}
}
else
{
throw EntityUtil.NotSupported();
//if/when Enum types are supported, then handle appropriately, for now is not a valid type for constants.
//result.Append(e.Value.ToString());
}
return result;
}
///
/// Helper function for
///
///
///
///
private static void WrapWithCastIfNeeded(bool cast, string value, string typeName, SqlBuilder result)
{
if (!cast)
{
result.Append(value);
}
else
{
result.Append("cast(");
result.Append(value);
result.Append(" as ");
result.Append(typeName);
result.Append(")");
}
}
///
/// We do not pass constants as parameters.
///
///
/// A
public override ISqlFragment Visit(DbConstantExpression e)
{
return VisitConstant(e, false /* isCastOptional */);
}
///
///
///
///
///
/// The DISTINCT has to be added to the beginning of SqlSelectStatement.Select,
/// but it might be too late for that. So, we use a flag on SqlSelectStatement
/// instead, and add the "DISTINCT" in the second phase.
///
///
/// A
public override ISqlFragment Visit(DbDistinctExpression e)
{
SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument);
if (!IsCompatible(result, e.ExpressionKind))
{
Symbol fromSymbol;
TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
result = CreateNewSelectStatement(result, "distinct", inputType, out fromSymbol);
AddFromSymbol(result, "distinct", fromSymbol, false);
}
result.IsDistinct = true;
return result;
}
///
/// An element expression returns a scalar - so it is translated to
/// ( Select ... )
///
///
///
///
///
///
/// Only concrete expression types will be visited.
///
///
///
///
///
///
/// If we are in a Join context, returns a
public override ISqlFragment Visit(DbScanExpression e)
{
EntitySetBase target = e.Target;
// ISSUE: Should we just return a string all the time, and let
// VisitInputExpression create the SqlSelectStatement?
if (IsParentAJoin)
{
SqlBuilder result = new SqlBuilder();
result.Append(GetTargetTSql(target));
return result;
}
else
{
SqlSelectStatement result = new SqlSelectStatement();
result.From.Append(GetTargetTSql(target));
return result;
}
}
///
/// Gets escaped TSql identifier describing this entity set.
///
///
/// The bodies of
/// - Visit the input expression
/// - Determine if the input's SQL statement can be reused, or a new
/// one must be created.
/// - Create a new symbol table scope
/// - Push the Sql statement onto a stack, so that children can
/// update the free variable list.
/// - Visit the non-input expression.
/// - Cleanup
///
///
///
/// A
public override ISqlFragment Visit(DbFilterExpression e)
{
return VisitFilterExpression(e.Input, e.Predicate, false);
}
///
/// Lambda functions are not supported.
/// The functions supported are:
///
/// - Canonical Functions - We recognize these by their dataspace, it is DataSpace.CSpace
/// - Store Functions - We recognize these by the BuiltInAttribute and not being Canonical
/// - User-defined Functions - All the rest except for Lambda functions
///
/// We handle Canonical and Store functions the same way: If they are in the list of functions
/// that need special handling, we invoke the appropriate handler, otherwise we translate them to
/// FunctionName(arg1, arg2, ..., argn).
/// We translate user-defined functions to NamespaceName.FunctionName(arg1, arg2, ..., argn).
///
///
/// A
public override ISqlFragment Visit(DbFunctionExpression e)
{
if (e.IsLambda)
{
throw EntityUtil.NotSupported();
}
//
// check if function requires special case processing, if so, delegates to it
//
if (IsSpecialCanonicalFunction(e))
{
return HandleSpecialCanonicalFunction(e);
}
if (IsSpecialStoreFunction(e))
{
return HandleSpecialStoreFunction(e);
}
return HandleFunctionDefault(e);
}
///
///
///
///
///
///
///
///
///
///
/// - If an expression being aggregated contains an outer reference, then that outer
/// reference must be the only column referenced in the expression (SQLBUDT #488741)
/// - Sql Server cannot perform an aggregate function on an expression containing
/// an aggregate or a subquery. (SQLBUDT #504600)
///- Sql Server requries each GROUP BY expression (key) to contain at least one column
/// that is not an outer reference. (SQLBUDT #616523)
/// - Aggregates on the right side of an APPLY cannot reference columns from the left side.
/// (SQLBUDT #617683)
///
///
/// The default translation, without inner query is:
///
/// SELECT
/// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
/// aggf1(aexpr1) AS agg1, .. aggfn(aexprn) AS aggn
/// FROM input AS a
/// GROUP BY kexp1, kexp2, .. kexpn
///
/// When we inject an innner query, the equivalent translation is:
///
/// SELECT
/// key1 AS key1, key2 AS key2, .. keyn AS keys,
/// aggf1(agg1) AS agg1, aggfn(aggn) AS aggn
/// FROM (
/// SELECT
/// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
/// aexpr1 AS agg1, .. aexprn AS aggn
/// FROM input AS a
/// ) as a
/// GROUP BY key1, key2, keyn
///
///
///
/// A
public override ISqlFragment Visit(DbGroupByExpression e)
{
Symbol fromSymbol;
SqlSelectStatement innerQuery = VisitInputExpression(e.Input.Expression,
e.Input.VariableName, e.Input.VariableType, out fromSymbol);
// GroupBy is compatible with Filter and OrderBy
// but not with Project, GroupBy
if (!IsCompatible(innerQuery, e.ExpressionKind))
{
innerQuery = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
}
selectStatementStack.Push(innerQuery);
symbolTable.EnterScope();
AddFromSymbol(innerQuery, e.Input.VariableName, fromSymbol);
// This line is not present for other relational nodes.
symbolTable.Add(e.Input.GroupVariableName, fromSymbol);
// The enumerator is shared by both the keys and the aggregates,
// so, we do not close it in between.
RowType groupByType = TypeHelpers.GetEdmType(TypeHelpers.GetEdmType(e.ResultType).TypeUsage);
//SQL Server does not support arbitrarily complex expressions inside aggregates,
// and requires keys to have reference to the input scope,
// so we check for the specific restrictions and if need we inject an inner query.
bool needsInnerQuery = GroupByAggregatesNeedInnerQuery(e.Aggregates, e.Input.GroupVariableName) || GroupByKeysNeedInnerQuery(e.Keys, e.Input.VariableName);
SqlSelectStatement result;
if (needsInnerQuery)
{
//Create the inner query
result = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, false, out fromSymbol);
AddFromSymbol(result, e.Input.VariableName, fromSymbol, false);
}
else
{
result = innerQuery;
}
using (IEnumerator members = groupByType.Properties.GetEnumerator())
{
members.MoveNext();
Debug.Assert(result.Select.IsEmpty);
string separator = "";
foreach (DbExpression key in e.Keys)
{
EdmProperty member = members.Current;
string alias = QuoteIdentifier(member.Name);
result.GroupBy.Append(separator);
ISqlFragment keySql = key.Accept(this);
if (!needsInnerQuery)
{
//Default translation: Key AS Alias
result.Select.Append(separator);
result.Select.AppendLine();
result.Select.Append(keySql);
result.Select.Append(" AS ");
result.Select.Append(alias);
result.GroupBy.Append(keySql);
}
else
{
// The inner query contains the default translation Key AS Alias
innerQuery.Select.Append(separator);
innerQuery.Select.AppendLine();
innerQuery.Select.Append(keySql);
innerQuery.Select.Append(" AS ");
innerQuery.Select.Append(alias);
//The outer resulting query projects over the key aliased in the inner query:
// fromSymbol.Alias AS Alias
result.Select.Append(separator);
result.Select.AppendLine();
result.Select.Append(fromSymbol);
result.Select.Append(".");
result.Select.Append(alias);
result.Select.Append(" AS ");
result.Select.Append(alias);
result.GroupBy.Append(alias);
}
separator = ", ";
members.MoveNext();
}
foreach (DbAggregate aggregate in e.Aggregates)
{
EdmProperty member = members.Current;
string alias = QuoteIdentifier(member.Name);
Debug.Assert(aggregate.Arguments.Count == 1);
ISqlFragment translatedAggregateArgument = aggregate.Arguments[0].Accept(this);
object aggregateArgument;
if (needsInnerQuery)
{
//In this case the argument to the aggratete is reference to the one projected out by the
// inner query
SqlBuilder wrappingAggregateArgument = new SqlBuilder();
wrappingAggregateArgument.Append(fromSymbol);
wrappingAggregateArgument.Append(".");
wrappingAggregateArgument.Append(alias);
aggregateArgument = wrappingAggregateArgument;
innerQuery.Select.Append(separator);
innerQuery.Select.AppendLine();
innerQuery.Select.Append(translatedAggregateArgument);
innerQuery.Select.Append(" AS ");
innerQuery.Select.Append(alias);
}
else
{
aggregateArgument = translatedAggregateArgument;
}
ISqlFragment aggregateResult = VisitAggregate(aggregate, aggregateArgument);
result.Select.Append(separator);
result.Select.AppendLine();
result.Select.Append(aggregateResult);
result.Select.Append(" AS ");
result.Select.Append(alias);
separator = ", ";
members.MoveNext();
}
}
symbolTable.ExitScope();
selectStatementStack.Pop();
return result;
}
///
///
///
///
/// Not(IsEmpty) has to be handled specially, so we delegate to
///
///
/// A [NOT] EXISTS( ... )
///
public override ISqlFragment Visit(DbIsEmptyExpression e)
{
return VisitIsEmptyExpression(e, false);
}
///
/// Not(IsNull) is handled specially, so we delegate to
///
///
/// A IS [NOT] NULL
///
public override ISqlFragment Visit(DbIsNullExpression e)
{
return VisitIsNullExpression(e, false);
}
///
/// No error is raised if the store cannot support this.
///
///
/// A
public override ISqlFragment Visit(DbIsOfExpression e)
{
throw EntityUtil.NotSupported();
}
///
///
///
/// A
public override ISqlFragment Visit(DbCrossJoinExpression e)
{
return VisitJoinExpression(e.Inputs, e.ExpressionKind, "CROSS JOIN", null);
}
///
///
///
/// A
public override ISqlFragment Visit(DbJoinExpression e)
{
#region Map join type to a string
string joinString;
switch (e.ExpressionKind)
{
case DbExpressionKind.FullOuterJoin:
joinString = "FULL OUTER JOIN";
break;
case DbExpressionKind.InnerJoin:
joinString = "INNER JOIN";
break;
case DbExpressionKind.LeftOuterJoin:
joinString = "LEFT OUTER JOIN";
break;
default:
Debug.Assert(false);
joinString = null;
break;
}
#endregion
List inputs = new List(2);
inputs.Add(e.Left);
inputs.Add(e.Right);
return VisitJoinExpression(inputs, e.ExpressionKind, joinString, e.JoinCondition);
}
///
///
///
///
/// A
public override ISqlFragment Visit(DbLikeExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append(e.Argument.Accept(this));
result.Append(" LIKE ");
result.Append(e.Pattern.Accept(this));
// if the ESCAPE expression is a DbNullExpression, then that's tantamount to
// not having an ESCAPE at all
if (e.Escape.ExpressionKind != DbExpressionKind.Null)
{
result.Append(" ESCAPE ");
result.Append(e.Escape.Accept(this));
}
return result;
}
///
/// Translates to TOP expression. For Sql8, limit can only be a constant expression
///
///
/// A
public override ISqlFragment Visit(DbLimitExpression e)
{
Debug.Assert(e.Limit is DbConstantExpression || e.Limit is DbParameterReferenceExpression, "DbLimitExpression.Limit is of invalid expression type");
Debug.Assert(!((this.SqlVersion == SqlVersion.Sql8) && (e.Limit is DbParameterReferenceExpression)), "DbLimitExpression.Limit is DbParameterReferenceExpression for SQL Server 2000.");
SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument, false);
Symbol fromSymbol;
if (!IsCompatible(result, e.ExpressionKind))
{
TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
result = CreateNewSelectStatement(result, "top", inputType, out fromSymbol);
AddFromSymbol(result, "top", fromSymbol, false);
}
ISqlFragment topCount = HandleCountExpression(e.Limit) ;
result.Top = new TopClause(topCount, e.WithTies);
return result;
}
#if METHOD_EXPRESSION
///
///
///
///
/// A
public override ISqlFragment Visit(MethodExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append(e.Instance.Accept(this));
result.Append(".");
result.Append(QuoteIdentifier(e.Method.Name));
result.Append("(");
// Since the VariableReferenceExpression is a proper child of ours, we can reset
// isVarSingle.
VariableReferenceExpression VariableReferenceExpression = e.Instance as VariableReferenceExpression;
if (VariableReferenceExpression != null)
{
isVarRefSingle = false;
}
string separator = "";
foreach (Expression argument in e.Arguments)
{
result.Append(separator);
result.Append(argument.Accept(this));
separator = ", ";
}
result.Append(")");
return result;
}
#endif
///
/// DbNewInstanceExpression is allowed as a child of DbProjectExpression only.
/// If anyone else is the parent, we throw.
/// We also perform special casing for collections - where we could convert
/// them into Unions
///
///
///
///
/// The Not expression may cause the translation of its child to change.
/// These children are
///
///
///
///
/// A
public override ISqlFragment Visit(DbNotExpression e)
{
// Flatten Not(Not(x)) to x.
DbNotExpression notExpression = e.Argument as DbNotExpression;
if (notExpression != null)
{
return notExpression.Argument.Accept(this);
}
DbIsEmptyExpression isEmptyExpression = e.Argument as DbIsEmptyExpression;
if (isEmptyExpression != null)
{
return VisitIsEmptyExpression(isEmptyExpression, true);
}
DbIsNullExpression isNullExpression = e.Argument as DbIsNullExpression;
if (isNullExpression != null)
{
return VisitIsNullExpression(isNullExpression, true);
}
DbComparisonExpression comparisonExpression = e.Argument as DbComparisonExpression;
if (comparisonExpression != null)
{
if (comparisonExpression.ExpressionKind == DbExpressionKind.Equals)
{
return VisitBinaryExpression(" <> ", DbExpressionKind.NotEquals, comparisonExpression.Left, comparisonExpression.Right);
}
}
SqlBuilder result = new SqlBuilder();
result.Append(" NOT (");
result.Append(e.Argument.Accept(this));
result.Append(")");
return result;
}
///
///
///
///
///
///
///
/// A
public override ISqlFragment Visit(DbOfTypeExpression e)
{
throw EntityUtil.NotSupported();
}
///
///
///
///
/// A
///
///
///
///
/// A
public override ISqlFragment Visit(DbParameterReferenceExpression e)
{
SqlBuilder result = new SqlBuilder();
// Do not quote this name.
// ISSUE: We are not checking that e.Name has no illegal characters. e.g. space
result.Append("@" + e.ParameterName);
return result;
}
///
///
///
/// A
/// newColumns;
result.Select.Append(VisitNewInstanceExpression(newInstanceExpression, aliasesNeedRenaming, out newColumns));
if (aliasesNeedRenaming)
{
result.OutputColumnsRenamed = true;
result.OutputColumns = newColumns;
}
}
else
{
result.Select.Append(e.Projection.Accept(this));
}
symbolTable.ExitScope();
selectStatementStack.Pop();
return result;
}
///
/// This method handles record flattening, which works as follows.
/// consider an expression Prop(y, Prop(x, Prop(d, Prop(c, Prop(b, Var(a)))))
/// where a,b,c are joins, d is an extent and x and y are fields.
/// b has been flattened into a, and has its own SELECT statement.
/// c has been flattened into b.
/// d has been flattened into c.
///
/// We visit the instance, so we reach Var(a) first. This gives us a (join)symbol.
/// Symbol(a).b gives us a join symbol, with a SELECT statement i.e. Symbol(b).
/// From this point on , we need to remember Symbol(b) as the source alias,
/// and then try to find the column. So, we use a SymbolPair.
///
/// We have reached the end when the symbol no longer points to a join symbol.
///
///
/// A
public override ISqlFragment Visit(DbPropertyExpression e)
{
SqlBuilder result;
ISqlFragment instanceSql = e.Instance.Accept(this);
// Since the DbVariableReferenceExpression is a proper child of ours, we can reset
// isVarSingle.
DbVariableReferenceExpression VariableReferenceExpression = e.Instance as DbVariableReferenceExpression;
if (VariableReferenceExpression != null)
{
isVarRefSingle = false;
}
// We need to flatten, and have not yet seen the first nested SELECT statement.
JoinSymbol joinSymbol = instanceSql as JoinSymbol;
if (joinSymbol != null)
{
Debug.Assert(joinSymbol.NameToExtent.ContainsKey(e.Property.Name));
if (joinSymbol.IsNestedJoin)
{
return new SymbolPair(joinSymbol, joinSymbol.NameToExtent[e.Property.Name]);
}
else
{
return joinSymbol.NameToExtent[e.Property.Name];
}
}
// ---------------------------------------
// We have seen the first nested SELECT statement, but not the column.
SymbolPair symbolPair = instanceSql as SymbolPair;
if (symbolPair != null)
{
JoinSymbol columnJoinSymbol = symbolPair.Column as JoinSymbol;
if (columnJoinSymbol != null)
{
symbolPair.Column = columnJoinSymbol.NameToExtent[e.Property.Name];
return symbolPair;
}
else
{
// symbolPair.Column has the base extent.
// we need the symbol for the column, since it might have been renamed
// when handling a JOIN.
if (symbolPair.Column.Columns.ContainsKey(e.Property.Name))
{
result = new SqlBuilder();
result.Append(symbolPair.Source);
result.Append(".");
result.Append(symbolPair.Column.Columns[e.Property.Name]);
return result;
}
}
}
// ---------------------------------------
result = new SqlBuilder();
result.Append(instanceSql);
result.Append(".");
Symbol symbol = instanceSql as Symbol;
if (symbol != null && symbol.OutputColumnsRenamed)
{
result.Append(symbol.Columns[e.Property.Name]);
}
else
{
// At this point the column name cannot be renamed, so we do
// not use a symbol.
result.Append(QuoteIdentifier(e.Property.Name));
}
return result;
}
///
/// Any(input, x) => Exists(Filter(input,x))
/// All(input, x) => Not Exists(Filter(input, not(x))
///
///
///
///
///
///
///
///
///
///
///
/// For Sql9 it translates to:
/// SELECT Y.x1, Y.x2, ..., Y.xn
/// FROM (
/// SELECT X.x1, X.x2, ..., X.xn, row_number() OVER (ORDER BY sk1, sk2, ...) AS [row_number]
/// FROM input as X
/// ) as Y
/// WHERE Y.[row_number] > count
/// ORDER BY sk1, sk2, ...
///
///
/// A
public override ISqlFragment Visit(DbSkipExpression e)
{
Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbSkipExpression when translating for SQL Server 2000.");
Debug.Assert(e.Count is DbConstantExpression || e.Count is DbParameterReferenceExpression, "DbSkipExpression.Count is of invalid expression type");
//Visit the input
Symbol fromSymbol;
SqlSelectStatement input = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
// Skip is not compatible with anything that OrderBy is not compatible with, as well as with distinct
if (!IsCompatible(input, e.ExpressionKind))
{
input = CreateNewSelectStatement(input, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
}
selectStatementStack.Push(input);
symbolTable.EnterScope();
AddFromSymbol(input, e.Input.VariableName, fromSymbol);
//Add the default columns
Debug.Assert(input.Select.IsEmpty);
List inputColumns = AddDefaultColumns(input);
input.Select.Append(", row_number() OVER (ORDER BY ");
AddSortKeys(input.Select, e.SortOrder);
input.Select.Append(") AS ");
Symbol row_numberSymbol = new Symbol("row_number", IntegerType);
input.Select.Append(row_numberSymbol);
//The inner statement is complete, its scopes need not be valid any longer
symbolTable.ExitScope();
selectStatementStack.Pop();
//Create the resulting statement
//See CreateNewSelectStatement, it is very similar
//Future Enhancement ([....]): Refactor to avoid duplication with CreateNewSelectStatement if we
// don't switch to using ExtensionExpression here
SqlSelectStatement result = new SqlSelectStatement();
result.From.Append("( ");
result.From.Append(input);
result.From.AppendLine();
result.From.Append(") ");
//Create a symbol for the input
Symbol resultFromSymbol = null;
if (input.FromExtents.Count == 1)
{
JoinSymbol oldJoinSymbol = input.FromExtents[0] as JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: input.FromExtents will not do, since it might
// just be an alias of joinSymbol, and we want an actual JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(e.Input.VariableName, e.Input.VariableType, oldJoinSymbol.ExtentList);
// This indicates that the oldStatement is a blocking scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = inputColumns;
newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
resultFromSymbol = newJoinSymbol;
}
}
if (resultFromSymbol == null)
{
// This is just a simple extent/SqlSelectStatement,
// and we can get the column list from the type.
resultFromSymbol = new Symbol(e.Input.VariableName, e.Input.VariableType);
}
//Add the ORDER BY part
selectStatementStack.Push(result);
symbolTable.EnterScope();
AddFromSymbol(result, e.Input.VariableName, resultFromSymbol);
//Add the predicate
result.Where.Append(resultFromSymbol);
result.Where.Append(".");
result.Where.Append(row_numberSymbol);
result.Where.Append(" > ");
result.Where.Append(HandleCountExpression(e.Count));
AddSortKeys(result.OrderBy, e.SortOrder);
symbolTable.ExitScope();
selectStatementStack.Pop();
return result;
}
///
///
///
/// A
///
///
///
///
/// A
public override ISqlFragment Visit(DbTreatExpression e)
{
throw EntityUtil.NotSupported();
}
///
/// This code is shared by
///
///
/// This method determines whether an extent from an outer scope(free variable)
/// is used in the CurrentSelectStatement.
///
/// An extent in an outer scope, if its symbol is not in the FromExtents
/// of the CurrentSelectStatement.
///
///
/// A
public override ISqlFragment Visit(DbVariableReferenceExpression e)
{
if (isVarRefSingle)
{
throw EntityUtil.NotSupported();
// A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
// This is also checked in GenerateSql(...) at the end of the visiting.
}
isVarRefSingle = true; // This will be reset by DbPropertyExpression or MethodExpression
Symbol result = symbolTable.Lookup(e.VariableName);
if (!CurrentSelectStatement.FromExtents.Contains(result))
{
CurrentSelectStatement.OuterExtents[result] = true;
}
return result;
}
#endregion
#region Visitor Helper Methods
#region 'Visitor' methods - Shared visitors and methods that do most of the visiting
///
/// Aggregates are not visited by the normal visitor walk.
///
/// The aggreate go be translated
/// The translated aggregate argument
///
/// Dump out an expression - optionally wrap it with parantheses if possible
///
///
///
private void ParanthesizeExpressionIfNeeded(DbExpression e, SqlBuilder result)
{
if (IsComplexExpression(e))
{
result.Append("(");
result.Append(e.Accept(this));
result.Append(")");
}
else
{
result.Append(e.Accept(this));
}
}
///
/// Handler for inline binary expressions.
/// Produces left op right.
/// For associative operations does flattening.
/// Puts parenthesis around the arguments if needed.
///
///
///
///
///
///
/// Private handler for comparison expressions - almost identical to VisitBinaryExpression.
/// We special case constants, so that we don't emit unnecessary casts
///
/// the comparison op
/// the left-side expression
/// the right-side expression
///
/// This is called by the relational nodes. It does the following
///
/// - If the input is not a SqlSelectStatement, it assumes that the input
/// is a collection expression, and creates a new SqlSelectStatement
///
///
///
///
///
///
/// A
private SqlSelectStatement VisitInputExpression(DbExpression inputExpression,
string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol)
{
SqlSelectStatement result;
ISqlFragment sqlFragment = inputExpression.Accept(this);
result = sqlFragment as SqlSelectStatement;
if (result == null)
{
result = new SqlSelectStatement();
WrapNonQueryExtent(result, sqlFragment, inputExpression.ExpressionKind);
}
if (result.FromExtents.Count == 0)
{
// input was an extent
fromSymbol = new Symbol(inputVarName, inputVarType);
}
else if (result.FromExtents.Count == 1)
{
// input was Filter/GroupBy/Project/OrderBy
// we are likely to reuse this statement.
fromSymbol = result.FromExtents[0];
}
else
{
// input was a join.
// we are reusing the select statement produced by a Join node
// we need to remove the original extents, and replace them with a
// new extent with just the Join symbol.
JoinSymbol joinSymbol = new JoinSymbol(inputVarName, inputVarType, result.FromExtents);
joinSymbol.FlattenedExtentList = result.AllJoinExtents;
fromSymbol = joinSymbol;
result.FromExtents.Clear();
result.FromExtents.Add(fromSymbol);
}
return result;
}
///
///
///
/// Was the parent a DbNotExpression?
///
/// Translate a NewInstance(Element(X)) expression into
/// "select top(1) * from X"
///
///
/// (e.ResultType);
Debug.Assert(collectionType != null);
bool isScalarElement = TypeSemantics.IsPrimitiveType(collectionType.TypeUsage);
SqlBuilder resultSql = new SqlBuilder();
string separator = "";
// handle empty table
if (e.Arguments.Count == 0)
{
Debug.Assert(isScalarElement);
resultSql.Append(" SELECT CAST(null as ");
resultSql.Append(GetSqlPrimitiveType(collectionType.TypeUsage));
resultSql.Append(") AS X FROM (SELECT 1) AS Y WHERE 1=0");
}
foreach (DbExpression arg in e.Arguments)
{
resultSql.Append(separator);
resultSql.Append(" SELECT ");
resultSql.Append(arg.Accept(this));
// For scalar elements, no alias is appended yet. Add this.
if (isScalarElement)
{
resultSql.Append(" AS X ");
}
separator = " UNION ALL ";
}
return resultSql;
}
///
///
///
/// Was the parent a DbNotExpression?
///
/// This handles the processing of join expressions.
/// The extents on a left spine are flattened, while joins
/// not on the left spine give rise to new nested sub queries.
///
/// Joins work differently from the rest of the visiting, in that
/// the parent (i.e. the join node) creates the SqlSelectStatement
/// for the children to use.
///
/// The "parameter" IsInJoinContext indicates whether a child extent should
/// add its stuff to the existing SqlSelectStatement, or create a new SqlSelectStatement
/// By passing true, we ask the children to add themselves to the parent join,
/// by passing false, we ask the children to create new Select statements for
/// themselves.
///
/// This method is called from
///
///
///
///
/// A
private ISqlFragment VisitJoinExpression(IList inputs, DbExpressionKind joinKind,
string joinString, DbExpression joinCondition)
{
SqlSelectStatement result;
// If the parent is not a join( or says that it is not),
// we should create a new SqlSelectStatement.
// otherwise, we add our child extents to the parent's FROM clause.
if (!IsParentAJoin)
{
result = new SqlSelectStatement();
result.AllJoinExtents = new List();
selectStatementStack.Push(result);
}
else
{
result = CurrentSelectStatement;
}
// Process each of the inputs, and then the joinCondition if it exists.
// It would be nice if we could call VisitInputExpression - that would
// avoid some code duplication
// but the Join postprocessing is messy and prevents this reuse.
symbolTable.EnterScope();
string separator = "";
bool isLeftMostInput = true;
int inputCount = inputs.Count;
for(int idx = 0; idx < inputCount; idx++)
{
DbExpressionBinding input = inputs[idx];
if (separator.Length != 0)
{
result.From.AppendLine();
}
result.From.Append(separator + " ");
// Change this if other conditions are required
// to force the child to produce a nested SqlStatement.
bool needsJoinContext = (input.Expression.ExpressionKind == DbExpressionKind.Scan)
|| (isLeftMostInput &&
(IsJoinExpression(input.Expression)
|| IsApplyExpression(input.Expression)))
;
isParentAJoinStack.Push(needsJoinContext ? true : false);
// if the child reuses our select statement, it will append the from
// symbols to our FromExtents list. So, we need to remember the
// start of the child's entries.
int fromSymbolStart = result.FromExtents.Count;
ISqlFragment fromExtentFragment = input.Expression.Accept(this);
isParentAJoinStack.Pop();
ProcessJoinInputResult(fromExtentFragment, result, input, fromSymbolStart);
separator = joinString;
isLeftMostInput = false;
}
// Visit the on clause/join condition.
switch (joinKind)
{
case DbExpressionKind.FullOuterJoin:
case DbExpressionKind.InnerJoin:
case DbExpressionKind.LeftOuterJoin:
result.From.Append(" ON ");
isParentAJoinStack.Push(false);
result.From.Append(joinCondition.Accept(this));
isParentAJoinStack.Pop();
break;
}
symbolTable.ExitScope();
if (!IsParentAJoin)
{
selectStatementStack.Pop();
}
return result;
}
///
/// This is called from
/// - The same as the parent's - this is treated specially.
/// - A sql select statement, which may need to be completed
/// - An extent - just copy it to the from clause
/// - Anything else (from a collection-valued expression) -
/// unnest and copy it.
///
///
/// If the input was a Join, we need to create a new join symbol,
/// otherwise, we create a normal symbol.
///
/// We then call AddFromSymbol to add the AS clause, and update the symbol table.
///
///
///
/// If the child's result was the same as the parent's, we have to clean up
/// the list of symbols in the FromExtents list, since this contains symbols from
/// the children of both the parent and the child.
/// The happens when the child visited is a Join, and is the leftmost child of
/// the parent.
///
///
///
///
///
private void ProcessJoinInputResult(ISqlFragment fromExtentFragment, SqlSelectStatement result,
DbExpressionBinding input, int fromSymbolStart)
{
Symbol fromSymbol = null;
if (result != fromExtentFragment)
{
// The child has its own select statement, and is not reusing
// our select statement.
// This should look a lot like VisitInputExpression().
SqlSelectStatement sqlSelectStatement = fromExtentFragment as SqlSelectStatement;
if (sqlSelectStatement != null)
{
if (sqlSelectStatement.Select.IsEmpty)
{
List columns = AddDefaultColumns(sqlSelectStatement);
if (IsJoinExpression(input.Expression)
|| IsApplyExpression(input.Expression))
{
List extents = sqlSelectStatement.FromExtents;
JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = columns;
fromSymbol = newJoinSymbol;
}
else
{
// this is a copy of the code in CreateNewSelectStatement.
// if the oldStatement has a join as its input, ...
// clone the join symbol, so that we "reuse" the
// join symbol. Normally, we create a new symbol - see the next block
// of code.
JoinSymbol oldJoinSymbol = sqlSelectStatement.FromExtents[0] as JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: sqlSelectStatement.FromExtents will not do, since it might
// just be an alias of joinSymbol, and we want an actual JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, oldJoinSymbol.ExtentList);
// This indicates that the sqlSelectStatement is a blocking scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = columns;
newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
fromSymbol = newJoinSymbol;
}
else if (sqlSelectStatement.FromExtents[0].OutputColumnsRenamed)
{
fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.FromExtents[0].Columns);
}
}
}
else if (sqlSelectStatement.OutputColumnsRenamed)
{
fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.OutputColumns);
}
result.From.Append(" (");
result.From.Append(sqlSelectStatement);
result.From.Append(" )");
}
else if (input.Expression is DbScanExpression)
{
result.From.Append(fromExtentFragment);
}
else // bracket it
{
WrapNonQueryExtent(result, fromExtentFragment, input.Expression.ExpressionKind);
}
if (fromSymbol == null) // i.e. not a join symbol
{
fromSymbol = new Symbol(input.VariableName, input.VariableType);
}
AddFromSymbol(result, input.VariableName, fromSymbol);
result.AllJoinExtents.Add(fromSymbol);
}
else // result == fromExtentFragment. The child extents have been merged into the parent's.
{
// we are adding extents to the current sql statement via flattening.
// We are replacing the child's extents with a single Join symbol.
// The child's extents are all those following the index fromSymbolStart.
//
List extents = new List();
// We cannot call extents.AddRange, since the is no simple way to
// get the range of symbols fromSymbolStart..result.FromExtents.Count
// from result.FromExtents.
// We copy these symbols to create the JoinSymbol later.
for (int i = fromSymbolStart; i < result.FromExtents.Count; ++i)
{
extents.Add(result.FromExtents[i]);
}
result.FromExtents.RemoveRange(fromSymbolStart, result.FromExtents.Count - fromSymbolStart);
fromSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
result.FromExtents.Add(fromSymbol);
// this Join Symbol does not have its own select statement, so we
// do not set IsNestedJoin
// We do not call AddFromSymbol(), since we do not want to add
// "AS alias" to the FROM clause- it has been done when the extent was added earlier.
symbolTable.Add(input.VariableName, fromSymbol);
}
}
///
/// We assume that this is only called as a child of a Project.
///
/// This replaces
///
///
///
/// A
private ISqlFragment VisitNewInstanceExpression(DbNewInstanceExpression e, bool aliasesNeedRenaming, out Dictionary newColumns)
{
SqlBuilder result = new SqlBuilder();
RowType rowType = e.ResultType.EdmType as RowType;
if (null != rowType)
{
if (aliasesNeedRenaming)
{
newColumns = new Dictionary(e.Arguments.Count);
}
else
{
newColumns = null;
}
ReadOnlyMetadataCollection members = rowType.Properties;
string separator = "";
for(int i = 0; i < e.Arguments.Count; ++i)
{
DbExpression argument = e.Arguments[i];
if (TypeSemantics.IsRowType(argument.ResultType))
{
// We do not support nested records or other complex objects.
throw EntityUtil.NotSupported();
}
EdmProperty member = members[i];
result.Append(separator);
result.AppendLine();
result.Append(argument.Accept(this));
result.Append(" AS ");
if (aliasesNeedRenaming)
{
Symbol column = new Symbol(member.Name, member.TypeUsage);
column.NeedsRenaming = true;
column.NewName = String.Concat("Internal_", member.Name);
result.Append(column);
newColumns.Add(member.Name, column);
}
else
{
result.Append(QuoteIdentifier(member.Name));
}
separator = ", ";
}
}
else
{
//
//
throw EntityUtil.NotSupported();
}
return result;
}
///
/// Handler for set operations
/// It generates left separator right.
/// Only for SQL 8.0 it may need to create a new select statement
/// above the set operation if the left child's output columns got renamed
///
///
///
///
///
/// Determines whether the given function is a store function that
/// requires special handling
///
///
///
/// Determines whether the given function is a canonical function that
/// requires special handling
///
///
///
/// Default handling for functions.
/// Translates them to FunctionName(arg1, arg2, ..., argn)
///
///
///
/// Default handling for functions with a given name.
/// Translates them to FunctionName(arg1, arg2, ..., argn)
///
///
///
///
/// Default handling on function arguments.
/// Appends the list of arguemnts to the given result
/// If the function is niladic it does not append anything,
/// otherwise it appends (arg1, arg2, .., argn)
///
///
///
private void HandleFunctionArgumentsDefault(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.NiladicFunctionsCannotHaveParameters);
}
if (!isNiladicFunction)
{
result.Append("(");
string separator = "";
foreach (DbExpression arg in e.Arguments)
{
result.Append(separator);
result.Append(arg.Accept(this));
separator = ", ";
}
result.Append(")");
}
}
///
/// Handler for functions that need to be translated to different store function based on version
///
///
///
///
///
/// Handler for special build in functions
///
///
///
/// Handler for special canonical functions
///
///
///
/// Dispatches the special function processing to the appropriate handler
///
///
///
/// handlers, 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](this, e);
}
///
/// 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
///
///
/// Whether the arguments should be enclosed in parethesis
///
///
///
///
///
///
///
///
///
/// 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.
///
///
///
///
/// Handler for canonical funcitons for extracting date parts.
/// For example:
/// Year(date) -> DATEPART( year, date)
///
///
///
///
/// Handler for canonical funcitons for GetTotalOffsetMinutes.
/// GetTotalOffsetMinutes(e) --> Datepart(tzoffset, e)
///
///
///
///
/// Handler for turning a canonical function into DATEPART
/// Results in DATEPART(datepart, e)
///
///
///
///
/// Handler for the canonical function CurrentDate
/// For Sql8 and Sql9: CurrentDate() -> GetDate()
/// For Sql10: CurrentDate() -> SysDateTime()
///
///
///
///
/// Handler for the canonical function CurrentUtcDateTime
/// For Sql8 and Sql9: CurrentUtcDateTime() -> GetUtcDate()
/// For Sql10: CurrentUtcDateTime() -> SysUtcDateTime()
///
///
///
///
/// Handler for the canonical function CurrentDateTimeOffset
/// For Sql8 and Sql9: throw
/// For Sql10: CurrentDateTimeOffset() -> SysDateTimeOffset()
///
///
///
///
/// Function rename IndexOf -> CHARINDEX
///
///
///
///
/// Function rename NewGuid -> NEWID
///
///
///
///
/// Function rename Length -> LEN
///
///
///
///
/// Round(numericExpression) -> Round(numericExpression, 0);
///
///
///
///
/// TRIM(string) -> LTRIM(RTRIM(string))
///
///
///
///
/// Function rename ToLower -> LOWER
///
///
///
///
/// Function rename ToUpper -> UPPER
///
///
///
///
/// Writes the function name to the given SqlBuilder.
///
///
///
private 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(QuoteIdentifier(function.NamespaceName));
}
else
{
result.Append(QuoteIdentifier(function.Schema));
}
result.Append(".");
result.Append(QuoteIdentifier(storeFunctionName));
}
}
#endregion
#region Other Helpers
///
///
/// - Add the SQL string for each column to the SELECT clause
/// - Add the column to the list of columns - so that it can
/// become part of the "type" of a JoinSymbol
/// - Check if the column name collides with a previous column added
/// to the same select statement. Flag both the columns for renaming if true.
/// - Add the column to a name lookup dictionary for collision detection.
///
///
/// The select statement that started off as SELECT *
/// The symbol containing the type information for
/// the columns to be added.
/// Columns that have been added to the Select statement.
/// This is created in columnList, Dictionary columnDictionary, ref string separator)
{
JoinSymbol joinSymbol = symbol as JoinSymbol;
if (joinSymbol != null)
{
if (!joinSymbol.IsNestedJoin)
{
// Recurse if the join symbol is a collection of flattened extents
foreach (Symbol sym in joinSymbol.ExtentList)
{
// if sym is ScalarType means we are at base case in the
// recursion and there are not columns to add, just skip
if ((sym.Type == null) || TypeSemantics.IsPrimitiveType(sym.Type))
{
continue;
}
AddColumns(selectStatement, sym, columnList, columnDictionary, ref separator);
}
}
else
{
foreach (Symbol joinColumn in joinSymbol.ColumnList)
{
// we write tableName.columnName
// rather than tableName.columnName as alias
// since the column name is unique (by the way we generate new column names)
//
// We use the symbols for both the table and the column,
// since they are subject to renaming.
selectStatement.Select.Append(separator);
selectStatement.Select.Append(symbol);
selectStatement.Select.Append(".");
selectStatement.Select.Append(joinColumn);
// check for name collisions. If there is,
// flag both the colliding symbols.
if (columnDictionary.ContainsKey(joinColumn.Name))
{
columnDictionary[joinColumn.Name].NeedsRenaming = true; // the original symbol
joinColumn.NeedsRenaming = true; // the current symbol.
}
else
{
columnDictionary[joinColumn.Name] = joinColumn;
}
columnList.Add(joinColumn);
separator = ", ";
}
}
}
else
{
// This is a non-join extent/select statement, and the CQT type has
// the relevant column information.
// The type could be a record type(e.g. Project(...),
// or an entity type ( e.g. EntityExpression(...)
// so, we check whether it is a structuralType.
// Consider an expression of the form J(a, b=P(E))
// The inner P(E) would have been translated to a SQL statement
// We should not use the raw names from the type, but the equivalent
// symbols (they are present in symbol.Columns) if they exist.
//
// We add the new columns to the symbol's columns if they do
// not already exist.
//
// If the symbol represents a SqlStatement with renamed output columns,
// we should use these instead of the rawnames and we should also mark
// this selectStatement as one with renamed columns
if (symbol.OutputColumnsRenamed)
{
selectStatement.OutputColumnsRenamed = true;
selectStatement.OutputColumns = new Dictionary();
}
if ((symbol.Type == null) || TypeSemantics.IsPrimitiveType(symbol.Type))
{
AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, "X");
}
else
{
foreach (EdmProperty property in TypeHelpers.GetProperties(symbol.Type))
{
AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, property.Name);
}
}
}
}
///
/// Helper method for AddColumns. Adds a column with the given column name
/// to the Select list of the given select statement.
///
/// The select statement to whose SELECT part the column should be added
/// The symbol from which the column to be added originated
/// Columns that have been added to the Select statement.
/// This is created in columnList, Dictionary columnDictionary, ref string separator, string columnName)
{
// Since all renaming happens in the second phase
// we lose nothing by setting the next column name index to 0
// many times.
allColumnNames[columnName] = 0;
// Create a new symbol/reuse existing symbol for the column
Symbol columnSymbol;
if (!symbol.Columns.TryGetValue(columnName, out columnSymbol))
{
// we do not care about the types of columns, so we pass null
// when construction the symbol.
columnSymbol = new Symbol(columnName, null);
symbol.Columns.Add(columnName, columnSymbol);
}
selectStatement.Select.Append(separator);
selectStatement.Select.Append(symbol);
selectStatement.Select.Append(".");
if (symbol.OutputColumnsRenamed)
{
selectStatement.Select.Append(columnSymbol);
selectStatement.OutputColumns.Add(columnSymbol.Name, columnSymbol);
}
// We use the actual name before the "AS", the new name goes
// after the AS.
else
{
selectStatement.Select.Append(QuoteIdentifier(columnName));
}
selectStatement.Select.Append(" AS ");
selectStatement.Select.Append(columnSymbol);
// Check for column name collisions.
if (columnDictionary.ContainsKey(columnName))
{
columnDictionary[columnName].NeedsRenaming = true;
columnSymbol.NeedsRenaming = true;
}
else
{
columnDictionary[columnName] = symbol.Columns[columnName];
}
columnList.Add(columnSymbol);
separator = ", ";
}
///
/// Expands Select * to "select the_list_of_columns"
/// If the columns are taken from an extent, they are written as
/// {original_column_name AS Symbol(original_column)} to allow renaming.
///
/// If the columns are taken from a Join, they are written as just
/// {original_column_name}, since there cannot be a name collision.
///
/// We concatenate the columns from each of the inputs to the select statement.
/// Since the inputs may be joins that are flattened, we need to recurse.
/// The inputs are inferred from the symbols in FromExtents.
///
///
/// AddDefaultColumns(SqlSelectStatement selectStatement)
{
// This is the list of columns added in this select statement
// This forms the "type" of the Select statement, if it has to
// be expanded in another SELECT *
List columnList = new List();
// A lookup for the previous set of columns to aid column name
// collision detection.
Dictionary columnDictionary = new Dictionary(StringComparer.OrdinalIgnoreCase);
string separator = "";
// The Select should usually be empty before we are called,
// but we do not mind if it is not.
if (!selectStatement.Select.IsEmpty)
{
separator = ", ";
}
foreach (Symbol symbol in selectStatement.FromExtents)
{
AddColumns(selectStatement, symbol, columnList, columnDictionary, ref separator);
}
return columnList;
}
///
///
///
///
///
private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol)
{
AddFromSymbol(selectStatement, inputVarName, fromSymbol, true);
}
///
/// This method is called after the input to a relational node is visited.
///
/// - The fromSymbol is new i.e. the select statement has just been
/// created, or a join extent has been added.
/// - The fromSymbol is old i.e. we are reusing a select statement.
///
///
/// If we are not reusing the select statement, we have to complete the
/// FROM clause with the alias
///
/// -- if the input was an extent
/// FROM = [SchemaName].[TableName]
/// -- if the input was a Project
/// FROM = (SELECT ... FROM ... WHERE ...)
///
///
/// These become
///
/// -- if the input was an extent
/// FROM = [SchemaName].[TableName] AS alias
/// -- if the input was a Project
/// FROM = (SELECT ... FROM ... WHERE ...) AS alias
///
/// and look like valid FROM clauses.
///
/// Finally, we have to add the alias to the global list of aliases used,
/// and also to the current symbol table.
///
///
/// The alias to be used.
///
///
private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol, bool addToSymbolTable)
{
// the first check is true if this is a new statement
// the second check is true if we are in a join - we do not
// check if we are in a join context.
// We do not want to add "AS alias" if it has been done already
// e.g. when we are reusing the Sql statement.
if (selectStatement.FromExtents.Count == 0 || fromSymbol != selectStatement.FromExtents[0])
{
selectStatement.FromExtents.Add(fromSymbol);
selectStatement.From.Append(" AS ");
selectStatement.From.Append(fromSymbol);
// We have this inside the if statement, since
// we only want to add extents that are actually used.
allExtentNames[fromSymbol.Name] = 0;
}
if (addToSymbolTable)
{
symbolTable.Add(inputVarName, fromSymbol);
}
}
///
/// Translates a list of SortClauses.
/// Used in the translation of OrderBy
///
/// The SqlBuilder to which the sort keys should be appended
///
private void AddSortKeys(SqlBuilder orderByClause, IList sortKeys)
{
string separator = "";
foreach (DbSortClause sortClause in sortKeys)
{
orderByClause.Append(separator);
orderByClause.Append(sortClause.Expression.Accept(this));
// Bug 431021: COLLATE clause must precede ASC/DESC
Debug.Assert(sortClause.Collation != null);
if (!String.IsNullOrEmpty(sortClause.Collation))
{
orderByClause.Append(" COLLATE ");
orderByClause.Append(sortClause.Collation);
}
orderByClause.Append(sortClause.Ascending ? " ASC" : " DESC");
separator = ", ";
}
}
///
///
///
///
///
///
///
/// This is called after a relational node's input has been visited, and the
/// input's sql statement cannot be reused.
///
///
///
///
///
/// A new select statement, with the old one as the from clause.
private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
string inputVarName, TypeUsage inputVarType, bool finalizeOldStatement, out Symbol fromSymbol)
{
fromSymbol = null;
// Finalize the old statement
if (finalizeOldStatement && oldStatement.Select.IsEmpty)
{
List columns = AddDefaultColumns(oldStatement);
// Thid could not have been called from a join node.
Debug.Assert(oldStatement.FromExtents.Count == 1);
// if the oldStatement has a join as its input, ...
// clone the join symbol, so that we "reuse" the
// join symbol. Normally, we create a new symbol - see the next block
// of code.
JoinSymbol oldJoinSymbol = oldStatement.FromExtents[0] as JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: oldStatement.FromExtents will not do, since it might
// just be an alias of joinSymbol, and we want an actual JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(inputVarName, inputVarType, oldJoinSymbol.ExtentList);
// This indicates that the oldStatement is a blocking scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = columns;
newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
fromSymbol = newJoinSymbol;
}
}
if (fromSymbol == null)
{
if (oldStatement.OutputColumnsRenamed)
{
fromSymbol = new Symbol(inputVarName, inputVarType, oldStatement.OutputColumns);
}
else
{
// This is just a simple extent/SqlSelectStatement,
// and we can get the column list from the type.
fromSymbol = new Symbol(inputVarName, inputVarType);
}
}
// Observe that the following looks like the body of Visit(ExtentExpression).
SqlSelectStatement selectStatement = new SqlSelectStatement();
selectStatement.From.Append("( ");
selectStatement.From.Append(oldStatement);
selectStatement.From.AppendLine();
selectStatement.From.Append(") ");
return selectStatement;
}
///
/// Before we embed a string literal in a SQL string, we should
/// convert all ' to '', and enclose the whole string in single quotes.
///
///
///
/// The escaped sql string.
private static string EscapeSingleQuote(string s, bool isUnicode)
{
return (isUnicode ? "N'" : "'") + s.Replace("'", "''") + "'";
}
///
/// Returns the sql primitive/native type name.
/// It will include size, precision or scale depending on type information present in the
/// type facets
///
///
///
/// Handles the expression represending DbLimitExpression.Limit and DbSkipExpression.Count.
/// If it is a constant expression, it simply does to string thus avoiding casting it to the specific value
/// (which would be done if
///
///
/// This is used to determine if a particular expression is an Apply operation.
/// This is only the case when the DbExpressionKind is CrossApply or OuterApply.
///
///
///
/// This is used to determine if a particular expression is a Join operation.
/// This is true for DbCrossJoinExpression and DbJoinExpression, the
/// latter of which may have one of several different ExpressionKinds.
///
///
///
/// This is used to determine if a calling expression needs to place
/// round brackets around the translation of the expression e.
///
/// Constants, parameters and properties do not require brackets,
/// everything else does.
///
///
/// true, if the expression needs brackets
private static bool IsComplexExpression(DbExpression e)
{
switch (e.ExpressionKind)
{
case DbExpressionKind.Constant:
case DbExpressionKind.ParameterReference:
case DbExpressionKind.Property:
return false;
default:
return true;
}
}
///
/// Determine if the owner expression can add its unique sql to the input's
/// SqlSelectStatement
///
/// The SqlSelectStatement of the input to the relational node.
/// The kind of the expression node(not the input's)
///
/// We use the normal box quotes for SQL server. We do not deal with ANSI quotes
/// i.e. double quotes.
///
///
///
/// Simply calls
///
///
/// This is called from
/// -- originally {expression}
/// -- change that to
/// SELECT *
/// FROM {expression} as c
///
///
/// DbLimitExpression needs to start the statement but not add the default columns
///
///
///
/// (e.ResultType).TypeUsage;
break;
}
result = VisitInputExpression(e, inputVarName, type, out fromSymbol);
AddFromSymbol(result, inputVarName, fromSymbol);
symbolTable.ExitScope();
break;
}
if (addDefaultColumns && result.Select.IsEmpty)
{
AddDefaultColumns(result);
}
return result;
}
///
/// This method is called by
///
///
/// This is passed from
/// If the sql fragment for an input expression is not a SqlSelect statement
/// or other acceptable form (e.g. an extent as a SqlBuilder), we need
/// to wrap it in a form acceptable in a FROM clause. These are
/// primarily the
///
/// - The set operation expressions - union all, intersect, except
/// - TVFs, which are conceptually similar to tables
///
///
///
///
///
private static void WrapNonQueryExtent(SqlSelectStatement result, ISqlFragment sqlFragment, DbExpressionKind expressionKind)
{
switch (expressionKind)
{
case DbExpressionKind.Function:
// TVF
result.From.Append(sqlFragment);
break;
default:
result.From.Append(" (");
result.From.Append(sqlFragment);
result.From.Append(")");
break;
}
}
///
/// Is this a Store function (ie) does it have the builtinAttribute specified and it is not a canonical function?
///
///
///
/// Helper method for the Group By visitor
/// Returns true if at least one of the aggregates in the given list
/// has an argument that is not a
/// - If an expression being aggregated contains an outer reference, then that outer
/// reference must be the only column referenced in the expression (SQLBUDT #488741)
/// - Sql Server cannot perform an aggregate function on an expression containing
/// an aggregate or a subquery. (SQLBUDT #504600)
///
/// Potentially, we could furhter optimize this.
///
///
///
/// aggregates, string inputVarRefName)
{
foreach (DbAggregate aggregate in aggregates)
{
Debug.Assert(aggregate.Arguments.Count == 1);
if (GroupByAggregateNeedsInnerQuery(aggregate.Arguments[0], inputVarRefName))
{
return true;
}
}
return false;
}
///
/// Returns true if the given expression is not a
///
///
///
/// Helper method for the Group By visitor
/// Returns true if at least one of the expressions in the given list
/// is not
///
///
/// keys, string inputVarRefName)
{
foreach (DbExpression key in keys)
{
if (GroupByKeyNeedsInnerQuery(key, inputVarRefName))
{
return true;
}
}
return false;
}
///
/// Returns true if the given expression is not
///
///
///
/// Helper method for processing Group By keys and aggregates.
/// Returns true if the given expression is not a
///
///
///
///
/// determines if the function requires the return type be enforeced by use of a cast expression
///
///
///
/// Throws not supported exception if the server is pre-katmai
///
///
private void AssertKatmaiOrNewer(PrimitiveTypeKind primitiveTypeKind)
{
if (this.IsPreKatmai)
{
throw EntityUtil.NotSupported(System.Data.Entity.Strings.PrimitiveTypeNotSupportedPriorSql10(primitiveTypeKind));
}
}
///
/// Throws not supported exception if the server is pre-katmai
///
///
private void AssertKatmaiOrNewer(DbFunctionExpression e)
{
if (this.IsPreKatmai)
{
throw EntityUtil.NotSupported(System.Data.Entity.Strings.CanonicalFunctionNotSupportedPriorSql10(e.Function.Name));
}
}
#endregion
#endregion
}
}
// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
//----------------------------------------------------------------------
//
// Copyright (c) Microsoft Corporation. All rights reserved.
//
//
// @owner [....], [....]
//---------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text;
using System.Data.Common;
using System.Data.Common.CommandTrees;
using System.Data.Common.CommandTrees.Internal;
using System.Data.Common.Utils;
using System.Data.Metadata.Edm;
using System.Data.SqlClient;
namespace System.Data.SqlClient.SqlGen
{
///
/// Translates the command object into a SQL string that can be executed on
/// SQL Server 2000 and SQL Server 2005.
///
///
/// The translation is implemented as a visitor
/// - Select statement minimization. Multiple nodes in the query tree
/// that can be part of a single SQL select statement are merged. e.g. a
/// Filter node that is the input of a Project node can typically share the
/// same SQL statement.
/// - Alpha-renaming. As a result of the statement minimization above, there
/// could be name collisions when using correlated subqueries
///
///
/// Filter(
/// b = Project( c.x
/// c = Extent(foo)
/// )
/// exists (
/// Filter(
/// c = Extent(foo)
/// b.x = c.x
/// )
/// )
/// )
///
/// The first Filter, Project and Extent will share the same SQL select statement.
/// The alias for the Project i.e. b, will be replaced with c.
/// If the alias c for the Filter within the exists clause is not renamed,
/// we will get c.x = c.x , which is incorrect.
/// Instead, the alias c within the second filter should be renamed to c1, to give
/// c.x = c1.x i.e. b is renamed to c, and c is renamed to c1.
///
///
/// - Join flattening. In the query tree, a list of join nodes is typically
/// represented as a tree of Join nodes, each with 2 children. e.g.
///
///
/// a = Join(InnerJoin
/// b = Join(CrossJoin
/// c = Extent(foo)
/// d = Extent(foo)
/// )
/// e = Extent(foo)
/// on b.c.x = e.x
/// )
///
/// If translated directly, this will be translated to
///
/// FROM ( SELECT c.*, d.*
/// FROM foo as c
/// CROSS JOIN foo as d) as b
/// INNER JOIN foo as e on b.x' = e.x
///
/// It would be better to translate this as
///
/// FROM foo as c
/// CROSS JOIN foo as d
/// INNER JOIN foo as e on c.x = e.x
///
/// This allows the optimizer to choose an appropriate join ordering for evaluation.
///
///
/// - Select * and column renaming. In the example above, we noticed that
/// in some cases we add
SELECT * FROM ... to complete the SQL
/// statement. i.e. there is no explicit PROJECT list.
/// In this case, we enumerate all the columns available in the FROM clause
/// This is particularly problematic in the case of Join trees, since the columns
/// from the extents joined might have the same name - this is illegal. To solve
/// this problem, we will have to rename columns if they are part of a SELECT *
/// for a JOIN node - we do not need renaming in any other situation.
///
///
///
///
/// Renaming issues.
/// When rows or columns are renamed, we produce names that are unique globally
/// with respect to the query. The names are derived from the original names,
/// with an integer as a suffix. e.g. CustomerId will be renamed to CustomerId1,
/// CustomerId2 etc.
///
/// Since the names generated are globally unique, they will not conflict when the
/// columns of a JOIN SELECT statement are joined with another JOIN.
///
///
///
///
/// Record flattening.
/// SQL server does not have the concept of records. However, a join statement
/// produces records. We have to flatten the record accesses into a simple
/// alias.column form.
///
///
/// Building the SQL.
/// There are 2 phases
///
/// - Traverse the tree, producing a sql builder
/// - Write the SqlBuilder into a string, renaming the aliases and columns
/// as needed.
///
///
/// In the first phase, we traverse the tree. We cannot generate the SQL string
/// right away, since
///
/// - The WHERE clause has to be visited before the from clause.
/// - extent aliases and column aliases need to be renamed. To minimize
/// renaming collisions, all the names used must be known, before any renaming
/// choice is made.
///
/// To defer the renaming choices, we use symbols
/// - A stack for the current SQL select statement. This is needed by
///
/// - A stack for the join context. When visiting an extent,
/// we need to know whether we are inside a join or not. If we are inside
/// a join, we do not create a new SELECT statement.
///
///
///
///
/// Global state.
/// To enable renaming, we maintain
///
/// - The set of all extent aliases used.
/// - The set of all parameter names.
/// - The set of all column names that may need to be renamed.
///
///
/// Finally, we have a symbol table to lookup variable references. All references
/// to the same extent have the same symbol.
///
///
///
/// Sql select statement sharing.
///
/// Each of the relational operator nodes
///
/// - Project
/// - Filter
/// - GroupBy
/// - Sort/OrderBy
///
/// can add its non-input (e.g. project, predicate, sort order etc.) to
/// the SQL statement for the input, or create a new SQL statement.
/// If it chooses to reuse the input's SQL statement, we play the following
/// symbol table trick to accomplish renaming. The symbol table entry for
/// the alias of the current node points to the symbol for the input in
/// the input's SQL statement.
///
///
/// Project(b.x
/// b = Filter(
/// c = Extent(foo)
/// c.x = 5)
/// )
///
/// The Extent node creates a new SqlSelectStatement. This is added to the
/// symbol table by the Filter as {c, Symbol(c)}. Thus, c.x is resolved to
/// Symbol(c).x .
/// Looking at the project node, we add {b, Symbol(c)} to the symbol table if the
/// SQL statement is reused, and {b, Symbol(b)}, if there is no reuse.
///
/// Thus, b.x is resolved to Symbol(c).x if there is reuse, and to
/// Symbol(b).x if there is no reuse.
///
///
///
internal sealed class SqlGenerator : DbExpressionVisitor
{
#region Visitor parameter stacks
///
/// Every relational node has to pass its SELECT statement to its children
/// This allows them (DbVariableReferenceExpression eventually) to update the list of
/// outer extents (free variables) used by this select statement.
///
private Stack selectStatementStack;
///
/// The top of the stack
///
private SqlSelectStatement CurrentSelectStatement
{
// There is always something on the stack, so we can always Peek.
get { return selectStatementStack.Peek(); }
}
///
/// Nested joins and extents need to know whether they should create
/// a new Select statement, or reuse the parent's. This flag
/// indicates whether the parent is a join or not.
///
private Stack isParentAJoinStack;
///
/// The top of the stack
///
private bool IsParentAJoin
{
// There might be no entry on the stack if a Join node has never
// been seen, so we return false in that case.
get { return isParentAJoinStack.Count == 0 ? false : isParentAJoinStack.Peek(); }
}
#endregion
#region Global lists and state
Dictionary allExtentNames;
internal Dictionary AllExtentNames
{
get { return allExtentNames; }
}
// For each column name, we store the last integer suffix that
// was added to produce a unique column name. This speeds up
// the creation of the next unique name for this column name.
Dictionary allColumnNames;
internal Dictionary AllColumnNames
{
get { return allColumnNames; }
}
SymbolTable symbolTable = new SymbolTable();
///
/// VariableReferenceExpressions are allowed only as children of DbPropertyExpression
/// or MethodExpression. The cheapest way to ensure this is to set the following
/// property in DbVariableReferenceExpression and reset it in the allowed parent expressions.
///
private bool isVarRefSingle;
#endregion
#region Statics
static private readonly Dictionary _storeFunctionHandlers = InitializeStoreFunctionHandlers();
static private readonly Dictionary _canonicalFunctionHandlers = InitializeCanonicalFunctionHandlers();
static private readonly Dictionary _functionNameToOperatorDictionary = InitializeFunctionNameToOperatorDictionary();
static private readonly Set _datepartKeywords = new Set(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 char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
static private readonly Set _functionRequiresReturnTypeCast = new Set(new string[] { "SqlServer.LEN" ,
"SqlServer.PATINDEX" ,
"SqlServer.CHARINDEX" ,
"SqlServer.DATALENGTH" ,
"Edm.IndexOf" ,
"Edm.Length" },
StringComparer.Ordinal).MakeReadOnly();
static private readonly Set _maxTypeNames = new Set(new string[] { "varchar(max)" ,
"nvarchar(max)" ,
"text" ,
"ntext" ,
"varbinary(max)" ,
"image" ,
"xml" },
StringComparer.Ordinal).MakeReadOnly();
const byte defaultDecimalPrecision = 18;
private delegate ISqlFragment FunctionHandler(SqlGenerator sqlgen, DbFunctionExpression functionExpr);
///
/// All special store functions and their handlers
///
/// InitializeStoreFunctionHandlers()
{
Dictionary functionHandlers = new Dictionary(5, StringComparer.Ordinal);
functionHandlers.Add("CONCAT", HandleConcatFunction);
functionHandlers.Add("DATEADD", HandleDatepartDateFunction);
functionHandlers.Add("DATEDIFF", HandleDatepartDateFunction);
functionHandlers.Add("DATENAME", HandleDatepartDateFunction);
functionHandlers.Add("DATEPART", HandleDatepartDateFunction);
return functionHandlers;
}
///
/// All special non-aggregate canonical functions and their handlers
///
/// InitializeCanonicalFunctionHandlers()
{
Dictionary functionHandlers = new Dictionary(16, StringComparer.Ordinal);
functionHandlers.Add("IndexOf", HandleCanonicalFunctionIndexOf);
functionHandlers.Add("Length", HandleCanonicalFunctionLength);
functionHandlers.Add("NewGuid", HandleCanonicalFunctionNewGuid);
functionHandlers.Add("Round", HandleCanonicalFunctionRound);
functionHandlers.Add("ToLower", HandleCanonicalFunctionToLower);
functionHandlers.Add("ToUpper", HandleCanonicalFunctionToUpper);
functionHandlers.Add("Trim", HandleCanonicalFunctionTrim);
//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("CurrentDateTime", HandleCanonicalFunctionCurrentDateTime);
functionHandlers.Add("CurrentUtcDateTime", HandleCanonicalFunctionCurrentUtcDateTime);
functionHandlers.Add("CurrentDateTimeOffset", HandleCanonicalFunctionCurrentDateTimeOffset);
functionHandlers.Add("GetTotalOffsetMinutes", HandleCanonicalFunctionGetTotalOffsetMinutes);
//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;
}
///
/// Initalizes the mapping from functions to TSql operators
/// for all functions that translate to TSql operators
///
/// InitializeFunctionNameToOperatorDictionary()
{
Dictionary functionNameToOperatorDictionary = new Dictionary(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;
}
#endregion
#region SqlVersion, Metadata, ...
///
/// The current SQL Server version
///
private SqlVersion sqlVersion;
internal SqlVersion SqlVersion
{
get { return sqlVersion; }
}
internal bool IsPreKatmai
{
get { return SqlVersionUtils.IsPreKatmai(this.SqlVersion); }
}
private MetadataWorkspace metadataWorkspace;
internal MetadataWorkspace Workspace
{
get { return metadataWorkspace; }
}
private TypeUsage integerType = null;
internal TypeUsage IntegerType
{
get
{
if (integerType == null)
{
integerType = GetPrimitiveType(PrimitiveTypeKind.Int64);
}
return integerType;
}
}
private StoreItemCollection _storeItemCollection;
#endregion
#region Constructor
///
/// Basic constructor.
///
/// server version
private SqlGenerator(SqlVersion sqlVersion)
{
this.sqlVersion = sqlVersion;
}
#endregion
#region Entry points
///
/// General purpose static function that can be called from System.Data assembly
///
/// Server version
/// command tree
/// Parameters to add to the command tree corresponding
/// to constants in the command tree. Used only in ModificationCommandTrees.
/// CommandType for generated command.
/// The string representing the SQL to be executed.
internal static string GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, out List parameters, out CommandType commandType)
{
SqlGenerator sqlGen;
commandType = CommandType.Text;
parameters = null;
switch (tree.CommandTreeKind)
{
case DbCommandTreeKind.Query:
sqlGen = new SqlGenerator(sqlVersion);
return sqlGen.GenerateSql((DbQueryCommandTree)tree);
case DbCommandTreeKind.Insert:
return DmlSqlGenerator.GenerateInsertSql((DbInsertCommandTree)tree, sqlVersion, out parameters);
case DbCommandTreeKind.Delete:
return DmlSqlGenerator.GenerateDeleteSql((DbDeleteCommandTree)tree, sqlVersion, out parameters);
case DbCommandTreeKind.Update:
return DmlSqlGenerator.GenerateUpdateSql((DbUpdateCommandTree)tree, sqlVersion, out parameters);
case DbCommandTreeKind.Function:
sqlGen = new SqlGenerator(sqlVersion);
return GenerateFunctionSql((DbFunctionCommandTree)tree, out commandType);
default:
//We have covered all command tree kinds
Debug.Assert(false, "Unknown command tree kind");
parameters = null;
return null;
}
}
private static string GenerateFunctionSql(DbFunctionCommandTree tree, out CommandType commandType)
{
tree.Validate();
EdmFunction function = tree.EdmFunction;
if (String.IsNullOrEmpty(function.CommandTextAttribute))
{
// build a quoted description of the function
commandType = CommandType.StoredProcedure;
// if the schema name is not explicitly given, it is assumed to be the metadata namespace
string schemaName = String.IsNullOrEmpty(function.Schema) ?
function.NamespaceName : function.Schema;
// if the function store name is not explicitly given, it is assumed to be the metadata name
string functionName = String.IsNullOrEmpty(function.StoreFunctionNameAttribute) ?
function.Name : function.StoreFunctionNameAttribute;
// quote elements of function text
string quotedSchemaName = QuoteIdentifier(schemaName);
string quotedFunctionName = QuoteIdentifier(functionName);
// separator
const string schemaSeparator = ".";
// concatenate elements of function text
string quotedFunctionText = quotedSchemaName + schemaSeparator + quotedFunctionName;
return quotedFunctionText;
}
else
{
// if the user has specified the command text, pass it through verbatim and choose CommandType.Text
commandType = CommandType.Text;
return function.CommandTextAttribute;
}
}
#endregion
#region Driver Methods
///
/// Translate a command tree to a SQL string.
///
/// The input tree could be translated to either a SQL SELECT statement
/// or a SELECT expression. This choice is made based on the return type
/// of the expression
/// CollectionType => select statement
/// non collection type => select expression
///
///
/// The string representing the SQL to be executed.
private string GenerateSql(DbQueryCommandTree tree)
{
tree.Validate();
DbQueryCommandTree targetTree = tree;
//If we are on Sql 8.0 rewrite the tree if needed
if (this.SqlVersion == SqlVersion.Sql8)
{
if (Sql8ConformanceChecker.NeedsRewrite(tree.Query))
{
targetTree = Sql8ExpressionRewriter.Rewrite(tree);
}
}
this.metadataWorkspace = targetTree.MetadataWorkspace;
// needed in Private Type Helpers section bellow
_storeItemCollection = (StoreItemCollection)this.Workspace.GetItemCollection(DataSpace.SSpace);
selectStatementStack = new Stack();
isParentAJoinStack = new Stack();
allExtentNames = new Dictionary(StringComparer.OrdinalIgnoreCase);
allColumnNames = new Dictionary(StringComparer.OrdinalIgnoreCase);
// Literals will not be converted to parameters.
ISqlFragment result;
if (TypeSemantics.IsCollectionType(targetTree.Query.ResultType))
{
SqlSelectStatement sqlStatement = VisitExpressionEnsureSqlStatement(targetTree.Query);
Debug.Assert(sqlStatement != null, "The outer most sql statment is null");
sqlStatement.IsTopMost = true;
result = sqlStatement;
}
else
{
SqlBuilder sqlBuilder = new SqlBuilder();
sqlBuilder.Append("SELECT ");
sqlBuilder.Append(targetTree.Query.Accept(this));
result = sqlBuilder;
}
if (isVarRefSingle)
{
throw EntityUtil.NotSupported();
// A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
}
// Check that the parameter stacks are not leaking.
Debug.Assert(selectStatementStack.Count == 0);
Debug.Assert(isParentAJoinStack.Count == 0);
return WriteSql(result);
}
///
/// Convert the SQL fragments to a string.
/// We have to setup the Stream for writing.
///
///
/// A string representing the SQL to be executed.
private string WriteSql(ISqlFragment sqlStatement)
{
StringBuilder builder = new StringBuilder(1024);
using (SqlWriter writer = new SqlWriter(builder))
{
sqlStatement.WriteSql(writer, this);
}
return builder.ToString();
}
#endregion
#region IExpressionVisitor Members
///
/// Translate(left) AND Translate(right)
///
///
/// A
public override ISqlFragment Visit(DbAndExpression e)
{
return VisitBinaryExpression(" AND ", DbExpressionKind.And, e.Left, e.Right);
}
///
/// An apply is just like a join, so it shares the common join processing
/// in
///
/// A
public override ISqlFragment Visit(DbApplyExpression e)
{
Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbApplyExpression when translating for SQL Server 2000.");
List inputs = new List();
inputs.Add(e.Input);
inputs.Add(e.Apply);
string joinString;
switch (e.ExpressionKind)
{
case DbExpressionKind.CrossApply:
joinString = "CROSS APPLY";
break;
case DbExpressionKind.OuterApply:
joinString = "OUTER APPLY";
break;
default:
Debug.Assert(false);
throw EntityUtil.InvalidOperation(String.Empty);
}
// The join condition does not exist in this case, so we use null.
// WE do not have a on clause, so we use JoinType.CrossJoin.
return VisitJoinExpression(inputs, DbExpressionKind.CrossJoin, joinString, null);
}
///
/// For binary expressions, we delegate to
///
/// A
public override ISqlFragment Visit(DbArithmeticExpression e)
{
SqlBuilder result;
switch (e.ExpressionKind)
{
case DbExpressionKind.Divide:
result = VisitBinaryExpression(" / ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Minus:
result = VisitBinaryExpression(" - ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Modulo:
result = VisitBinaryExpression(" % ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Multiply:
result = VisitBinaryExpression(" * ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.Plus:
result = VisitBinaryExpression(" + ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
break;
case DbExpressionKind.UnaryMinus:
result = new SqlBuilder();
result.Append(" -(");
result.Append(e.Arguments[0].Accept(this));
result.Append(")");
break;
default:
Debug.Assert(false);
throw EntityUtil.InvalidOperation(String.Empty);
}
return result;
}
///
/// If the ELSE clause is null, we do not write it out.
///
///
/// A
public override ISqlFragment Visit(DbCaseExpression e)
{
SqlBuilder result = new SqlBuilder();
Debug.Assert(e.When.Count == e.Then.Count);
result.Append("CASE");
for (int i = 0; i < e.When.Count; ++i)
{
result.Append(" WHEN (");
result.Append(e.When[i].Accept(this));
result.Append(") THEN ");
result.Append(e.Then[i].Accept(this));
}
//
if (e.Else != null && !(e.Else is DbNullExpression))
{
result.Append(" ELSE ");
result.Append(e.Else.Accept(this));
}
result.Append(" END");
return result;
}
///
///
///
///
///
/// The parser generates Not(Equals(...)) for <>.
///
///
/// A
public override ISqlFragment Visit(DbComparisonExpression e)
{
SqlBuilder result;
switch (e.ExpressionKind)
{
case DbExpressionKind.Equals:
result = VisitComparisonExpression(" = ", e.Left, e.Right);
break;
case DbExpressionKind.LessThan:
result = VisitComparisonExpression(" < ", e.Left, e.Right);
break;
case DbExpressionKind.LessThanOrEquals:
result = VisitComparisonExpression(" <= ", e.Left, e.Right);
break;
case DbExpressionKind.GreaterThan:
result = VisitComparisonExpression(" > ", e.Left, e.Right);
break;
case DbExpressionKind.GreaterThanOrEquals:
result = VisitComparisonExpression(" >= ", e.Left, e.Right);
break;
// The parser does not generate the expression kind below.
case DbExpressionKind.NotEquals:
result = VisitComparisonExpression(" <> ", e.Left, e.Right);
break;
default:
Debug.Assert(false); // The constructor should have prevented this
throw EntityUtil.InvalidOperation(String.Empty);
}
return result;
}
///
/// Generate tsql for a constant. Avoid the explicit cast (if possible) when
/// the isCastOptional parameter is set
///
/// the constant expression
/// can we avoid the CAST
/// the tsql fragment
private ISqlFragment VisitConstant(DbConstantExpression e, bool isCastOptional)
{
// Constants will be send to the store as part of the generated TSQL, not as parameters
SqlBuilder result = new SqlBuilder();
PrimitiveTypeKind typeKind;
// Model Types can be (at the time of this implementation):
// Binary, Boolean, Byte, Date, DateTime, DateTimeOffset, Decimal, Double, Guid, Int16, Int32, Int64, Single, String, Time
if (TypeHelpers.TryGetPrimitiveTypeKind(e.ResultType, out typeKind))
{
switch (typeKind)
{
case PrimitiveTypeKind.Int32:
// default sql server type for integral values.
result.Append(e.Value.ToString());
break;
case PrimitiveTypeKind.Binary:
result.Append(" 0x");
result.Append(ByteArrayToBinaryString((Byte[])e.Value));
result.Append(" ");
break;
case PrimitiveTypeKind.Boolean:
// Bugs 450277, 430294: Need to preserve the boolean type-ness of
// this value for round-trippability
WrapWithCastIfNeeded(!isCastOptional, (bool)e.Value ? "1" : "0", "bit", result);
break;
case PrimitiveTypeKind.Byte:
WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "tinyint", result);
break;
case PrimitiveTypeKind.DateTime:
result.Append("convert(");
result.Append(this.IsPreKatmai ? "datetime" : "datetime2");
result.Append(", ");
result.Append(EscapeSingleQuote(((System.DateTime)e.Value).ToString(this.IsPreKatmai ? "yyyy-MM-dd HH:mm:ss.fff" : "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture), false /* IsUnicode */));
result.Append(", 121)");
break;
case PrimitiveTypeKind.Time:
AssertKatmaiOrNewer(typeKind);
result.Append("convert(");
result.Append(e.ResultType.EdmType.Name);
result.Append(", ");
result.Append(EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */));
result.Append(", 121)");
break;
case PrimitiveTypeKind.DateTimeOffset:
AssertKatmaiOrNewer(typeKind);
result.Append("convert(");
result.Append(e.ResultType.EdmType.Name);
result.Append(", ");
result.Append(EscapeSingleQuote(((System.DateTimeOffset)e.Value).ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture), false /* IsUnicode */));
result.Append(", 121)");
break;
case PrimitiveTypeKind.Decimal:
string strDecimal = ((Decimal)e.Value).ToString(CultureInfo.InvariantCulture);
// if the decimal value has no decimal part, cast as decimal to preserve type
// if the number has precision > int64 max precision, it will be handled as decimal by sql server
// and does not need cast. if precision is lest then 20, then cast using Max(literal precision, sql default precision)
bool needsCast = -1 == strDecimal.IndexOf('.') && (strDecimal.TrimStart(new char[] { '-' }).Length < 20);
byte precision = (byte)Math.Max((Byte)strDecimal.Length, defaultDecimalPrecision);
Debug.Assert(precision > 0, "Precision must be greater than zero");
string decimalType = "decimal(" + precision.ToString(CultureInfo.InvariantCulture) + ")";
WrapWithCastIfNeeded(needsCast, strDecimal, decimalType, result);
break;
case PrimitiveTypeKind.Double:
WrapWithCastIfNeeded(true, ((Double)e.Value).ToString("R", CultureInfo.InvariantCulture), "float(53)", result);
break;
case PrimitiveTypeKind.Guid:
WrapWithCastIfNeeded(true, EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */), "uniqueidentifier", result);
break;
case PrimitiveTypeKind.Int16:
WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "smallint", result);
break;
case PrimitiveTypeKind.Int64:
WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "bigint", result);
break;
case PrimitiveTypeKind.Single:
WrapWithCastIfNeeded(true, ((Single)e.Value).ToString("R", CultureInfo.InvariantCulture), "real", result);
break;
case PrimitiveTypeKind.String:
bool isUnicode;
if (!TypeHelpers.TryGetIsUnicode(e.ResultType, out isUnicode))
{
isUnicode = true;
}
result.Append(EscapeSingleQuote(e.Value as string, isUnicode));
break;
default:
// all known scalar types should been handled already.
throw EntityUtil.NotSupported();
}
}
else
{
throw EntityUtil.NotSupported();
//if/when Enum types are supported, then handle appropriately, for now is not a valid type for constants.
//result.Append(e.Value.ToString());
}
return result;
}
///
/// Helper function for
///
///
///
///
private static void WrapWithCastIfNeeded(bool cast, string value, string typeName, SqlBuilder result)
{
if (!cast)
{
result.Append(value);
}
else
{
result.Append("cast(");
result.Append(value);
result.Append(" as ");
result.Append(typeName);
result.Append(")");
}
}
///
/// We do not pass constants as parameters.
///
///
/// A
public override ISqlFragment Visit(DbConstantExpression e)
{
return VisitConstant(e, false /* isCastOptional */);
}
///
///
///
///
///
/// The DISTINCT has to be added to the beginning of SqlSelectStatement.Select,
/// but it might be too late for that. So, we use a flag on SqlSelectStatement
/// instead, and add the "DISTINCT" in the second phase.
///
///
/// A
public override ISqlFragment Visit(DbDistinctExpression e)
{
SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument);
if (!IsCompatible(result, e.ExpressionKind))
{
Symbol fromSymbol;
TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
result = CreateNewSelectStatement(result, "distinct", inputType, out fromSymbol);
AddFromSymbol(result, "distinct", fromSymbol, false);
}
result.IsDistinct = true;
return result;
}
///
/// An element expression returns a scalar - so it is translated to
/// ( Select ... )
///
///
///
///
///
///
/// Only concrete expression types will be visited.
///
///
///
///
///
///
/// If we are in a Join context, returns a
public override ISqlFragment Visit(DbScanExpression e)
{
EntitySetBase target = e.Target;
// ISSUE: Should we just return a string all the time, and let
// VisitInputExpression create the SqlSelectStatement?
if (IsParentAJoin)
{
SqlBuilder result = new SqlBuilder();
result.Append(GetTargetTSql(target));
return result;
}
else
{
SqlSelectStatement result = new SqlSelectStatement();
result.From.Append(GetTargetTSql(target));
return result;
}
}
///
/// Gets escaped TSql identifier describing this entity set.
///
///
/// The bodies of
/// - Visit the input expression
/// - Determine if the input's SQL statement can be reused, or a new
/// one must be created.
/// - Create a new symbol table scope
/// - Push the Sql statement onto a stack, so that children can
/// update the free variable list.
/// - Visit the non-input expression.
/// - Cleanup
///
///
///
/// A
public override ISqlFragment Visit(DbFilterExpression e)
{
return VisitFilterExpression(e.Input, e.Predicate, false);
}
///
/// Lambda functions are not supported.
/// The functions supported are:
///
/// - Canonical Functions - We recognize these by their dataspace, it is DataSpace.CSpace
/// - Store Functions - We recognize these by the BuiltInAttribute and not being Canonical
/// - User-defined Functions - All the rest except for Lambda functions
///
/// We handle Canonical and Store functions the same way: If they are in the list of functions
/// that need special handling, we invoke the appropriate handler, otherwise we translate them to
/// FunctionName(arg1, arg2, ..., argn).
/// We translate user-defined functions to NamespaceName.FunctionName(arg1, arg2, ..., argn).
///
///
/// A
public override ISqlFragment Visit(DbFunctionExpression e)
{
if (e.IsLambda)
{
throw EntityUtil.NotSupported();
}
//
// check if function requires special case processing, if so, delegates to it
//
if (IsSpecialCanonicalFunction(e))
{
return HandleSpecialCanonicalFunction(e);
}
if (IsSpecialStoreFunction(e))
{
return HandleSpecialStoreFunction(e);
}
return HandleFunctionDefault(e);
}
///
///
///
///
///
///
///
///
///
///
/// - If an expression being aggregated contains an outer reference, then that outer
/// reference must be the only column referenced in the expression (SQLBUDT #488741)
/// - Sql Server cannot perform an aggregate function on an expression containing
/// an aggregate or a subquery. (SQLBUDT #504600)
///- Sql Server requries each GROUP BY expression (key) to contain at least one column
/// that is not an outer reference. (SQLBUDT #616523)
/// - Aggregates on the right side of an APPLY cannot reference columns from the left side.
/// (SQLBUDT #617683)
///
///
/// The default translation, without inner query is:
///
/// SELECT
/// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
/// aggf1(aexpr1) AS agg1, .. aggfn(aexprn) AS aggn
/// FROM input AS a
/// GROUP BY kexp1, kexp2, .. kexpn
///
/// When we inject an innner query, the equivalent translation is:
///
/// SELECT
/// key1 AS key1, key2 AS key2, .. keyn AS keys,
/// aggf1(agg1) AS agg1, aggfn(aggn) AS aggn
/// FROM (
/// SELECT
/// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
/// aexpr1 AS agg1, .. aexprn AS aggn
/// FROM input AS a
/// ) as a
/// GROUP BY key1, key2, keyn
///
///
///
/// A
public override ISqlFragment Visit(DbGroupByExpression e)
{
Symbol fromSymbol;
SqlSelectStatement innerQuery = VisitInputExpression(e.Input.Expression,
e.Input.VariableName, e.Input.VariableType, out fromSymbol);
// GroupBy is compatible with Filter and OrderBy
// but not with Project, GroupBy
if (!IsCompatible(innerQuery, e.ExpressionKind))
{
innerQuery = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
}
selectStatementStack.Push(innerQuery);
symbolTable.EnterScope();
AddFromSymbol(innerQuery, e.Input.VariableName, fromSymbol);
// This line is not present for other relational nodes.
symbolTable.Add(e.Input.GroupVariableName, fromSymbol);
// The enumerator is shared by both the keys and the aggregates,
// so, we do not close it in between.
RowType groupByType = TypeHelpers.GetEdmType(TypeHelpers.GetEdmType(e.ResultType).TypeUsage);
//SQL Server does not support arbitrarily complex expressions inside aggregates,
// and requires keys to have reference to the input scope,
// so we check for the specific restrictions and if need we inject an inner query.
bool needsInnerQuery = GroupByAggregatesNeedInnerQuery(e.Aggregates, e.Input.GroupVariableName) || GroupByKeysNeedInnerQuery(e.Keys, e.Input.VariableName);
SqlSelectStatement result;
if (needsInnerQuery)
{
//Create the inner query
result = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, false, out fromSymbol);
AddFromSymbol(result, e.Input.VariableName, fromSymbol, false);
}
else
{
result = innerQuery;
}
using (IEnumerator members = groupByType.Properties.GetEnumerator())
{
members.MoveNext();
Debug.Assert(result.Select.IsEmpty);
string separator = "";
foreach (DbExpression key in e.Keys)
{
EdmProperty member = members.Current;
string alias = QuoteIdentifier(member.Name);
result.GroupBy.Append(separator);
ISqlFragment keySql = key.Accept(this);
if (!needsInnerQuery)
{
//Default translation: Key AS Alias
result.Select.Append(separator);
result.Select.AppendLine();
result.Select.Append(keySql);
result.Select.Append(" AS ");
result.Select.Append(alias);
result.GroupBy.Append(keySql);
}
else
{
// The inner query contains the default translation Key AS Alias
innerQuery.Select.Append(separator);
innerQuery.Select.AppendLine();
innerQuery.Select.Append(keySql);
innerQuery.Select.Append(" AS ");
innerQuery.Select.Append(alias);
//The outer resulting query projects over the key aliased in the inner query:
// fromSymbol.Alias AS Alias
result.Select.Append(separator);
result.Select.AppendLine();
result.Select.Append(fromSymbol);
result.Select.Append(".");
result.Select.Append(alias);
result.Select.Append(" AS ");
result.Select.Append(alias);
result.GroupBy.Append(alias);
}
separator = ", ";
members.MoveNext();
}
foreach (DbAggregate aggregate in e.Aggregates)
{
EdmProperty member = members.Current;
string alias = QuoteIdentifier(member.Name);
Debug.Assert(aggregate.Arguments.Count == 1);
ISqlFragment translatedAggregateArgument = aggregate.Arguments[0].Accept(this);
object aggregateArgument;
if (needsInnerQuery)
{
//In this case the argument to the aggratete is reference to the one projected out by the
// inner query
SqlBuilder wrappingAggregateArgument = new SqlBuilder();
wrappingAggregateArgument.Append(fromSymbol);
wrappingAggregateArgument.Append(".");
wrappingAggregateArgument.Append(alias);
aggregateArgument = wrappingAggregateArgument;
innerQuery.Select.Append(separator);
innerQuery.Select.AppendLine();
innerQuery.Select.Append(translatedAggregateArgument);
innerQuery.Select.Append(" AS ");
innerQuery.Select.Append(alias);
}
else
{
aggregateArgument = translatedAggregateArgument;
}
ISqlFragment aggregateResult = VisitAggregate(aggregate, aggregateArgument);
result.Select.Append(separator);
result.Select.AppendLine();
result.Select.Append(aggregateResult);
result.Select.Append(" AS ");
result.Select.Append(alias);
separator = ", ";
members.MoveNext();
}
}
symbolTable.ExitScope();
selectStatementStack.Pop();
return result;
}
///
///
///
///
/// Not(IsEmpty) has to be handled specially, so we delegate to
///
///
/// A [NOT] EXISTS( ... )
///
public override ISqlFragment Visit(DbIsEmptyExpression e)
{
return VisitIsEmptyExpression(e, false);
}
///
/// Not(IsNull) is handled specially, so we delegate to
///
///
/// A IS [NOT] NULL
///
public override ISqlFragment Visit(DbIsNullExpression e)
{
return VisitIsNullExpression(e, false);
}
///
/// No error is raised if the store cannot support this.
///
///
/// A
public override ISqlFragment Visit(DbIsOfExpression e)
{
throw EntityUtil.NotSupported();
}
///
///
///
/// A
public override ISqlFragment Visit(DbCrossJoinExpression e)
{
return VisitJoinExpression(e.Inputs, e.ExpressionKind, "CROSS JOIN", null);
}
///
///
///
/// A
public override ISqlFragment Visit(DbJoinExpression e)
{
#region Map join type to a string
string joinString;
switch (e.ExpressionKind)
{
case DbExpressionKind.FullOuterJoin:
joinString = "FULL OUTER JOIN";
break;
case DbExpressionKind.InnerJoin:
joinString = "INNER JOIN";
break;
case DbExpressionKind.LeftOuterJoin:
joinString = "LEFT OUTER JOIN";
break;
default:
Debug.Assert(false);
joinString = null;
break;
}
#endregion
List inputs = new List(2);
inputs.Add(e.Left);
inputs.Add(e.Right);
return VisitJoinExpression(inputs, e.ExpressionKind, joinString, e.JoinCondition);
}
///
///
///
///
/// A
public override ISqlFragment Visit(DbLikeExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append(e.Argument.Accept(this));
result.Append(" LIKE ");
result.Append(e.Pattern.Accept(this));
// if the ESCAPE expression is a DbNullExpression, then that's tantamount to
// not having an ESCAPE at all
if (e.Escape.ExpressionKind != DbExpressionKind.Null)
{
result.Append(" ESCAPE ");
result.Append(e.Escape.Accept(this));
}
return result;
}
///
/// Translates to TOP expression. For Sql8, limit can only be a constant expression
///
///
/// A
public override ISqlFragment Visit(DbLimitExpression e)
{
Debug.Assert(e.Limit is DbConstantExpression || e.Limit is DbParameterReferenceExpression, "DbLimitExpression.Limit is of invalid expression type");
Debug.Assert(!((this.SqlVersion == SqlVersion.Sql8) && (e.Limit is DbParameterReferenceExpression)), "DbLimitExpression.Limit is DbParameterReferenceExpression for SQL Server 2000.");
SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument, false);
Symbol fromSymbol;
if (!IsCompatible(result, e.ExpressionKind))
{
TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
result = CreateNewSelectStatement(result, "top", inputType, out fromSymbol);
AddFromSymbol(result, "top", fromSymbol, false);
}
ISqlFragment topCount = HandleCountExpression(e.Limit) ;
result.Top = new TopClause(topCount, e.WithTies);
return result;
}
#if METHOD_EXPRESSION
///
///
///
///
/// A
public override ISqlFragment Visit(MethodExpression e)
{
SqlBuilder result = new SqlBuilder();
result.Append(e.Instance.Accept(this));
result.Append(".");
result.Append(QuoteIdentifier(e.Method.Name));
result.Append("(");
// Since the VariableReferenceExpression is a proper child of ours, we can reset
// isVarSingle.
VariableReferenceExpression VariableReferenceExpression = e.Instance as VariableReferenceExpression;
if (VariableReferenceExpression != null)
{
isVarRefSingle = false;
}
string separator = "";
foreach (Expression argument in e.Arguments)
{
result.Append(separator);
result.Append(argument.Accept(this));
separator = ", ";
}
result.Append(")");
return result;
}
#endif
///
/// DbNewInstanceExpression is allowed as a child of DbProjectExpression only.
/// If anyone else is the parent, we throw.
/// We also perform special casing for collections - where we could convert
/// them into Unions
///
///
///
///
/// The Not expression may cause the translation of its child to change.
/// These children are
///
///
///
///
/// A
public override ISqlFragment Visit(DbNotExpression e)
{
// Flatten Not(Not(x)) to x.
DbNotExpression notExpression = e.Argument as DbNotExpression;
if (notExpression != null)
{
return notExpression.Argument.Accept(this);
}
DbIsEmptyExpression isEmptyExpression = e.Argument as DbIsEmptyExpression;
if (isEmptyExpression != null)
{
return VisitIsEmptyExpression(isEmptyExpression, true);
}
DbIsNullExpression isNullExpression = e.Argument as DbIsNullExpression;
if (isNullExpression != null)
{
return VisitIsNullExpression(isNullExpression, true);
}
DbComparisonExpression comparisonExpression = e.Argument as DbComparisonExpression;
if (comparisonExpression != null)
{
if (comparisonExpression.ExpressionKind == DbExpressionKind.Equals)
{
return VisitBinaryExpression(" <> ", DbExpressionKind.NotEquals, comparisonExpression.Left, comparisonExpression.Right);
}
}
SqlBuilder result = new SqlBuilder();
result.Append(" NOT (");
result.Append(e.Argument.Accept(this));
result.Append(")");
return result;
}
///
///
///
///
///
///
///
/// A
public override ISqlFragment Visit(DbOfTypeExpression e)
{
throw EntityUtil.NotSupported();
}
///
///
///
///
/// A
///
///
///
///
/// A
public override ISqlFragment Visit(DbParameterReferenceExpression e)
{
SqlBuilder result = new SqlBuilder();
// Do not quote this name.
// ISSUE: We are not checking that e.Name has no illegal characters. e.g. space
result.Append("@" + e.ParameterName);
return result;
}
///
///
///
/// A
/// newColumns;
result.Select.Append(VisitNewInstanceExpression(newInstanceExpression, aliasesNeedRenaming, out newColumns));
if (aliasesNeedRenaming)
{
result.OutputColumnsRenamed = true;
result.OutputColumns = newColumns;
}
}
else
{
result.Select.Append(e.Projection.Accept(this));
}
symbolTable.ExitScope();
selectStatementStack.Pop();
return result;
}
///
/// This method handles record flattening, which works as follows.
/// consider an expression Prop(y, Prop(x, Prop(d, Prop(c, Prop(b, Var(a)))))
/// where a,b,c are joins, d is an extent and x and y are fields.
/// b has been flattened into a, and has its own SELECT statement.
/// c has been flattened into b.
/// d has been flattened into c.
///
/// We visit the instance, so we reach Var(a) first. This gives us a (join)symbol.
/// Symbol(a).b gives us a join symbol, with a SELECT statement i.e. Symbol(b).
/// From this point on , we need to remember Symbol(b) as the source alias,
/// and then try to find the column. So, we use a SymbolPair.
///
/// We have reached the end when the symbol no longer points to a join symbol.
///
///
/// A
public override ISqlFragment Visit(DbPropertyExpression e)
{
SqlBuilder result;
ISqlFragment instanceSql = e.Instance.Accept(this);
// Since the DbVariableReferenceExpression is a proper child of ours, we can reset
// isVarSingle.
DbVariableReferenceExpression VariableReferenceExpression = e.Instance as DbVariableReferenceExpression;
if (VariableReferenceExpression != null)
{
isVarRefSingle = false;
}
// We need to flatten, and have not yet seen the first nested SELECT statement.
JoinSymbol joinSymbol = instanceSql as JoinSymbol;
if (joinSymbol != null)
{
Debug.Assert(joinSymbol.NameToExtent.ContainsKey(e.Property.Name));
if (joinSymbol.IsNestedJoin)
{
return new SymbolPair(joinSymbol, joinSymbol.NameToExtent[e.Property.Name]);
}
else
{
return joinSymbol.NameToExtent[e.Property.Name];
}
}
// ---------------------------------------
// We have seen the first nested SELECT statement, but not the column.
SymbolPair symbolPair = instanceSql as SymbolPair;
if (symbolPair != null)
{
JoinSymbol columnJoinSymbol = symbolPair.Column as JoinSymbol;
if (columnJoinSymbol != null)
{
symbolPair.Column = columnJoinSymbol.NameToExtent[e.Property.Name];
return symbolPair;
}
else
{
// symbolPair.Column has the base extent.
// we need the symbol for the column, since it might have been renamed
// when handling a JOIN.
if (symbolPair.Column.Columns.ContainsKey(e.Property.Name))
{
result = new SqlBuilder();
result.Append(symbolPair.Source);
result.Append(".");
result.Append(symbolPair.Column.Columns[e.Property.Name]);
return result;
}
}
}
// ---------------------------------------
result = new SqlBuilder();
result.Append(instanceSql);
result.Append(".");
Symbol symbol = instanceSql as Symbol;
if (symbol != null && symbol.OutputColumnsRenamed)
{
result.Append(symbol.Columns[e.Property.Name]);
}
else
{
// At this point the column name cannot be renamed, so we do
// not use a symbol.
result.Append(QuoteIdentifier(e.Property.Name));
}
return result;
}
///
/// Any(input, x) => Exists(Filter(input,x))
/// All(input, x) => Not Exists(Filter(input, not(x))
///
///
///
///
///
///
///
///
///
///
///
/// For Sql9 it translates to:
/// SELECT Y.x1, Y.x2, ..., Y.xn
/// FROM (
/// SELECT X.x1, X.x2, ..., X.xn, row_number() OVER (ORDER BY sk1, sk2, ...) AS [row_number]
/// FROM input as X
/// ) as Y
/// WHERE Y.[row_number] > count
/// ORDER BY sk1, sk2, ...
///
///
/// A
public override ISqlFragment Visit(DbSkipExpression e)
{
Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbSkipExpression when translating for SQL Server 2000.");
Debug.Assert(e.Count is DbConstantExpression || e.Count is DbParameterReferenceExpression, "DbSkipExpression.Count is of invalid expression type");
//Visit the input
Symbol fromSymbol;
SqlSelectStatement input = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
// Skip is not compatible with anything that OrderBy is not compatible with, as well as with distinct
if (!IsCompatible(input, e.ExpressionKind))
{
input = CreateNewSelectStatement(input, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
}
selectStatementStack.Push(input);
symbolTable.EnterScope();
AddFromSymbol(input, e.Input.VariableName, fromSymbol);
//Add the default columns
Debug.Assert(input.Select.IsEmpty);
List inputColumns = AddDefaultColumns(input);
input.Select.Append(", row_number() OVER (ORDER BY ");
AddSortKeys(input.Select, e.SortOrder);
input.Select.Append(") AS ");
Symbol row_numberSymbol = new Symbol("row_number", IntegerType);
input.Select.Append(row_numberSymbol);
//The inner statement is complete, its scopes need not be valid any longer
symbolTable.ExitScope();
selectStatementStack.Pop();
//Create the resulting statement
//See CreateNewSelectStatement, it is very similar
//Future Enhancement ([....]): Refactor to avoid duplication with CreateNewSelectStatement if we
// don't switch to using ExtensionExpression here
SqlSelectStatement result = new SqlSelectStatement();
result.From.Append("( ");
result.From.Append(input);
result.From.AppendLine();
result.From.Append(") ");
//Create a symbol for the input
Symbol resultFromSymbol = null;
if (input.FromExtents.Count == 1)
{
JoinSymbol oldJoinSymbol = input.FromExtents[0] as JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: input.FromExtents will not do, since it might
// just be an alias of joinSymbol, and we want an actual JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(e.Input.VariableName, e.Input.VariableType, oldJoinSymbol.ExtentList);
// This indicates that the oldStatement is a blocking scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = inputColumns;
newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
resultFromSymbol = newJoinSymbol;
}
}
if (resultFromSymbol == null)
{
// This is just a simple extent/SqlSelectStatement,
// and we can get the column list from the type.
resultFromSymbol = new Symbol(e.Input.VariableName, e.Input.VariableType);
}
//Add the ORDER BY part
selectStatementStack.Push(result);
symbolTable.EnterScope();
AddFromSymbol(result, e.Input.VariableName, resultFromSymbol);
//Add the predicate
result.Where.Append(resultFromSymbol);
result.Where.Append(".");
result.Where.Append(row_numberSymbol);
result.Where.Append(" > ");
result.Where.Append(HandleCountExpression(e.Count));
AddSortKeys(result.OrderBy, e.SortOrder);
symbolTable.ExitScope();
selectStatementStack.Pop();
return result;
}
///
///
///
/// A
///
///
///
///
/// A
public override ISqlFragment Visit(DbTreatExpression e)
{
throw EntityUtil.NotSupported();
}
///
/// This code is shared by
///
///
/// This method determines whether an extent from an outer scope(free variable)
/// is used in the CurrentSelectStatement.
///
/// An extent in an outer scope, if its symbol is not in the FromExtents
/// of the CurrentSelectStatement.
///
///
/// A
public override ISqlFragment Visit(DbVariableReferenceExpression e)
{
if (isVarRefSingle)
{
throw EntityUtil.NotSupported();
// A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
// This is also checked in GenerateSql(...) at the end of the visiting.
}
isVarRefSingle = true; // This will be reset by DbPropertyExpression or MethodExpression
Symbol result = symbolTable.Lookup(e.VariableName);
if (!CurrentSelectStatement.FromExtents.Contains(result))
{
CurrentSelectStatement.OuterExtents[result] = true;
}
return result;
}
#endregion
#region Visitor Helper Methods
#region 'Visitor' methods - Shared visitors and methods that do most of the visiting
///
/// Aggregates are not visited by the normal visitor walk.
///
/// The aggreate go be translated
/// The translated aggregate argument
///
/// Dump out an expression - optionally wrap it with parantheses if possible
///
///
///
private void ParanthesizeExpressionIfNeeded(DbExpression e, SqlBuilder result)
{
if (IsComplexExpression(e))
{
result.Append("(");
result.Append(e.Accept(this));
result.Append(")");
}
else
{
result.Append(e.Accept(this));
}
}
///
/// Handler for inline binary expressions.
/// Produces left op right.
/// For associative operations does flattening.
/// Puts parenthesis around the arguments if needed.
///
///
///
///
///
///
/// Private handler for comparison expressions - almost identical to VisitBinaryExpression.
/// We special case constants, so that we don't emit unnecessary casts
///
/// the comparison op
/// the left-side expression
/// the right-side expression
///
/// This is called by the relational nodes. It does the following
///
/// - If the input is not a SqlSelectStatement, it assumes that the input
/// is a collection expression, and creates a new SqlSelectStatement
///
///
///
///
///
///
/// A
private SqlSelectStatement VisitInputExpression(DbExpression inputExpression,
string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol)
{
SqlSelectStatement result;
ISqlFragment sqlFragment = inputExpression.Accept(this);
result = sqlFragment as SqlSelectStatement;
if (result == null)
{
result = new SqlSelectStatement();
WrapNonQueryExtent(result, sqlFragment, inputExpression.ExpressionKind);
}
if (result.FromExtents.Count == 0)
{
// input was an extent
fromSymbol = new Symbol(inputVarName, inputVarType);
}
else if (result.FromExtents.Count == 1)
{
// input was Filter/GroupBy/Project/OrderBy
// we are likely to reuse this statement.
fromSymbol = result.FromExtents[0];
}
else
{
// input was a join.
// we are reusing the select statement produced by a Join node
// we need to remove the original extents, and replace them with a
// new extent with just the Join symbol.
JoinSymbol joinSymbol = new JoinSymbol(inputVarName, inputVarType, result.FromExtents);
joinSymbol.FlattenedExtentList = result.AllJoinExtents;
fromSymbol = joinSymbol;
result.FromExtents.Clear();
result.FromExtents.Add(fromSymbol);
}
return result;
}
///
///
///
/// Was the parent a DbNotExpression?
///
/// Translate a NewInstance(Element(X)) expression into
/// "select top(1) * from X"
///
///
/// (e.ResultType);
Debug.Assert(collectionType != null);
bool isScalarElement = TypeSemantics.IsPrimitiveType(collectionType.TypeUsage);
SqlBuilder resultSql = new SqlBuilder();
string separator = "";
// handle empty table
if (e.Arguments.Count == 0)
{
Debug.Assert(isScalarElement);
resultSql.Append(" SELECT CAST(null as ");
resultSql.Append(GetSqlPrimitiveType(collectionType.TypeUsage));
resultSql.Append(") AS X FROM (SELECT 1) AS Y WHERE 1=0");
}
foreach (DbExpression arg in e.Arguments)
{
resultSql.Append(separator);
resultSql.Append(" SELECT ");
resultSql.Append(arg.Accept(this));
// For scalar elements, no alias is appended yet. Add this.
if (isScalarElement)
{
resultSql.Append(" AS X ");
}
separator = " UNION ALL ";
}
return resultSql;
}
///
///
///
/// Was the parent a DbNotExpression?
///
/// This handles the processing of join expressions.
/// The extents on a left spine are flattened, while joins
/// not on the left spine give rise to new nested sub queries.
///
/// Joins work differently from the rest of the visiting, in that
/// the parent (i.e. the join node) creates the SqlSelectStatement
/// for the children to use.
///
/// The "parameter" IsInJoinContext indicates whether a child extent should
/// add its stuff to the existing SqlSelectStatement, or create a new SqlSelectStatement
/// By passing true, we ask the children to add themselves to the parent join,
/// by passing false, we ask the children to create new Select statements for
/// themselves.
///
/// This method is called from
///
///
///
///
/// A
private ISqlFragment VisitJoinExpression(IList inputs, DbExpressionKind joinKind,
string joinString, DbExpression joinCondition)
{
SqlSelectStatement result;
// If the parent is not a join( or says that it is not),
// we should create a new SqlSelectStatement.
// otherwise, we add our child extents to the parent's FROM clause.
if (!IsParentAJoin)
{
result = new SqlSelectStatement();
result.AllJoinExtents = new List();
selectStatementStack.Push(result);
}
else
{
result = CurrentSelectStatement;
}
// Process each of the inputs, and then the joinCondition if it exists.
// It would be nice if we could call VisitInputExpression - that would
// avoid some code duplication
// but the Join postprocessing is messy and prevents this reuse.
symbolTable.EnterScope();
string separator = "";
bool isLeftMostInput = true;
int inputCount = inputs.Count;
for(int idx = 0; idx < inputCount; idx++)
{
DbExpressionBinding input = inputs[idx];
if (separator.Length != 0)
{
result.From.AppendLine();
}
result.From.Append(separator + " ");
// Change this if other conditions are required
// to force the child to produce a nested SqlStatement.
bool needsJoinContext = (input.Expression.ExpressionKind == DbExpressionKind.Scan)
|| (isLeftMostInput &&
(IsJoinExpression(input.Expression)
|| IsApplyExpression(input.Expression)))
;
isParentAJoinStack.Push(needsJoinContext ? true : false);
// if the child reuses our select statement, it will append the from
// symbols to our FromExtents list. So, we need to remember the
// start of the child's entries.
int fromSymbolStart = result.FromExtents.Count;
ISqlFragment fromExtentFragment = input.Expression.Accept(this);
isParentAJoinStack.Pop();
ProcessJoinInputResult(fromExtentFragment, result, input, fromSymbolStart);
separator = joinString;
isLeftMostInput = false;
}
// Visit the on clause/join condition.
switch (joinKind)
{
case DbExpressionKind.FullOuterJoin:
case DbExpressionKind.InnerJoin:
case DbExpressionKind.LeftOuterJoin:
result.From.Append(" ON ");
isParentAJoinStack.Push(false);
result.From.Append(joinCondition.Accept(this));
isParentAJoinStack.Pop();
break;
}
symbolTable.ExitScope();
if (!IsParentAJoin)
{
selectStatementStack.Pop();
}
return result;
}
///
/// This is called from
/// - The same as the parent's - this is treated specially.
/// - A sql select statement, which may need to be completed
/// - An extent - just copy it to the from clause
/// - Anything else (from a collection-valued expression) -
/// unnest and copy it.
///
///
/// If the input was a Join, we need to create a new join symbol,
/// otherwise, we create a normal symbol.
///
/// We then call AddFromSymbol to add the AS clause, and update the symbol table.
///
///
///
/// If the child's result was the same as the parent's, we have to clean up
/// the list of symbols in the FromExtents list, since this contains symbols from
/// the children of both the parent and the child.
/// The happens when the child visited is a Join, and is the leftmost child of
/// the parent.
///
///
///
///
///
private void ProcessJoinInputResult(ISqlFragment fromExtentFragment, SqlSelectStatement result,
DbExpressionBinding input, int fromSymbolStart)
{
Symbol fromSymbol = null;
if (result != fromExtentFragment)
{
// The child has its own select statement, and is not reusing
// our select statement.
// This should look a lot like VisitInputExpression().
SqlSelectStatement sqlSelectStatement = fromExtentFragment as SqlSelectStatement;
if (sqlSelectStatement != null)
{
if (sqlSelectStatement.Select.IsEmpty)
{
List columns = AddDefaultColumns(sqlSelectStatement);
if (IsJoinExpression(input.Expression)
|| IsApplyExpression(input.Expression))
{
List extents = sqlSelectStatement.FromExtents;
JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = columns;
fromSymbol = newJoinSymbol;
}
else
{
// this is a copy of the code in CreateNewSelectStatement.
// if the oldStatement has a join as its input, ...
// clone the join symbol, so that we "reuse" the
// join symbol. Normally, we create a new symbol - see the next block
// of code.
JoinSymbol oldJoinSymbol = sqlSelectStatement.FromExtents[0] as JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: sqlSelectStatement.FromExtents will not do, since it might
// just be an alias of joinSymbol, and we want an actual JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, oldJoinSymbol.ExtentList);
// This indicates that the sqlSelectStatement is a blocking scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = columns;
newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
fromSymbol = newJoinSymbol;
}
else if (sqlSelectStatement.FromExtents[0].OutputColumnsRenamed)
{
fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.FromExtents[0].Columns);
}
}
}
else if (sqlSelectStatement.OutputColumnsRenamed)
{
fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.OutputColumns);
}
result.From.Append(" (");
result.From.Append(sqlSelectStatement);
result.From.Append(" )");
}
else if (input.Expression is DbScanExpression)
{
result.From.Append(fromExtentFragment);
}
else // bracket it
{
WrapNonQueryExtent(result, fromExtentFragment, input.Expression.ExpressionKind);
}
if (fromSymbol == null) // i.e. not a join symbol
{
fromSymbol = new Symbol(input.VariableName, input.VariableType);
}
AddFromSymbol(result, input.VariableName, fromSymbol);
result.AllJoinExtents.Add(fromSymbol);
}
else // result == fromExtentFragment. The child extents have been merged into the parent's.
{
// we are adding extents to the current sql statement via flattening.
// We are replacing the child's extents with a single Join symbol.
// The child's extents are all those following the index fromSymbolStart.
//
List extents = new List();
// We cannot call extents.AddRange, since the is no simple way to
// get the range of symbols fromSymbolStart..result.FromExtents.Count
// from result.FromExtents.
// We copy these symbols to create the JoinSymbol later.
for (int i = fromSymbolStart; i < result.FromExtents.Count; ++i)
{
extents.Add(result.FromExtents[i]);
}
result.FromExtents.RemoveRange(fromSymbolStart, result.FromExtents.Count - fromSymbolStart);
fromSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
result.FromExtents.Add(fromSymbol);
// this Join Symbol does not have its own select statement, so we
// do not set IsNestedJoin
// We do not call AddFromSymbol(), since we do not want to add
// "AS alias" to the FROM clause- it has been done when the extent was added earlier.
symbolTable.Add(input.VariableName, fromSymbol);
}
}
///
/// We assume that this is only called as a child of a Project.
///
/// This replaces
///
///
///
/// A
private ISqlFragment VisitNewInstanceExpression(DbNewInstanceExpression e, bool aliasesNeedRenaming, out Dictionary newColumns)
{
SqlBuilder result = new SqlBuilder();
RowType rowType = e.ResultType.EdmType as RowType;
if (null != rowType)
{
if (aliasesNeedRenaming)
{
newColumns = new Dictionary(e.Arguments.Count);
}
else
{
newColumns = null;
}
ReadOnlyMetadataCollection members = rowType.Properties;
string separator = "";
for(int i = 0; i < e.Arguments.Count; ++i)
{
DbExpression argument = e.Arguments[i];
if (TypeSemantics.IsRowType(argument.ResultType))
{
// We do not support nested records or other complex objects.
throw EntityUtil.NotSupported();
}
EdmProperty member = members[i];
result.Append(separator);
result.AppendLine();
result.Append(argument.Accept(this));
result.Append(" AS ");
if (aliasesNeedRenaming)
{
Symbol column = new Symbol(member.Name, member.TypeUsage);
column.NeedsRenaming = true;
column.NewName = String.Concat("Internal_", member.Name);
result.Append(column);
newColumns.Add(member.Name, column);
}
else
{
result.Append(QuoteIdentifier(member.Name));
}
separator = ", ";
}
}
else
{
//
//
throw EntityUtil.NotSupported();
}
return result;
}
///
/// Handler for set operations
/// It generates left separator right.
/// Only for SQL 8.0 it may need to create a new select statement
/// above the set operation if the left child's output columns got renamed
///
///
///
///
///
/// Determines whether the given function is a store function that
/// requires special handling
///
///
///
/// Determines whether the given function is a canonical function that
/// requires special handling
///
///
///
/// Default handling for functions.
/// Translates them to FunctionName(arg1, arg2, ..., argn)
///
///
///
/// Default handling for functions with a given name.
/// Translates them to FunctionName(arg1, arg2, ..., argn)
///
///
///
///
/// Default handling on function arguments.
/// Appends the list of arguemnts to the given result
/// If the function is niladic it does not append anything,
/// otherwise it appends (arg1, arg2, .., argn)
///
///
///
private void HandleFunctionArgumentsDefault(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.NiladicFunctionsCannotHaveParameters);
}
if (!isNiladicFunction)
{
result.Append("(");
string separator = "";
foreach (DbExpression arg in e.Arguments)
{
result.Append(separator);
result.Append(arg.Accept(this));
separator = ", ";
}
result.Append(")");
}
}
///
/// Handler for functions that need to be translated to different store function based on version
///
///
///
///
///
/// Handler for special build in functions
///
///
///
/// Handler for special canonical functions
///
///
///
/// Dispatches the special function processing to the appropriate handler
///
///
///
/// handlers, 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](this, e);
}
///
/// 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
///
///
/// Whether the arguments should be enclosed in parethesis
///
///
///
///
///
///
///
///
///
/// 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.
///
///
///
///
/// Handler for canonical funcitons for extracting date parts.
/// For example:
/// Year(date) -> DATEPART( year, date)
///
///
///
///
/// Handler for canonical funcitons for GetTotalOffsetMinutes.
/// GetTotalOffsetMinutes(e) --> Datepart(tzoffset, e)
///
///
///
///
/// Handler for turning a canonical function into DATEPART
/// Results in DATEPART(datepart, e)
///
///
///
///
/// Handler for the canonical function CurrentDate
/// For Sql8 and Sql9: CurrentDate() -> GetDate()
/// For Sql10: CurrentDate() -> SysDateTime()
///
///
///
///
/// Handler for the canonical function CurrentUtcDateTime
/// For Sql8 and Sql9: CurrentUtcDateTime() -> GetUtcDate()
/// For Sql10: CurrentUtcDateTime() -> SysUtcDateTime()
///
///
///
///
/// Handler for the canonical function CurrentDateTimeOffset
/// For Sql8 and Sql9: throw
/// For Sql10: CurrentDateTimeOffset() -> SysDateTimeOffset()
///
///
///
///
/// Function rename IndexOf -> CHARINDEX
///
///
///
///
/// Function rename NewGuid -> NEWID
///
///
///
///
/// Function rename Length -> LEN
///
///
///
///
/// Round(numericExpression) -> Round(numericExpression, 0);
///
///
///
///
/// TRIM(string) -> LTRIM(RTRIM(string))
///
///
///
///
/// Function rename ToLower -> LOWER
///
///
///
///
/// Function rename ToUpper -> UPPER
///
///
///
///
/// Writes the function name to the given SqlBuilder.
///
///
///
private 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(QuoteIdentifier(function.NamespaceName));
}
else
{
result.Append(QuoteIdentifier(function.Schema));
}
result.Append(".");
result.Append(QuoteIdentifier(storeFunctionName));
}
}
#endregion
#region Other Helpers
///
///
/// - Add the SQL string for each column to the SELECT clause
/// - Add the column to the list of columns - so that it can
/// become part of the "type" of a JoinSymbol
/// - Check if the column name collides with a previous column added
/// to the same select statement. Flag both the columns for renaming if true.
/// - Add the column to a name lookup dictionary for collision detection.
///
///
/// The select statement that started off as SELECT *
/// The symbol containing the type information for
/// the columns to be added.
/// Columns that have been added to the Select statement.
/// This is created in columnList, Dictionary columnDictionary, ref string separator)
{
JoinSymbol joinSymbol = symbol as JoinSymbol;
if (joinSymbol != null)
{
if (!joinSymbol.IsNestedJoin)
{
// Recurse if the join symbol is a collection of flattened extents
foreach (Symbol sym in joinSymbol.ExtentList)
{
// if sym is ScalarType means we are at base case in the
// recursion and there are not columns to add, just skip
if ((sym.Type == null) || TypeSemantics.IsPrimitiveType(sym.Type))
{
continue;
}
AddColumns(selectStatement, sym, columnList, columnDictionary, ref separator);
}
}
else
{
foreach (Symbol joinColumn in joinSymbol.ColumnList)
{
// we write tableName.columnName
// rather than tableName.columnName as alias
// since the column name is unique (by the way we generate new column names)
//
// We use the symbols for both the table and the column,
// since they are subject to renaming.
selectStatement.Select.Append(separator);
selectStatement.Select.Append(symbol);
selectStatement.Select.Append(".");
selectStatement.Select.Append(joinColumn);
// check for name collisions. If there is,
// flag both the colliding symbols.
if (columnDictionary.ContainsKey(joinColumn.Name))
{
columnDictionary[joinColumn.Name].NeedsRenaming = true; // the original symbol
joinColumn.NeedsRenaming = true; // the current symbol.
}
else
{
columnDictionary[joinColumn.Name] = joinColumn;
}
columnList.Add(joinColumn);
separator = ", ";
}
}
}
else
{
// This is a non-join extent/select statement, and the CQT type has
// the relevant column information.
// The type could be a record type(e.g. Project(...),
// or an entity type ( e.g. EntityExpression(...)
// so, we check whether it is a structuralType.
// Consider an expression of the form J(a, b=P(E))
// The inner P(E) would have been translated to a SQL statement
// We should not use the raw names from the type, but the equivalent
// symbols (they are present in symbol.Columns) if they exist.
//
// We add the new columns to the symbol's columns if they do
// not already exist.
//
// If the symbol represents a SqlStatement with renamed output columns,
// we should use these instead of the rawnames and we should also mark
// this selectStatement as one with renamed columns
if (symbol.OutputColumnsRenamed)
{
selectStatement.OutputColumnsRenamed = true;
selectStatement.OutputColumns = new Dictionary();
}
if ((symbol.Type == null) || TypeSemantics.IsPrimitiveType(symbol.Type))
{
AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, "X");
}
else
{
foreach (EdmProperty property in TypeHelpers.GetProperties(symbol.Type))
{
AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, property.Name);
}
}
}
}
///
/// Helper method for AddColumns. Adds a column with the given column name
/// to the Select list of the given select statement.
///
/// The select statement to whose SELECT part the column should be added
/// The symbol from which the column to be added originated
/// Columns that have been added to the Select statement.
/// This is created in columnList, Dictionary columnDictionary, ref string separator, string columnName)
{
// Since all renaming happens in the second phase
// we lose nothing by setting the next column name index to 0
// many times.
allColumnNames[columnName] = 0;
// Create a new symbol/reuse existing symbol for the column
Symbol columnSymbol;
if (!symbol.Columns.TryGetValue(columnName, out columnSymbol))
{
// we do not care about the types of columns, so we pass null
// when construction the symbol.
columnSymbol = new Symbol(columnName, null);
symbol.Columns.Add(columnName, columnSymbol);
}
selectStatement.Select.Append(separator);
selectStatement.Select.Append(symbol);
selectStatement.Select.Append(".");
if (symbol.OutputColumnsRenamed)
{
selectStatement.Select.Append(columnSymbol);
selectStatement.OutputColumns.Add(columnSymbol.Name, columnSymbol);
}
// We use the actual name before the "AS", the new name goes
// after the AS.
else
{
selectStatement.Select.Append(QuoteIdentifier(columnName));
}
selectStatement.Select.Append(" AS ");
selectStatement.Select.Append(columnSymbol);
// Check for column name collisions.
if (columnDictionary.ContainsKey(columnName))
{
columnDictionary[columnName].NeedsRenaming = true;
columnSymbol.NeedsRenaming = true;
}
else
{
columnDictionary[columnName] = symbol.Columns[columnName];
}
columnList.Add(columnSymbol);
separator = ", ";
}
///
/// Expands Select * to "select the_list_of_columns"
/// If the columns are taken from an extent, they are written as
/// {original_column_name AS Symbol(original_column)} to allow renaming.
///
/// If the columns are taken from a Join, they are written as just
/// {original_column_name}, since there cannot be a name collision.
///
/// We concatenate the columns from each of the inputs to the select statement.
/// Since the inputs may be joins that are flattened, we need to recurse.
/// The inputs are inferred from the symbols in FromExtents.
///
///
/// AddDefaultColumns(SqlSelectStatement selectStatement)
{
// This is the list of columns added in this select statement
// This forms the "type" of the Select statement, if it has to
// be expanded in another SELECT *
List columnList = new List();
// A lookup for the previous set of columns to aid column name
// collision detection.
Dictionary columnDictionary = new Dictionary(StringComparer.OrdinalIgnoreCase);
string separator = "";
// The Select should usually be empty before we are called,
// but we do not mind if it is not.
if (!selectStatement.Select.IsEmpty)
{
separator = ", ";
}
foreach (Symbol symbol in selectStatement.FromExtents)
{
AddColumns(selectStatement, symbol, columnList, columnDictionary, ref separator);
}
return columnList;
}
///
///
///
///
///
private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol)
{
AddFromSymbol(selectStatement, inputVarName, fromSymbol, true);
}
///
/// This method is called after the input to a relational node is visited.
///
/// - The fromSymbol is new i.e. the select statement has just been
/// created, or a join extent has been added.
/// - The fromSymbol is old i.e. we are reusing a select statement.
///
///
/// If we are not reusing the select statement, we have to complete the
/// FROM clause with the alias
///
/// -- if the input was an extent
/// FROM = [SchemaName].[TableName]
/// -- if the input was a Project
/// FROM = (SELECT ... FROM ... WHERE ...)
///
///
/// These become
///
/// -- if the input was an extent
/// FROM = [SchemaName].[TableName] AS alias
/// -- if the input was a Project
/// FROM = (SELECT ... FROM ... WHERE ...) AS alias
///
/// and look like valid FROM clauses.
///
/// Finally, we have to add the alias to the global list of aliases used,
/// and also to the current symbol table.
///
///
/// The alias to be used.
///
///
private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol, bool addToSymbolTable)
{
// the first check is true if this is a new statement
// the second check is true if we are in a join - we do not
// check if we are in a join context.
// We do not want to add "AS alias" if it has been done already
// e.g. when we are reusing the Sql statement.
if (selectStatement.FromExtents.Count == 0 || fromSymbol != selectStatement.FromExtents[0])
{
selectStatement.FromExtents.Add(fromSymbol);
selectStatement.From.Append(" AS ");
selectStatement.From.Append(fromSymbol);
// We have this inside the if statement, since
// we only want to add extents that are actually used.
allExtentNames[fromSymbol.Name] = 0;
}
if (addToSymbolTable)
{
symbolTable.Add(inputVarName, fromSymbol);
}
}
///
/// Translates a list of SortClauses.
/// Used in the translation of OrderBy
///
/// The SqlBuilder to which the sort keys should be appended
///
private void AddSortKeys(SqlBuilder orderByClause, IList sortKeys)
{
string separator = "";
foreach (DbSortClause sortClause in sortKeys)
{
orderByClause.Append(separator);
orderByClause.Append(sortClause.Expression.Accept(this));
// Bug 431021: COLLATE clause must precede ASC/DESC
Debug.Assert(sortClause.Collation != null);
if (!String.IsNullOrEmpty(sortClause.Collation))
{
orderByClause.Append(" COLLATE ");
orderByClause.Append(sortClause.Collation);
}
orderByClause.Append(sortClause.Ascending ? " ASC" : " DESC");
separator = ", ";
}
}
///
///
///
///
///
///
///
/// This is called after a relational node's input has been visited, and the
/// input's sql statement cannot be reused.
///
///
///
///
///
/// A new select statement, with the old one as the from clause.
private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
string inputVarName, TypeUsage inputVarType, bool finalizeOldStatement, out Symbol fromSymbol)
{
fromSymbol = null;
// Finalize the old statement
if (finalizeOldStatement && oldStatement.Select.IsEmpty)
{
List columns = AddDefaultColumns(oldStatement);
// Thid could not have been called from a join node.
Debug.Assert(oldStatement.FromExtents.Count == 1);
// if the oldStatement has a join as its input, ...
// clone the join symbol, so that we "reuse" the
// join symbol. Normally, we create a new symbol - see the next block
// of code.
JoinSymbol oldJoinSymbol = oldStatement.FromExtents[0] as JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: oldStatement.FromExtents will not do, since it might
// just be an alias of joinSymbol, and we want an actual JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(inputVarName, inputVarType, oldJoinSymbol.ExtentList);
// This indicates that the oldStatement is a blocking scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = columns;
newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
fromSymbol = newJoinSymbol;
}
}
if (fromSymbol == null)
{
if (oldStatement.OutputColumnsRenamed)
{
fromSymbol = new Symbol(inputVarName, inputVarType, oldStatement.OutputColumns);
}
else
{
// This is just a simple extent/SqlSelectStatement,
// and we can get the column list from the type.
fromSymbol = new Symbol(inputVarName, inputVarType);
}
}
// Observe that the following looks like the body of Visit(ExtentExpression).
SqlSelectStatement selectStatement = new SqlSelectStatement();
selectStatement.From.Append("( ");
selectStatement.From.Append(oldStatement);
selectStatement.From.AppendLine();
selectStatement.From.Append(") ");
return selectStatement;
}
///
/// Before we embed a string literal in a SQL string, we should
/// convert all ' to '', and enclose the whole string in single quotes.
///
///
///
/// The escaped sql string.
private static string EscapeSingleQuote(string s, bool isUnicode)
{
return (isUnicode ? "N'" : "'") + s.Replace("'", "''") + "'";
}
///
/// Returns the sql primitive/native type name.
/// It will include size, precision or scale depending on type information present in the
/// type facets
///
///
///
/// Handles the expression represending DbLimitExpression.Limit and DbSkipExpression.Count.
/// If it is a constant expression, it simply does to string thus avoiding casting it to the specific value
/// (which would be done if
///
///
/// This is used to determine if a particular expression is an Apply operation.
/// This is only the case when the DbExpressionKind is CrossApply or OuterApply.
///
///
///
/// This is used to determine if a particular expression is a Join operation.
/// This is true for DbCrossJoinExpression and DbJoinExpression, the
/// latter of which may have one of several different ExpressionKinds.
///
///
///
/// This is used to determine if a calling expression needs to place
/// round brackets around the translation of the expression e.
///
/// Constants, parameters and properties do not require brackets,
/// everything else does.
///
///
/// true, if the expression needs brackets
private static bool IsComplexExpression(DbExpression e)
{
switch (e.ExpressionKind)
{
case DbExpressionKind.Constant:
case DbExpressionKind.ParameterReference:
case DbExpressionKind.Property:
return false;
default:
return true;
}
}
///
/// Determine if the owner expression can add its unique sql to the input's
/// SqlSelectStatement
///
/// The SqlSelectStatement of the input to the relational node.
/// The kind of the expression node(not the input's)
///
/// We use the normal box quotes for SQL server. We do not deal with ANSI quotes
/// i.e. double quotes.
///
///
///
/// Simply calls
///
///
/// This is called from
/// -- originally {expression}
/// -- change that to
/// SELECT *
/// FROM {expression} as c
///
///
/// DbLimitExpression needs to start the statement but not add the default columns
///
///
///
/// (e.ResultType).TypeUsage;
break;
}
result = VisitInputExpression(e, inputVarName, type, out fromSymbol);
AddFromSymbol(result, inputVarName, fromSymbol);
symbolTable.ExitScope();
break;
}
if (addDefaultColumns && result.Select.IsEmpty)
{
AddDefaultColumns(result);
}
return result;
}
///
/// This method is called by
///
///
/// This is passed from
/// If the sql fragment for an input expression is not a SqlSelect statement
/// or other acceptable form (e.g. an extent as a SqlBuilder), we need
/// to wrap it in a form acceptable in a FROM clause. These are
/// primarily the
///
/// - The set operation expressions - union all, intersect, except
/// - TVFs, which are conceptually similar to tables
///
///
///
///
///
private static void WrapNonQueryExtent(SqlSelectStatement result, ISqlFragment sqlFragment, DbExpressionKind expressionKind)
{
switch (expressionKind)
{
case DbExpressionKind.Function:
// TVF
result.From.Append(sqlFragment);
break;
default:
result.From.Append(" (");
result.From.Append(sqlFragment);
result.From.Append(")");
break;
}
}
///
/// Is this a Store function (ie) does it have the builtinAttribute specified and it is not a canonical function?
///
///
///
/// Helper method for the Group By visitor
/// Returns true if at least one of the aggregates in the given list
/// has an argument that is not a
/// - If an expression being aggregated contains an outer reference, then that outer
/// reference must be the only column referenced in the expression (SQLBUDT #488741)
/// - Sql Server cannot perform an aggregate function on an expression containing
/// an aggregate or a subquery. (SQLBUDT #504600)
///
/// Potentially, we could furhter optimize this.
///
///
///
/// aggregates, string inputVarRefName)
{
foreach (DbAggregate aggregate in aggregates)
{
Debug.Assert(aggregate.Arguments.Count == 1);
if (GroupByAggregateNeedsInnerQuery(aggregate.Arguments[0], inputVarRefName))
{
return true;
}
}
return false;
}
///
/// Returns true if the given expression is not a
///
///
///
/// Helper method for the Group By visitor
/// Returns true if at least one of the expressions in the given list
/// is not
///
///
/// keys, string inputVarRefName)
{
foreach (DbExpression key in keys)
{
if (GroupByKeyNeedsInnerQuery(key, inputVarRefName))
{
return true;
}
}
return false;
}
///
/// Returns true if the given expression is not
///
///
///
/// Helper method for processing Group By keys and aggregates.
/// Returns true if the given expression is not a
///
///
///
///
/// determines if the function requires the return type be enforeced by use of a cast expression
///
///
///
/// Throws not supported exception if the server is pre-katmai
///
///
private void AssertKatmaiOrNewer(PrimitiveTypeKind primitiveTypeKind)
{
if (this.IsPreKatmai)
{
throw EntityUtil.NotSupported(System.Data.Entity.Strings.PrimitiveTypeNotSupportedPriorSql10(primitiveTypeKind));
}
}
///
/// Throws not supported exception if the server is pre-katmai
///
///
private void AssertKatmaiOrNewer(DbFunctionExpression e)
{
if (this.IsPreKatmai)
{
throw EntityUtil.NotSupported(System.Data.Entity.Strings.CanonicalFunctionNotSupportedPriorSql10(e.Function.Name));
}
}
#endregion
#endregion
}
}
// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
Link Menu
This book is available now!
Buy at Amazon US or
Buy at Amazon UK
- StaticSiteMapProvider.cs
- SimpleTableProvider.cs
- Site.cs
- MetadataArtifactLoader.cs
- TemplateControlCodeDomTreeGenerator.cs
- SqlConnectionString.cs
- UriParserTemplates.cs
- SchemaImporterExtension.cs
- DESCryptoServiceProvider.cs
- SqlFileStream.cs
- BindingValueChangedEventArgs.cs
- StatementContext.cs
- EventlogProvider.cs
- KnownTypes.cs
- nulltextcontainer.cs
- OdbcHandle.cs
- FaultPropagationRecord.cs
- XmlProcessingInstruction.cs
- NetStream.cs
- WebPartDescriptionCollection.cs
- CustomSignedXml.cs
- Vector3D.cs
- MatrixStack.cs
- ByteRangeDownloader.cs
- Activator.cs
- SqlXml.cs
- PropertyMetadata.cs
- AsymmetricSignatureFormatter.cs
- ProfilePropertySettings.cs
- Tablet.cs
- EditorPartChrome.cs
- PasswordPropertyTextAttribute.cs
- HttpListenerElement.cs
- CharacterMetrics.cs
- HitTestFilterBehavior.cs
- PolicyFactory.cs
- DataGridViewRowHeaderCell.cs
- QuestionEventArgs.cs
- AutomationElement.cs
- SqlCaseSimplifier.cs
- InheritablePropertyChangeInfo.cs
- ContainerControl.cs
- TrackingExtract.cs
- UnsafeNativeMethods.cs
- ObjectPropertyMapping.cs
- ProcessManager.cs
- MethodInfo.cs
- ScrollBar.cs
- LingerOption.cs
- CustomAttributeSerializer.cs
- LabelEditEvent.cs
- UITypeEditor.cs
- XmlDictionaryReader.cs
- RC2.cs
- MaskedTextProvider.cs
- AnnotationResourceChangedEventArgs.cs
- RenderData.cs
- AmbientProperties.cs
- ComboBox.cs
- DataStreamFromComStream.cs
- ToolStripItemImageRenderEventArgs.cs
- DynamicRendererThreadManager.cs
- DataBinding.cs
- ReaderOutput.cs
- WebRequestModulesSection.cs
- _AcceptOverlappedAsyncResult.cs
- PeerCollaboration.cs
- SqlTypeSystemProvider.cs
- ClientSection.cs
- DocumentViewerBaseAutomationPeer.cs
- XmlExtensionFunction.cs
- DataRelationCollection.cs
- ControlCollection.cs
- XmlMtomWriter.cs
- HelpFileFileNameEditor.cs
- FlowDocument.cs
- DateRangeEvent.cs
- GeneratedCodeAttribute.cs
- WebPartCancelEventArgs.cs
- _NetRes.cs
- Query.cs
- QueryableDataSourceView.cs
- WinEventHandler.cs
- DiscoveryInnerClientAdhocCD1.cs
- StsCommunicationException.cs
- FormViewUpdateEventArgs.cs
- MenuStrip.cs
- TypeExtensions.cs
- DataGridViewElement.cs
- IdnElement.cs
- MouseButton.cs
- ActivityDesignerHighlighter.cs
- SystemResourceHost.cs
- OracleColumn.cs
- ListViewItemCollectionEditor.cs
- FontSourceCollection.cs
- EntityConnectionStringBuilderItem.cs
- IndexedEnumerable.cs
- Border.cs
- SmtpSection.cs