1 //---------------------------------------------------------------------
2 // <copyright file="SqlGenerator.cs" company="Microsoft">
3 // Copyright (c) Microsoft Corporation. All rights reserved.
8 //---------------------------------------------------------------------
10 namespace System.Data.SqlClient.SqlGen
13 using System.Collections.Generic;
14 using System.Data.Common;
15 using System.Data.Common.CommandTrees;
16 using System.Data.Common.CommandTrees.ExpressionBuilder.Spatial;
17 using System.Data.Common.Utils;
18 using System.Data.Metadata.Edm;
19 using System.Data.Spatial;
20 using System.Data.SqlClient;
21 using System.Data.SqlClient.Internal;
22 using System.Diagnostics;
23 using System.Globalization;
28 /// Translates the command object into a SQL string that can be executed on
29 /// SQL Server 2000 and SQL Server 2005.
32 /// The translation is implemented as a visitor <see cref="DbExpressionVisitor{T}"/>
33 /// over the query tree. It makes a single pass over the tree, collecting the sql
34 /// fragments for the various nodes in the tree <see cref="ISqlFragment"/>.
36 /// The major operations are
37 /// <list type="bullet">
38 /// <item>Select statement minimization. Multiple nodes in the query tree
39 /// that can be part of a single SQL select statement are merged. e.g. a
40 /// Filter node that is the input of a Project node can typically share the
41 /// same SQL statement.</item>
42 /// <item>Alpha-renaming. As a result of the statement minimization above, there
43 /// could be name collisions when using correlated subqueries
58 /// The first Filter, Project and Extent will share the same SQL select statement.
59 /// The alias for the Project i.e. b, will be replaced with c.
60 /// If the alias c for the Filter within the exists clause is not renamed,
61 /// we will get <c>c.x = c.x</c>, which is incorrect.
62 /// Instead, the alias c within the second filter should be renamed to c1, to give
63 /// <c>c.x = c1.x</c> i.e. b is renamed to c, and c is renamed to c1.
66 /// <item>Join flattening. In the query tree, a list of join nodes is typically
67 /// represented as a tree of Join nodes, each with 2 children. e.g.
70 /// a = Join(InnerJoin
71 /// b = Join(CrossJoin
79 /// If translated directly, this will be translated to
81 /// FROM ( SELECT c.*, d.*
83 /// CROSS JOIN foo as d) as b
84 /// INNER JOIN foo as e on b.x' = e.x
86 /// It would be better to translate this as
89 /// CROSS JOIN foo as d
90 /// INNER JOIN foo as e on c.x = e.x
92 /// This allows the optimizer to choose an appropriate join ordering for evaluation.
95 /// <item>Select * and column renaming. In the example above, we noticed that
96 /// in some cases we add <c>SELECT * FROM ...</c> to complete the SQL
97 /// statement. i.e. there is no explicit PROJECT list.
98 /// In this case, we enumerate all the columns available in the FROM clause
99 /// This is particularly problematic in the case of Join trees, since the columns
100 /// from the extents joined might have the same name - this is illegal. To solve
101 /// this problem, we will have to rename columns if they are part of a SELECT *
102 /// for a JOIN node - we do not need renaming in any other situation.
103 /// <see cref="SqlGenerator.AddDefaultColumns"/>.
109 /// When rows or columns are renamed, we produce names that are unique globally
110 /// with respect to the query. The names are derived from the original names,
111 /// with an integer as a suffix. e.g. CustomerId will be renamed to CustomerId1,
114 /// Since the names generated are globally unique, they will not conflict when the
115 /// columns of a JOIN SELECT statement are joined with another JOIN.
120 /// Record flattening.
121 /// SQL server does not have the concept of records. However, a join statement
122 /// produces records. We have to flatten the record accesses into a simple
123 /// <c>alias.column</c> form. <see cref="SqlGenerator.Visit(DbPropertyExpression)"/>
127 /// Building the SQL.
128 /// There are 2 phases
129 /// <list type="numbered">
130 /// <item>Traverse the tree, producing a sql builder <see cref="SqlBuilder"/></item>
131 /// <item>Write the SqlBuilder into a string, renaming the aliases and columns
132 /// as needed.</item>
135 /// In the first phase, we traverse the tree. We cannot generate the SQL string
136 /// right away, since
137 /// <list type="bullet">
138 /// <item>The WHERE clause has to be visited before the from clause.</item>
139 /// <item>extent aliases and column aliases need to be renamed. To minimize
140 /// renaming collisions, all the names used must be known, before any renaming
141 /// choice is made.</item>
143 /// To defer the renaming choices, we use symbols <see cref="Symbol"/>. These
144 /// are renamed in the second phase.
146 /// Since visitor methods cannot transfer information to child nodes through
147 /// parameters, we use some global stacks,
148 /// <list type="bullet">
149 /// <item>A stack for the current SQL select statement. This is needed by
150 /// <see cref="SqlGenerator.Visit(DbVariableReferenceExpression)"/> to create a
151 /// list of free variables used by a select statement. This is needed for
154 /// <item>A stack for the join context. When visiting an extent,
155 /// we need to know whether we are inside a join or not. If we are inside
156 /// a join, we do not create a new SELECT statement.</item>
162 /// To enable renaming, we maintain
163 /// <list type="bullet">
164 /// <item>The set of all extent aliases used.</item>
165 /// <item>The set of all parameter names.</item>
166 /// <item>The set of all column names that may need to be renamed.</item>
169 /// Finally, we have a symbol table to lookup variable references. All references
170 /// to the same extent have the same symbol.
174 /// Sql select statement sharing.
176 /// Each of the relational operator nodes
177 /// <list type="bullet">
178 /// <item>Project</item>
179 /// <item>Filter</item>
180 /// <item>GroupBy</item>
181 /// <item>Sort/OrderBy</item>
183 /// can add its non-input (e.g. project, predicate, sort order etc.) to
184 /// the SQL statement for the input, or create a new SQL statement.
185 /// If it chooses to reuse the input's SQL statement, we play the following
186 /// symbol table trick to accomplish renaming. The symbol table entry for
187 /// the alias of the current node points to the symbol for the input in
188 /// the input's SQL statement.
197 /// The Extent node creates a new SqlSelectStatement. This is added to the
198 /// symbol table by the Filter as {c, Symbol(c)}. Thus, <c>c.x</c> is resolved to
199 /// <c>Symbol(c).x</c>.
200 /// Looking at the project node, we add {b, Symbol(c)} to the symbol table if the
201 /// SQL statement is reused, and {b, Symbol(b)}, if there is no reuse.
203 /// Thus, <c>b.x</c> is resolved to <c>Symbol(c).x</c> if there is reuse, and to
204 /// <c>Symbol(b).x</c> if there is no reuse.
208 internal sealed class SqlGenerator : DbExpressionVisitor<ISqlFragment>
210 #region Visitor parameter stacks
212 /// Every relational node has to pass its SELECT statement to its children
213 /// This allows them (DbVariableReferenceExpression eventually) to update the list of
214 /// outer extents (free variables) used by this select statement.
216 private Stack<SqlSelectStatement> selectStatementStack;
219 /// The top of the stack
221 private SqlSelectStatement CurrentSelectStatement
223 // There is always something on the stack, so we can always Peek.
224 get { return selectStatementStack.Peek(); }
228 /// Nested joins and extents need to know whether they should create
229 /// a new Select statement, or reuse the parent's. This flag
230 /// indicates whether the parent is a join or not.
232 private Stack<bool> isParentAJoinStack;
235 /// Determine if the parent is a join.
237 private bool IsParentAJoin
239 // There might be no entry on the stack if a Join node has never
240 // been seen, so we return false in that case.
241 get { return isParentAJoinStack.Count == 0 ? false : isParentAJoinStack.Peek(); }
246 #region Global lists and state
247 Dictionary<string, int> allExtentNames;
248 internal Dictionary<string, int> AllExtentNames
250 get { return allExtentNames; }
253 // For each column name, we store the last integer suffix that
254 // was added to produce a unique column name. This speeds up
255 // the creation of the next unique name for this column name.
256 Dictionary<string, int> allColumnNames;
257 internal Dictionary<string, int> AllColumnNames
259 get { return allColumnNames; }
262 readonly SymbolTable symbolTable = new SymbolTable();
265 /// VariableReferenceExpressions are allowed only as children of DbPropertyExpression
266 /// or MethodExpression. The cheapest way to ensure this is to set the following
267 /// property in DbVariableReferenceExpression and reset it in the allowed parent expressions.
269 private bool isVarRefSingle;
271 private readonly SymbolUsageManager optionalColumnUsageManager = new SymbolUsageManager();
274 /// Maintain the list of (string) DbParameterReferenceExpressions that should be compensated, viz.
275 /// forced to non-unicode format. A parameter is added to the list if it is being compared to a
276 /// non-unicode store column and none of its other usages in the query tree, disqualify it
277 /// (For example - if the parameter is also being projected or compared to a unicode column)
278 /// The goal of the compensation is to have the store index picked up by the server.
279 /// String constants are also compensated and the decision is local, unlike parameters.
281 private Dictionary<string, bool> _candidateParametersToForceNonUnicode = new Dictionary<string, bool>();
283 /// Set and reset in DbComparisonExpression and DbLikeExpression visit methods. Maintains
284 /// global state information that the children of these nodes are candidates for compensation.
286 private bool _forceNonUnicode = false;
289 /// Set when it is is safe to ignore the unicode/non-unicode aspect. See <see cref="VisitIsNullExpression"/> for an example.
291 private bool _ignoreForceNonUnicodeFlag;
297 const byte defaultDecimalPrecision = 18;
298 static private readonly char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
300 // Define lists of functions that take string arugments and return strings.
301 static private readonly Set<string> _canonicalStringFunctionsOneArg = new Set<string>(new string[] { "Edm.Trim" ,
310 StringComparer.Ordinal).MakeReadOnly();
312 static private readonly Set<string> _canonicalStringFunctionsTwoArgs = new Set<string>(new string[] { "Edm.Concat" },
313 StringComparer.Ordinal).MakeReadOnly();
315 static private readonly Set<string> _canonicalStringFunctionsThreeArgs = new Set<string>(new string[] { "Edm.Replace" },
316 StringComparer.Ordinal).MakeReadOnly();
318 static private readonly Set<string> _storeStringFunctionsOneArg = new Set<string>(new string[] { "SqlServer.RTRIM" ,
322 "SqlServer.SUBSTRING" ,
325 "SqlServer.REVERSE" },
326 StringComparer.Ordinal).MakeReadOnly();
328 static private readonly Set<string> _storeStringFunctionsThreeArgs = new Set<string>(new string[] { "SqlServer.REPLACE" },
329 StringComparer.Ordinal).MakeReadOnly();
333 #region SqlVersion, Metadata, ...
336 /// The current SQL Server version
338 private SqlVersion sqlVersion;
339 internal SqlVersion SqlVersion
341 get { return sqlVersion; }
343 internal bool IsPreKatmai
345 get { return SqlVersionUtils.IsPreKatmai(this.SqlVersion); }
348 private MetadataWorkspace metadataWorkspace;
349 internal MetadataWorkspace Workspace
351 get { return metadataWorkspace; }
354 private TypeUsage integerType = null;
355 internal TypeUsage IntegerType
359 if (integerType == null)
361 integerType = GetPrimitiveType(PrimitiveTypeKind.Int64);
367 private string defaultStringTypeName;
368 internal string DefaultStringTypeName
372 if (defaultStringTypeName == null)
374 defaultStringTypeName = GetSqlPrimitiveType(TypeUsage.CreateStringTypeUsage(this.metadataWorkspace.GetModelPrimitiveType(PrimitiveTypeKind.String), isUnicode: true, isFixedLength: false));
376 return defaultStringTypeName;
380 private StoreItemCollection _storeItemCollection;
381 internal StoreItemCollection StoreItemCollection
383 get { return _storeItemCollection; }
389 /// Basic constructor.
391 /// <param name="sqlVersion">server version</param>
392 private SqlGenerator(SqlVersion sqlVersion)
394 this.sqlVersion = sqlVersion;
400 /// General purpose static function that can be called from System.Data assembly
402 /// <param name="sqlVersion">Server version</param>
403 /// <param name="tree">command tree</param>
404 /// <param name="parameters">Parameters to add to the command tree corresponding
405 /// to constants in the command tree. Used only in ModificationCommandTrees.</param>
406 /// <param name="commandType">CommandType for generated command.</param>
407 /// <returns>The string representing the SQL to be executed.</returns>
408 internal static string GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters, out CommandType commandType, out HashSet<string> paramsToForceNonUnicode)
411 commandType = CommandType.Text;
413 paramsToForceNonUnicode = null;
415 switch (tree.CommandTreeKind)
417 case DbCommandTreeKind.Query:
418 sqlGen = new SqlGenerator(sqlVersion);
419 return sqlGen.GenerateSql((DbQueryCommandTree)tree, out paramsToForceNonUnicode);
421 case DbCommandTreeKind.Insert:
422 return DmlSqlGenerator.GenerateInsertSql((DbInsertCommandTree)tree, sqlVersion, out parameters);
424 case DbCommandTreeKind.Delete:
425 return DmlSqlGenerator.GenerateDeleteSql((DbDeleteCommandTree)tree, sqlVersion, out parameters);
427 case DbCommandTreeKind.Update:
428 return DmlSqlGenerator.GenerateUpdateSql((DbUpdateCommandTree)tree, sqlVersion, out parameters);
430 case DbCommandTreeKind.Function:
431 sqlGen = new SqlGenerator(sqlVersion);
432 return GenerateFunctionSql((DbFunctionCommandTree)tree, out commandType);
435 //We have covered all command tree kinds
436 Debug.Assert(false, "Unknown command tree kind");
442 private static string GenerateFunctionSql(DbFunctionCommandTree tree, out CommandType commandType)
444 Debug.Assert(tree.EdmFunction != null, "DbFunctionCommandTree function cannot be null");
446 EdmFunction function = tree.EdmFunction;
448 if (String.IsNullOrEmpty(function.CommandTextAttribute))
450 // build a quoted description of the function
451 commandType = CommandType.StoredProcedure;
453 // if the schema name is not explicitly given, it is assumed to be the metadata namespace
454 string schemaName = String.IsNullOrEmpty(function.Schema) ?
455 function.NamespaceName : function.Schema;
457 // if the function store name is not explicitly given, it is assumed to be the metadata name
458 string functionName = String.IsNullOrEmpty(function.StoreFunctionNameAttribute) ?
459 function.Name : function.StoreFunctionNameAttribute;
461 // quote elements of function text
462 string quotedSchemaName = QuoteIdentifier(schemaName);
463 string quotedFunctionName = QuoteIdentifier(functionName);
466 const string schemaSeparator = ".";
468 // concatenate elements of function text
469 string quotedFunctionText = quotedSchemaName + schemaSeparator + quotedFunctionName;
471 return quotedFunctionText;
475 // if the user has specified the command text, pass it through verbatim and choose CommandType.Text
476 commandType = CommandType.Text;
477 return function.CommandTextAttribute;
482 #region Driver Methods
484 /// Translate a command tree to a SQL string.
486 /// The input tree could be translated to either a SQL SELECT statement
487 /// or a SELECT expression. This choice is made based on the return type
488 /// of the expression
489 /// CollectionType => select statement
490 /// non collection type => select expression
492 /// <param name="tree"></param>
493 /// <returns>The string representing the SQL to be executed.</returns>
494 private string GenerateSql(DbQueryCommandTree tree, out HashSet<string> paramsToForceNonUnicode)
496 Debug.Assert(tree.Query != null, "DbQueryCommandTree Query cannot be null");
498 DbQueryCommandTree targetTree = tree;
500 //If we are on Sql 8.0 rewrite the tree if needed
501 if (this.SqlVersion == SqlVersion.Sql8)
503 if (Sql8ConformanceChecker.NeedsRewrite(tree.Query))
505 targetTree = Sql8ExpressionRewriter.Rewrite(tree);
509 this.metadataWorkspace = targetTree.MetadataWorkspace;
510 // needed in Private Type Helpers section bellow
511 _storeItemCollection = (StoreItemCollection)this.Workspace.GetItemCollection(DataSpace.SSpace);
513 selectStatementStack = new Stack<SqlSelectStatement>();
514 isParentAJoinStack = new Stack<bool>();
516 allExtentNames = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
517 allColumnNames = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
519 // Literals will not be converted to parameters.
522 if (TypeSemantics.IsCollectionType(targetTree.Query.ResultType))
524 SqlSelectStatement sqlStatement = VisitExpressionEnsureSqlStatement(targetTree.Query);
525 Debug.Assert(sqlStatement != null, "The outer most sql statment is null");
526 sqlStatement.IsTopMost = true;
527 result = sqlStatement;
532 SqlBuilder sqlBuilder = new SqlBuilder();
533 sqlBuilder.Append("SELECT ");
534 sqlBuilder.Append(targetTree.Query.Accept(this));
541 throw EntityUtil.NotSupported();
542 // A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
545 // Check that the parameter stacks are not leaking.
546 Debug.Assert(selectStatementStack.Count == 0);
547 Debug.Assert(isParentAJoinStack.Count == 0);
549 paramsToForceNonUnicode = new HashSet<string>(_candidateParametersToForceNonUnicode.Where(p => p.Value).Select(q => q.Key).ToList());
551 return WriteSql(result);
555 /// Convert the SQL fragments to a string.
556 /// We have to setup the Stream for writing.
558 /// <param name="sqlStatement"></param>
559 /// <returns>A string representing the SQL to be executed.</returns>
560 private string WriteSql(ISqlFragment sqlStatement)
562 StringBuilder builder = new StringBuilder(1024);
563 using (SqlWriter writer = new SqlWriter(builder))
565 sqlStatement.WriteSql(writer, this);
568 return builder.ToString();
572 #region IExpressionVisitor Members
575 /// Translate(left) AND Translate(right)
577 /// <param name="e"></param>
578 /// <returns>A <see cref="SqlBuilder"/>.</returns>
579 public override ISqlFragment Visit(DbAndExpression e)
581 return VisitBinaryExpression(" AND ", DbExpressionKind.And, e.Left, e.Right);
585 /// An apply is just like a join, so it shares the common join processing
586 /// in <see cref="VisitJoinExpression"/>
588 /// <param name="e"></param>
589 /// <returns>A <see cref="SqlSelectStatement"/>.</returns>
590 public override ISqlFragment Visit(DbApplyExpression e)
592 Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbApplyExpression when translating for SQL Server 2000.");
594 List<DbExpressionBinding> inputs = new List<DbExpressionBinding>();
599 switch (e.ExpressionKind)
601 case DbExpressionKind.CrossApply:
602 joinString = "CROSS APPLY";
605 case DbExpressionKind.OuterApply:
606 joinString = "OUTER APPLY";
611 throw EntityUtil.InvalidOperation(String.Empty);
614 // The join condition does not exist in this case, so we use null.
615 // WE do not have a on clause, so we use JoinType.CrossJoin.
616 return VisitJoinExpression(inputs, DbExpressionKind.CrossJoin, joinString, null);
620 /// For binary expressions, we delegate to <see cref="VisitBinaryExpression"/>.
621 /// We handle the other expressions directly.
623 /// <param name="e"></param>
624 /// <returns>A <see cref="SqlBuilder"/></returns>
625 public override ISqlFragment Visit(DbArithmeticExpression e)
629 switch (e.ExpressionKind)
631 case DbExpressionKind.Divide:
632 result = VisitBinaryExpression(" / ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
634 case DbExpressionKind.Minus:
635 result = VisitBinaryExpression(" - ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
637 case DbExpressionKind.Modulo:
638 result = VisitBinaryExpression(" % ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
640 case DbExpressionKind.Multiply:
641 result = VisitBinaryExpression(" * ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
643 case DbExpressionKind.Plus:
644 result = VisitBinaryExpression(" + ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
647 case DbExpressionKind.UnaryMinus:
648 result = new SqlBuilder();
649 result.Append(" -(");
650 result.Append(e.Arguments[0].Accept(this));
656 throw EntityUtil.InvalidOperation(String.Empty);
663 /// If the ELSE clause is null, we do not write it out.
665 /// <param name="e"></param>
666 /// <returns>A <see cref="SqlBuilder"/></returns>
667 public override ISqlFragment Visit(DbCaseExpression e)
669 SqlBuilder result = new SqlBuilder();
671 Debug.Assert(e.When.Count == e.Then.Count);
673 result.Append("CASE");
674 for (int i = 0; i < e.When.Count; ++i)
676 result.Append(" WHEN (");
677 result.Append(e.When[i].Accept(this));
678 result.Append(") THEN ");
679 result.Append(e.Then[i].Accept(this));
683 if (e.Else != null && !(e.Else is DbNullExpression))
685 result.Append(" ELSE ");
686 result.Append(e.Else.Accept(this));
689 result.Append(" END");
697 /// <param name="e"></param>
698 /// <returns></returns>
699 public override ISqlFragment Visit(DbCastExpression e)
701 if (Helper.IsSpatialType(e.ResultType))
703 return e.Argument.Accept(this);
707 SqlBuilder result = new SqlBuilder();
708 result.Append(" CAST( ");
709 result.Append(e.Argument.Accept(this));
710 result.Append(" AS ");
711 result.Append(GetSqlPrimitiveType(e.ResultType));
719 /// The parser generates Not(Equals(...)) for <>.
721 /// <param name="e"></param>
722 /// <returns>A <see cref="SqlBuilder"/>.</returns>
723 public override ISqlFragment Visit(DbComparisonExpression e)
727 // Don't try to optimize the comparison, if one of the sides isn't of type string.
728 if (TypeSemantics.IsPrimitiveType(e.Left.ResultType, PrimitiveTypeKind.String))
730 // Check if the Comparison expression is a candidate for compensation in order to optimize store performance.
731 _forceNonUnicode = CheckIfForceNonUnicodeRequired(e);
734 switch (e.ExpressionKind)
736 case DbExpressionKind.Equals:
737 result = VisitComparisonExpression(" = ", e.Left, e.Right);
739 case DbExpressionKind.LessThan:
740 result = VisitComparisonExpression(" < ", e.Left, e.Right);
742 case DbExpressionKind.LessThanOrEquals:
743 result = VisitComparisonExpression(" <= ", e.Left, e.Right);
745 case DbExpressionKind.GreaterThan:
746 result = VisitComparisonExpression(" > ", e.Left, e.Right);
748 case DbExpressionKind.GreaterThanOrEquals:
749 result = VisitComparisonExpression(" >= ", e.Left, e.Right);
751 // The parser does not generate the expression kind below.
752 case DbExpressionKind.NotEquals:
753 result = VisitComparisonExpression(" <> ", e.Left, e.Right);
757 Debug.Assert(false); // The constructor should have prevented this
758 throw EntityUtil.InvalidOperation(String.Empty);
761 // Reset the force non-unicode, global state variable if it was set by CheckIfForceNonUnicodeRequired().
762 _forceNonUnicode = false;
768 /// Checks if the arguments of the input Comparison or Like expression are candidates
769 /// for compensation. If yes, sets global state variable - _forceNonUnicode.
771 /// <param name="e">DBComparisonExpression or DbLikeExpression</param>
772 private bool CheckIfForceNonUnicodeRequired(DbExpression e)
774 if (_forceNonUnicode)
777 throw EntityUtil.NotSupported();
779 return MatchPatternForForcingNonUnicode(e);
783 /// The grammar for the pattern that we are looking for is -
785 /// Pattern := Target OP Source | Source OP Target
786 /// OP := Like | Comparison
787 /// Source := Non-unicode DbPropertyExpression
788 /// Target := Target FUNC Target | DbConstantExpression | DBParameterExpression
789 /// FUNC := CONCAT | RTRIM | LTRIM | TRIM | SUBSTRING | TOLOWER | TOUPPER | REVERSE | REPLACE
791 /// <param name="e"></param>
792 /// <returns></returns>
793 private bool MatchPatternForForcingNonUnicode(DbExpression e)
795 if (e.ExpressionKind == DbExpressionKind.Like)
797 DbLikeExpression likeExpr = (DbLikeExpression)e;
798 return MatchSourcePatternForForcingNonUnicode(likeExpr.Argument) &&
799 MatchTargetPatternForForcingNonUnicode(likeExpr.Pattern) &&
800 MatchTargetPatternForForcingNonUnicode(likeExpr.Escape);
803 // DBExpressionKind is any of (Equals, LessThan, LessThanOrEquals, GreaterThan, GreaterThanOrEquals, NotEquals)
804 DbComparisonExpression compareExpr = (DbComparisonExpression)e;
805 DbExpression left = compareExpr.Left;
806 DbExpression right = compareExpr.Right;
808 return (MatchSourcePatternForForcingNonUnicode(left) && MatchTargetPatternForForcingNonUnicode(right)) ||
809 (MatchSourcePatternForForcingNonUnicode(right) && MatchTargetPatternForForcingNonUnicode(left));
813 /// Matches the non-terminal symbol "target" in above grammar.
815 /// <param name="expr"></param>
816 /// <returns></returns>
817 private bool MatchTargetPatternForForcingNonUnicode(DbExpression expr)
819 if (IsConstParamOrNullExpressionUnicodeNotSpecified(expr))
824 if (expr.ExpressionKind == DbExpressionKind.Function)
826 DbFunctionExpression functionExpr = (DbFunctionExpression)expr;
827 EdmFunction function = functionExpr.Function;
829 if (!TypeHelpers.IsCanonicalFunction(function) && !SqlFunctionCallHandler.IsStoreFunction(function))
834 // All string arguments to the function must be candidates to match target pattern.
835 String functionFullName = function.FullName;
836 bool ifQualifies = false;
838 if (_canonicalStringFunctionsOneArg.Contains(functionFullName) ||
839 _storeStringFunctionsOneArg.Contains(functionFullName))
841 ifQualifies = MatchTargetPatternForForcingNonUnicode(functionExpr.Arguments[0]);
843 else if (_canonicalStringFunctionsTwoArgs.Contains(functionFullName))
845 ifQualifies = ( MatchTargetPatternForForcingNonUnicode(functionExpr.Arguments[0]) &&
846 MatchTargetPatternForForcingNonUnicode(functionExpr.Arguments[1]) );
848 else if (_canonicalStringFunctionsThreeArgs.Contains(functionFullName) ||
849 _storeStringFunctionsThreeArgs.Contains(functionFullName))
851 ifQualifies = ( MatchTargetPatternForForcingNonUnicode(functionExpr.Arguments[0]) &&
852 MatchTargetPatternForForcingNonUnicode(functionExpr.Arguments[1]) &&
853 MatchTargetPatternForForcingNonUnicode(functionExpr.Arguments[2]) );
862 /// Determines if the expression represents a non-unicode string column(char/varchar store type)
864 /// <param name="argument"></param>
865 /// <returns></returns>
866 private bool MatchSourcePatternForForcingNonUnicode(DbExpression argument)
869 return (argument.ExpressionKind == DbExpressionKind.Property) &&
870 (TypeHelpers.TryGetIsUnicode(argument.ResultType, out isUnicode)) &&
875 /// Determines if the expression represents a string constant or parameter with the facet, unicode=null.
877 /// <param name="argument"></param>
878 /// <returns></returns>
879 private bool IsConstParamOrNullExpressionUnicodeNotSpecified(DbExpression argument)
882 DbExpressionKind expressionKind = argument.ExpressionKind;
883 TypeUsage type = argument.ResultType;
885 if (!TypeSemantics.IsPrimitiveType(type, PrimitiveTypeKind.String))
890 return (expressionKind == DbExpressionKind.Constant ||
891 expressionKind == DbExpressionKind.ParameterReference ||
892 expressionKind == DbExpressionKind.Null) &&
893 (!TypeHelpers.TryGetBooleanFacetValue(type, DbProviderManifest.UnicodeFacetName, out isUnicode));
897 /// Generate tsql for a constant. Avoid the explicit cast (if possible) when
898 /// the isCastOptional parameter is set
900 /// <param name="e">the constant expression</param>
901 /// <param name="isCastOptional">can we avoid the CAST</param>
902 /// <returns>the tsql fragment</returns>
903 private ISqlFragment VisitConstant(DbConstantExpression e, bool isCastOptional)
905 // Constants will be sent to the store as part of the generated TSQL, not as parameters
906 SqlBuilder result = new SqlBuilder();
908 TypeUsage resultType = e.ResultType;
909 PrimitiveTypeKind typeKind;
910 // Model Types can be (at the time of this implementation):
911 // Binary, Boolean, Byte, Date, DateTime, DateTimeOffset, Decimal, Double, Guid, Int16, Int32, Int64, Single, String, Time
912 if (TypeHelpers.TryGetPrimitiveTypeKind(resultType, out typeKind))
916 case PrimitiveTypeKind.Int32:
917 // default sql server type for integral values.
918 result.Append(e.Value.ToString());
921 case PrimitiveTypeKind.Binary:
922 result.Append(" 0x");
923 result.Append(ByteArrayToBinaryString((Byte[])e.Value));
927 case PrimitiveTypeKind.Boolean:
928 // Bugs 450277, 430294: Need to preserve the boolean type-ness of
929 // this value for round-trippability
930 WrapWithCastIfNeeded(!isCastOptional, (bool)e.Value ? "1" : "0", "bit", result);
933 case PrimitiveTypeKind.Byte:
934 WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "tinyint", result);
937 case PrimitiveTypeKind.DateTime:
938 result.Append("convert(");
939 result.Append(this.IsPreKatmai ? "datetime" : "datetime2");
941 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 */));
942 result.Append(", 121)");
945 case PrimitiveTypeKind.Time:
946 AssertKatmaiOrNewer(typeKind);
947 result.Append("convert(");
948 result.Append(e.ResultType.EdmType.Name);
950 result.Append(EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */));
951 result.Append(", 121)");
954 case PrimitiveTypeKind.DateTimeOffset:
955 AssertKatmaiOrNewer(typeKind);
956 result.Append("convert(");
957 result.Append(e.ResultType.EdmType.Name);
959 result.Append(EscapeSingleQuote(((System.DateTimeOffset)e.Value).ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture), false /* IsUnicode */));
960 result.Append(", 121)");
963 case PrimitiveTypeKind.Decimal:
964 string strDecimal = ((Decimal)e.Value).ToString(CultureInfo.InvariantCulture);
965 // if the decimal value has no decimal part, cast as decimal to preserve type
966 // if the number has precision > int64 max precision, it will be handled as decimal by sql server
967 // and does not need cast. if precision is lest then 20, then cast using Max(literal precision, sql default precision)
968 bool needsCast = -1 == strDecimal.IndexOf('.') && (strDecimal.TrimStart(new char[] { '-' }).Length < 20);
970 byte precision = (byte)Math.Max((Byte)strDecimal.Length, defaultDecimalPrecision);
971 Debug.Assert(precision > 0, "Precision must be greater than zero");
973 string decimalType = "decimal(" + precision.ToString(CultureInfo.InvariantCulture) + ")";
975 WrapWithCastIfNeeded(needsCast, strDecimal, decimalType, result);
978 case PrimitiveTypeKind.Double:
980 double doubleValue = (Double)e.Value;
981 AssertValidDouble(doubleValue);
982 WrapWithCastIfNeeded(true, doubleValue.ToString("R", CultureInfo.InvariantCulture), "float(53)", result);
986 case PrimitiveTypeKind.Geography:
987 AppendSpatialConstant(result, ((DbGeography)e.Value).AsSpatialValue());
990 case PrimitiveTypeKind.Geometry:
991 AppendSpatialConstant(result, ((DbGeometry)e.Value).AsSpatialValue());
994 case PrimitiveTypeKind.Guid:
995 WrapWithCastIfNeeded(true, EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */), "uniqueidentifier", result);
998 case PrimitiveTypeKind.Int16:
999 WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "smallint", result);
1002 case PrimitiveTypeKind.Int64:
1003 WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "bigint", result);
1006 case PrimitiveTypeKind.Single:
1008 float singleValue = (float)e.Value;
1009 AssertValidSingle(singleValue);
1010 WrapWithCastIfNeeded(true, singleValue.ToString("R", CultureInfo.InvariantCulture), "real", result);
1014 case PrimitiveTypeKind.String:
1017 if (!TypeHelpers.TryGetIsUnicode(e.ResultType, out isUnicode))
1019 // If the unicode facet is not specified, if needed force non-unicode, otherwise default to unicode.
1020 isUnicode = !_forceNonUnicode;
1022 result.Append(EscapeSingleQuote(e.Value as string, isUnicode));
1026 // all known scalar types should been handled already.
1027 throw EntityUtil.NotSupported(System.Data.Entity.Strings.NoStoreTypeForEdmType(resultType.Identity, ((PrimitiveType)(resultType.EdmType)).PrimitiveTypeKind));
1032 throw EntityUtil.NotSupported();
1033 //if/when Enum types are supported, then handle appropriately, for now is not a valid type for constants.
1034 //result.Append(e.Value.ToString());
1040 private void AppendSpatialConstant(SqlBuilder result, IDbSpatialValue spatialValue)
1042 // Spatial constants are represented by calls to a static constructor function. The attempt is made to extract an
1043 // appropriate representation from the value (which may not implement the required methods). If an SRID value and
1044 // a text, binary or GML representation of the spatial value can be extracted, the the corresponding function call
1045 // expression is built and processed.
1046 DbFunctionExpression functionExpression = null;
1047 int? srid = spatialValue.CoordinateSystemId;
1050 string wellKnownText = spatialValue.WellKnownText;
1051 if (wellKnownText != null)
1053 functionExpression = (spatialValue.IsGeography ? SpatialEdmFunctions.GeographyFromText(wellKnownText, srid.Value) : SpatialEdmFunctions.GeometryFromText(wellKnownText, srid.Value));
1057 byte[] wellKnownBinary = spatialValue.WellKnownBinary;
1058 if (wellKnownBinary != null)
1060 functionExpression = (spatialValue.IsGeography ? SpatialEdmFunctions.GeographyFromBinary(wellKnownBinary, srid.Value) : SpatialEdmFunctions.GeometryFromBinary(wellKnownBinary, srid.Value));
1064 string gmlString = spatialValue.GmlString;
1065 if (gmlString != null)
1067 functionExpression = (spatialValue.IsGeography ? SpatialEdmFunctions.GeographyFromGml(gmlString, srid.Value) : SpatialEdmFunctions.GeometryFromGml(gmlString, srid.Value));
1073 if (functionExpression != null)
1075 result.Append(SqlFunctionCallHandler.GenerateFunctionCallSql(this, functionExpression));
1079 throw spatialValue.NotSqlCompatible();
1084 /// Helper method for <see cref="VisitConstant"/>
1086 /// <param name="value">A double value</param>
1087 /// <exception cref="NotSupportedException">If a value of positive or negative infinity, or <see cref="double.NaN"/> is specified</exception>
1088 private static void AssertValidDouble(double value)
1090 if (double.IsNaN(value))
1092 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_TypedNaNNotSupported(Enum.GetName(typeof(PrimitiveTypeKind), PrimitiveTypeKind.Double)));
1094 else if(double.IsPositiveInfinity(value))
1096 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_TypedPositiveInfinityNotSupported(Enum.GetName(typeof(PrimitiveTypeKind), PrimitiveTypeKind.Double), typeof(Double).Name));
1098 else if(double.IsNegativeInfinity(value))
1100 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_TypedNegativeInfinityNotSupported(Enum.GetName(typeof(PrimitiveTypeKind), PrimitiveTypeKind.Double), typeof(Double).Name));
1105 /// Helper method for <see cref="VisitConstant"/>
1107 /// <param name="value">A single value</param>
1108 /// <exception cref="NotSupportedException">If a value of positive or negative infinity, or <see cref="float.NaN"/> is specified</exception>
1109 private static void AssertValidSingle(float value)
1111 if (float.IsNaN(value))
1113 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_TypedNaNNotSupported(Enum.GetName(typeof(PrimitiveTypeKind), PrimitiveTypeKind.Single)));
1115 else if(float.IsPositiveInfinity(value))
1117 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_TypedPositiveInfinityNotSupported(Enum.GetName(typeof(PrimitiveTypeKind), PrimitiveTypeKind.Single), typeof(Single).Name));
1119 else if(float.IsNegativeInfinity(value))
1121 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_TypedNegativeInfinityNotSupported(Enum.GetName(typeof(PrimitiveTypeKind), PrimitiveTypeKind.Single), typeof(Single).Name));
1126 /// Helper function for <see cref="VisitConstant"/>
1127 /// Appends the given constant value to the result either 'as is' or wrapped with a cast to the given type.
1129 /// <param name="cast"></param>
1130 /// <param name="value"></param>
1131 /// <param name="typeName"></param>
1132 /// <param name="result"></param>
1133 private static void WrapWithCastIfNeeded(bool cast, string value, string typeName, SqlBuilder result)
1137 result.Append(value);
1141 result.Append("cast(");
1142 result.Append(value);
1143 result.Append(" as ");
1144 result.Append(typeName);
1150 /// We do not pass constants as parameters.
1152 /// <param name="e"></param>
1153 /// <returns>A <see cref="SqlBuilder"/>. Strings are wrapped in single
1154 /// quotes and escaped. Numbers are written literally.</returns>
1155 public override ISqlFragment Visit(DbConstantExpression e)
1157 return VisitConstant(e, false /* isCastOptional */);
1163 /// <param name="e"></param>
1164 /// <returns></returns>
1165 public override ISqlFragment Visit(DbDerefExpression e)
1167 throw EntityUtil.NotSupported();
1171 /// The DISTINCT has to be added to the beginning of SqlSelectStatement.Select,
1172 /// but it might be too late for that. So, we use a flag on SqlSelectStatement
1173 /// instead, and add the "DISTINCT" in the second phase.
1175 /// <param name="e"></param>
1176 /// <returns>A <see cref="SqlSelectStatement"/></returns>
1177 public override ISqlFragment Visit(DbDistinctExpression e)
1179 SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument);
1181 if (!IsCompatible(result, e.ExpressionKind))
1184 TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
1185 result = CreateNewSelectStatement(result, "distinct", inputType, out fromSymbol);
1186 AddFromSymbol(result, "distinct", fromSymbol, false);
1189 result.Select.IsDistinct = true;
1194 /// An element expression returns a scalar - so it is translated to
1197 /// <param name="e"></param>
1198 /// <returns></returns>
1199 public override ISqlFragment Visit(DbElementExpression e)
1201 // ISSUE: What happens if the DbElementExpression is used as an input expression?
1202 // i.e. adding the '(' might not be right in all cases.
1203 SqlBuilder result = new SqlBuilder();
1205 result.Append(VisitExpressionEnsureSqlStatement(e.Argument));
1212 /// <see cref="Visit(DbUnionAllExpression)"/>
1214 /// <param name="e"></param>
1215 /// <returns></returns>
1216 public override ISqlFragment Visit(DbExceptExpression e)
1218 Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbExceptExpression when translating for SQL Server 2000.");
1220 return VisitSetOpExpression(e.Left, e.Right, "EXCEPT");
1224 /// Only concrete expression types will be visited.
1226 /// <param name="e"></param>
1227 /// <returns></returns>
1228 public override ISqlFragment Visit(DbExpression e)
1230 throw EntityUtil.InvalidOperation(String.Empty);
1236 /// <param name="e"></param>
1237 /// <returns>If we are in a Join context, returns a <see cref="SqlBuilder"/>
1238 /// with the extent name, otherwise, a new <see cref="SqlSelectStatement"/>
1239 /// with the From field set.</returns>
1240 public override ISqlFragment Visit(DbScanExpression e)
1242 EntitySetBase target = e.Target;
1244 // ISSUE: Should we just return a string all the time, and let
1245 // VisitInputExpression create the SqlSelectStatement?
1249 SqlBuilder result = new SqlBuilder();
1250 result.Append(GetTargetTSql(target));
1256 SqlSelectStatement result = new SqlSelectStatement();
1257 result.From.Append(GetTargetTSql(target));
1264 /// Gets escaped TSql identifier describing this entity set.
1266 /// <returns></returns>
1267 internal static string GetTargetTSql(EntitySetBase entitySetBase)
1269 if (null == entitySetBase.CachedProviderSql)
1271 if (null == entitySetBase.DefiningQuery)
1273 // construct escaped T-SQL referencing entity set
1274 StringBuilder builder = new StringBuilder(50);
1275 if (!string.IsNullOrEmpty(entitySetBase.Schema))
1277 builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Schema));
1278 builder.Append(".");
1282 builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.EntityContainer.Name));
1283 builder.Append(".");
1286 if (!string.IsNullOrEmpty(entitySetBase.Table))
1288 builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Table));
1292 builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Name));
1294 entitySetBase.CachedProviderSql = builder.ToString();
1298 entitySetBase.CachedProviderSql = "(" + entitySetBase.DefiningQuery + ")";
1301 return entitySetBase.CachedProviderSql;
1305 /// The bodies of <see cref="Visit(DbFilterExpression)"/>, <see cref="Visit(DbGroupByExpression)"/>,
1306 /// <see cref="Visit(DbProjectExpression)"/>, <see cref="Visit(DbSortExpression)"/> are similar.
1307 /// Each does the following.
1308 /// <list type="number">
1309 /// <item> Visit the input expression</item>
1310 /// <item> Determine if the input's SQL statement can be reused, or a new
1311 /// one must be created.</item>
1312 /// <item>Create a new symbol table scope</item>
1313 /// <item>Push the Sql statement onto a stack, so that children can
1314 /// update the free variable list.</item>
1315 /// <item>Visit the non-input expression.</item>
1316 /// <item>Cleanup</item>
1319 /// <param name="e"></param>
1320 /// <returns>A <see cref="SqlSelectStatement"/></returns>
1321 public override ISqlFragment Visit(DbFilterExpression e)
1323 return VisitFilterExpression(e.Input, e.Predicate, false);
1327 /// Lambda functions are not supported.
1328 /// The functions supported are:
1329 /// <list type="number">
1330 /// <item>Canonical Functions - We recognize these by their dataspace, it is DataSpace.CSpace</item>
1331 /// <item>Store Functions - We recognize these by the BuiltInAttribute and not being Canonical</item>
1332 /// <item>User-defined Functions - All the rest</item>
1334 /// We handle Canonical and Store functions the same way: If they are in the list of functions
1335 /// that need special handling, we invoke the appropriate handler, otherwise we translate them to
1336 /// FunctionName(arg1, arg2, ..., argn).
1337 /// We translate user-defined functions to NamespaceName.FunctionName(arg1, arg2, ..., argn).
1339 /// <param name="e"></param>
1340 /// <returns>A <see cref="SqlBuilder"/></returns>
1341 public override ISqlFragment Visit(DbFunctionExpression e)
1343 return SqlFunctionCallHandler.GenerateFunctionCallSql(this, e);
1346 public override ISqlFragment Visit(DbLambdaExpression expression)
1348 throw EntityUtil.NotSupported();
1354 /// <param name="e"></param>
1355 /// <returns></returns>
1356 public override ISqlFragment Visit(DbEntityRefExpression e)
1358 throw EntityUtil.NotSupported();
1364 /// <param name="e"></param>
1365 /// <returns></returns>
1366 public override ISqlFragment Visit(DbRefKeyExpression e)
1368 throw EntityUtil.NotSupported();
1372 /// <see cref="Visit(DbFilterExpression)"/> for general details.
1373 /// We modify both the GroupBy and the Select fields of the SqlSelectStatement.
1374 /// GroupBy gets just the keys without aliases,
1375 /// and Select gets the keys and the aggregates with aliases.
1377 /// Sql Server does not support arbitrarily complex expressions inside aggregates,
1378 /// and requires keys to have reference to the input scope,
1379 /// so in some cases we create a nested query in which we alias the arguments to the aggregates.
1380 /// The exact limitations of Sql Server are:
1381 /// <list type="number">
1382 /// <item>If an expression being aggregated contains an outer reference, then that outer
1383 /// reference must be the only column referenced in the expression (SQLBUDT #488741)</item>
1384 /// <item>Sql Server cannot perform an aggregate function on an expression containing
1385 /// an aggregate or a subquery. (SQLBUDT #504600)</item>
1386 ///<item>Sql Server requries each GROUP BY expression (key) to contain at least one column
1387 /// that is not an outer reference. (SQLBUDT #616523)</item>
1388 /// <item>Aggregates on the right side of an APPLY cannot reference columns from the left side.
1389 /// (SQLBUDT #617683) </item>
1392 /// The default translation, without inner query is:
1395 /// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
1396 /// aggf1(aexpr1) AS agg1, .. aggfn(aexprn) AS aggn
1398 /// GROUP BY kexp1, kexp2, .. kexpn
1400 /// When we inject an innner query, the equivalent translation is:
1403 /// key1 AS key1, key2 AS key2, .. keyn AS keys,
1404 /// aggf1(agg1) AS agg1, aggfn(aggn) AS aggn
1407 /// kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
1408 /// aexpr1 AS agg1, .. aexprn AS aggn
1411 /// GROUP BY key1, key2, keyn
1414 /// <param name="e"></param>
1415 /// <returns>A <see cref="SqlSelectStatement"/></returns>
1416 public override ISqlFragment Visit(DbGroupByExpression e)
1419 SqlSelectStatement innerQuery = VisitInputExpression(e.Input.Expression,
1420 e.Input.VariableName, e.Input.VariableType, out fromSymbol);
1422 // GroupBy is compatible with Filter and OrderBy
1423 // but not with Project, GroupBy
1424 if (!IsCompatible(innerQuery, e.ExpressionKind))
1426 innerQuery = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
1429 selectStatementStack.Push(innerQuery);
1430 symbolTable.EnterScope();
1432 AddFromSymbol(innerQuery, e.Input.VariableName, fromSymbol);
1433 // This line is not present for other relational nodes.
1434 symbolTable.Add(e.Input.GroupVariableName, fromSymbol);
1436 // The enumerator is shared by both the keys and the aggregates,
1437 // so, we do not close it in between.
1438 RowType groupByType = TypeHelpers.GetEdmType<RowType>(TypeHelpers.GetEdmType<CollectionType>(e.ResultType).TypeUsage);
1440 //SQL Server does not support arbitrarily complex expressions inside aggregates,
1441 // and requires keys to have reference to the input scope,
1442 // so we check for the specific restrictions and if need we inject an inner query.
1443 bool needsInnerQuery = GroupByAggregatesNeedInnerQuery(e.Aggregates, e.Input.GroupVariableName) || GroupByKeysNeedInnerQuery(e.Keys, e.Input.VariableName);
1445 SqlSelectStatement result;
1446 if (needsInnerQuery)
1448 //Create the inner query
1449 result = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, false, out fromSymbol);
1450 AddFromSymbol(result, e.Input.VariableName, fromSymbol, false);
1454 result = innerQuery;
1457 using (IEnumerator<EdmProperty> members = groupByType.Properties.GetEnumerator())
1460 Debug.Assert(result.Select.IsEmpty);
1462 string separator = "";
1464 foreach (DbExpression key in e.Keys)
1466 EdmProperty member = members.Current;
1467 string alias = QuoteIdentifier(member.Name);
1469 result.GroupBy.Append(separator);
1471 ISqlFragment keySql = key.Accept(this);
1473 if (!needsInnerQuery)
1475 //Default translation: Key AS Alias
1476 result.Select.Append(separator);
1477 result.Select.AppendLine();
1478 result.Select.Append(keySql);
1479 result.Select.Append(" AS ");
1480 result.Select.Append(alias);
1482 result.GroupBy.Append(keySql);
1486 // The inner query contains the default translation Key AS Alias
1487 innerQuery.Select.Append(separator);
1488 innerQuery.Select.AppendLine();
1489 innerQuery.Select.Append(keySql);
1490 innerQuery.Select.Append(" AS ");
1491 innerQuery.Select.Append(alias);
1493 //The outer resulting query projects over the key aliased in the inner query:
1494 // fromSymbol.Alias AS Alias
1495 result.Select.Append(separator);
1496 result.Select.AppendLine();
1497 result.Select.Append(fromSymbol);
1498 result.Select.Append(".");
1499 result.Select.Append(alias);
1500 result.Select.Append(" AS ");
1501 result.Select.Append(alias);
1503 result.GroupBy.Append(alias);
1510 foreach (DbAggregate aggregate in e.Aggregates)
1512 EdmProperty member = members.Current;
1513 string alias = QuoteIdentifier(member.Name);
1515 Debug.Assert(aggregate.Arguments.Count == 1);
1516 ISqlFragment translatedAggregateArgument = aggregate.Arguments[0].Accept(this);
1518 object aggregateArgument;
1520 if (needsInnerQuery)
1522 //In this case the argument to the aggratete is reference to the one projected out by the
1524 SqlBuilder wrappingAggregateArgument = new SqlBuilder();
1525 wrappingAggregateArgument.Append(fromSymbol);
1526 wrappingAggregateArgument.Append(".");
1527 wrappingAggregateArgument.Append(alias);
1528 aggregateArgument = wrappingAggregateArgument;
1530 innerQuery.Select.Append(separator);
1531 innerQuery.Select.AppendLine();
1532 innerQuery.Select.Append(translatedAggregateArgument);
1533 innerQuery.Select.Append(" AS ");
1534 innerQuery.Select.Append(alias);
1538 aggregateArgument = translatedAggregateArgument;
1541 ISqlFragment aggregateResult = VisitAggregate(aggregate, aggregateArgument);
1543 result.Select.Append(separator);
1544 result.Select.AppendLine();
1545 result.Select.Append(aggregateResult);
1546 result.Select.Append(" AS ");
1547 result.Select.Append(alias);
1554 symbolTable.ExitScope();
1555 selectStatementStack.Pop();
1561 /// <see cref="Visit(DbUnionAllExpression)"/>
1563 /// <param name="e"></param>
1564 /// <returns></returns>
1565 public override ISqlFragment Visit(DbIntersectExpression e)
1567 Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbIntersectExpression when translating for SQL Server 2000.");
1569 return VisitSetOpExpression(e.Left, e.Right, "INTERSECT");
1573 /// Not(IsEmpty) has to be handled specially, so we delegate to
1574 /// <see cref="VisitIsEmptyExpression"/>.
1577 /// <param name="e"></param>
1578 /// <returns>A <see cref="SqlBuilder"/>.
1579 /// <code>[NOT] EXISTS( ... )</code>
1581 public override ISqlFragment Visit(DbIsEmptyExpression e)
1583 return VisitIsEmptyExpression(e, false);
1587 /// Not(IsNull) is handled specially, so we delegate to
1588 /// <see cref="VisitIsNullExpression"/>
1590 /// <param name="e"></param>
1591 /// <returns>A <see cref="SqlBuilder"/>
1592 /// <code>IS [NOT] NULL</code>
1594 public override ISqlFragment Visit(DbIsNullExpression e)
1596 return VisitIsNullExpression(e, false);
1600 /// No error is raised if the store cannot support this.
1602 /// <param name="e"></param>
1603 /// <returns>A <see cref="SqlBuilder"/></returns>
1604 public override ISqlFragment Visit(DbIsOfExpression e)
1606 throw EntityUtil.NotSupported();
1610 /// <see cref="VisitJoinExpression"/>
1612 /// <param name="e"></param>
1613 /// <returns>A <see cref="SqlSelectStatement"/>.</returns>
1614 public override ISqlFragment Visit(DbCrossJoinExpression e)
1616 return VisitJoinExpression(e.Inputs, e.ExpressionKind, "CROSS JOIN", null);
1620 /// <see cref="VisitJoinExpression"/>
1622 /// <param name="e"></param>
1623 /// <returns>A <see cref="SqlSelectStatement"/>.</returns>
1624 public override ISqlFragment Visit(DbJoinExpression e)
1626 #region Map join type to a string
1628 switch (e.ExpressionKind)
1630 case DbExpressionKind.FullOuterJoin:
1631 joinString = "FULL OUTER JOIN";
1634 case DbExpressionKind.InnerJoin:
1635 joinString = "INNER JOIN";
1638 case DbExpressionKind.LeftOuterJoin:
1639 joinString = "LEFT OUTER JOIN";
1643 Debug.Assert(false);
1649 List<DbExpressionBinding> inputs = new List<DbExpressionBinding>(2);
1651 inputs.Add(e.Right);
1653 return VisitJoinExpression(inputs, e.ExpressionKind, joinString, e.JoinCondition);
1659 /// <param name="e"></param>
1660 /// <returns>A <see cref="SqlBuilder"/></returns>
1661 public override ISqlFragment Visit(DbLikeExpression e)
1663 // Check if the LIKE expression is a candidate for compensation in order to optimize store performance.
1664 _forceNonUnicode = CheckIfForceNonUnicodeRequired(e);
1666 SqlBuilder result = new SqlBuilder();
1667 result.Append(e.Argument.Accept(this));
1668 result.Append(" LIKE ");
1669 result.Append(e.Pattern.Accept(this));
1671 // if the ESCAPE expression is a DbNullExpression, then that's tantamount to
1672 // not having an ESCAPE at all
1673 if (e.Escape.ExpressionKind != DbExpressionKind.Null)
1675 result.Append(" ESCAPE ");
1676 result.Append(e.Escape.Accept(this));
1679 // Reset the force non-unicode, global state variable if it was set by CheckIfForceNonUnicodeRequired().
1680 _forceNonUnicode = false;
1686 /// Translates to TOP expression. For Sql8, limit can only be a constant expression
1688 /// <param name="e"></param>
1689 /// <returns>A <see cref="SqlBuilder"/></returns>
1690 public override ISqlFragment Visit(DbLimitExpression e)
1692 Debug.Assert(e.Limit is DbConstantExpression || e.Limit is DbParameterReferenceExpression, "DbLimitExpression.Limit is of invalid expression type");
1693 Debug.Assert(!((this.SqlVersion == SqlVersion.Sql8) && (e.Limit is DbParameterReferenceExpression)), "DbLimitExpression.Limit is DbParameterReferenceExpression for SQL Server 2000.");
1695 SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument, false, false);
1698 if (!IsCompatible(result, e.ExpressionKind))
1700 TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
1702 result = CreateNewSelectStatement(result, "top", inputType, out fromSymbol);
1703 AddFromSymbol(result, "top", fromSymbol, false);
1706 ISqlFragment topCount = HandleCountExpression(e.Limit) ;
1708 result.Select.Top = new TopClause(topCount, e.WithTies);
1712 #if METHOD_EXPRESSION
1716 /// <param name="e"></param>
1717 /// <returns>A <see cref="SqlBuilder"/></returns>
1718 public override ISqlFragment Visit(MethodExpression e)
1720 SqlBuilder result = new SqlBuilder();
1722 result.Append(e.Instance.Accept(this));
1724 result.Append(QuoteIdentifier(e.Method.Name));
1727 // Since the VariableReferenceExpression is a proper child of ours, we can reset
1729 VariableReferenceExpression VariableReferenceExpression = e.Instance as VariableReferenceExpression;
1730 if (VariableReferenceExpression != null)
1732 isVarRefSingle = false;
1735 string separator = "";
1736 foreach (Expression argument in e.Arguments)
1738 result.Append(separator);
1739 result.Append(argument.Accept(this));
1749 /// DbNewInstanceExpression is allowed as a child of DbProjectExpression only.
1750 /// If anyone else is the parent, we throw.
1751 /// We also perform special casing for collections - where we could convert
1752 /// them into Unions
1754 /// <see cref="VisitNewInstanceExpression"/> for the actual implementation.
1757 /// <param name="e"></param>
1758 /// <returns></returns>
1759 public override ISqlFragment Visit(DbNewInstanceExpression e)
1761 if (TypeSemantics.IsCollectionType(e.ResultType))
1763 return VisitCollectionConstructor(e);
1765 throw EntityUtil.NotSupported();
1769 /// The Not expression may cause the translation of its child to change.
1770 /// These children are
1771 /// <list type="bullet">
1772 /// <item><see cref="DbNotExpression"/>NOT(Not(x)) becomes x</item>
1773 /// <item><see cref="DbIsEmptyExpression"/>NOT EXISTS becomes EXISTS</item>
1774 /// <item><see cref="DbIsNullExpression"/>IS NULL becomes IS NOT NULL</item>
1775 /// <item><see cref="DbComparisonExpression"/>= becomes<> </item>
1778 /// <param name="e"></param>
1779 /// <returns>A <see cref="SqlBuilder"/></returns>
1780 public override ISqlFragment Visit(DbNotExpression e)
1782 // Flatten Not(Not(x)) to x.
1783 DbNotExpression notExpression = e.Argument as DbNotExpression;
1784 if (notExpression != null)
1786 return notExpression.Argument.Accept(this);
1789 DbIsEmptyExpression isEmptyExpression = e.Argument as DbIsEmptyExpression;
1790 if (isEmptyExpression != null)
1792 return VisitIsEmptyExpression(isEmptyExpression, true);
1795 DbIsNullExpression isNullExpression = e.Argument as DbIsNullExpression;
1796 if (isNullExpression != null)
1798 return VisitIsNullExpression(isNullExpression, true);
1801 DbComparisonExpression comparisonExpression = e.Argument as DbComparisonExpression;
1802 if (comparisonExpression != null)
1804 if (comparisonExpression.ExpressionKind == DbExpressionKind.Equals)
1806 bool forceNonUnicodeLocal = _forceNonUnicode; // Save flag
1807 // Don't try to optimize the comparison, if one of the sides isn't of type string.
1808 if (TypeSemantics.IsPrimitiveType(comparisonExpression.Left.ResultType, PrimitiveTypeKind.String))
1810 _forceNonUnicode = CheckIfForceNonUnicodeRequired(comparisonExpression);
1812 SqlBuilder binaryResult = VisitBinaryExpression(" <> ", DbExpressionKind.NotEquals, comparisonExpression.Left, comparisonExpression.Right);
1813 _forceNonUnicode = forceNonUnicodeLocal; // Reset flag
1814 return binaryResult;
1818 SqlBuilder result = new SqlBuilder();
1819 result.Append(" NOT (");
1820 result.Append(e.Argument.Accept(this));
1828 /// <param name="e"></param>
1829 /// <returns><see cref="SqlBuilder"/></returns>
1830 public override ISqlFragment Visit(DbNullExpression e)
1832 SqlBuilder result = new SqlBuilder();
1834 // always cast nulls - sqlserver doesn't like case expressions where the "then" clause is null
1835 result.Append("CAST(NULL AS ");
1836 TypeUsage type = e.ResultType;
1839 // Use the narrowest type possible - especially for strings where we don't want
1840 // to produce unicode strings always.
1842 Debug.Assert(Helper.IsPrimitiveType(type.EdmType), "Type must be primitive type");
1843 PrimitiveType primitiveType = type.EdmType as PrimitiveType;
1844 switch(primitiveType.PrimitiveTypeKind)
1846 case PrimitiveTypeKind.String:
1847 result.Append("varchar(1)");
1849 case PrimitiveTypeKind.Binary:
1850 result.Append("varbinary(1)");
1853 result.Append(GetSqlPrimitiveType(type));
1864 /// <param name="e"></param>
1865 /// <returns>A <see cref="SqlBuilder"/></returns>
1866 public override ISqlFragment Visit(DbOfTypeExpression e)
1868 throw EntityUtil.NotSupported();
1872 /// Visit a DbOrExpression and consider the subexpressions
1873 /// for whether to generate OR conditions or an IN clause.
1875 /// <param name="e">DbOrExpression to be visited</param>
1876 /// <returns>A <see cref="SqlBuilder"/>Fragment of SQL generated</returns>
1877 /// <seealso cref="Visit(DbAndExpression)"/>
1878 public override ISqlFragment Visit(DbOrExpression e)
1880 ISqlFragment result = null;
1881 if (TryTranslateIntoIn(e, out result))
1886 return VisitBinaryExpression(" OR ", e.ExpressionKind, e.Left, e.Right);
1890 /// Determine if a DbOrExpression can be optimized into one or more IN clauses
1891 /// and generate an ISqlFragment if it is possible.
1893 /// <param name="e">DbOrExpression to attempt translation upon</param>
1894 /// <param name="sqlFragment">Fragment of SQL generated</param>
1895 /// <returns>True if an IN clause is possible and sqlFragment has been generated, false otherwise</returns>
1896 private bool TryTranslateIntoIn(DbOrExpression e, out ISqlFragment sqlFragment)
1898 var map = new KeyToListMap<DbExpression, DbExpression>(KeyFieldExpressionComparer.Singleton);
1899 bool useInClause = HasBuiltMapForIn(e, map) && map.Keys.Count() > 0;
1907 var sqlBuilder = new SqlBuilder();
1908 bool firstKey = true;
1909 foreach (var key in map.Keys)
1911 var values = map.ListForKey(key);
1914 sqlBuilder.Append(" OR ");
1921 var realValues = values.Where(v => v.ExpressionKind != DbExpressionKind.IsNull);
1922 int realValueCount = realValues.Count();
1925 // Should non-unicode be forced over the key or any of the values
1926 // If the key qualifies as a source, we force it over the values that qualify as targets
1927 // If all the values qualify as sources, we force it over the key
1929 bool forceNonUnicodeOnQualifyingValues = false;
1930 bool forceNonUnicodeOnKey = false;
1931 if (TypeSemantics.IsPrimitiveType(key.ResultType, PrimitiveTypeKind.String))
1933 forceNonUnicodeOnQualifyingValues = MatchSourcePatternForForcingNonUnicode(key);
1934 forceNonUnicodeOnKey = !forceNonUnicodeOnQualifyingValues && MatchTargetPatternForForcingNonUnicode(key) && realValues.All(v => MatchSourcePatternForForcingNonUnicode(v));
1937 if (realValueCount == 1)
1939 // When only one value we leave it as an equality test
1940 HandleInKey(sqlBuilder, key, forceNonUnicodeOnKey);
1941 sqlBuilder.Append(" = ");
1942 var value = realValues.First();
1944 HandleInValue(sqlBuilder, value, key.ResultType.EdmType == value.ResultType.EdmType, forceNonUnicodeOnQualifyingValues);
1947 if (realValueCount > 1)
1949 // More than one value becomes an IN
1950 HandleInKey(sqlBuilder, key, forceNonUnicodeOnKey);
1951 sqlBuilder.Append(" IN (");
1953 bool firstValue = true;
1954 foreach(var value in realValues)
1958 sqlBuilder.Append(",");
1964 HandleInValue(sqlBuilder, value, key.ResultType.EdmType == value.ResultType.EdmType, forceNonUnicodeOnQualifyingValues);
1966 sqlBuilder.Append(")");
1969 // Deal with a null for this key
1970 DbIsNullExpression isNullExpression = values.FirstOrDefault(v => v.ExpressionKind == DbExpressionKind.IsNull) as DbIsNullExpression;
1971 if (isNullExpression != null)
1973 if (realValueCount > 0)
1975 sqlBuilder.Append(" OR ");
1977 sqlBuilder.Append(VisitIsNullExpression(isNullExpression, false)); // We never try to build IN with a NOT in the tree
1981 sqlFragment = sqlBuilder;
1985 private void HandleInValue(SqlBuilder sqlBuilder, DbExpression value, bool isSameEdmType, bool forceNonUnicodeOnQualifyingValues)
1987 ForcingNonUnicode(() => ParenthesizeExpressionWithoutRedundantConstantCasts(value, sqlBuilder, isSameEdmType),
1988 forceNonUnicodeOnQualifyingValues && MatchTargetPatternForForcingNonUnicode(value));
1991 private void HandleInKey(SqlBuilder sqlBuilder, DbExpression key, bool forceNonUnicodeOnKey)
1993 ForcingNonUnicode(() => ParenthesizeExpressionIfNeeded(key, sqlBuilder), forceNonUnicodeOnKey);
1996 private void ForcingNonUnicode(Action action, bool forceNonUnicode)
1999 if (forceNonUnicode && !_forceNonUnicode)
2001 _forceNonUnicode = true;
2007 _forceNonUnicode = false;
2011 private void ParenthesizeExpressionWithoutRedundantConstantCasts(DbExpression value, SqlBuilder sqlBuilder, Boolean isSameEdmType)
2013 switch (value.ExpressionKind)
2015 case DbExpressionKind.Constant:
2017 // We don't want unnecessary casts
2018 sqlBuilder.Append(VisitConstant((DbConstantExpression)value, isSameEdmType));
2023 ParenthesizeExpressionIfNeeded(value, sqlBuilder);
2029 #region Helper methods and classes for translation into "In"
2032 /// Required by the KeyToListMap to allow certain DbExpression subclasses to be used as a key
2033 /// which is not normally possible given their lack of Equals and GetHashCode implementations
2034 /// for testing object value equality.
2036 private class KeyFieldExpressionComparer: IEqualityComparer<DbExpression>
2038 internal static readonly KeyFieldExpressionComparer Singleton = new KeyFieldExpressionComparer();
2039 private KeyFieldExpressionComparer() { }
2042 /// Compare two DbExpressions to see if they are equal for the purposes of
2043 /// our key management. We only support DbPropertyExpression, DbParameterReferenceExpression,
2044 /// VariableReferenceExpression and DbCastExpression types. Everything else will fail to
2045 /// be considered equal.
2047 /// <param name="x">First DbExpression to consider for equality</param>
2048 /// <param name="y">Second DbExpression to consider for equality</param>
2049 /// <returns>True if the types are allowed and equal, false otherwise</returns>
2050 public bool Equals(DbExpression x, DbExpression y)
2052 if (x.ExpressionKind != y.ExpressionKind)
2056 switch (x.ExpressionKind)
2058 case DbExpressionKind.Property:
2060 var first = (DbPropertyExpression)x;
2061 var second = (DbPropertyExpression)y;
2062 return first.Property == second.Property && this.Equals(first.Instance, second.Instance);
2064 case DbExpressionKind.ParameterReference:
2066 var first = (DbParameterReferenceExpression)x;
2067 var second = (DbParameterReferenceExpression)y;
2068 return first.ParameterName == second.ParameterName;
2070 case DbExpressionKind.VariableReference:
2074 case DbExpressionKind.Cast:
2076 var first = (DbCastExpression)x;
2077 var second = (DbCastExpression)y;
2078 return first.ResultType == second.ResultType && this.Equals(first.Argument, second.Argument);
2085 /// Calculates a hashcode for a given number of DbExpression subclasses to allow the KeyToListMap
2086 /// to efficiently and reliably locate existing keys.
2088 /// <param name="obj">DbExpression to calculate a hashcode for</param>
2089 /// <returns>Integer containing the hashcode</returns>
2090 public int GetHashCode(DbExpression obj)
2092 switch(obj.ExpressionKind)
2094 case DbExpressionKind.Property:
2096 return ((DbPropertyExpression)obj).Property.GetHashCode();
2098 case DbExpressionKind.ParameterReference:
2100 return ((DbParameterReferenceExpression)obj).ParameterName.GetHashCode() ^ Int32.MaxValue;
2102 case DbExpressionKind.VariableReference:
2104 return ((DbVariableReferenceExpression)obj).VariableName.GetHashCode();
2106 case DbExpressionKind.Cast:
2108 return GetHashCode(((DbCastExpression)obj).Argument);
2112 return obj.GetHashCode();
2119 /// Determines if a DbExpression is a valid key for the purposes of generating an In clause optimization.
2121 /// <param name="e">DbExpression to consider</param>
2122 /// <returns>True if the expression can be used as a key, false otherwise</returns>
2123 private bool IsKeyForIn(DbExpression e)
2125 return (e.ExpressionKind == DbExpressionKind.Property
2126 || e.ExpressionKind == DbExpressionKind.VariableReference
2127 || e.ExpressionKind == DbExpressionKind.ParameterReference);
2131 /// Looks at both sides of a DbBinaryExpression to consider if either side is a valid candidate to
2132 /// be a key and if so adds it to the KeyToListMap as a key with the other side as the value.
2134 /// <param name="e">DbBinaryExpression to consider</param>
2135 /// <param name="values">KeyToListMap to add the sides of the binary expression to</param>
2136 /// <returns>True if the expression was added, false otherwise</returns>
2137 private bool TryAddExpressionForIn(DbBinaryExpression e, KeyToListMap<DbExpression, DbExpression> values)
2139 if (IsKeyForIn(e.Left))
2141 values.Add(e.Left, e.Right);
2144 else if (IsKeyForIn(e.Right))
2146 values.Add(e.Right, e.Left);
2153 /// Attempts to build a KeyToListMap containing valid references and the appropriate value equality
2154 /// tests associated with each so that they can be optimized into IN clauses. Calls itself recursively
2155 /// to consider additional OR branches.
2157 /// <param name="e">DbExpression representing the branch to evaluate</param>
2158 /// <param name="values">KeyToListMap to which to add references and value equality tests encountered</param>
2159 /// <returns>True if this branch contained just equality tests or further OR branches, false otherwise</returns>
2160 private bool HasBuiltMapForIn(DbExpression e, KeyToListMap<DbExpression, DbExpression> values)
2162 switch(e.ExpressionKind)
2164 case DbExpressionKind.Equals:
2166 return TryAddExpressionForIn((DbBinaryExpression)e, values);
2168 case DbExpressionKind.IsNull:
2170 var potentialKey = ((DbIsNullExpression)e).Argument;
2171 if (IsKeyForIn(potentialKey))
2173 values.Add(potentialKey, e);
2178 case DbExpressionKind.Or:
2180 var comparisonExpression = e as DbBinaryExpression;
2181 return HasBuiltMapForIn(comparisonExpression.Left, values) && HasBuiltMapForIn(comparisonExpression.Right, values);
2193 /// This method handles the DBParameterReference expressions. If the parameter is in
2194 /// a part of the tree, which matches our criteria for forcing to non-unicode, then
2195 /// we add it to the list of candidate parameters. If the parameter occurs in a different
2196 /// usage scenario, then disqualify it from being forced to non-unicode.
2198 /// <param name="e"></param>
2199 /// <returns>A <see cref="SqlBuilder"/></returns>
2200 public override ISqlFragment Visit(DbParameterReferenceExpression e)
2202 // Update the dictionary value only if we are not inside a DbIsNullExpression.
2203 if (!_ignoreForceNonUnicodeFlag)
2205 if (!_forceNonUnicode)
2207 //This parameter is being used in a different way than in the force non-unicode pattern. So disqualify it.
2208 _candidateParametersToForceNonUnicode[e.ParameterName] = false;
2210 else if (!_candidateParametersToForceNonUnicode.ContainsKey(e.ParameterName))
2212 //This parameter matches our criteria for forcing to non-unicode. So add to dictionary
2213 _candidateParametersToForceNonUnicode[e.ParameterName] = true;
2217 SqlBuilder result = new SqlBuilder();
2218 // Do not quote this name.
2219 // ISSUE: We are not checking that e.Name has no illegal characters. e.g. space
2220 result.Append("@" + e.ParameterName);
2226 /// <see cref="Visit(DbFilterExpression)"/> for the general ideas.
2228 /// <param name="e"></param>
2229 /// <returns>A <see cref="SqlSelectStatement"/></returns>
2230 /// <seealso cref="Visit(DbFilterExpression)"/>
2231 public override ISqlFragment Visit(DbProjectExpression e)
2234 SqlSelectStatement result = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
2236 //#444002 Aliases need renaming only for Sql8 when there is Order By
2237 bool aliasesNeedRenaming = false;
2239 // Project is compatible with Filter
2240 // but not with Project, GroupBy
2241 if (!IsCompatible(result, e.ExpressionKind))
2243 result = CreateNewSelectStatement(result, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
2245 else if ((this.SqlVersion == SqlVersion.Sql8) && !result.OrderBy.IsEmpty)
2247 aliasesNeedRenaming = true;
2250 selectStatementStack.Push(result);
2251 symbolTable.EnterScope();
2253 AddFromSymbol(result, e.Input.VariableName, fromSymbol);
2255 // Project is the only node that can have DbNewInstanceExpression as a child
2256 // so we have to check it here.
2257 // We call VisitNewInstanceExpression instead of Visit(DbNewInstanceExpression), since
2258 // the latter throws.
2259 DbNewInstanceExpression newInstanceExpression = e.Projection as DbNewInstanceExpression;
2260 if (newInstanceExpression != null)
2262 Dictionary<string, Symbol> newColumns;
2263 result.Select.Append(VisitNewInstanceExpression(newInstanceExpression, aliasesNeedRenaming, out newColumns));
2264 if (aliasesNeedRenaming)
2266 result.OutputColumnsRenamed = true;
2268 result.OutputColumns = newColumns;
2272 result.Select.Append(e.Projection.Accept(this));
2275 symbolTable.ExitScope();
2276 selectStatementStack.Pop();
2282 /// This method handles record flattening, which works as follows.
2283 /// consider an expression <c>Prop(y, Prop(x, Prop(d, Prop(c, Prop(b, Var(a)))))</c>
2284 /// where a,b,c are joins, d is an extent and x and y are fields.
2285 /// b has been flattened into a, and has its own SELECT statement.
2286 /// c has been flattened into b.
2287 /// d has been flattened into c.
2289 /// We visit the instance, so we reach Var(a) first. This gives us a (join)symbol.
2290 /// Symbol(a).b gives us a join symbol, with a SELECT statement i.e. Symbol(b).
2291 /// From this point on , we need to remember Symbol(b) as the source alias,
2292 /// and then try to find the column. So, we use a SymbolPair.
2294 /// We have reached the end when the symbol no longer points to a join symbol.
2296 /// <param name="e"></param>
2297 /// <returns>A <see cref="JoinSymbol"/> if we have not reached the first
2298 /// Join node that has a SELECT statement.
2299 /// A <see cref="SymbolPair"/> if we have seen the JoinNode, and it has
2300 /// a SELECT statement.
2301 /// A <see cref="SqlBuilder"/> with {Input}.propertyName otherwise.
2303 public override ISqlFragment Visit(DbPropertyExpression e)
2307 ISqlFragment instanceSql = e.Instance.Accept(this);
2309 // Since the DbVariableReferenceExpression is a proper child of ours, we can reset
2311 DbVariableReferenceExpression VariableReferenceExpression = e.Instance as DbVariableReferenceExpression;
2312 if (VariableReferenceExpression != null)
2314 isVarRefSingle = false;
2317 // We need to flatten, and have not yet seen the first nested SELECT statement.
2318 JoinSymbol joinSymbol = instanceSql as JoinSymbol;
2319 if (joinSymbol != null)
2321 Debug.Assert(joinSymbol.NameToExtent.ContainsKey(e.Property.Name));
2322 if (joinSymbol.IsNestedJoin)
2324 return new SymbolPair(joinSymbol, joinSymbol.NameToExtent[e.Property.Name]);
2328 return joinSymbol.NameToExtent[e.Property.Name];
2332 // ---------------------------------------
2333 // We have seen the first nested SELECT statement, but not the column.
2334 SymbolPair symbolPair = instanceSql as SymbolPair;
2335 if (symbolPair != null)
2337 JoinSymbol columnJoinSymbol = symbolPair.Column as JoinSymbol;
2338 if (columnJoinSymbol != null)
2340 symbolPair.Column = columnJoinSymbol.NameToExtent[e.Property.Name];
2345 // symbolPair.Column has the base extent.
2346 // we need the symbol for the column, since it might have been renamed
2347 // when handling a JOIN.
2348 if (symbolPair.Column.Columns.ContainsKey(e.Property.Name))
2350 result = new SqlBuilder();
2351 result.Append(symbolPair.Source);
2353 Symbol columnSymbol = symbolPair.Column.Columns[e.Property.Name];
2354 optionalColumnUsageManager.MarkAsUsed(columnSymbol);
2355 result.Append(columnSymbol);
2360 // ---------------------------------------
2362 result = new SqlBuilder();
2363 result.Append(instanceSql);
2366 Symbol symbol = instanceSql as Symbol;
2368 if (symbol != null && symbol.OutputColumns.TryGetValue(e.Property.Name, out colSymbol))
2370 optionalColumnUsageManager.MarkAsUsed(colSymbol);
2371 if (symbol.OutputColumnsRenamed)
2373 result.Append(colSymbol);
2377 result.Append(QuoteIdentifier(e.Property.Name));
2382 // At this point the column name cannot be renamed, so we do
2383 // not use a symbol.
2384 result.Append(QuoteIdentifier(e.Property.Name));
2390 /// Any(input, x) => Exists(Filter(input,x))
2391 /// All(input, x) => Not Exists(Filter(input, not(x))
2393 /// <param name="e"></param>
2394 /// <returns></returns>
2395 public override ISqlFragment Visit(DbQuantifierExpression e)
2397 SqlBuilder result = new SqlBuilder();
2399 bool negatePredicate = (e.ExpressionKind == DbExpressionKind.All);
2400 if (e.ExpressionKind == DbExpressionKind.Any)
2402 result.Append("EXISTS (");
2406 Debug.Assert(e.ExpressionKind == DbExpressionKind.All);
2407 result.Append("NOT EXISTS (");
2410 SqlSelectStatement filter = VisitFilterExpression(e.Input, e.Predicate, negatePredicate);
2411 if (filter.Select.IsEmpty)
2413 AddDefaultColumns(filter);
2416 result.Append(filter);
2425 /// <param name="e"></param>
2426 /// <returns></returns>
2427 public override ISqlFragment Visit(DbRefExpression e)
2429 throw EntityUtil.NotSupported();
2435 /// <param name="e"></param>
2436 /// <returns></returns>
2437 public override ISqlFragment Visit(DbRelationshipNavigationExpression e)
2439 throw EntityUtil.NotSupported();
2443 /// For Sql9 it translates to:
2444 /// SELECT Y.x1, Y.x2, ..., Y.xn
2446 /// SELECT X.x1, X.x2, ..., X.xn, row_number() OVER (ORDER BY sk1, sk2, ...) AS [row_number]
2449 /// WHERE Y.[row_number] > count
2450 /// ORDER BY sk1, sk2, ...
2452 /// <param name="e"></param>
2453 /// <returns>A <see cref="SqlBuilder"/></returns>
2454 public override ISqlFragment Visit(DbSkipExpression e)
2456 Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbSkipExpression when translating for SQL Server 2000.");
2458 Debug.Assert(e.Count is DbConstantExpression || e.Count is DbParameterReferenceExpression, "DbSkipExpression.Count is of invalid expression type");
2462 SqlSelectStatement input = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
2464 // Skip is not compatible with anything that OrderBy is not compatible with, as well as with distinct
2465 if (!IsCompatible(input, e.ExpressionKind))
2467 input = CreateNewSelectStatement(input, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
2470 selectStatementStack.Push(input);
2471 symbolTable.EnterScope();
2473 AddFromSymbol(input, e.Input.VariableName, fromSymbol);
2475 //Add the default columns
2476 Debug.Assert(input.Select.IsEmpty);
2477 List<Symbol> inputColumns = AddDefaultColumns(input);
2479 input.Select.Append("row_number() OVER (ORDER BY ");
2480 AddSortKeys(input.Select, e.SortOrder);
2481 input.Select.Append(") AS ");
2483 string row_numberName = "row_number";
2484 Symbol row_numberSymbol = new Symbol(row_numberName, IntegerType);
2485 if (inputColumns.Any(c => String.Equals(c.Name, row_numberName, StringComparison.OrdinalIgnoreCase)))
2487 row_numberSymbol.NeedsRenaming = true;
2490 input.Select.Append(row_numberSymbol);
2492 //The inner statement is complete, its scopes need not be valid any longer
2493 symbolTable.ExitScope();
2494 selectStatementStack.Pop();
2496 //Create the resulting statement
2497 //See CreateNewSelectStatement, it is very similar
2498 //Future Enhancement ([....]): Refactor to avoid duplication with CreateNewSelectStatement if we
2499 // don't switch to using ExtensionExpression here
2500 SqlSelectStatement result = new SqlSelectStatement();
2501 result.From.Append("( ");
2502 result.From.Append(input);
2503 result.From.AppendLine();
2504 result.From.Append(") ");
2506 //Create a symbol for the input
2507 Symbol resultFromSymbol = null;
2509 if (input.FromExtents.Count == 1)
2511 JoinSymbol oldJoinSymbol = input.FromExtents[0] as JoinSymbol;
2512 if (oldJoinSymbol != null)
2514 // Note: input.FromExtents will not do, since it might
2515 // just be an alias of joinSymbol, and we want an actual JoinSymbol.
2516 JoinSymbol newJoinSymbol = new JoinSymbol(e.Input.VariableName, e.Input.VariableType, oldJoinSymbol.ExtentList);
2517 // This indicates that the oldStatement is a blocking scope
2518 // i.e. it hides/renames extent columns
2519 newJoinSymbol.IsNestedJoin = true;
2520 newJoinSymbol.ColumnList = inputColumns;
2521 newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
2523 resultFromSymbol = newJoinSymbol;
2527 if (resultFromSymbol == null)
2529 // This is just a simple extent/SqlSelectStatement,
2530 // and we can get the column list from the type.
2531 resultFromSymbol = new Symbol(e.Input.VariableName, e.Input.VariableType, input.OutputColumns, false);
2533 //Add the ORDER BY part
2534 selectStatementStack.Push(result);
2535 symbolTable.EnterScope();
2537 AddFromSymbol(result, e.Input.VariableName, resultFromSymbol);
2540 result.Where.Append(resultFromSymbol);
2541 result.Where.Append(".");
2542 result.Where.Append(row_numberSymbol);
2543 result.Where.Append(" > ");
2544 result.Where.Append(HandleCountExpression(e.Count));
2546 AddSortKeys(result.OrderBy, e.SortOrder);
2548 symbolTable.ExitScope();
2549 selectStatementStack.Pop();
2555 /// <see cref="Visit(DbFilterExpression)"/>
2557 /// <param name="e"></param>
2558 /// <returns>A <see cref="SqlSelectStatement"/></returns>
2559 /// <seealso cref="Visit(DbFilterExpression)"/>
2560 public override ISqlFragment Visit(DbSortExpression e)
2563 SqlSelectStatement result = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
2565 // OrderBy is compatible with Filter
2567 if (!IsCompatible(result, e.ExpressionKind))
2569 result = CreateNewSelectStatement(result, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
2572 selectStatementStack.Push(result);
2573 symbolTable.EnterScope();
2575 AddFromSymbol(result, e.Input.VariableName, fromSymbol);
2577 AddSortKeys(result.OrderBy, e.SortOrder);
2579 symbolTable.ExitScope();
2580 selectStatementStack.Pop();
2588 /// <param name="e"></param>
2589 /// <returns>A <see cref="SqlBuilder"/></returns>
2590 public override ISqlFragment Visit(DbTreatExpression e)
2592 throw EntityUtil.NotSupported();
2596 /// This code is shared by <see cref="Visit(DbExceptExpression)"/>
2597 /// and <see cref="Visit(DbIntersectExpression)"/>
2599 /// <see cref="VisitSetOpExpression"/>
2600 /// Since the left and right expression may not be Sql select statements,
2601 /// we must wrap them up to look like SQL select statements.
2603 /// <param name="e"></param>
2604 /// <returns></returns>
2605 public override ISqlFragment Visit(DbUnionAllExpression e)
2607 return VisitSetOpExpression(e.Left, e.Right, "UNION ALL");
2611 /// This method determines whether an extent from an outer scope(free variable)
2612 /// is used in the CurrentSelectStatement.
2614 /// An extent in an outer scope, if its symbol is not in the FromExtents
2615 /// of the CurrentSelectStatement.
2617 /// <param name="e"></param>
2618 /// <returns>A <see cref="Symbol"/>.</returns>
2619 public override ISqlFragment Visit(DbVariableReferenceExpression e)
2623 throw EntityUtil.NotSupported();
2624 // A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
2625 // This is also checked in GenerateSql(...) at the end of the visiting.
2627 isVarRefSingle = true; // This will be reset by DbPropertyExpression or MethodExpression
2629 Symbol result = symbolTable.Lookup(e.VariableName);
2630 optionalColumnUsageManager.MarkAsUsed(result);
2631 if (!CurrentSelectStatement.FromExtents.Contains(result))
2633 CurrentSelectStatement.OuterExtents[result] = true;
2640 #region Visitor Helper Methods
2642 #region 'Visitor' methods - Shared visitors and methods that do most of the visiting
2645 /// Aggregates are not visited by the normal visitor walk.
2647 /// <param name="aggregate">The aggregate go be translated</param>
2648 /// <param name="aggregateArgument">The translated aggregate argument</param>
2649 /// <returns></returns>
2650 private static SqlBuilder VisitAggregate(DbAggregate aggregate, object aggregateArgument)
2652 SqlBuilder aggregateResult = new SqlBuilder();
2653 DbFunctionAggregate functionAggregate = aggregate as DbFunctionAggregate;
2655 if (functionAggregate == null)
2657 throw EntityUtil.NotSupported();
2660 //The only aggregate function with different name is Big_Count
2661 //Note: If another such function is to be added, a dictionary should be created
2662 if (TypeHelpers.IsCanonicalFunction(functionAggregate.Function)
2663 && String.Equals(functionAggregate.Function.Name, "BigCount", StringComparison.Ordinal))
2665 aggregateResult.Append("COUNT_BIG");
2669 SqlFunctionCallHandler.WriteFunctionName(aggregateResult, functionAggregate.Function);
2672 aggregateResult.Append("(");
2674 DbFunctionAggregate fnAggr = functionAggregate;
2675 if ((null != fnAggr) && (fnAggr.Distinct))
2677 aggregateResult.Append("DISTINCT ");
2680 aggregateResult.Append(aggregateArgument);
2682 aggregateResult.Append(")");
2683 return aggregateResult;
2687 /// Dump out an expression - optionally wrap it with parantheses if possible
2689 /// <param name="e"></param>
2690 /// <param name="result"></param>
2691 internal void ParenthesizeExpressionIfNeeded(DbExpression e, SqlBuilder result)
2693 if (IsComplexExpression(e))
2696 result.Append(e.Accept(this));
2701 result.Append(e.Accept(this));
2706 /// Handler for inline binary expressions.
2707 /// Produces left op right.
2708 /// For associative operations does flattening.
2709 /// Puts parenthesis around the arguments if needed.
2711 /// <param name="op"></param>
2712 /// <param name="expressionKind"></param>
2713 /// <param name="left"></param>
2714 /// <param name="right"></param>
2715 /// <returns></returns>
2716 private SqlBuilder VisitBinaryExpression(string op, DbExpressionKind expressionKind, DbExpression left, DbExpression right)
2718 SqlBuilder result = new SqlBuilder();
2720 bool isFirst = true;
2721 foreach (DbExpression argument in SqlGenerator.FlattenAssociativeExpression(expressionKind, left, right))
2731 ParenthesizeExpressionIfNeeded(argument, result);
2737 /// Creates a flat list of the associative arguments.
2738 /// For example, for ((A1 + (A2 - A3)) + A4) it will create A1, (A2 - A3), A4
2739 /// Only 'unfolds' the given arguments that are of the given expression kind.
2741 /// <param name="expressionKind"></param>
2742 /// <param name="arguments"></param>
2743 /// <returns></returns>
2744 private static IEnumerable<DbExpression> FlattenAssociativeExpression(DbExpressionKind kind, DbExpression left, DbExpression right)
2746 if(kind != DbExpressionKind.Or &&
2747 kind != DbExpressionKind.And &&
2748 kind != DbExpressionKind.Plus &&
2749 kind != DbExpressionKind.Multiply)
2751 return new[] { left, right };
2754 List<DbExpression> argumentList = new List<DbExpression>();
2755 ExtractAssociativeArguments(kind, argumentList, left);
2756 ExtractAssociativeArguments(kind, argumentList, right);
2758 return argumentList;
2762 /// Helper method for FlattenAssociativeExpression.
2763 /// Creates a flat list of the associative arguments and appends to the given argument list.
2764 /// For example, for ((A1 + (A2 - A3)) + A4) it will add A1, (A2 - A3), A4 to the list.
2765 /// Only 'unfolds' the given expression if it is of the given expression kind.
2767 /// <param name="expressionKind"></param>
2768 /// <param name="argumentList"></param>
2769 /// <param name="expression"></param>
2770 private static void ExtractAssociativeArguments(DbExpressionKind expressionKind, List<DbExpression> argumentList, DbExpression expression)
2772 IEnumerable<DbExpression> result = Helpers.GetLeafNodes(
2774 exp => exp.ExpressionKind != expressionKind,
2777 //All associative expressions are binary, thus we must be dealing with a DbBinaryExpresson or
2778 // a DbArithmeticExpression with 2 arguments.
2779 DbBinaryExpression binaryExpression = exp as DbBinaryExpression;
2780 if(binaryExpression != null)
2782 return new[] { binaryExpression.Left, binaryExpression.Right };
2784 DbArithmeticExpression arithmeticExpression = (DbArithmeticExpression)exp;
2785 return arithmeticExpression.Arguments;
2789 argumentList.AddRange(result);
2793 /// Private handler for comparison expressions - almost identical to VisitBinaryExpression.
2794 /// We special case constants, so that we don't emit unnecessary casts
2796 /// <param name="op">the comparison op</param>
2797 /// <param name="left">the left-side expression</param>
2798 /// <param name="right">the right-side expression</param>
2799 /// <returns></returns>
2800 private SqlBuilder VisitComparisonExpression(string op, DbExpression left, DbExpression right)
2802 SqlBuilder result = new SqlBuilder();
2804 bool isCastOptional = left.ResultType.EdmType == right.ResultType.EdmType;
2806 if (left.ExpressionKind == DbExpressionKind.Constant)
2808 result.Append(VisitConstant((DbConstantExpression)left, isCastOptional));
2812 ParenthesizeExpressionIfNeeded(left, result);
2817 if (right.ExpressionKind == DbExpressionKind.Constant)
2819 result.Append(VisitConstant((DbConstantExpression)right, isCastOptional));
2823 ParenthesizeExpressionIfNeeded(right, result);
2830 /// This is called by the relational nodes. It does the following
2832 /// <item>If the input is not a SqlSelectStatement, it assumes that the input
2833 /// is a collection expression, and creates a new SqlSelectStatement </item>
2836 /// <param name="inputExpression"></param>
2837 /// <param name="inputVarName"></param>
2838 /// <param name="inputVarType"></param>
2839 /// <param name="fromSymbol"></param>
2840 /// <returns>A <see cref="SqlSelectStatement"/> and the main fromSymbol
2841 /// for this select statement.</returns>
2842 private SqlSelectStatement VisitInputExpression(DbExpression inputExpression,
2843 string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol)
2845 SqlSelectStatement result;
2846 ISqlFragment sqlFragment = inputExpression.Accept(this);
2847 result = sqlFragment as SqlSelectStatement;
2851 result = new SqlSelectStatement();
2852 WrapNonQueryExtent(result, sqlFragment, inputExpression.ExpressionKind);
2855 if (result.FromExtents.Count == 0)
2857 // input was an extent
2858 fromSymbol = new Symbol(inputVarName, inputVarType);
2860 else if (result.FromExtents.Count == 1)
2862 // input was Filter/GroupBy/Project/OrderBy
2863 // we are likely to reuse this statement.
2864 fromSymbol = result.FromExtents[0];
2868 // input was a join.
2869 // we are reusing the select statement produced by a Join node
2870 // we need to remove the original extents, and replace them with a
2871 // new extent with just the Join symbol.
2872 JoinSymbol joinSymbol = new JoinSymbol(inputVarName, inputVarType, result.FromExtents);
2873 joinSymbol.FlattenedExtentList = result.AllJoinExtents;
2875 fromSymbol = joinSymbol;
2876 result.FromExtents.Clear();
2877 result.FromExtents.Add(fromSymbol);
2884 /// <see cref="Visit(DbIsEmptyExpression)"/>
2886 /// <param name="e"></param>
2887 /// <param name="negate">Was the parent a DbNotExpression?</param>
2888 /// <returns></returns>
2889 SqlBuilder VisitIsEmptyExpression(DbIsEmptyExpression e, bool negate)
2891 SqlBuilder result = new SqlBuilder();
2894 result.Append(" NOT");
2896 result.Append(" EXISTS (");
2897 result.Append(VisitExpressionEnsureSqlStatement(e.Argument));
2898 result.AppendLine();
2906 /// Translate a NewInstance(Element(X)) expression into
2907 /// "select top(1) * from X"
2909 /// <param name="e"></param>
2910 /// <returns></returns>
2911 private ISqlFragment VisitCollectionConstructor(DbNewInstanceExpression e)
2913 Debug.Assert(e.Arguments.Count <= 1);
2915 if (e.Arguments.Count == 1 && e.Arguments[0].ExpressionKind == DbExpressionKind.Element)
2917 DbElementExpression elementExpr = e.Arguments[0] as DbElementExpression;
2918 SqlSelectStatement result = VisitExpressionEnsureSqlStatement(elementExpr.Argument);
2920 if (!IsCompatible(result, DbExpressionKind.Element))
2923 TypeUsage inputType = TypeHelpers.GetElementTypeUsage(elementExpr.Argument.ResultType);
2925 result = CreateNewSelectStatement(result, "element", inputType, out fromSymbol);
2926 AddFromSymbol(result, "element", fromSymbol, false);
2928 result.Select.Top = new TopClause(1, false);
2933 // Otherwise simply build this out as a union-all ladder
2934 CollectionType collectionType = TypeHelpers.GetEdmType<CollectionType>(e.ResultType);
2935 Debug.Assert(collectionType != null);
2936 bool isScalarElement = TypeSemantics.IsPrimitiveType(collectionType.TypeUsage);
2938 SqlBuilder resultSql = new SqlBuilder();
2939 string separator = "";
2941 // handle empty table
2942 if (e.Arguments.Count == 0)
2944 Debug.Assert(isScalarElement);
2945 resultSql.Append(" SELECT CAST(null as ");
2946 resultSql.Append(GetSqlPrimitiveType(collectionType.TypeUsage));
2947 resultSql.Append(") AS X FROM (SELECT 1) AS Y WHERE 1=0");
2950 foreach (DbExpression arg in e.Arguments)
2952 resultSql.Append(separator);
2953 resultSql.Append(" SELECT ");
2954 resultSql.Append(arg.Accept(this));
2955 // For scalar elements, no alias is appended yet. Add this.
2956 if (isScalarElement)
2958 resultSql.Append(" AS X ");
2960 separator = " UNION ALL ";
2967 /// <see cref="Visit(DbIsNullExpression)"/>
2969 /// <param name="e"></param>
2970 /// <param name="negate">Was the parent a DbNotExpression?</param>
2971 /// <returns></returns>
2972 private SqlBuilder VisitIsNullExpression(DbIsNullExpression e, bool negate)
2974 SqlBuilder result = new SqlBuilder();
2975 if (e.Argument.ExpressionKind == DbExpressionKind.ParameterReference)
2977 _ignoreForceNonUnicodeFlag = true;
2979 result.Append(e.Argument.Accept(this));
2980 // reset flag, it is not possible to reach this function with this flag set to true.
2981 _ignoreForceNonUnicodeFlag = false;
2984 result.Append(" IS NULL");
2988 result.Append(" IS NOT NULL");
2995 /// This handles the processing of join expressions.
2996 /// The extents on a left spine are flattened, while joins
2997 /// not on the left spine give rise to new nested sub queries.
2999 /// Joins work differently from the rest of the visiting, in that
3000 /// the parent (i.e. the join node) creates the SqlSelectStatement
3001 /// for the children to use.
3003 /// The "parameter" IsInJoinContext indicates whether a child extent should
3004 /// add its stuff to the existing SqlSelectStatement, or create a new SqlSelectStatement
3005 /// By passing true, we ask the children to add themselves to the parent join,
3006 /// by passing false, we ask the children to create new Select statements for
3009 /// This method is called from <see cref="Visit(DbApplyExpression)"/> and
3010 /// <see cref="Visit(DbJoinExpression)"/>.
3012 /// <param name="inputs"></param>
3013 /// <param name="joinKind"></param>
3014 /// <param name="joinString"></param>
3015 /// <param name="joinCondition"></param>
3016 /// <returns> A <see cref="SqlSelectStatement"/></returns>
3017 private ISqlFragment VisitJoinExpression(IList<DbExpressionBinding> inputs, DbExpressionKind joinKind,
3018 string joinString, DbExpression joinCondition)
3020 SqlSelectStatement result;
3021 // If the parent is not a join( or says that it is not),
3022 // we should create a new SqlSelectStatement.
3023 // otherwise, we add our child extents to the parent's FROM clause.
3026 result = new SqlSelectStatement();
3027 result.AllJoinExtents = new List<Symbol>();
3028 selectStatementStack.Push(result);
3032 result = CurrentSelectStatement;
3035 // Process each of the inputs, and then the joinCondition if it exists.
3036 // It would be nice if we could call VisitInputExpression - that would
3037 // avoid some code duplication
3038 // but the Join postprocessing is messy and prevents this reuse.
3039 symbolTable.EnterScope();
3041 string separator = "";
3042 bool isLeftMostInput = true;
3043 int inputCount = inputs.Count;
3044 for(int idx = 0; idx < inputCount; idx++)
3046 DbExpressionBinding input = inputs[idx];
3048 if (separator.Length != 0)
3050 result.From.AppendLine();
3052 result.From.Append(separator + " ");
3053 // Change this if other conditions are required
3054 // to force the child to produce a nested SqlStatement.
3055 bool needsJoinContext = (input.Expression.ExpressionKind == DbExpressionKind.Scan)
3056 || (isLeftMostInput &&
3057 (IsJoinExpression(input.Expression)
3058 || IsApplyExpression(input.Expression)))
3061 isParentAJoinStack.Push(needsJoinContext ? true : false);
3062 // if the child reuses our select statement, it will append the from
3063 // symbols to our FromExtents list. So, we need to remember the
3064 // start of the child's entries.
3065 int fromSymbolStart = result.FromExtents.Count;
3067 ISqlFragment fromExtentFragment = input.Expression.Accept(this);
3069 isParentAJoinStack.Pop();
3071 ProcessJoinInputResult(fromExtentFragment, result, input, fromSymbolStart);
3072 separator = joinString;
3074 isLeftMostInput = false;
3077 // Visit the on clause/join condition.
3080 case DbExpressionKind.FullOuterJoin:
3081 case DbExpressionKind.InnerJoin:
3082 case DbExpressionKind.LeftOuterJoin:
3083 result.From.Append(" ON ");
3084 isParentAJoinStack.Push(false);
3085 result.From.Append(joinCondition.Accept(this));
3086 isParentAJoinStack.Pop();
3090 symbolTable.ExitScope();
3094 selectStatementStack.Pop();
3101 /// This is called from <see cref="VisitJoinExpression"/>.
3103 /// This is responsible for maintaining the symbol table after visiting
3104 /// a child of a join expression.
3106 /// The child's sql statement may need to be completed.
3108 /// The child's result could be one of
3109 /// <list type="number">
3110 /// <item>The same as the parent's - this is treated specially.</item>
3111 /// <item>A sql select statement, which may need to be completed</item>
3112 /// <item>An extent - just copy it to the from clause</item>
3113 /// <item>Anything else (from a collection-valued expression) -
3114 /// unnest and copy it.</item>
3117 /// If the input was a Join, we need to create a new join symbol,
3118 /// otherwise, we create a normal symbol.
3120 /// We then call AddFromSymbol to add the AS clause, and update the symbol table.
3124 /// If the child's result was the same as the parent's, we have to clean up
3125 /// the list of symbols in the FromExtents list, since this contains symbols from
3126 /// the children of both the parent and the child.
3127 /// The happens when the child visited is a Join, and is the leftmost child of
3130 /// <param name="fromExtentFragment"></param>
3131 /// <param name="result"></param>
3132 /// <param name="input"></param>
3133 /// <param name="fromSymbolStart"></param>
3134 private void ProcessJoinInputResult(ISqlFragment fromExtentFragment, SqlSelectStatement result,
3135 DbExpressionBinding input, int fromSymbolStart)
3137 Symbol fromSymbol = null;
3139 if (result != fromExtentFragment)
3141 // The child has its own select statement, and is not reusing
3142 // our select statement.
3143 // This should look a lot like VisitInputExpression().
3144 SqlSelectStatement sqlSelectStatement = fromExtentFragment as SqlSelectStatement;
3145 if (sqlSelectStatement != null)
3147 if (sqlSelectStatement.Select.IsEmpty)
3149 List<Symbol> columns = AddDefaultColumns(sqlSelectStatement);
3151 if (IsJoinExpression(input.Expression)
3152 || IsApplyExpression(input.Expression))
3154 List<Symbol> extents = sqlSelectStatement.FromExtents;
3155 JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
3156 newJoinSymbol.IsNestedJoin = true;
3157 newJoinSymbol.ColumnList = columns;
3159 fromSymbol = newJoinSymbol;
3163 // this is a copy of the code in CreateNewSelectStatement.
3165 // if the oldStatement has a join as its input, ...
3166 // clone the join symbol, so that we "reuse" the
3167 // join symbol. Normally, we create a new symbol - see the next block
3169 JoinSymbol oldJoinSymbol = sqlSelectStatement.FromExtents[0] as JoinSymbol;
3170 if (oldJoinSymbol != null)
3172 // Note: sqlSelectStatement.FromExtents will not do, since it might
3173 // just be an alias of joinSymbol, and we want an actual JoinSymbol.
3174 JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, oldJoinSymbol.ExtentList);
3175 // This indicates that the sqlSelectStatement is a blocking scope
3176 // i.e. it hides/renames extent columns
3177 newJoinSymbol.IsNestedJoin = true;
3178 newJoinSymbol.ColumnList = columns;
3179 newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
3181 fromSymbol = newJoinSymbol;
3185 fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.OutputColumns, sqlSelectStatement.OutputColumnsRenamed);
3191 fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.OutputColumns, sqlSelectStatement.OutputColumnsRenamed);
3193 result.From.Append(" (");
3194 result.From.Append(sqlSelectStatement);
3195 result.From.Append(" )");
3197 else if (input.Expression is DbScanExpression)
3199 result.From.Append(fromExtentFragment);
3203 WrapNonQueryExtent(result, fromExtentFragment, input.Expression.ExpressionKind);
3206 if (fromSymbol == null) // i.e. not a join symbol
3208 fromSymbol = new Symbol(input.VariableName, input.VariableType);
3212 AddFromSymbol(result, input.VariableName, fromSymbol);
3213 result.AllJoinExtents.Add(fromSymbol);
3215 else // result == fromExtentFragment. The child extents have been merged into the parent's.
3217 // we are adding extents to the current sql statement via flattening.
3218 // We are replacing the child's extents with a single Join symbol.
3219 // The child's extents are all those following the index fromSymbolStart.
3221 List<Symbol> extents = new List<Symbol>();
3223 // We cannot call extents.AddRange, since the is no simple way to
3224 // get the range of symbols fromSymbolStart..result.FromExtents.Count
3225 // from result.FromExtents.
3226 // We copy these symbols to create the JoinSymbol later.
3227 for (int i = fromSymbolStart; i < result.FromExtents.Count; ++i)
3229 extents.Add(result.FromExtents[i]);
3231 result.FromExtents.RemoveRange(fromSymbolStart, result.FromExtents.Count - fromSymbolStart);
3232 fromSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
3233 result.FromExtents.Add(fromSymbol);
3234 // this Join Symbol does not have its own select statement, so we
3235 // do not set IsNestedJoin
3238 // We do not call AddFromSymbol(), since we do not want to add
3239 // "AS alias" to the FROM clause- it has been done when the extent was added earlier.
3240 symbolTable.Add(input.VariableName, fromSymbol);
3245 /// We assume that this is only called as a child of a Project.
3247 /// This replaces <see cref="Visit(DbNewInstanceExpression)"/>, since
3248 /// we do not allow DbNewInstanceExpression as a child of any node other than
3249 /// DbProjectExpression.
3251 /// We write out the translation of each of the columns in the record.
3253 /// <param name="e"></param>
3254 /// <param name="aliasesNeedRenaming"></param>
3255 /// <param name="newColumns"></param>
3256 /// <returns>A <see cref="SqlBuilder"/></returns>
3257 private ISqlFragment VisitNewInstanceExpression(DbNewInstanceExpression e, bool aliasesNeedRenaming, out Dictionary<string, Symbol> newColumns)
3259 SqlBuilder result = new SqlBuilder();
3260 RowType rowType = e.ResultType.EdmType as RowType;
3262 if (null != rowType)
3264 newColumns = new Dictionary<string, Symbol>(e.Arguments.Count);
3266 ReadOnlyMetadataCollection<EdmProperty> members = rowType.Properties;
3267 string separator = "";
3268 for(int i = 0; i < e.Arguments.Count; ++i)
3270 DbExpression argument = e.Arguments[i];
3271 if (TypeSemantics.IsRowType(argument.ResultType))
3273 // We do not support nested records or other complex objects.
3274 throw EntityUtil.NotSupported();
3277 EdmProperty member = members[i];
3278 result.Append(separator);
3279 result.AppendLine();
3280 result.Append(argument.Accept(this));
3281 result.Append(" AS ");
3282 if (aliasesNeedRenaming)
3284 Symbol column = new Symbol(member.Name, member.TypeUsage);
3285 column.NeedsRenaming = true;
3286 column.NewName = String.Concat("Internal_", member.Name);
3287 result.Append(column);
3288 newColumns.Add(member.Name, column);
3292 result.Append(QuoteIdentifier(member.Name));
3303 throw EntityUtil.NotSupported();
3310 /// Handler for set operations
3311 /// It generates left separator right.
3312 /// Only for SQL 8.0 it may need to create a new select statement
3313 /// above the set operation if the left child's output columns got renamed
3315 /// <param name="left"></param>
3316 /// <param name="right"></param>
3317 /// <param name="separator"></param>
3318 /// <returns></returns>
3319 private ISqlFragment VisitSetOpExpression(DbExpression left, DbExpression right, string separator)
3322 SqlSelectStatement leftSelectStatement = VisitExpressionEnsureSqlStatement(left, true, true);
3323 SqlSelectStatement rightSelectStatement = VisitExpressionEnsureSqlStatement(right, true, true);
3325 SqlBuilder setStatement = new SqlBuilder();
3326 setStatement.Append(leftSelectStatement);
3327 setStatement.AppendLine();
3328 setStatement.Append(separator); // e.g. UNION ALL
3329 setStatement.AppendLine();
3330 setStatement.Append(rightSelectStatement);
3333 //This is the common scenario
3334 if (!leftSelectStatement.OutputColumnsRenamed)
3336 return setStatement;
3341 // This is case only for SQL 8.0 when the left child has order by in it
3342 // If the output columns of the left child got renamed,
3343 // then the output of the union all is renamed
3344 // All this currenlty only happens for UNION ALL, because INTERSECT and
3345 // EXCEPT get translated for SQL 8.0 before SqlGen.
3346 SqlSelectStatement selectStatement = new SqlSelectStatement();
3347 selectStatement.From.Append("( ");
3348 selectStatement.From.Append(setStatement);
3349 selectStatement.From.AppendLine();
3350 selectStatement.From.Append(") ");
3352 Symbol fromSymbol = new Symbol("X", TypeHelpers.GetElementTypeUsage(left.ResultType), leftSelectStatement.OutputColumns, true);
3353 AddFromSymbol(selectStatement, null, fromSymbol, false);
3355 return selectStatement;
3361 #region Other Helpers
3363 /// <see cref="AddDefaultColumns"/>
3364 /// Add the column names from the referenced extent/join to the
3365 /// select statement.
3367 /// If the symbol is a JoinSymbol, we recursively visit all the extents,
3368 /// halting at real extents and JoinSymbols that have an associated SqlSelectStatement.
3370 /// The column names for a real extent can be derived from its type.
3371 /// The column names for a Join Select statement can be got from the
3372 /// list of columns that was created when the Join's select statement
3375 /// We do the following for each column.
3376 /// <list type="number">
3377 /// <item>Add the SQL string for each column to the SELECT clause</item>
3378 /// <item>Add the column to the list of columns - so that it can
3379 /// become part of the "type" of a JoinSymbol</item>
3380 /// <item>Check if the column name collides with a previous column added
3381 /// to the same select statement. Flag both the columns for renaming if true.</item>
3382 /// <item>Add the column to a name lookup dictionary for collision detection.</item>
3385 /// <param name="selectStatement">The select statement that started off as SELECT *</param>
3386 /// <param name="symbol">The symbol containing the type information for
3387 /// the columns to be added.</param>
3388 /// <param name="columnList">Columns that have been added to the Select statement.
3389 /// This is created in <see cref="AddDefaultColumns"/>.</param>
3390 /// <param name="columnDictionary">A dictionary of the columns above.</param>
3391 private void AddColumns(SqlSelectStatement selectStatement, Symbol symbol,
3392 List<Symbol> columnList, Dictionary<string, Symbol> columnDictionary)
3394 JoinSymbol joinSymbol = symbol as JoinSymbol;
3395 if (joinSymbol != null)
3397 if (!joinSymbol.IsNestedJoin)
3399 // Recurse if the join symbol is a collection of flattened extents
3400 foreach (Symbol sym in joinSymbol.ExtentList)
3402 // if sym is ScalarType means we are at base case in the
3403 // recursion and there are not columns to add, just skip
3404 if ((sym.Type == null) || TypeSemantics.IsPrimitiveType(sym.Type))
3409 AddColumns(selectStatement, sym, columnList, columnDictionary);
3414 foreach (Symbol joinColumn in joinSymbol.ColumnList)
3416 // we write tableName.columnName
3417 // rather than tableName.columnName as alias
3418 // since the column name is unique (by the way we generate new column names)
3420 // We use the symbols for both the table and the column,
3421 // since they are subject to renaming.
3423 //This is called from AddDefaultColumns. To avoid adding columns that may not be used later,
3424 // we add an optional column, that will only be added if needed.
3425 OptionalColumn optionalColumn = CreateOptionalColumn(null, joinColumn);
3427 optionalColumn.Append(symbol);
3428 optionalColumn.Append(".");
3429 optionalColumn.Append(joinColumn);
3431 selectStatement.Select.AddOptionalColumn(optionalColumn);
3433 // check for name collisions. If there is,
3434 // flag both the colliding symbols.
3435 if (columnDictionary.ContainsKey(joinColumn.Name))
3437 columnDictionary[joinColumn.Name].NeedsRenaming = true; // the original symbol
3438 joinColumn.NeedsRenaming = true; // the current symbol.
3442 columnDictionary[joinColumn.Name] = joinColumn;
3445 columnList.Add(joinColumn);
3451 // This is a non-join extent/select statement, and the CQT type has
3452 // the relevant column information.
3454 // The type could be a record type(e.g. Project(...),
3455 // or an entity type ( e.g. EntityExpression(...)
3456 // so, we check whether it is a structuralType.
3458 // Consider an expression of the form J(a, b=P(E))
3459 // The inner P(E) would have been translated to a SQL statement
3460 // We should not use the raw names from the type, but the equivalent
3461 // symbols (they are present in symbol.Columns) if they exist.
3463 // We add the new columns to the symbol's columns if they do
3464 // not already exist.
3466 // If the symbol represents a SqlStatement with renamed output columns,
3467 // we should use these instead of the rawnames and we should also mark
3468 // this selectStatement as one with renamed columns
3470 if (symbol.OutputColumnsRenamed)
3472 selectStatement.OutputColumnsRenamed = true;
3475 if (selectStatement.OutputColumns == null)
3477 selectStatement.OutputColumns = new Dictionary<string, Symbol>();
3480 if ((symbol.Type == null) || TypeSemantics.IsPrimitiveType(symbol.Type))
3482 AddColumn(selectStatement, symbol, columnList, columnDictionary, "X");
3486 foreach (EdmProperty property in TypeHelpers.GetProperties(symbol.Type))
3488 AddColumn(selectStatement, symbol, columnList, columnDictionary, property.Name);
3495 /// Creates an optional column and registers the corresponding symbol with
3496 /// the optionalColumnUsageManager it has not already been registered.
3498 /// <param name="inputColumnSymbol"></param>
3499 /// <param name="column"></param>
3500 /// <returns></returns>
3501 private OptionalColumn CreateOptionalColumn(Symbol inputColumnSymbol, Symbol column)
3503 if (!optionalColumnUsageManager.ContainsKey(column))
3505 optionalColumnUsageManager.Add(inputColumnSymbol, column);
3507 return new OptionalColumn(optionalColumnUsageManager, column);
3511 /// Helper method for AddColumns. Adds a column with the given column name
3512 /// to the Select list of the given select statement.
3514 /// <param name="selectStatement">The select statement to whose SELECT part the column should be added</param>
3515 /// <param name="symbol">The symbol from which the column to be added originated</param>
3516 /// <param name="columnList">Columns that have been added to the Select statement.
3517 /// This is created in <see cref="AddDefaultColumns"/>.</param>
3518 /// <param name="columnDictionary">A dictionary of the columns above.</param>
3519 /// <param name="columnName">The name of the column to be added.</param>
3520 private void AddColumn(SqlSelectStatement selectStatement, Symbol symbol,
3521 List<Symbol> columnList, Dictionary<string, Symbol> columnDictionary, string columnName)
3523 // Since all renaming happens in the second phase
3524 // we lose nothing by setting the next column name index to 0
3526 allColumnNames[columnName] = 0;
3528 Symbol inputSymbol = null;
3529 symbol.OutputColumns.TryGetValue(columnName, out inputSymbol);
3531 // Create a new symbol/reuse existing symbol for the column
3532 Symbol columnSymbol;
3533 if (!symbol.Columns.TryGetValue(columnName, out columnSymbol))
3535 // we do not care about the types of columns, so we pass null
3536 // when construction the symbol.
3537 columnSymbol = ((inputSymbol != null) && symbol.OutputColumnsRenamed)? inputSymbol : new Symbol(columnName, null);
3538 symbol.Columns.Add(columnName, columnSymbol);
3541 OptionalColumn optionalColumn = CreateOptionalColumn(inputSymbol, columnSymbol);
3543 optionalColumn.Append(symbol);
3544 optionalColumn.Append(".");
3546 if (symbol.OutputColumnsRenamed)
3548 optionalColumn.Append(inputSymbol);
3552 optionalColumn.Append(QuoteIdentifier(columnName));
3555 optionalColumn.Append(" AS ");
3556 optionalColumn.Append(columnSymbol);
3558 selectStatement.Select.AddOptionalColumn(optionalColumn);
3560 //If the columnName is already in the output columns, it means it is being tracked
3561 // via the join symbol mechanism
3562 if (!selectStatement.OutputColumns.ContainsKey(columnName))
3564 selectStatement.OutputColumns.Add(columnName, columnSymbol);
3567 // Check for column name collisions.
3568 if (columnDictionary.ContainsKey(columnName))
3570 columnDictionary[columnName].NeedsRenaming = true;
3571 columnSymbol.NeedsRenaming = true;
3575 columnDictionary[columnName] = symbol.Columns[columnName];
3578 columnList.Add(columnSymbol);
3582 /// Expands Select * to "select the_list_of_columns"
3583 /// If the columns are taken from an extent, they are written as
3584 /// {original_column_name AS Symbol(original_column)} to allow renaming.
3586 /// If the columns are taken from a Join, they are written as just
3587 /// {original_column_name}, since there cannot be a name collision.
3589 /// We concatenate the columns from each of the inputs to the select statement.
3590 /// Since the inputs may be joins that are flattened, we need to recurse.
3591 /// The inputs are inferred from the symbols in FromExtents.
3593 /// <param name="selectStatement"></param>
3594 /// <returns></returns>
3595 private List<Symbol> AddDefaultColumns(SqlSelectStatement selectStatement)
3597 // This is the list of columns added in this select statement
3598 // This forms the "type" of the Select statement, if it has to
3599 // be expanded in another SELECT *
3600 List<Symbol> columnList = new List<Symbol>();
3602 // A lookup for the previous set of columns to aid column name
3603 // collision detection.
3604 Dictionary<string, Symbol> columnDictionary = new Dictionary<string, Symbol>(StringComparer.OrdinalIgnoreCase);
3606 foreach (Symbol symbol in selectStatement.FromExtents)
3608 AddColumns(selectStatement, symbol, columnList, columnDictionary);
3615 /// <see cref="AddFromSymbol(SqlSelectStatement, string, Symbol, bool)"/>
3617 /// <param name="selectStatement"></param>
3618 /// <param name="inputVarName"></param>
3619 /// <param name="fromSymbol"></param>
3620 private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol)
3622 AddFromSymbol(selectStatement, inputVarName, fromSymbol, true);
3626 /// This method is called after the input to a relational node is visited.
3627 /// <see cref="Visit(DbProjectExpression)"/> and <see cref="ProcessJoinInputResult"/>
3628 /// There are 2 scenarios
3629 /// <list type="number">
3630 /// <item>The fromSymbol is new i.e. the select statement has just been
3631 /// created, or a join extent has been added.</item>
3632 /// <item>The fromSymbol is old i.e. we are reusing a select statement.</item>
3635 /// If we are not reusing the select statement, we have to complete the
3636 /// FROM clause with the alias
3638 /// -- if the input was an extent
3639 /// FROM = [SchemaName].[TableName]
3640 /// -- if the input was a Project
3641 /// FROM = (SELECT ... FROM ... WHERE ...)
3646 /// -- if the input was an extent
3647 /// FROM = [SchemaName].[TableName] AS alias
3648 /// -- if the input was a Project
3649 /// FROM = (SELECT ... FROM ... WHERE ...) AS alias
3651 /// and look like valid FROM clauses.
3653 /// Finally, we have to add the alias to the global list of aliases used,
3654 /// and also to the current symbol table.
3656 /// <param name="selectStatement"></param>
3657 /// <param name="inputVarName">The alias to be used.</param>
3658 /// <param name="fromSymbol"></param>
3659 /// <param name="addToSymbolTable"></param>
3660 private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol, bool addToSymbolTable)
3662 // the first check is true if this is a new statement
3663 // the second check is true if we are in a join - we do not
3664 // check if we are in a join context.
3665 // We do not want to add "AS alias" if it has been done already
3666 // e.g. when we are reusing the Sql statement.
3667 if (selectStatement.FromExtents.Count == 0 || fromSymbol != selectStatement.FromExtents[0])
3669 selectStatement.FromExtents.Add(fromSymbol);
3670 selectStatement.From.Append(" AS ");
3671 selectStatement.From.Append(fromSymbol);
3673 // We have this inside the if statement, since
3674 // we only want to add extents that are actually used.
3675 allExtentNames[fromSymbol.Name] = 0;
3678 if (addToSymbolTable)
3680 symbolTable.Add(inputVarName, fromSymbol);
3685 /// Translates a list of SortClauses.
3686 /// Used in the translation of OrderBy
3688 /// <param name="orderByClause">The SqlBuilder to which the sort keys should be appended</param>
3689 /// <param name="sortKeys"></param>
3690 private void AddSortKeys(SqlBuilder orderByClause, IList<DbSortClause> sortKeys)
3692 string separator = "";
3693 foreach (DbSortClause sortClause in sortKeys)
3695 orderByClause.Append(separator);
3696 orderByClause.Append(sortClause.Expression.Accept(this));
3697 // Bug 431021: COLLATE clause must precede ASC/DESC
3698 Debug.Assert(sortClause.Collation != null);
3699 if (!String.IsNullOrEmpty(sortClause.Collation))
3701 orderByClause.Append(" COLLATE ");
3702 orderByClause.Append(sortClause.Collation);
3705 orderByClause.Append(sortClause.Ascending ? " ASC" : " DESC");
3712 /// <see cref="CreateNewSelectStatement(SqlSelectStatement, string, TypeUsage, bool, out Symbol)"/>
3714 /// <param name="oldStatement"></param>
3715 /// <param name="inputVarName"></param>
3716 /// <param name="inputVarType"></param>
3717 /// <param name="fromSymbol"></param>
3718 /// <returns></returns>
3719 private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
3720 string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol)
3722 return CreateNewSelectStatement(oldStatement, inputVarName, inputVarType, true, out fromSymbol);
3726 /// This is called after a relational node's input has been visited, and the
3727 /// input's sql statement cannot be reused. <see cref="Visit(DbProjectExpression)"/>
3729 /// When the input's sql statement cannot be reused, we create a new sql
3730 /// statement, with the old one as the from clause of the new statement.
3732 /// The old statement must be completed i.e. if it has an empty select list,
3733 /// the list of columns must be projected out.
3735 /// If the old statement being completed has a join symbol as its from extent,
3736 /// the new statement must have a clone of the join symbol as its extent.
3737 /// We cannot reuse the old symbol, but the new select statement must behave
3738 /// as though it is working over the "join" record.
3740 /// <param name="oldStatement"></param>
3741 /// <param name="inputVarName"></param>
3742 /// <param name="inputVarType"></param>
3743 /// <param name="finalizeOldStatement"></param>
3744 /// <param name="fromSymbol"></param>
3745 /// <returns>A new select statement, with the old one as the from clause.</returns>
3746 private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
3747 string inputVarName, TypeUsage inputVarType, bool finalizeOldStatement, out Symbol fromSymbol)
3751 // Finalize the old statement
3752 if (finalizeOldStatement && oldStatement.Select.IsEmpty)
3754 List<Symbol> columns = AddDefaultColumns(oldStatement);
3756 // Thid could not have been called from a join node.
3757 Debug.Assert(oldStatement.FromExtents.Count == 1);
3759 // if the oldStatement has a join as its input, ...
3760 // clone the join symbol, so that we "reuse" the
3761 // join symbol. Normally, we create a new symbol - see the next block
3763 JoinSymbol oldJoinSymbol = oldStatement.FromExtents[0] as JoinSymbol;
3764 if (oldJoinSymbol != null)
3766 // Note: oldStatement.FromExtents will not do, since it might
3767 // just be an alias of joinSymbol, and we want an actual JoinSymbol.
3768 JoinSymbol newJoinSymbol = new JoinSymbol(inputVarName, inputVarType, oldJoinSymbol.ExtentList);
3769 // This indicates that the oldStatement is a blocking scope
3770 // i.e. it hides/renames extent columns
3771 newJoinSymbol.IsNestedJoin = true;
3772 newJoinSymbol.ColumnList = columns;
3773 newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
3775 fromSymbol = newJoinSymbol;
3779 if (fromSymbol == null)
3781 fromSymbol = new Symbol(inputVarName, inputVarType, oldStatement.OutputColumns, oldStatement.OutputColumnsRenamed);
3784 // Observe that the following looks like the body of Visit(ExtentExpression).
3785 SqlSelectStatement selectStatement = new SqlSelectStatement();
3786 selectStatement.From.Append("( ");
3787 selectStatement.From.Append(oldStatement);
3788 selectStatement.From.AppendLine();
3789 selectStatement.From.Append(") ");
3792 return selectStatement;
3796 /// Before we embed a string literal in a SQL string, we should
3797 /// convert all ' to '', and enclose the whole string in single quotes.
3799 /// <param name="s"></param>
3800 /// <param name="isUnicode"></param>
3801 /// <returns>The escaped sql string.</returns>
3802 private static string EscapeSingleQuote(string s, bool isUnicode)
3804 return (isUnicode ? "N'" : "'") + s.Replace("'", "''") + "'";
3808 /// Returns the sql primitive/native type name.
3809 /// It will include size, precision or scale depending on type information present in the
3812 /// <param name="type"></param>
3813 /// <returns></returns>
3814 private string GetSqlPrimitiveType(TypeUsage type)
3816 Debug.Assert(type.EdmType.DataSpace == DataSpace.CSpace, "Type must be in cSpace");
3818 TypeUsage storeTypeUsage = this._storeItemCollection.StoreProviderManifest.GetStoreType(type);
3819 return GenerateSqlForStoreType(this.sqlVersion, storeTypeUsage);
3822 internal static string GenerateSqlForStoreType(SqlVersion sqlVersion, TypeUsage storeTypeUsage)
3824 Debug.Assert(Helper.IsPrimitiveType(storeTypeUsage.EdmType), "Type must be primitive type");
3826 string typeName = storeTypeUsage.EdmType.Name;
3827 bool hasFacet = false;
3829 byte decimalPrecision = 0;
3830 byte decimalScale = 0;
3832 PrimitiveTypeKind primitiveTypeKind = ((PrimitiveType)storeTypeUsage.EdmType).PrimitiveTypeKind;
3834 switch (primitiveTypeKind)
3836 case PrimitiveTypeKind.Binary:
3837 if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.MaxLengthFacetName))
3839 hasFacet = TypeHelpers.TryGetMaxLength(storeTypeUsage, out maxLength);
3840 Debug.Assert(hasFacet, "Binary type did not have MaxLength facet");
3841 typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
3845 case PrimitiveTypeKind.String:
3846 if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.MaxLengthFacetName))
3848 hasFacet = TypeHelpers.TryGetMaxLength(storeTypeUsage, out maxLength);
3849 Debug.Assert(hasFacet, "String type did not have MaxLength facet");
3850 typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
3854 case PrimitiveTypeKind.DateTime:
3855 typeName = SqlVersionUtils.IsPreKatmai(sqlVersion) ? "datetime" : "datetime2";
3857 case PrimitiveTypeKind.Time:
3858 AssertKatmaiOrNewer(sqlVersion, primitiveTypeKind);
3861 case PrimitiveTypeKind.DateTimeOffset:
3862 AssertKatmaiOrNewer(sqlVersion, primitiveTypeKind);
3863 typeName = "datetimeoffset";
3866 case PrimitiveTypeKind.Decimal:
3867 if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.PrecisionFacetName))
3869 hasFacet = TypeHelpers.TryGetPrecision(storeTypeUsage, out decimalPrecision);
3870 Debug.Assert(hasFacet, "decimal must have precision facet");
3871 Debug.Assert(decimalPrecision > 0, "decimal precision must be greater than zero");
3872 hasFacet = TypeHelpers.TryGetScale(storeTypeUsage, out decimalScale);
3873 Debug.Assert(hasFacet, "decimal must have scale facet");
3874 Debug.Assert(decimalPrecision >= decimalScale, "decimalPrecision must be greater or equal to decimalScale");
3875 typeName = typeName + "(" + decimalPrecision + "," + decimalScale + ")";
3887 /// Handles the expression represending DbLimitExpression.Limit and DbSkipExpression.Count.
3888 /// If it is a constant expression, it simply does to string thus avoiding casting it to the specific value
3889 /// (which would be done if <see cref="Visit(DbConstantExpression)"/> is called)
3891 /// <param name="e"></param>
3892 /// <returns></returns>
3893 private ISqlFragment HandleCountExpression(DbExpression e)
3895 ISqlFragment result;
3897 if (e.ExpressionKind == DbExpressionKind.Constant)
3899 //For constant expression we should not cast the value,
3900 // thus we don't go throught the default DbConstantExpression handling
3901 SqlBuilder sqlBuilder = new SqlBuilder();
3902 sqlBuilder.Append(((DbConstantExpression)e).Value.ToString());
3903 result = sqlBuilder;
3907 result = e.Accept(this);
3914 /// This is used to determine if a particular expression is an Apply operation.
3915 /// This is only the case when the DbExpressionKind is CrossApply or OuterApply.
3917 /// <param name="e"></param>
3918 /// <returns></returns>
3919 static bool IsApplyExpression(DbExpression e)
3921 return (DbExpressionKind.CrossApply == e.ExpressionKind || DbExpressionKind.OuterApply == e.ExpressionKind);
3925 /// This is used to determine if a particular expression is a Join operation.
3926 /// This is true for DbCrossJoinExpression and DbJoinExpression, the
3927 /// latter of which may have one of several different ExpressionKinds.
3929 /// <param name="e"></param>
3930 /// <returns></returns>
3931 static bool IsJoinExpression(DbExpression e)
3933 return (DbExpressionKind.CrossJoin == e.ExpressionKind ||
3934 DbExpressionKind.FullOuterJoin == e.ExpressionKind ||
3935 DbExpressionKind.InnerJoin == e.ExpressionKind ||
3936 DbExpressionKind.LeftOuterJoin == e.ExpressionKind);
3940 /// This is used to determine if a calling expression needs to place
3941 /// round brackets around the translation of the expression e.
3943 /// Constants, parameters and properties do not require brackets,
3944 /// everything else does.
3946 /// <param name="e"></param>
3947 /// <returns>true, if the expression needs brackets </returns>
3948 private static bool IsComplexExpression(DbExpression e)
3950 switch (e.ExpressionKind)
3952 case DbExpressionKind.Constant:
3953 case DbExpressionKind.ParameterReference:
3954 case DbExpressionKind.Property:
3955 case DbExpressionKind.Cast:
3964 /// Determine if the owner expression can add its unique sql to the input's
3965 /// SqlSelectStatement
3967 /// <param name="result">The SqlSelectStatement of the input to the relational node.</param>
3968 /// <param name="expressionKind">The kind of the expression node(not the input's)</param>
3969 /// <returns></returns>
3970 private static bool IsCompatible(SqlSelectStatement result, DbExpressionKind expressionKind)
3972 switch (expressionKind)
3974 case DbExpressionKind.Distinct:
3975 return result.Select.Top == null
3976 // #494803: The projection after distinct may not project all
3977 // columns used in the Order By
3978 // Improvement: Consider getting rid of the Order By instead
3979 && result.OrderBy.IsEmpty;
3981 case DbExpressionKind.Filter:
3982 return result.Select.IsEmpty
3983 && result.Where.IsEmpty
3984 && result.GroupBy.IsEmpty
3985 && result.Select.Top == null;
3987 case DbExpressionKind.GroupBy:
3988 return result.Select.IsEmpty
3989 && result.GroupBy.IsEmpty
3990 && result.OrderBy.IsEmpty
3991 && result.Select.Top == null
3992 && !result.Select.IsDistinct;
3994 case DbExpressionKind.Limit:
3995 case DbExpressionKind.Element:
3996 return result.Select.Top == null;
3998 case DbExpressionKind.Project:
3999 // SQLBUDT #427998: Allow a Project to be compatible with an OrderBy
4000 // Otherwise we won't be able to sort an input, and project out only
4001 // a subset of the input columns
4002 return result.Select.IsEmpty
4003 && result.GroupBy.IsEmpty
4004 // SQLBUDT #513640 - If distinct is specified, the projection may affect
4005 // the cardinality of the results, thus a new statement must be started.
4006 && !result.Select.IsDistinct;
4008 case DbExpressionKind.Skip:
4009 return result.Select.IsEmpty
4010 && result.GroupBy.IsEmpty
4011 && result.OrderBy.IsEmpty
4012 && !result.Select.IsDistinct;
4014 case DbExpressionKind.Sort:
4015 return result.Select.IsEmpty
4016 && result.GroupBy.IsEmpty
4017 && result.OrderBy.IsEmpty
4018 // SQLBUDT #513640 - A Project may be on the top of the Sort, and if so, it would need
4019 // to be in the same statement as the Sort (see comment above for the Project case).
4020 // A Distinct in the same statement would prevent that, and therefore if Distinct is present,
4021 // we need to start a new statement.
4022 && !result.Select.IsDistinct;
4025 Debug.Assert(false);
4026 throw EntityUtil.InvalidOperation(String.Empty);
4032 /// We use the normal box quotes for SQL server. We do not deal with ANSI quotes
4033 /// i.e. double quotes.
4035 /// <param name="name"></param>
4036 /// <returns></returns>
4037 internal static string QuoteIdentifier(string name)
4039 Debug.Assert(!String.IsNullOrEmpty(name));
4040 // We assume that the names are not quoted to begin with.
4041 return "[" + name.Replace("]", "]]") + "]";
4045 /// Simply calls <see cref="VisitExpressionEnsureSqlStatement(DbExpression, bool, bool)"/>
4046 /// with addDefaultColumns set to true and markAllDefaultColumnsAsUsed set to false.
4048 /// <param name="e"></param>
4049 /// <returns></returns>
4050 private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e)
4052 return VisitExpressionEnsureSqlStatement(e, true, false);
4056 /// This is called from <see cref="GenerateSql(DbQueryCommandTree, out Dictionary<string, bool>)"/> and nodes which require a
4057 /// select statement as an argument e.g. <see cref="Visit(DbIsEmptyExpression)"/>,
4058 /// <see cref="Visit(DbUnionAllExpression)"/>.
4060 /// SqlGenerator needs its child to have a proper alias if the child is
4061 /// just an extent or a join.
4063 /// The normal relational nodes result in complete valid SQL statements.
4064 /// For the rest, we need to treat them as there was a dummy
4066 /// -- originally {expression}
4067 /// -- change that to
4069 /// FROM {expression} as c
4072 /// DbLimitExpression needs to start the statement but not add the default columns
4074 /// <param name="e"></param>
4075 /// <param name="addDefaultColumns"></param>
4076 /// <param name="markAllDefaultColumnsAsUsed"></param>
4077 /// <returns></returns>
4078 private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e, bool addDefaultColumns, bool markAllDefaultColumnsAsUsed)
4080 Debug.Assert(TypeSemantics.IsCollectionType(e.ResultType));
4082 SqlSelectStatement result;
4083 switch (e.ExpressionKind)
4085 case DbExpressionKind.Project:
4086 case DbExpressionKind.Filter:
4087 case DbExpressionKind.GroupBy:
4088 case DbExpressionKind.Sort:
4089 result = e.Accept(this) as SqlSelectStatement;
4094 string inputVarName = "c"; // any name will do - this is my random choice.
4095 symbolTable.EnterScope();
4097 TypeUsage type = null;
4098 switch (e.ExpressionKind)
4100 case DbExpressionKind.Scan:
4101 case DbExpressionKind.CrossJoin:
4102 case DbExpressionKind.FullOuterJoin:
4103 case DbExpressionKind.InnerJoin:
4104 case DbExpressionKind.LeftOuterJoin:
4105 case DbExpressionKind.CrossApply:
4106 case DbExpressionKind.OuterApply:
4107 // #490026: It used to be type = e.ResultType.
4108 type = TypeHelpers.GetElementTypeUsage(e.ResultType);
4112 Debug.Assert(TypeSemantics.IsCollectionType(e.ResultType));
4113 type = TypeHelpers.GetEdmType<CollectionType>(e.ResultType).TypeUsage;
4118 result = VisitInputExpression(e, inputVarName, type, out fromSymbol);
4119 AddFromSymbol(result, inputVarName, fromSymbol);
4120 symbolTable.ExitScope();
4124 if (addDefaultColumns && result.Select.IsEmpty)
4126 List<Symbol> defaultColumns = AddDefaultColumns(result);
4127 if (markAllDefaultColumnsAsUsed)
4129 foreach (Symbol symbol in defaultColumns)
4131 this.optionalColumnUsageManager.MarkAsUsed(symbol);
4140 /// This method is called by <see cref="Visit(DbFilterExpression)"/> and
4141 /// <see cref="Visit(DbQuantifierExpression)"/>
4144 /// <param name="input"></param>
4145 /// <param name="predicate"></param>
4146 /// <param name="negatePredicate">This is passed from <see cref="Visit(DbQuantifierExpression)"/>
4147 /// in the All(...) case.</param>
4148 /// <returns></returns>
4149 private SqlSelectStatement VisitFilterExpression(DbExpressionBinding input, DbExpression predicate, bool negatePredicate)
4152 SqlSelectStatement result = VisitInputExpression(input.Expression,
4153 input.VariableName, input.VariableType, out fromSymbol);
4155 // Filter is compatible with OrderBy
4156 // but not with Project, another Filter or GroupBy
4157 if (!IsCompatible(result, DbExpressionKind.Filter))
4159 result = CreateNewSelectStatement(result, input.VariableName, input.VariableType, out fromSymbol);
4162 selectStatementStack.Push(result);
4163 symbolTable.EnterScope();
4165 AddFromSymbol(result, input.VariableName, fromSymbol);
4167 if (negatePredicate)
4169 result.Where.Append("NOT (");
4171 result.Where.Append(predicate.Accept(this));
4172 if (negatePredicate)
4174 result.Where.Append(")");
4177 symbolTable.ExitScope();
4178 selectStatementStack.Pop();
4184 /// If the sql fragment for an input expression is not a SqlSelect statement
4185 /// or other acceptable form (e.g. an extent as a SqlBuilder), we need
4186 /// to wrap it in a form acceptable in a FROM clause. These are
4188 /// <list type="bullet">
4189 /// <item>The set operation expressions - union all, intersect, except</item>
4190 /// <item>TVFs, which are conceptually similar to tables</item>
4193 /// <param name="result"></param>
4194 /// <param name="sqlFragment"></param>
4195 /// <param name="expressionKind"></param>
4196 private static void WrapNonQueryExtent(SqlSelectStatement result, ISqlFragment sqlFragment, DbExpressionKind expressionKind)
4198 switch (expressionKind)
4200 case DbExpressionKind.Function:
4202 result.From.Append(sqlFragment);
4206 result.From.Append(" (");
4207 result.From.Append(sqlFragment);
4208 result.From.Append(")");
4213 private static string ByteArrayToBinaryString( Byte[] binaryArray )
4215 StringBuilder sb = new StringBuilder( binaryArray.Length * 2 );
4216 for (int i = 0 ; i < binaryArray.Length ; i++)
4218 sb.Append(hexDigits[(binaryArray[i]&0xF0) >>4]).Append(hexDigits[binaryArray[i]&0x0F]);
4220 return sb.ToString();
4223 private TypeUsage GetPrimitiveType(PrimitiveTypeKind modelType)
4225 TypeUsage type = null;
4226 PrimitiveType mappedType = this._storeItemCollection.GetMappedPrimitiveType(modelType);
4228 Debug.Assert(mappedType != null, "Could not get type usage for primitive type");
4230 type = TypeUsage.CreateDefaultTypeUsage(mappedType);
4235 /// Helper method for the Group By visitor
4236 /// Returns true if at least one of the aggregates in the given list
4237 /// has an argument that is not a <see cref="DbConstantExpression"/> and is not
4238 /// a <see cref="DbPropertyExpression"/> over <see cref="DbVariableReferenceExpression"/>,
4239 /// either potentially capped with a <see cref="DbCastExpression"/>
4241 /// This is really due to the following two limitations of Sql Server:
4242 /// <list type="number">
4243 /// <item>If an expression being aggregated contains an outer reference, then that outer
4244 /// reference must be the only column referenced in the expression (SQLBUDT #488741)</item>
4245 /// <item>Sql Server cannot perform an aggregate function on an expression containing
4246 /// an aggregate or a subquery. (SQLBUDT #504600)</item>
4248 /// Potentially, we could furhter optimize this.
4250 /// <param name="aggregates"></param>
4251 /// <param name="inputVarRefName"></param>
4252 /// <returns></returns>
4253 static bool GroupByAggregatesNeedInnerQuery(IList<DbAggregate> aggregates, string inputVarRefName)
4255 foreach (DbAggregate aggregate in aggregates)
4257 Debug.Assert(aggregate.Arguments.Count == 1);
4258 if (GroupByAggregateNeedsInnerQuery(aggregate.Arguments[0], inputVarRefName))
4267 /// Returns true if the given expression is not a <see cref="DbConstantExpression"/> or a
4268 /// <see cref="DbPropertyExpression"/> over a <see cref="DbVariableReferenceExpression"/>
4269 /// referencing the given inputVarRefName, either
4270 /// potentially capped with a <see cref="DbCastExpression"/>.
4272 /// <param name="expression"></param>
4273 /// <param name="inputVarRefName"></param>
4274 /// <returns></returns>
4275 private static bool GroupByAggregateNeedsInnerQuery(DbExpression expression, string inputVarRefName)
4277 return GroupByExpressionNeedsInnerQuery(expression, inputVarRefName, true);
4281 /// Helper method for the Group By visitor
4282 /// Returns true if at least one of the expressions in the given list
4283 /// is not <see cref="DbPropertyExpression"/> over <see cref="DbVariableReferenceExpression"/>
4284 /// referencing the given inputVarRefName potentially capped with a <see cref="DbCastExpression"/>.
4286 /// This is really due to the following limitation: Sql Server requires each GROUP BY expression
4287 /// (key) to contain at least one column that is not an outer reference. (SQLBUDT #616523)
4288 /// Potentially, we could further optimize this.
4290 /// <param name="keys"></param>
4291 /// <param name="inputVarRefName"></param>
4292 /// <returns></returns>
4293 static bool GroupByKeysNeedInnerQuery(IList<DbExpression> keys, string inputVarRefName)
4295 foreach (DbExpression key in keys)
4297 if (GroupByKeyNeedsInnerQuery(key, inputVarRefName))
4306 /// Returns true if the given expression is not <see cref="DbPropertyExpression"/> over
4307 /// <see cref="DbVariableReferenceExpression"/> referencing the given inputVarRefName
4308 /// potentially capped with a <see cref="DbCastExpression"/>.
4309 /// This is really due to the following limitation: Sql Server requires each GROUP BY expression
4310 /// (key) to contain at least one column that is not an outer reference. (SQLBUDT #616523)
4311 /// Potentially, we could further optimize this.
4313 /// <param name="expression"></param>
4314 /// <param name="inputVarRefName"></param>
4315 /// <returns></returns>
4316 private static bool GroupByKeyNeedsInnerQuery(DbExpression expression, string inputVarRefName)
4318 return GroupByExpressionNeedsInnerQuery(expression, inputVarRefName, false);
4322 /// Helper method for processing Group By keys and aggregates.
4323 /// Returns true if the given expression is not a <see cref="DbConstantExpression"/>
4324 /// (and allowConstants is specified)or a <see cref="DbPropertyExpression"/> over
4325 /// a <see cref="DbVariableReferenceExpression"/> referencing the given inputVarRefName,
4326 /// either potentially capped with a <see cref="DbCastExpression"/>.
4328 /// <param name="expression"></param>
4329 /// <param name="inputVarRefName"></param>
4330 /// <param name="allowConstants"></param>
4331 /// <returns></returns>
4332 private static bool GroupByExpressionNeedsInnerQuery(DbExpression expression, string inputVarRefName, bool allowConstants)
4334 //Skip a constant if constants are allowed
4335 if (allowConstants && (expression.ExpressionKind == DbExpressionKind.Constant))
4340 //Skip a cast expression
4341 if (expression.ExpressionKind == DbExpressionKind.Cast)
4343 DbCastExpression castExpression = (DbCastExpression)expression;
4344 return GroupByExpressionNeedsInnerQuery(castExpression.Argument, inputVarRefName, allowConstants);
4347 //Allow Property(Property(...)), needed when the input is a join
4348 if (expression.ExpressionKind == DbExpressionKind.Property)
4350 DbPropertyExpression propertyExpression = (DbPropertyExpression)expression;
4351 return GroupByExpressionNeedsInnerQuery(propertyExpression.Instance, inputVarRefName, allowConstants);
4354 if (expression.ExpressionKind == DbExpressionKind.VariableReference)
4356 DbVariableReferenceExpression varRefExpression = expression as DbVariableReferenceExpression;
4357 return !varRefExpression.VariableName.Equals(inputVarRefName);
4364 /// Throws not supported exception if the server is pre-katmai
4366 /// <param name="primitiveTypeKind"></param>
4367 private void AssertKatmaiOrNewer(PrimitiveTypeKind primitiveTypeKind)
4369 AssertKatmaiOrNewer(this.sqlVersion, primitiveTypeKind);
4372 private static void AssertKatmaiOrNewer(SqlVersion sqlVersion, PrimitiveTypeKind primitiveTypeKind)
4374 if (SqlVersionUtils.IsPreKatmai(sqlVersion))
4376 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_PrimitiveTypeNotSupportedPriorSql10(primitiveTypeKind));
4381 /// Throws not supported exception if the server is pre-katmai
4383 /// <param name="e"></param>
4384 internal void AssertKatmaiOrNewer(DbFunctionExpression e)
4386 if (this.IsPreKatmai)
4388 throw EntityUtil.NotSupported(System.Data.Entity.Strings.SqlGen_CanonicalFunctionNotSupportedPriorSql10(e.Function.Name));