SqlGenerator.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ Net / Net / 3.5.50727.3053 / DEVDIV / depot / DevDiv / releases / Orcas / SP / ndp / fx / src / DataEntity / System / Data / SqlClient / SqlGen / SqlGenerator.cs / 4 / SqlGenerator.cs

                            //---------------------------------------------------------------------- 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// 
// @owner  [....], [....]
//--------------------------------------------------------------------- 
 
using System;
using System.Collections.Generic; 
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text; 
using System.Data.Common;
using System.Data.Common.CommandTrees; 
using System.Data.Common.CommandTrees.Internal; 
using System.Data.Common.Utils;
using System.Data.Metadata.Edm; 
using System.Data.SqlClient;

namespace System.Data.SqlClient.SqlGen
{ 
    /// 
    /// Translates the command object into a SQL string that can be executed on 
    /// SQL Server 2000 and SQL Server 2005. 
    /// 
    ///  
    /// The translation is implemented as a visitor 
    /// over the query tree.  It makes a single pass over the tree, collecting the sql
    /// fragments for the various nodes in the tree .
    /// 
    /// The major operations are
    ///  
    /// Select statement minimization.  Multiple nodes in the query tree 
    /// that can be part of a single SQL select statement are merged. e.g. a
    /// Filter node that is the input of a Project node can typically share the 
    /// same SQL statement.
    /// Alpha-renaming.  As a result of the statement minimization above, there
    /// could be name collisions when using correlated subqueries
    ///  
    /// 
    /// Filter( 
    ///     b = Project( c.x 
    ///         c = Extent(foo)
    ///         ) 
    ///     exists (
    ///         Filter(
    ///             c = Extent(foo)
    ///             b.x = c.x 
    ///             )
    ///     ) 
    /// ) 
    /// 
    /// The first Filter, Project and Extent will share the same SQL select statement. 
    /// The alias for the Project i.e. b, will be replaced with c.
    /// If the alias c for the Filter within the exists clause is not renamed,
    /// we will get c.x = c.x, which is incorrect.
    /// Instead, the alias c within the second filter should be renamed to c1, to give 
    /// c.x = c1.x i.e. b is renamed to c, and c is renamed to c1.
    ///  
    ///  
    /// Join flattening.  In the query tree, a list of join nodes is typically
    /// represented as a tree of Join nodes, each with 2 children. e.g. 
    /// 
    /// 
    /// a = Join(InnerJoin
    ///     b = Join(CrossJoin 
    ///         c = Extent(foo)
    ///         d = Extent(foo) 
    ///         ) 
    ///     e = Extent(foo)
    ///     on b.c.x = e.x 
    ///     )
    /// 
    /// If translated directly, this will be translated to
    ///  
    /// FROM ( SELECT c.*, d.*
    ///         FROM foo as c 
    ///         CROSS JOIN foo as d) as b 
    /// INNER JOIN foo as e on b.x' = e.x
    ///  
    /// It would be better to translate this as
    /// 
    /// FROM foo as c
    /// CROSS JOIN foo as d 
    /// INNER JOIN foo as e on c.x = e.x
    ///  
    /// This allows the optimizer to choose an appropriate join ordering for evaluation. 
    /// 
    ///  
    /// Select * and column renaming.  In the example above, we noticed that
    /// in some cases we add SELECT * FROM ... to complete the SQL
    /// statement. i.e. there is no explicit PROJECT list.
    /// In this case, we enumerate all the columns available in the FROM clause 
    /// This is particularly problematic in the case of Join trees, since the columns
    /// from the extents joined might have the same name - this is illegal.  To solve 
    /// this problem, we will have to rename columns if they are part of a SELECT * 
    /// for a JOIN node - we do not need renaming in any other situation.
    /// . 
    /// 
    /// 
    ///
    ///  
    /// Renaming issues.
    /// When rows or columns are renamed, we produce names that are unique globally 
    /// with respect to the query.  The names are derived from the original names, 
    /// with an integer as a suffix. e.g. CustomerId will be renamed to CustomerId1,
    /// CustomerId2 etc. 
    ///
    /// Since the names generated are globally unique, they will not conflict when the
    /// columns of a JOIN SELECT statement are joined with another JOIN.
    /// 
    /// 
    /// 
    ///  
    /// Record flattening.
    /// SQL server does not have the concept of records.  However, a join statement 
    /// produces records.  We have to flatten the record accesses into a simple
    /// alias.column form.  
    /// 
    /// 
    /// 
    /// Building the SQL. 
    /// There are 2 phases 
    /// 
    /// Traverse the tree, producing a sql builder  
    /// Write the SqlBuilder into a string, renaming the aliases and columns
    /// as needed.
    /// 
    /// 
    /// In the first phase, we traverse the tree.  We cannot generate the SQL string
    /// right away, since 
    ///  
    /// The WHERE clause has to be visited before the from clause.
    /// extent aliases and column aliases need to be renamed.  To minimize 
    /// renaming collisions, all the names used must be known, before any renaming
    /// choice is made.
    /// 
    /// To defer the renaming choices, we use symbols .  These 
    /// are renamed in the second phase.
    /// 
    /// Since visitor methods cannot transfer information to child nodes through 
    /// parameters, we use some global stacks,
    ///  
    /// A stack for the current SQL select statement.  This is needed by
    ///  to create a
    /// list of free variables used by a select statement.  This is needed for
    /// alias renaming. 
    /// 
    /// A stack for the join context.  When visiting an extent, 
    /// we need to know whether we are inside a join or not.  If we are inside 
    /// a join, we do not create a new SELECT statement.
    ///  
    /// 
    ///
    /// 
    /// Global state. 
    /// To enable renaming, we maintain
    ///  
    /// The set of all extent aliases used. 
    /// The set of all parameter names.
    /// The set of all column names that may need to be renamed. 
    /// 
    ///
    /// Finally, we have a symbol table to lookup variable references.  All references
    /// to the same extent have the same symbol. 
    /// 
    /// 
    ///  
    /// Sql select statement sharing.
    /// 
    /// Each of the relational operator nodes
    /// 
    /// Project
    /// Filter 
    /// GroupBy
    /// Sort/OrderBy 
    ///  
    /// can add its non-input (e.g. project, predicate, sort order etc.) to
    /// the SQL statement for the input, or create a new SQL statement. 
    /// If it chooses to reuse the input's SQL statement, we play the following
    /// symbol table trick to accomplish renaming.  The symbol table entry for
    /// the alias of the current node points to the symbol for the input in
    /// the input's SQL statement. 
    /// 
    ///  
    /// Project(b.x 
    ///     b = Filter(
    ///         c = Extent(foo) 
    ///         c.x = 5)
    ///     )
    /// 
    /// The Extent node creates a new SqlSelectStatement.  This is added to the 
    /// symbol table by the Filter as {c, Symbol(c)}.  Thus, c.x is resolved to
    /// Symbol(c).x. 
    /// Looking at the project node, we add {b, Symbol(c)} to the symbol table if the 
    /// SQL statement is reused, and {b, Symbol(b)}, if there is no reuse.
    /// 
    /// Thus, b.x is resolved to Symbol(c).x if there is reuse, and to
    /// Symbol(b).x if there is no reuse.
    /// 
    ///  
    /// 
    internal sealed class SqlGenerator : DbExpressionVisitor 
    { 
        #region Visitor parameter stacks
        ///  
        /// Every relational node has to pass its SELECT statement to its children
        /// This allows them (DbVariableReferenceExpression eventually) to update the list of
        /// outer extents (free variables) used by this select statement.
        ///  
        private Stack selectStatementStack;
 
        ///  
        /// The top of the stack
        ///  
        private SqlSelectStatement CurrentSelectStatement
        {
            // There is always something on the stack, so we can always Peek.
            get { return selectStatementStack.Peek(); } 
        }
 
        ///  
        /// Nested joins and extents need to know whether they should create
        /// a new Select statement, or reuse the parent's.  This flag 
        /// indicates whether the parent is a join or not.
        /// 
        private Stack isParentAJoinStack;
 
        /// 
        /// The top of the stack 
        ///  
        private bool IsParentAJoin
        { 
            // There might be no entry on the stack if a Join node has never
            // been seen, so we return false in that case.
            get { return isParentAJoinStack.Count == 0 ? false : isParentAJoinStack.Peek(); }
        } 

        #endregion 
 
        #region Global lists and state
        Dictionary allExtentNames; 
        internal Dictionary AllExtentNames
        {
            get { return allExtentNames; }
        } 

        // For each column name, we store the last integer suffix that 
        // was added to produce a unique column name.  This speeds up 
        // the creation of the next unique name for this column name.
        Dictionary allColumnNames; 
        internal Dictionary AllColumnNames
        {
            get { return allColumnNames; }
        } 

        SymbolTable symbolTable = new SymbolTable(); 
 
        /// 
        /// VariableReferenceExpressions are allowed only as children of DbPropertyExpression 
        /// or MethodExpression.  The cheapest way to ensure this is to set the following
        /// property in DbVariableReferenceExpression and reset it in the allowed parent expressions.
        /// 
        private bool isVarRefSingle; 

        #endregion 
 
        #region Statics
        static private readonly Dictionary _storeFunctionHandlers = InitializeStoreFunctionHandlers(); 
        static private readonly Dictionary _canonicalFunctionHandlers = InitializeCanonicalFunctionHandlers();
        static private readonly Dictionary _functionNameToOperatorDictionary = InitializeFunctionNameToOperatorDictionary();
        static private readonly Set _datepartKeywords = new Set(new string[] {  "year", "yy", "yyyy",
                                                                                                 "quarter", "qq", "q", 
                                                                                                 "month", "mm", "m",
                                                                                                 "dayofyear", "dy", "y", 
                                                                                                 "day", "dd", "d", 
                                                                                                 "week", "wk", "ww",
                                                                                                 "weekday", "dw", "w", 
                                                                                                 "hour", "hh",
                                                                                                 "minute", "mi", "n",
                                                                                                 "second", "ss", "s",
                                                                                                 "millisecond", "ms", 
                                                                                                 "microsecond", "mcs",
                                                                                                 "nanosecond", "ns", 
                                                                                                 "tzoffset", "tz", 
                                                                                                 "iso_week", "isoww", "isowk"},
                                                                                        StringComparer.OrdinalIgnoreCase).MakeReadOnly(); 
        static private readonly char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
        static private readonly Set _functionRequiresReturnTypeCast = new Set(new string[] { "SqlServer.LEN"         ,
                                                                                                                 "SqlServer.PATINDEX"    ,
                                                                                                                 "SqlServer.CHARINDEX"   , 
                                                                                                                 "SqlServer.DATALENGTH"  ,
                                                                                                                 "Edm.IndexOf"           , 
                                                                                                                 "Edm.Length"            }, 
                                                                                                      StringComparer.Ordinal).MakeReadOnly();
        static private readonly Set _maxTypeNames = new Set(new string[] { "varchar(max)"    , 
                                                                                           "nvarchar(max)"   ,
                                                                                           "text"            ,
                                                                                           "ntext"           ,
                                                                                           "varbinary(max)"  , 
                                                                                           "image"           ,
                                                                                           "xml"             }, 
                                                                                StringComparer.Ordinal).MakeReadOnly(); 
        const byte defaultDecimalPrecision = 18;
 
        private delegate ISqlFragment FunctionHandler(SqlGenerator sqlgen, DbFunctionExpression functionExpr);

        /// 
        /// All special store functions and their handlers 
        /// 
        ///  
        private static Dictionary InitializeStoreFunctionHandlers() 
        {
            Dictionary functionHandlers = new Dictionary(5, StringComparer.Ordinal); 
            functionHandlers.Add("CONCAT", HandleConcatFunction);
            functionHandlers.Add("DATEADD", HandleDatepartDateFunction);
            functionHandlers.Add("DATEDIFF", HandleDatepartDateFunction);
            functionHandlers.Add("DATENAME", HandleDatepartDateFunction); 
            functionHandlers.Add("DATEPART", HandleDatepartDateFunction);
            return functionHandlers; 
        } 

        ///  
        /// All special non-aggregate canonical functions and their handlers
        /// 
        /// 
        private static Dictionary InitializeCanonicalFunctionHandlers() 
        {
            Dictionary functionHandlers = new Dictionary(16, StringComparer.Ordinal); 
            functionHandlers.Add("IndexOf", HandleCanonicalFunctionIndexOf); 
            functionHandlers.Add("Length", HandleCanonicalFunctionLength);
            functionHandlers.Add("NewGuid", HandleCanonicalFunctionNewGuid); 
            functionHandlers.Add("Round", HandleCanonicalFunctionRound);
            functionHandlers.Add("ToLower", HandleCanonicalFunctionToLower);
            functionHandlers.Add("ToUpper", HandleCanonicalFunctionToUpper);
            functionHandlers.Add("Trim", HandleCanonicalFunctionTrim); 

            //DateTime Functions 
            functionHandlers.Add("Year", HandleCanonicalFunctionDatepart); 
            functionHandlers.Add("Month", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Day", HandleCanonicalFunctionDatepart); 
            functionHandlers.Add("Hour", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Minute", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Second", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Millisecond", HandleCanonicalFunctionDatepart); 
            functionHandlers.Add("CurrentDateTime", HandleCanonicalFunctionCurrentDateTime);
            functionHandlers.Add("CurrentUtcDateTime", HandleCanonicalFunctionCurrentUtcDateTime); 
            functionHandlers.Add("CurrentDateTimeOffset", HandleCanonicalFunctionCurrentDateTimeOffset); 
            functionHandlers.Add("GetTotalOffsetMinutes", HandleCanonicalFunctionGetTotalOffsetMinutes);
 
            //Functions that translate to operators
            functionHandlers.Add("Concat", HandleConcatFunction);
            functionHandlers.Add("BitwiseAnd", HandleCanonicalFunctionBitwise);
            functionHandlers.Add("BitwiseNot", HandleCanonicalFunctionBitwise); 
            functionHandlers.Add("BitwiseOr", HandleCanonicalFunctionBitwise);
            functionHandlers.Add("BitwiseXor", HandleCanonicalFunctionBitwise); 
 
            return functionHandlers;
        } 

        /// 
        /// Initalizes the mapping from functions to TSql operators
        /// for all functions that translate to TSql operators 
        /// 
        ///  
        private static Dictionary InitializeFunctionNameToOperatorDictionary() 
        {
            Dictionary functionNameToOperatorDictionary = new Dictionary(5, StringComparer.Ordinal); 
            functionNameToOperatorDictionary.Add("Concat", "+");    //canonical
            functionNameToOperatorDictionary.Add("CONCAT", "+");    //store
            functionNameToOperatorDictionary.Add("BitwiseAnd", "&");
            functionNameToOperatorDictionary.Add("BitwiseNot", "~"); 
            functionNameToOperatorDictionary.Add("BitwiseOr", "|");
            functionNameToOperatorDictionary.Add("BitwiseXor", "^"); 
            return functionNameToOperatorDictionary; 
        }
 
        #endregion

        #region SqlVersion, Metadata, ...
 
        /// 
        /// The current SQL Server version 
        ///  
        private SqlVersion sqlVersion;
        internal SqlVersion SqlVersion 
        {
            get { return sqlVersion; }
        }
        internal bool IsPreKatmai 
        {
            get { return SqlVersionUtils.IsPreKatmai(this.SqlVersion); } 
        } 

        private MetadataWorkspace metadataWorkspace; 
        internal MetadataWorkspace Workspace
        {
            get { return metadataWorkspace; }
        } 

        private TypeUsage integerType = null; 
        internal TypeUsage IntegerType 
        {
            get 
            {
                if (integerType == null)
                {
                    integerType = GetPrimitiveType(PrimitiveTypeKind.Int64); 
                }
                return integerType; 
            } 
        }
        private StoreItemCollection _storeItemCollection; 
        #endregion

        #region Constructor
        ///  
        /// Basic constructor.
        ///  
        /// server version 
        private SqlGenerator(SqlVersion sqlVersion)
        { 
            this.sqlVersion = sqlVersion;
        }
        #endregion
 
        #region Entry points
        ///  
        /// General purpose static function that can be called from System.Data assembly 
        /// 
        /// Server version 
        /// command tree
        /// Parameters to add to the command tree corresponding
        /// to constants in the command tree. Used only in ModificationCommandTrees.
        /// CommandType for generated command. 
        /// The string representing the SQL to be executed.
        internal static string GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, out List parameters, out CommandType commandType) 
        { 
            SqlGenerator sqlGen;
            commandType = CommandType.Text; 
            parameters = null;

            switch (tree.CommandTreeKind)
            { 
                case DbCommandTreeKind.Query:
                    sqlGen = new SqlGenerator(sqlVersion); 
                    return sqlGen.GenerateSql((DbQueryCommandTree)tree); 

                case DbCommandTreeKind.Insert: 
                    return DmlSqlGenerator.GenerateInsertSql((DbInsertCommandTree)tree, sqlVersion, out parameters);

                case DbCommandTreeKind.Delete:
                    return DmlSqlGenerator.GenerateDeleteSql((DbDeleteCommandTree)tree, sqlVersion, out parameters); 

                case DbCommandTreeKind.Update: 
                    return DmlSqlGenerator.GenerateUpdateSql((DbUpdateCommandTree)tree, sqlVersion, out parameters); 

                case DbCommandTreeKind.Function: 
                    sqlGen = new SqlGenerator(sqlVersion);
                    return GenerateFunctionSql((DbFunctionCommandTree)tree, out commandType);

                default: 
                    //We have covered all command tree kinds
                    Debug.Assert(false, "Unknown command tree kind"); 
                    parameters = null; 
                    return null;
            } 
        }

        private static string GenerateFunctionSql(DbFunctionCommandTree tree, out CommandType commandType)
        { 
            tree.Validate();
 
            EdmFunction function = tree.EdmFunction; 

            if (String.IsNullOrEmpty(function.CommandTextAttribute)) 
            {
                // build a quoted description of the function
                commandType = CommandType.StoredProcedure;
 
                // if the schema name is not explicitly given, it is assumed to be the metadata namespace
                string schemaName = String.IsNullOrEmpty(function.Schema) ? 
                    function.NamespaceName : function.Schema; 

                // if the function store name is not explicitly given, it is assumed to be the metadata name 
                string functionName = String.IsNullOrEmpty(function.StoreFunctionNameAttribute) ?
                    function.Name : function.StoreFunctionNameAttribute;

                // quote elements of function text 
                string quotedSchemaName = QuoteIdentifier(schemaName);
                string quotedFunctionName = QuoteIdentifier(functionName); 
 
                // separator
                const string schemaSeparator = "."; 

                // concatenate elements of function text
                string quotedFunctionText = quotedSchemaName + schemaSeparator + quotedFunctionName;
 
                return quotedFunctionText;
            } 
            else 
            {
                // if the user has specified the command text, pass it through verbatim and choose CommandType.Text 
                commandType = CommandType.Text;
                return function.CommandTextAttribute;
            }
        } 
        #endregion
 
        #region Driver Methods 
        /// 
        /// Translate a command tree to a SQL string. 
        ///
        /// The input tree could be translated to either a SQL SELECT statement
        /// or a SELECT expression.  This choice is made based on the return type
        /// of the expression 
        /// CollectionType => select statement
        /// non collection type => select expression 
        ///  
        /// 
        /// The string representing the SQL to be executed. 
        private string GenerateSql(DbQueryCommandTree tree)
        {
            tree.Validate();
 
            DbQueryCommandTree targetTree = tree;
 
            //If we are on Sql 8.0 rewrite the tree if needed 
            if (this.SqlVersion == SqlVersion.Sql8)
            { 
                if (Sql8ConformanceChecker.NeedsRewrite(tree.Query))
                {
                    targetTree = Sql8ExpressionRewriter.Rewrite(tree);
                } 
            }
 
            this.metadataWorkspace = targetTree.MetadataWorkspace; 
            // needed in Private Type Helpers section bellow
            _storeItemCollection = (StoreItemCollection)this.Workspace.GetItemCollection(DataSpace.SSpace); 

            selectStatementStack = new Stack();
            isParentAJoinStack = new Stack();
 
            allExtentNames = new Dictionary(StringComparer.OrdinalIgnoreCase);
            allColumnNames = new Dictionary(StringComparer.OrdinalIgnoreCase); 
 
            // Literals will not be converted to parameters.
 
            ISqlFragment result;
            if (TypeSemantics.IsCollectionType(targetTree.Query.ResultType))
            {
                SqlSelectStatement sqlStatement = VisitExpressionEnsureSqlStatement(targetTree.Query); 
                Debug.Assert(sqlStatement != null, "The outer most sql statment is null");
                sqlStatement.IsTopMost = true; 
                result = sqlStatement; 

            } 
            else
            {
                SqlBuilder sqlBuilder = new SqlBuilder();
                sqlBuilder.Append("SELECT "); 
                sqlBuilder.Append(targetTree.Query.Accept(this));
 
                result = sqlBuilder; 
            }
 
            if (isVarRefSingle)
            {
                throw EntityUtil.NotSupported();
                // A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression 
            }
 
            // Check that the parameter stacks are not leaking. 
            Debug.Assert(selectStatementStack.Count == 0);
            Debug.Assert(isParentAJoinStack.Count == 0); 

            return WriteSql(result);
        }
 
        /// 
        /// Convert the SQL fragments to a string. 
        /// We have to setup the Stream for writing. 
        /// 
        ///  
        /// A string representing the SQL to be executed.
        private string WriteSql(ISqlFragment sqlStatement)
        {
            StringBuilder builder = new StringBuilder(1024); 
            using (SqlWriter writer = new SqlWriter(builder))
            { 
                sqlStatement.WriteSql(writer, this); 
            }
 
            return builder.ToString();
        }
        #endregion
 
        #region IExpressionVisitor Members
 
        ///  
        /// Translate(left) AND Translate(right)
        ///  
        /// 
        /// A .
        public override ISqlFragment Visit(DbAndExpression e)
        { 
            return VisitBinaryExpression(" AND ", DbExpressionKind.And, e.Left, e.Right);
        } 
 
        /// 
        /// An apply is just like a join, so it shares the common join processing 
        /// in 
        /// 
        /// 
        /// A . 
        public override ISqlFragment Visit(DbApplyExpression e)
        { 
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbApplyExpression when translating for SQL Server 2000."); 

            List inputs = new List(); 
            inputs.Add(e.Input);
            inputs.Add(e.Apply);

            string joinString; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.CrossApply: 
                    joinString = "CROSS APPLY";
                    break; 

                case DbExpressionKind.OuterApply:
                    joinString = "OUTER APPLY";
                    break; 

                default: 
                    Debug.Assert(false); 
                    throw EntityUtil.InvalidOperation(String.Empty);
            } 

            // The join condition does not exist in this case, so we use null.
            // WE do not have a on clause, so we use JoinType.CrossJoin.
            return VisitJoinExpression(inputs, DbExpressionKind.CrossJoin, joinString, null); 
        }
 
        ///  
        /// For binary expressions, we delegate to .
        /// We handle the other expressions directly. 
        /// 
        /// 
        /// A 
        public override ISqlFragment Visit(DbArithmeticExpression e) 
        {
            SqlBuilder result; 
 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.Divide:
                    result = VisitBinaryExpression(" / ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break;
                case DbExpressionKind.Minus: 
                    result = VisitBinaryExpression(" - ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break; 
                case DbExpressionKind.Modulo: 
                    result = VisitBinaryExpression(" % ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break; 
                case DbExpressionKind.Multiply:
                    result = VisitBinaryExpression(" * ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break;
                case DbExpressionKind.Plus: 
                    result = VisitBinaryExpression(" + ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break; 
 
                case DbExpressionKind.UnaryMinus:
                    result = new SqlBuilder(); 
                    result.Append(" -(");
                    result.Append(e.Arguments[0].Accept(this));
                    result.Append(")");
                    break; 

                default: 
                    Debug.Assert(false); 
                    throw EntityUtil.InvalidOperation(String.Empty);
            } 

            return result;
        }
 
        /// 
        /// If the ELSE clause is null, we do not write it out. 
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbCaseExpression e)
        {
            SqlBuilder result = new SqlBuilder();
 
            Debug.Assert(e.When.Count == e.Then.Count);
 
            result.Append("CASE"); 
            for (int i = 0; i < e.When.Count; ++i)
            { 
                result.Append(" WHEN (");
                result.Append(e.When[i].Accept(this));
                result.Append(") THEN ");
                result.Append(e.Then[i].Accept(this)); 
            }
            // 
 
            if (e.Else != null && !(e.Else is DbNullExpression))
            { 
                result.Append(" ELSE ");
                result.Append(e.Else.Accept(this));
            }
 
            result.Append(" END");
 
            return result; 
        }
 
        /// 
        ///
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbCastExpression e) 
        { 
            SqlBuilder result = new SqlBuilder();
            result.Append(" CAST( "); 
            result.Append(e.Argument.Accept(this));
            result.Append(" AS ");
            result.Append(GetSqlPrimitiveType(e.ResultType));
            result.Append(")"); 

            return result; 
        } 

        ///  
        /// The parser generates Not(Equals(...)) for <>.
        /// 
        /// 
        /// A . 
        public override ISqlFragment Visit(DbComparisonExpression e)
        { 
            SqlBuilder result; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.Equals:
                    result = VisitComparisonExpression(" = ", e.Left, e.Right);
                    break;
                case DbExpressionKind.LessThan: 
                    result = VisitComparisonExpression(" < ", e.Left, e.Right);
                    break; 
                case DbExpressionKind.LessThanOrEquals: 
                    result = VisitComparisonExpression(" <= ", e.Left, e.Right);
                    break; 
                case DbExpressionKind.GreaterThan:
                    result = VisitComparisonExpression(" > ", e.Left, e.Right);
                    break;
                case DbExpressionKind.GreaterThanOrEquals: 
                    result = VisitComparisonExpression(" >= ", e.Left, e.Right);
                    break; 
                    // The parser does not generate the expression kind below. 
                case DbExpressionKind.NotEquals:
                    result = VisitComparisonExpression(" <> ", e.Left, e.Right); 
                    break;

                default:
                    Debug.Assert(false);  // The constructor should have prevented this 
                    throw EntityUtil.InvalidOperation(String.Empty);
            } 
 
            return result;
        } 

        /// 
        /// Generate tsql for a constant. Avoid the explicit cast (if possible) when
        /// the isCastOptional parameter is set 
        /// 
        /// the constant expression 
        /// can we avoid the CAST 
        /// the tsql fragment
        private ISqlFragment VisitConstant(DbConstantExpression e, bool isCastOptional) 
        {
            // Constants will be send to the store as part of the generated TSQL, not as parameters

            SqlBuilder result = new SqlBuilder(); 

            PrimitiveTypeKind typeKind; 
            // Model Types can be (at the time of this implementation): 
            //      Binary, Boolean, Byte, Date, DateTime, DateTimeOffset, Decimal, Double, Guid, Int16, Int32, Int64, Single, String, Time
            if (TypeHelpers.TryGetPrimitiveTypeKind(e.ResultType, out typeKind)) 
            {
                switch (typeKind)
                {
                    case PrimitiveTypeKind.Int32: 
                        // default sql server type for integral values.
                        result.Append(e.Value.ToString()); 
                        break; 

                    case PrimitiveTypeKind.Binary: 
                        result.Append(" 0x");
                        result.Append(ByteArrayToBinaryString((Byte[])e.Value));
                        result.Append(" ");
                        break; 

                    case PrimitiveTypeKind.Boolean: 
                        // Bugs 450277, 430294: Need to preserve the boolean type-ness of 
                        // this value for round-trippability
                        WrapWithCastIfNeeded(!isCastOptional, (bool)e.Value ? "1" : "0", "bit", result); 
                        break;

                    case PrimitiveTypeKind.Byte:
                        WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "tinyint", result); 
                        break;
 
                    case PrimitiveTypeKind.DateTime: 
                        result.Append("convert(");
                        result.Append(this.IsPreKatmai ? "datetime" : "datetime2"); 
                        result.Append(", ");
                        result.Append(EscapeSingleQuote(((System.DateTime)e.Value).ToString(this.IsPreKatmai ? "yyyy-MM-dd HH:mm:ss.fff" : "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture), false /* IsUnicode */));
                        result.Append(", 121)");
                        break; 

                    case PrimitiveTypeKind.Time: 
                        AssertKatmaiOrNewer(typeKind); 
                        result.Append("convert(");
                        result.Append(e.ResultType.EdmType.Name); 
                        result.Append(", ");
                        result.Append(EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */));
                        result.Append(", 121)");
                        break; 

                    case PrimitiveTypeKind.DateTimeOffset: 
                        AssertKatmaiOrNewer(typeKind); 
                        result.Append("convert(");
                        result.Append(e.ResultType.EdmType.Name); 
                        result.Append(", ");
                        result.Append(EscapeSingleQuote(((System.DateTimeOffset)e.Value).ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture), false /* IsUnicode */));
                        result.Append(", 121)");
                        break; 

                    case PrimitiveTypeKind.Decimal: 
                        string strDecimal = ((Decimal)e.Value).ToString(CultureInfo.InvariantCulture); 
                        // if the decimal value has no decimal part, cast as decimal to preserve type
                        // if the number has precision > int64 max precision, it will be handled as decimal by sql server 
                        // and does not need cast. if precision is lest then 20, then cast using Max(literal precision, sql default precision)
                        bool needsCast = -1 == strDecimal.IndexOf('.') && (strDecimal.TrimStart(new char[] { '-' }).Length < 20);

                        byte precision = (byte)Math.Max((Byte)strDecimal.Length, defaultDecimalPrecision); 
                        Debug.Assert(precision > 0, "Precision must be greater than zero");
 
                        string decimalType = "decimal(" + precision.ToString(CultureInfo.InvariantCulture) + ")"; 

                        WrapWithCastIfNeeded(needsCast, strDecimal, decimalType, result); 
                        break;

                    case PrimitiveTypeKind.Double:
                        WrapWithCastIfNeeded(true, ((Double)e.Value).ToString("R", CultureInfo.InvariantCulture), "float(53)", result); 
                        break;
 
                    case PrimitiveTypeKind.Guid: 
                        WrapWithCastIfNeeded(true, EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */), "uniqueidentifier", result);
                        break; 

                    case PrimitiveTypeKind.Int16:
                        WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "smallint", result);
                        break; 

                    case PrimitiveTypeKind.Int64: 
                        WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "bigint", result); 
                        break;
 
                    case PrimitiveTypeKind.Single:
                        WrapWithCastIfNeeded(true, ((Single)e.Value).ToString("R", CultureInfo.InvariantCulture), "real", result);
                        break;
 
                    case PrimitiveTypeKind.String:
                        bool isUnicode; 
                        if (!TypeHelpers.TryGetIsUnicode(e.ResultType, out isUnicode)) 
                        {
                            isUnicode = true; 
                        }
                        result.Append(EscapeSingleQuote(e.Value as string, isUnicode));
                        break;
 
                    default:
                        // all known scalar types should been handled already. 
                        throw EntityUtil.NotSupported(); 
                }
            } 
            else
            {
                throw EntityUtil.NotSupported();
                //if/when Enum types are supported, then handle appropriately, for now is not a valid type for constants. 
                //result.Append(e.Value.ToString());
            } 
 
            return result;
        } 

        /// 
        /// Helper function for 
        /// Appneds the given constant value to the result either 'as is' or wrapped with a cast to the given type. 
        /// 
        ///  
        ///  
        /// 
        ///  
        private static void WrapWithCastIfNeeded(bool cast, string value, string typeName, SqlBuilder result)
        {
            if (!cast)
            { 
                result.Append(value);
            } 
            else 
            {
                result.Append("cast("); 
                result.Append(value);
                result.Append(" as ");
                result.Append(typeName);
                result.Append(")"); 
            }
        } 
 
        /// 
        /// We do not pass constants as parameters. 
        /// 
        /// 
        /// A .  Strings are wrapped in single
        /// quotes and escaped.  Numbers are written literally. 
        public override ISqlFragment Visit(DbConstantExpression e)
        { 
            return VisitConstant(e, false /* isCastOptional */); 
        }
 
        /// 
        ///
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbDerefExpression e) 
        { 
            throw EntityUtil.NotSupported();
        } 

        /// 
        /// The DISTINCT has to be added to the beginning of SqlSelectStatement.Select,
        /// but it might be too late for that.  So, we use a flag on SqlSelectStatement 
        /// instead, and add the "DISTINCT" in the second phase.
        ///  
        ///  
        /// A 
        public override ISqlFragment Visit(DbDistinctExpression e) 
        {
            SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument);

            if (!IsCompatible(result, e.ExpressionKind)) 
            {
                Symbol fromSymbol; 
                TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType); 
                result = CreateNewSelectStatement(result, "distinct", inputType, out fromSymbol);
                AddFromSymbol(result, "distinct", fromSymbol, false); 
            }

            result.IsDistinct = true;
            return result; 
        }
 
        ///  
        /// An element expression returns a scalar - so it is translated to
        /// ( Select ... ) 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbElementExpression e) 
        {
            // ISSUE: What happens if the DbElementExpression is used as an input expression? 
            // i.e. adding the '('  might not be right in all cases. 
            SqlBuilder result = new SqlBuilder();
            result.Append("("); 
            result.Append(VisitExpressionEnsureSqlStatement(e.Argument));
            result.Append(")");

            return result; 
        }
 
        ///  
        /// 
        ///  
        /// 
        /// 
        public override ISqlFragment Visit(DbExceptExpression e)
        { 
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbExceptExpression when translating for SQL Server 2000.");
 
            return VisitSetOpExpression(e.Left, e.Right, "EXCEPT"); 
        }
 
        /// 
        /// Only concrete expression types will be visited.
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbExpression e) 
        { 
            throw EntityUtil.InvalidOperation(String.Empty);
        } 

        /// 
        ///
        ///  
        /// 
        /// If we are in a Join context, returns a  
        /// with the extent name, otherwise, a new  
        /// with the From field set.
        public override ISqlFragment Visit(DbScanExpression e) 
        {
            EntitySetBase target = e.Target;

            // ISSUE: Should we just return a string all the time, and let 
            // VisitInputExpression create the SqlSelectStatement?
 
            if (IsParentAJoin) 
            {
                SqlBuilder result = new SqlBuilder(); 
                result.Append(GetTargetTSql(target));

                return result;
            } 
            else
            { 
                SqlSelectStatement result = new SqlSelectStatement(); 
                result.From.Append(GetTargetTSql(target));
 
                return result;
            }
        }
 
        /// 
        /// Gets escaped TSql identifier describing this entity set. 
        ///  
        /// 
        internal static string GetTargetTSql(EntitySetBase entitySetBase) 
        {
            if (null == entitySetBase.CachedProviderSql)
            {
                if (null == entitySetBase.DefiningQuery) 
                {
                    // construct escaped T-SQL referencing entity set 
                    StringBuilder builder = new StringBuilder(50); 
                    if (!string.IsNullOrEmpty(entitySetBase.Schema))
                    { 
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Schema));
                        builder.Append(".");
                    }
                    else 
                    {
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.EntityContainer.Name)); 
                        builder.Append("."); 
                    }
 
                    if (!string.IsNullOrEmpty(entitySetBase.Table))
                    {
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Table));
                    } 
                    else
                    { 
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Name)); 
                    }
                    entitySetBase.CachedProviderSql = builder.ToString(); 
                }
                else
                {
                    entitySetBase.CachedProviderSql = "(" + entitySetBase.DefiningQuery + ")"; 
                }
            } 
            return entitySetBase.CachedProviderSql; 
        }
 
        /// 
        /// The bodies of , ,
        /// ,  are similar.
        /// Each does the following. 
        /// 
        ///  Visit the input expression 
        ///  Determine if the input's SQL statement can be reused, or a new 
        /// one must be created.
        /// Create a new symbol table scope 
        /// Push the Sql statement onto a stack, so that children can
        /// update the free variable list.
        /// Visit the non-input expression.
        /// Cleanup 
        /// 
        ///  
        ///  
        /// A 
        public override ISqlFragment Visit(DbFilterExpression e) 
        {
            return VisitFilterExpression(e.Input, e.Predicate, false);
        }
 
        /// 
        /// Lambda functions are not supported. 
        /// The functions supported are: 
        /// 
        /// Canonical Functions - We recognize these by their dataspace, it is DataSpace.CSpace 
        /// Store Functions - We recognize these by the BuiltInAttribute and not being Canonical
        /// User-defined Functions - All the rest except for Lambda functions
        /// 
        /// We handle Canonical and Store functions the same way: If they are in the list of functions 
        /// that need special handling, we invoke the appropriate handler, otherwise we translate them to
        /// FunctionName(arg1, arg2, ..., argn). 
        /// We translate user-defined functions to NamespaceName.FunctionName(arg1, arg2, ..., argn). 
        /// 
        ///  
        /// A 
        public override ISqlFragment Visit(DbFunctionExpression e)
        {
            if (e.IsLambda) 
            {
                throw EntityUtil.NotSupported(); 
            } 

            // 
            // check if function requires special case processing, if so, delegates to it
            //
            if (IsSpecialCanonicalFunction(e))
            { 
                return HandleSpecialCanonicalFunction(e);
            } 
 
            if (IsSpecialStoreFunction(e))
            { 
                return HandleSpecialStoreFunction(e);
            }

            return HandleFunctionDefault(e); 
        }
 
        ///  
        ///
        ///  
        /// 
        /// 
        public override ISqlFragment Visit(DbEntityRefExpression e)
        { 
            throw EntityUtil.NotSupported();
        } 
 
        /// 
        /// 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbRefKeyExpression e) 
        {
            throw EntityUtil.NotSupported(); 
        } 

        ///  
        ///  for general details.
        /// We modify both the GroupBy and the Select fields of the SqlSelectStatement.
        /// GroupBy gets just the keys without aliases,
        /// and Select gets the keys and the aggregates with aliases. 
        ///
        /// Sql Server does not support arbitrarily complex expressions inside aggregates, 
        /// and requires keys to have reference to the input scope, 
        /// so in some cases we create a nested query in which we alias the arguments to the aggregates.
        /// The exact limitations of Sql Server are: 
        /// 
        /// If an expression being aggregated contains an outer reference, then that outer
        /// reference must be the only column referenced in the expression (SQLBUDT #488741)
        /// Sql Server cannot perform an aggregate function on an expression containing 
        /// an aggregate or a subquery. (SQLBUDT #504600)
        ///Sql Server requries each GROUP BY expression (key) to contain at least one column 
        /// that is not an outer reference. (SQLBUDT #616523) 
        /// Aggregates on the right side of an APPLY cannot reference columns from the left side.
        /// (SQLBUDT #617683)  
        /// 
        ///
        /// The default translation, without inner query is:
        /// 
        ///     SELECT
        ///         kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn, 
        ///         aggf1(aexpr1) AS agg1, .. aggfn(aexprn) AS aggn 
        ///     FROM input AS a
        ///     GROUP BY kexp1, kexp2, .. kexpn 
        ///
        /// When we inject an innner query, the equivalent translation is:
        ///
        ///     SELECT 
        ///         key1 AS key1, key2 AS key2, .. keyn AS keys,
        ///         aggf1(agg1) AS agg1, aggfn(aggn) AS aggn 
        ///     FROM ( 
        ///             SELECT
        ///                 kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn, 
        ///                 aexpr1 AS agg1, .. aexprn AS aggn
        ///             FROM input AS a
        ///         ) as a
        ///     GROUP BY key1, key2, keyn 
        ///
        ///  
        ///  
        /// A 
        public override ISqlFragment Visit(DbGroupByExpression e) 
        {
            Symbol fromSymbol;
            SqlSelectStatement innerQuery = VisitInputExpression(e.Input.Expression,
                e.Input.VariableName, e.Input.VariableType, out fromSymbol); 

            // GroupBy is compatible with Filter and OrderBy 
            // but not with Project, GroupBy 
            if (!IsCompatible(innerQuery, e.ExpressionKind))
            { 
                innerQuery = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
            }

            selectStatementStack.Push(innerQuery); 
            symbolTable.EnterScope();
 
            AddFromSymbol(innerQuery, e.Input.VariableName, fromSymbol); 
            // This line is not present for other relational nodes.
            symbolTable.Add(e.Input.GroupVariableName, fromSymbol); 

            // The enumerator is shared by both the keys and the aggregates,
            // so, we do not close it in between.
            RowType groupByType = TypeHelpers.GetEdmType(TypeHelpers.GetEdmType(e.ResultType).TypeUsage); 

            //SQL Server does not support arbitrarily complex expressions inside aggregates, 
            // and requires keys to have reference to the input scope, 
            // so we check for the specific restrictions and if need we inject an inner query.
            bool needsInnerQuery = GroupByAggregatesNeedInnerQuery(e.Aggregates, e.Input.GroupVariableName) || GroupByKeysNeedInnerQuery(e.Keys, e.Input.VariableName); 

            SqlSelectStatement result;
            if (needsInnerQuery)
            { 
                //Create the inner query
                result = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, false, out fromSymbol); 
                AddFromSymbol(result, e.Input.VariableName, fromSymbol, false); 
            }
            else 
            {
                result = innerQuery;
            }
 
            using (IEnumerator members = groupByType.Properties.GetEnumerator())
            { 
                members.MoveNext(); 
                Debug.Assert(result.Select.IsEmpty);
 
                string separator = "";

                foreach (DbExpression key in e.Keys)
                { 
                    EdmProperty member = members.Current;
                    string alias = QuoteIdentifier(member.Name); 
 
                    result.GroupBy.Append(separator);
 
                    ISqlFragment keySql = key.Accept(this);

                    if (!needsInnerQuery)
                    { 
                        //Default translation: Key AS Alias
                        result.Select.Append(separator); 
                        result.Select.AppendLine(); 
                        result.Select.Append(keySql);
                        result.Select.Append(" AS "); 
                        result.Select.Append(alias);

                        result.GroupBy.Append(keySql);
                    } 
                    else
                    { 
                        // The inner query contains the default translation Key AS Alias 
                        innerQuery.Select.Append(separator);
                        innerQuery.Select.AppendLine(); 
                        innerQuery.Select.Append(keySql);
                        innerQuery.Select.Append(" AS ");
                        innerQuery.Select.Append(alias);
 
                        //The outer resulting query projects over the key aliased in the inner query:
                        //  fromSymbol.Alias AS Alias 
                        result.Select.Append(separator); 
                        result.Select.AppendLine();
                        result.Select.Append(fromSymbol); 
                        result.Select.Append(".");
                        result.Select.Append(alias);
                        result.Select.Append(" AS ");
                        result.Select.Append(alias); 

                        result.GroupBy.Append(alias); 
                    } 

                    separator = ", "; 
                    members.MoveNext();
                }

                foreach (DbAggregate aggregate in e.Aggregates) 
                {
                    EdmProperty member = members.Current; 
                    string alias = QuoteIdentifier(member.Name); 

                    Debug.Assert(aggregate.Arguments.Count == 1); 
                    ISqlFragment translatedAggregateArgument = aggregate.Arguments[0].Accept(this);

                    object aggregateArgument;
 
                    if (needsInnerQuery)
                    { 
                        //In this case the argument to the aggratete is reference to the one projected out by the 
                        // inner query
                        SqlBuilder wrappingAggregateArgument = new SqlBuilder(); 
                        wrappingAggregateArgument.Append(fromSymbol);
                        wrappingAggregateArgument.Append(".");
                        wrappingAggregateArgument.Append(alias);
                        aggregateArgument = wrappingAggregateArgument; 

                        innerQuery.Select.Append(separator); 
                        innerQuery.Select.AppendLine(); 
                        innerQuery.Select.Append(translatedAggregateArgument);
                        innerQuery.Select.Append(" AS "); 
                        innerQuery.Select.Append(alias);
                    }
                    else
                    { 
                        aggregateArgument = translatedAggregateArgument;
                    } 
 
                    ISqlFragment aggregateResult = VisitAggregate(aggregate, aggregateArgument);
 
                    result.Select.Append(separator);
                    result.Select.AppendLine();
                    result.Select.Append(aggregateResult);
                    result.Select.Append(" AS "); 
                    result.Select.Append(alias);
 
                    separator = ", "; 
                    members.MoveNext();
                } 
            }

            symbolTable.ExitScope();
            selectStatementStack.Pop(); 

            return result; 
        } 

        ///  
        /// 
        /// 
        /// 
        ///  
        public override ISqlFragment Visit(DbIntersectExpression e)
        { 
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbIntersectExpression when translating for SQL Server 2000."); 

            return VisitSetOpExpression(e.Left, e.Right, "INTERSECT"); 
        }

        /// 
        /// Not(IsEmpty) has to be handled specially, so we delegate to 
        /// .
        /// 
        ///  
        /// 
        /// A . 
        /// [NOT] EXISTS( ... )
        /// 
        public override ISqlFragment Visit(DbIsEmptyExpression e)
        { 
            return VisitIsEmptyExpression(e, false);
        } 
 
        /// 
        /// Not(IsNull) is handled specially, so we delegate to 
        /// 
        /// 
        /// 
        /// A  
        /// IS [NOT] NULL
        ///  
        public override ISqlFragment Visit(DbIsNullExpression e) 
        {
            return VisitIsNullExpression(e, false); 
        }

        /// 
        /// No error is raised if the store cannot support this. 
        /// 
        ///  
        /// A  
        public override ISqlFragment Visit(DbIsOfExpression e)
        { 
            throw EntityUtil.NotSupported();
        }

        ///  
        /// 
        ///  
        ///  
        /// A .
        public override ISqlFragment Visit(DbCrossJoinExpression e) 
        {
            return VisitJoinExpression(e.Inputs, e.ExpressionKind, "CROSS JOIN", null);
        }
 
        /// 
        ///  
        ///  
        /// 
        /// A . 
        public override ISqlFragment Visit(DbJoinExpression e)
        {
            #region Map join type to a string
            string joinString; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.FullOuterJoin: 
                    joinString = "FULL OUTER JOIN";
                    break; 

                case DbExpressionKind.InnerJoin:
                    joinString = "INNER JOIN";
                    break; 

                case DbExpressionKind.LeftOuterJoin: 
                    joinString = "LEFT OUTER JOIN"; 
                    break;
 
                default:
                    Debug.Assert(false);
                    joinString = null;
                    break; 
            }
            #endregion 
 
            List inputs = new List(2);
            inputs.Add(e.Left); 
            inputs.Add(e.Right);

            return VisitJoinExpression(inputs, e.ExpressionKind, joinString, e.JoinCondition);
        } 

        ///  
        /// 
        /// 
        ///  
        /// A 
        public override ISqlFragment Visit(DbLikeExpression e)
        {
            SqlBuilder result = new SqlBuilder(); 
            result.Append(e.Argument.Accept(this));
            result.Append(" LIKE "); 
            result.Append(e.Pattern.Accept(this)); 

            // if the ESCAPE expression is a DbNullExpression, then that's tantamount to 
            // not having an ESCAPE at all
            if (e.Escape.ExpressionKind != DbExpressionKind.Null)
            {
                result.Append(" ESCAPE "); 
                result.Append(e.Escape.Accept(this));
            } 
 
            return result;
        } 

        /// 
        ///  Translates to TOP expression. For Sql8, limit can only be a constant expression
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbLimitExpression e) 
        {
            Debug.Assert(e.Limit is DbConstantExpression || e.Limit is DbParameterReferenceExpression, "DbLimitExpression.Limit is of invalid expression type"); 
            Debug.Assert(!((this.SqlVersion == SqlVersion.Sql8) && (e.Limit is DbParameterReferenceExpression)), "DbLimitExpression.Limit is DbParameterReferenceExpression for SQL Server 2000.");

            SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument, false);
            Symbol fromSymbol; 

            if (!IsCompatible(result, e.ExpressionKind)) 
            { 
                TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
 
                result = CreateNewSelectStatement(result, "top", inputType, out fromSymbol);
                AddFromSymbol(result, "top", fromSymbol, false);
            }
 
            ISqlFragment topCount = HandleCountExpression(e.Limit) ;
 
            result.Top = new TopClause(topCount, e.WithTies); 
            return result;
        } 

#if METHOD_EXPRESSION
        /// 
        /// 
        /// 
        ///  
        /// A  
        public override ISqlFragment Visit(MethodExpression e)
        { 
            SqlBuilder result = new SqlBuilder();

            result.Append(e.Instance.Accept(this));
            result.Append("."); 
            result.Append(QuoteIdentifier(e.Method.Name));
            result.Append("("); 
 
            // Since the VariableReferenceExpression is a proper child of ours, we can reset
            // isVarSingle. 
            VariableReferenceExpression VariableReferenceExpression = e.Instance as VariableReferenceExpression;
            if (VariableReferenceExpression != null)
            {
                isVarRefSingle = false; 
            }
 
            string separator = ""; 
            foreach (Expression argument in e.Arguments)
            { 
                result.Append(separator);
                result.Append(argument.Accept(this));
                separator = ", ";
            } 
            result.Append(")");
 
            return result; 
        }
#endif 

        /// 
        /// DbNewInstanceExpression is allowed as a child of DbProjectExpression only.
        /// If anyone else is the parent, we throw. 
        /// We also perform special casing for collections - where we could convert
        /// them into Unions 
        /// 
        ///  for the actual implementation.
        /// 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbNewInstanceExpression e) 
        {
            if (TypeSemantics.IsCollectionType(e.ResultType)) 
            { 
                return VisitCollectionConstructor(e);
            } 
            throw EntityUtil.NotSupported();
        }

        ///  
        /// The Not expression may cause the translation of its child to change.
        /// These children are 
        ///  
        /// NOT(Not(x)) becomes x
        /// NOT EXISTS becomes EXISTS 
        /// IS NULL becomes IS NOT NULL
        /// = becomes<> 
        /// 
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbNotExpression e) 
        {
            // Flatten Not(Not(x)) to x. 
            DbNotExpression notExpression = e.Argument as DbNotExpression;
            if (notExpression != null)
            {
                return notExpression.Argument.Accept(this); 
            }
 
            DbIsEmptyExpression isEmptyExpression = e.Argument as DbIsEmptyExpression; 
            if (isEmptyExpression != null)
            { 
                return VisitIsEmptyExpression(isEmptyExpression, true);
            }

            DbIsNullExpression isNullExpression = e.Argument as DbIsNullExpression; 
            if (isNullExpression != null)
            { 
                return VisitIsNullExpression(isNullExpression, true); 
            }
 
            DbComparisonExpression comparisonExpression = e.Argument as DbComparisonExpression;
            if (comparisonExpression != null)
            {
                if (comparisonExpression.ExpressionKind == DbExpressionKind.Equals) 
                {
                    return VisitBinaryExpression(" <> ", DbExpressionKind.NotEquals, comparisonExpression.Left, comparisonExpression.Right); 
                } 
            }
 
            SqlBuilder result = new SqlBuilder();
            result.Append(" NOT (");
            result.Append(e.Argument.Accept(this));
            result.Append(")"); 

            return result; 
        } 

        ///  
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbNullExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
 
            // always cast nulls - sqlserver doesn't like case expressions where the "then" clause is null
            result.Append("CAST(NULL AS "); 
            TypeUsage type = e.ResultType;

            //
            // Use the narrowest type possible - especially for strings where we don't want 
            // to produce unicode strings always.
            // 
            Debug.Assert(Helper.IsPrimitiveType(type.EdmType), "Type must be primitive type"); 
            PrimitiveType primitiveType = type.EdmType as PrimitiveType;
            switch(primitiveType.PrimitiveTypeKind) 
            {
                case PrimitiveTypeKind.String:
                    result.Append("varchar(1)");
                    break; 
                case PrimitiveTypeKind.Binary:
                    result.Append("varbinary(1)"); 
                    break; 
                default:
                    result.Append(GetSqlPrimitiveType(type)); 
                    break;
            }

            result.Append(")"); 
            return result;
        } 
 
        /// 
        /// 
        /// 
        /// 
        /// A 
        public override ISqlFragment Visit(DbOfTypeExpression e) 
        {
            throw EntityUtil.NotSupported(); 
        } 

        ///  
        ///
        /// 
        /// 
        /// A  
        /// 
        public override ISqlFragment Visit(DbOrExpression e) 
        { 
            return VisitBinaryExpression(" OR ", e.ExpressionKind, e.Left, e.Right);
        } 

        /// 
        ///
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbParameterReferenceExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
            // Do not quote this name.
            // ISSUE: We are not checking that e.Name has no illegal characters. e.g. space
            result.Append("@" + e.ParameterName);
 
            return result;
        } 
 
        /// 
        ///  for the general ideas. 
        /// 
        /// 
        /// A 
        ///  
        public override ISqlFragment Visit(DbProjectExpression e)
        { 
            Symbol fromSymbol; 
            SqlSelectStatement result = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
 
            //#444002 Aliases need renaming only for Sql8 when there is Order By
            bool aliasesNeedRenaming = false;

            // Project is compatible with Filter 
            // but not with Project, GroupBy
            if (!IsCompatible(result, e.ExpressionKind)) 
            { 
                result = CreateNewSelectStatement(result, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
            } 
            else if ((this.SqlVersion == SqlVersion.Sql8) && !result.OrderBy.IsEmpty)
            {
                aliasesNeedRenaming = true;
            } 

            selectStatementStack.Push(result); 
            symbolTable.EnterScope(); 

            AddFromSymbol(result, e.Input.VariableName, fromSymbol); 

            // Project is the only node that can have DbNewInstanceExpression as a child
            // so we have to check it here.
            // We call VisitNewInstanceExpression instead of Visit(DbNewInstanceExpression), since 
            // the latter throws.
            DbNewInstanceExpression newInstanceExpression = e.Projection as DbNewInstanceExpression; 
            if (newInstanceExpression != null) 
            {
                Dictionary newColumns; 
                result.Select.Append(VisitNewInstanceExpression(newInstanceExpression, aliasesNeedRenaming, out newColumns));
                if (aliasesNeedRenaming)
                {
                    result.OutputColumnsRenamed = true; 
                    result.OutputColumns = newColumns;
                } 
            } 
            else
            { 
                result.Select.Append(e.Projection.Accept(this));
            }

            symbolTable.ExitScope(); 
            selectStatementStack.Pop();
 
            return result; 
        }
 
        /// 
        /// This method handles record flattening, which works as follows.
        /// consider an expression Prop(y, Prop(x, Prop(d, Prop(c, Prop(b, Var(a)))))
        /// where a,b,c are joins, d is an extent and x and y are fields. 
        /// b has been flattened into a, and has its own SELECT statement.
        /// c has been flattened into b. 
        /// d has been flattened into c. 
        ///
        /// We visit the instance, so we reach Var(a) first.  This gives us a (join)symbol. 
        /// Symbol(a).b gives us a join symbol, with a SELECT statement i.e. Symbol(b).
        /// From this point on , we need to remember Symbol(b) as the source alias,
        /// and then try to find the column.  So, we use a SymbolPair.
        /// 
        /// We have reached the end when the symbol no longer points to a join symbol.
        ///  
        ///  
        /// A  if we have not reached the first
        /// Join node that has a SELECT statement. 
        /// A  if we have seen the JoinNode, and it has
        /// a SELECT statement.
        /// A  with {Input}.propertyName otherwise.
        ///  
        public override ISqlFragment Visit(DbPropertyExpression e)
        { 
            SqlBuilder result; 

            ISqlFragment instanceSql = e.Instance.Accept(this); 

            // Since the DbVariableReferenceExpression is a proper child of ours, we can reset
            // isVarSingle.
            DbVariableReferenceExpression VariableReferenceExpression = e.Instance as DbVariableReferenceExpression; 
            if (VariableReferenceExpression != null)
            { 
                isVarRefSingle = false; 
            }
 
            // We need to flatten, and have not yet seen the first nested SELECT statement.
            JoinSymbol joinSymbol = instanceSql as JoinSymbol;
            if (joinSymbol != null)
            { 
                Debug.Assert(joinSymbol.NameToExtent.ContainsKey(e.Property.Name));
                if (joinSymbol.IsNestedJoin) 
                { 
                    return new SymbolPair(joinSymbol, joinSymbol.NameToExtent[e.Property.Name]);
                } 
                else
                {
                    return joinSymbol.NameToExtent[e.Property.Name];
                } 
            }
 
            // --------------------------------------- 
            // We have seen the first nested SELECT statement, but not the column.
            SymbolPair symbolPair = instanceSql as SymbolPair; 
            if (symbolPair != null)
            {
                JoinSymbol columnJoinSymbol = symbolPair.Column as JoinSymbol;
                if (columnJoinSymbol != null) 
                {
                    symbolPair.Column = columnJoinSymbol.NameToExtent[e.Property.Name]; 
                    return symbolPair; 
                }
                else 
                {
                    // symbolPair.Column has the base extent.
                    // we need the symbol for the column, since it might have been renamed
                    // when handling a JOIN. 
                    if (symbolPair.Column.Columns.ContainsKey(e.Property.Name))
                    { 
                        result = new SqlBuilder(); 
                        result.Append(symbolPair.Source);
                        result.Append("."); 
                        result.Append(symbolPair.Column.Columns[e.Property.Name]);
                        return result;
                    }
                } 
            }
            // --------------------------------------- 
 
            result = new SqlBuilder();
            result.Append(instanceSql); 
            result.Append(".");

            Symbol symbol = instanceSql as Symbol;
 
            if (symbol != null && symbol.OutputColumnsRenamed)
            { 
                result.Append(symbol.Columns[e.Property.Name]); 
            }
            else 
            {
                // At this point the column name cannot be renamed, so we do
                // not use a symbol.
                result.Append(QuoteIdentifier(e.Property.Name)); 
            }
            return result; 
        } 

        ///  
        /// Any(input, x) => Exists(Filter(input,x))
        /// All(input, x) => Not Exists(Filter(input, not(x))
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbQuantifierExpression e) 
        { 
            SqlBuilder result = new SqlBuilder();
 
            bool negatePredicate = (e.ExpressionKind == DbExpressionKind.All);
            if (e.ExpressionKind == DbExpressionKind.Any)
            {
                result.Append("EXISTS ("); 
            }
            else 
            { 
                Debug.Assert(e.ExpressionKind == DbExpressionKind.All);
                result.Append("NOT EXISTS ("); 
            }

            SqlSelectStatement filter = VisitFilterExpression(e.Input, e.Predicate, negatePredicate);
            if (filter.Select.IsEmpty) 
            {
                AddDefaultColumns(filter); 
            } 

            result.Append(filter); 
            result.Append(")");

            return result;
        } 

        ///  
        /// 
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbRefExpression e)
        {
            throw EntityUtil.NotSupported(); 
        }
 
        ///  
        ///
        ///  
        /// 
        /// 
        public override ISqlFragment Visit(DbRelationshipNavigationExpression e)
        { 
            throw EntityUtil.NotSupported();
        } 
 
        /// 
        /// For Sql9 it translates to: 
        /// SELECT Y.x1, Y.x2, ..., Y.xn
        /// FROM (
        ///     SELECT X.x1, X.x2, ..., X.xn, row_number() OVER (ORDER BY sk1, sk2, ...) AS [row_number]
        ///     FROM input as X 
        ///     ) as Y
        /// WHERE Y.[row_number] > count 
        /// ORDER BY sk1, sk2, ... 
        /// 
        ///  
        /// A 
        public override ISqlFragment Visit(DbSkipExpression e)
        {
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbSkipExpression when translating for SQL Server 2000."); 

            Debug.Assert(e.Count is DbConstantExpression || e.Count is DbParameterReferenceExpression, "DbSkipExpression.Count is of invalid expression type"); 
 
            //Visit the input
            Symbol fromSymbol; 
            SqlSelectStatement input = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);

            // Skip is not compatible with anything that OrderBy is not compatible with, as well as with distinct
            if (!IsCompatible(input, e.ExpressionKind)) 
            {
                input = CreateNewSelectStatement(input, e.Input.VariableName, e.Input.VariableType, out fromSymbol); 
            } 

            selectStatementStack.Push(input); 
            symbolTable.EnterScope();

            AddFromSymbol(input, e.Input.VariableName, fromSymbol);
 
            //Add the default columns
            Debug.Assert(input.Select.IsEmpty); 
            List inputColumns = AddDefaultColumns(input); 

            input.Select.Append(", row_number() OVER (ORDER BY "); 
            AddSortKeys(input.Select, e.SortOrder);
            input.Select.Append(") AS ");

            Symbol row_numberSymbol = new Symbol("row_number", IntegerType); 

            input.Select.Append(row_numberSymbol); 
 
            //The inner statement is complete, its scopes need not be valid any longer
            symbolTable.ExitScope(); 
            selectStatementStack.Pop();

            //Create the resulting statement
            //See CreateNewSelectStatement, it is very similar 
            //Future Enhancement ([....]): Refactor to avoid duplication with CreateNewSelectStatement if we
            // don't switch to using ExtensionExpression here 
            SqlSelectStatement result = new SqlSelectStatement(); 
            result.From.Append("( ");
            result.From.Append(input); 
            result.From.AppendLine();
            result.From.Append(") ");

            //Create a symbol for the input 
            Symbol resultFromSymbol = null;
 
            if (input.FromExtents.Count == 1) 
            {
                JoinSymbol oldJoinSymbol = input.FromExtents[0] as JoinSymbol; 
                if (oldJoinSymbol != null)
                {
                    // Note: input.FromExtents will not do, since it might
                    // just be an alias of joinSymbol, and we want an actual JoinSymbol. 
                    JoinSymbol newJoinSymbol = new JoinSymbol(e.Input.VariableName, e.Input.VariableType, oldJoinSymbol.ExtentList);
                    // This indicates that the oldStatement is a blocking scope 
                    // i.e. it hides/renames extent columns 
                    newJoinSymbol.IsNestedJoin = true;
                    newJoinSymbol.ColumnList = inputColumns; 
                    newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;

                    resultFromSymbol = newJoinSymbol;
                } 
            }
 
            if (resultFromSymbol == null) 
            {
                // This is just a simple extent/SqlSelectStatement, 
                // and we can get the column list from the type.
                resultFromSymbol = new Symbol(e.Input.VariableName, e.Input.VariableType);
            }
            //Add the ORDER BY part 
            selectStatementStack.Push(result);
            symbolTable.EnterScope(); 
 
            AddFromSymbol(result, e.Input.VariableName, resultFromSymbol);
 
            //Add the predicate
            result.Where.Append(resultFromSymbol);
            result.Where.Append(".");
            result.Where.Append(row_numberSymbol); 
            result.Where.Append(" > ");
            result.Where.Append(HandleCountExpression(e.Count)); 
 
            AddSortKeys(result.OrderBy, e.SortOrder);
 
            symbolTable.ExitScope();
            selectStatementStack.Pop();

            return result; 
        }
 
        ///  
        /// 
        ///  
        /// 
        /// A 
        /// 
        public override ISqlFragment Visit(DbSortExpression e) 
        {
            Symbol fromSymbol; 
            SqlSelectStatement result = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol); 

            // OrderBy is compatible with Filter 
            // and nothing else
            if (!IsCompatible(result, e.ExpressionKind))
            {
                result = CreateNewSelectStatement(result, e.Input.VariableName, e.Input.VariableType, out fromSymbol); 
            }
 
            selectStatementStack.Push(result); 
            symbolTable.EnterScope();
 
            AddFromSymbol(result, e.Input.VariableName, fromSymbol);

            AddSortKeys(result.OrderBy, e.SortOrder);
 
            symbolTable.ExitScope();
            selectStatementStack.Pop(); 
 
            return result;
        } 

        /// 
        ///
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbTreatExpression e) 
        {
            throw EntityUtil.NotSupported(); 
        }

        /// 
        /// This code is shared by  
        /// and 
        /// 
        ///  
        /// Since the left and right expression may not be Sql select statements,
        /// we must wrap them up to look like SQL select statements. 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbUnionAllExpression e) 
        {
            return VisitSetOpExpression(e.Left, e.Right, "UNION ALL"); 
        } 

        ///  
        /// This method determines whether an extent from an outer scope(free variable)
        /// is used in the CurrentSelectStatement.
        ///
        /// An extent in an outer scope, if its symbol is not in the FromExtents 
        /// of the CurrentSelectStatement.
        ///  
        ///  
        /// A .
        public override ISqlFragment Visit(DbVariableReferenceExpression e) 
        {
            if (isVarRefSingle)
            {
                throw EntityUtil.NotSupported(); 
                // A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
                // This is also checked in GenerateSql(...) at the end of the visiting. 
            } 
            isVarRefSingle = true; // This will be reset by DbPropertyExpression or MethodExpression
 
            Symbol result = symbolTable.Lookup(e.VariableName);
            if (!CurrentSelectStatement.FromExtents.Contains(result))
            {
                CurrentSelectStatement.OuterExtents[result] = true; 
            }
 
            return result; 
        }
        #endregion 

        #region Visitor Helper Methods

        #region 'Visitor' methods - Shared visitors and methods that do most of the visiting 

        ///  
        /// Aggregates are not visited by the normal visitor walk. 
        /// 
        /// The aggreate go be translated 
        /// The translated aggregate argument
        /// 
        private static SqlBuilder VisitAggregate(DbAggregate aggregate, object aggregateArgument)
        { 
            SqlBuilder aggregateResult = new SqlBuilder();
            DbFunctionAggregate functionAggregate = aggregate as DbFunctionAggregate; 
 
            if (functionAggregate == null)
            { 
                throw EntityUtil.NotSupported();
            }

            //The only aggregate function with different name is Big_Count 
            //Note: If another such function is to be added, a dictionary should be created
            if (TypeHelpers.IsCanonicalFunction(functionAggregate.Function) 
                && String.Equals(functionAggregate.Function.Name, "BigCount", StringComparison.Ordinal)) 
            {
                aggregateResult.Append("COUNT_BIG"); 
            }
            else
            {
                WriteFunctionName(aggregateResult, functionAggregate.Function); 
            }
 
            aggregateResult.Append("("); 

            DbFunctionAggregate fnAggr = functionAggregate; 
            if ((null != fnAggr) && (fnAggr.Distinct))
            {
                aggregateResult.Append("DISTINCT ");
            } 

            aggregateResult.Append(aggregateArgument); 
 
            aggregateResult.Append(")");
            return aggregateResult; 
        }

        /// 
        /// Dump out an expression - optionally wrap it with parantheses if possible 
        /// 
        ///  
        ///  
        private void ParanthesizeExpressionIfNeeded(DbExpression e, SqlBuilder result)
        { 
            if (IsComplexExpression(e))
            {
                result.Append("(");
                result.Append(e.Accept(this)); 
                result.Append(")");
            } 
            else 
            {
                result.Append(e.Accept(this)); 
            }
        }

        ///  
        /// Handler for inline binary expressions.
        /// Produces left op right. 
        /// For associative operations does flattening. 
        /// Puts parenthesis around the arguments if needed.
        ///  
        /// 
        /// 
        /// 
        ///  
        /// 
        private SqlBuilder VisitBinaryExpression(string op, DbExpressionKind expressionKind, DbExpression left, DbExpression right) 
        { 
            SqlBuilder result = new SqlBuilder();
 
            bool isFirst = true;
            foreach (DbExpression argument in CommandTreeUtils.FlattenAssociativeExpression(expressionKind, left, right))
            {
                if (isFirst) 
                {
                    isFirst = false; 
                } 
                else
                { 
                    result.Append(op);
                }
                ParanthesizeExpressionIfNeeded(argument, result);
            } 
            return result;
        } 
 
        /// 
        /// Private handler for comparison expressions - almost identical to VisitBinaryExpression. 
        /// We special case constants, so that we don't emit unnecessary casts
        /// 
        /// the comparison op
        /// the left-side expression 
        /// the right-side expression
        ///  
        private SqlBuilder VisitComparisonExpression(string op, DbExpression left, DbExpression right) 
        {
            SqlBuilder result = new SqlBuilder(); 

            bool isCastOptional = left.ResultType.EdmType == right.ResultType.EdmType;

            if (left.ExpressionKind == DbExpressionKind.Constant) 
            {
                result.Append(VisitConstant((DbConstantExpression)left, isCastOptional)); 
            } 
            else
            { 
                ParanthesizeExpressionIfNeeded(left, result);
            }

            result.Append(op); 

            if (right.ExpressionKind == DbExpressionKind.Constant) 
            { 
                result.Append(VisitConstant((DbConstantExpression)right, isCastOptional));
            } 
            else
            {
                ParanthesizeExpressionIfNeeded(right, result);
            } 

            return result; 
        } 

        ///  
        /// This is called by the relational nodes.  It does the following
        /// 
        /// If the input is not a SqlSelectStatement, it assumes that the input
        /// is a collection expression, and creates a new SqlSelectStatement  
        /// 
        ///  
        ///  
        /// 
        ///  
        /// 
        /// A  and the main fromSymbol
        /// for this select statement.
        private SqlSelectStatement VisitInputExpression(DbExpression inputExpression, 
            string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol)
        { 
            SqlSelectStatement result; 
            ISqlFragment sqlFragment = inputExpression.Accept(this);
            result = sqlFragment as SqlSelectStatement; 

            if (result == null)
            {
                result = new SqlSelectStatement(); 
                WrapNonQueryExtent(result, sqlFragment, inputExpression.ExpressionKind);
            } 
 
            if (result.FromExtents.Count == 0)
            { 
                // input was an extent
                fromSymbol = new Symbol(inputVarName, inputVarType);
            }
            else if (result.FromExtents.Count == 1) 
            {
                // input was Filter/GroupBy/Project/OrderBy 
                // we are likely to reuse this statement. 
                fromSymbol = result.FromExtents[0];
            } 
            else
            {
                // input was a join.
                // we are reusing the select statement produced by a Join node 
                // we need to remove the original extents, and replace them with a
                // new extent with just the Join symbol. 
                JoinSymbol joinSymbol = new JoinSymbol(inputVarName, inputVarType, result.FromExtents); 
                joinSymbol.FlattenedExtentList = result.AllJoinExtents;
 
                fromSymbol = joinSymbol;
                result.FromExtents.Clear();
                result.FromExtents.Add(fromSymbol);
            } 

            return result; 
        } 

        ///  
        /// 
        /// 
        /// 
        /// Was the parent a DbNotExpression? 
        /// 
        SqlBuilder VisitIsEmptyExpression(DbIsEmptyExpression e, bool negate) 
        { 
            SqlBuilder result = new SqlBuilder();
            if (!negate) 
            {
                result.Append(" NOT");
            }
            result.Append(" EXISTS ("); 
            result.Append(VisitExpressionEnsureSqlStatement(e.Argument));
            result.AppendLine(); 
            result.Append(")"); 

            return result; 
        }


        ///  
        /// Translate a NewInstance(Element(X)) expression into
        ///   "select top(1) * from X" 
        ///  
        /// 
        ///  
        private ISqlFragment VisitCollectionConstructor(DbNewInstanceExpression e)
        {
            Debug.Assert(e.Arguments.Count <= 1);
 
            if (e.Arguments.Count == 1 && e.Arguments[0].ExpressionKind == DbExpressionKind.Element)
            { 
                DbElementExpression elementExpr = e.Arguments[0] as DbElementExpression; 
                SqlSelectStatement result = VisitExpressionEnsureSqlStatement(elementExpr.Argument);
 
                if (!IsCompatible(result, DbExpressionKind.Element))
                {
                    Symbol fromSymbol;
                    TypeUsage inputType = TypeHelpers.GetElementTypeUsage(elementExpr.Argument.ResultType); 

                    result = CreateNewSelectStatement(result, "element", inputType, out fromSymbol); 
                    AddFromSymbol(result, "element", fromSymbol, false); 
                }
                result.Top = new TopClause(1, false); 
                return result;
            }

 
            // Otherwise simply build this out as a union-all ladder
            CollectionType collectionType = TypeHelpers.GetEdmType(e.ResultType); 
            Debug.Assert(collectionType != null); 
            bool isScalarElement = TypeSemantics.IsPrimitiveType(collectionType.TypeUsage);
 
            SqlBuilder resultSql = new SqlBuilder();
            string separator = "";

            // handle empty table 
            if (e.Arguments.Count == 0)
            { 
                Debug.Assert(isScalarElement); 
                resultSql.Append(" SELECT CAST(null as ");
                resultSql.Append(GetSqlPrimitiveType(collectionType.TypeUsage)); 
                resultSql.Append(") AS X FROM (SELECT 1) AS Y WHERE 1=0");
            }

            foreach (DbExpression arg in e.Arguments) 
            {
                resultSql.Append(separator); 
                resultSql.Append(" SELECT "); 
                resultSql.Append(arg.Accept(this));
                // For scalar elements, no alias is appended yet. Add this. 
                if (isScalarElement)
                {
                    resultSql.Append(" AS X ");
                } 
                separator = " UNION ALL ";
            } 
 
            return resultSql;
        } 

        /// 
        /// 
        ///  
        /// 
        /// Was the parent a DbNotExpression? 
        ///  
        private SqlBuilder VisitIsNullExpression(DbIsNullExpression e, bool negate)
        { 
            SqlBuilder result = new SqlBuilder();
            result.Append(e.Argument.Accept(this));
            if (!negate)
            { 
                result.Append(" IS NULL");
            } 
            else 
            {
                result.Append(" IS NOT NULL"); 
            }

            return result;
        } 

        ///  
        /// This handles the processing of join expressions. 
        /// The extents on a left spine are flattened, while joins
        /// not on the left spine give rise to new nested sub queries. 
        ///
        /// Joins work differently from the rest of the visiting, in that
        /// the parent (i.e. the join node) creates the SqlSelectStatement
        /// for the children to use. 
        ///
        /// The "parameter" IsInJoinContext indicates whether a child extent should 
        /// add its stuff to the existing SqlSelectStatement, or create a new SqlSelectStatement 
        /// By passing true, we ask the children to add themselves to the parent join,
        /// by passing false, we ask the children to create new Select statements for 
        /// themselves.
        ///
        /// This method is called from  and
        /// . 
        /// 
        ///  
        ///  
        /// 
        ///  
        ///  A 
        private ISqlFragment VisitJoinExpression(IList inputs, DbExpressionKind joinKind,
            string joinString, DbExpression joinCondition)
        { 
            SqlSelectStatement result;
            // If the parent is not a join( or says that it is not), 
            // we should create a new SqlSelectStatement. 
            // otherwise, we add our child extents to the parent's FROM clause.
            if (!IsParentAJoin) 
            {
                result = new SqlSelectStatement();
                result.AllJoinExtents = new List();
                selectStatementStack.Push(result); 
            }
            else 
            { 
                result = CurrentSelectStatement;
            } 

            // Process each of the inputs, and then the joinCondition if it exists.
            // It would be nice if we could call VisitInputExpression - that would
            // avoid some code duplication 
            // but the Join postprocessing is messy and prevents this reuse.
            symbolTable.EnterScope(); 
 
            string separator = "";
            bool isLeftMostInput = true; 
            int inputCount = inputs.Count;
            for(int idx = 0; idx < inputCount; idx++)
            {
                DbExpressionBinding input = inputs[idx]; 

                if (separator.Length != 0) 
                { 
                    result.From.AppendLine();
                } 
                result.From.Append(separator + " ");
                // Change this if other conditions are required
                // to force the child to produce a nested SqlStatement.
                bool needsJoinContext = (input.Expression.ExpressionKind == DbExpressionKind.Scan) 
                                        || (isLeftMostInput &&
                                            (IsJoinExpression(input.Expression) 
                                             || IsApplyExpression(input.Expression))) 
                                        ;
 
                isParentAJoinStack.Push(needsJoinContext ? true : false);
                // if the child reuses our select statement, it will append the from
                // symbols to our FromExtents list.  So, we need to remember the
                // start of the child's entries. 
                int fromSymbolStart = result.FromExtents.Count;
 
                ISqlFragment fromExtentFragment = input.Expression.Accept(this); 

                isParentAJoinStack.Pop(); 

                ProcessJoinInputResult(fromExtentFragment, result, input, fromSymbolStart);
                separator = joinString;
 
                isLeftMostInput = false;
            } 
 
            // Visit the on clause/join condition.
            switch (joinKind) 
            {
                case DbExpressionKind.FullOuterJoin:
                case DbExpressionKind.InnerJoin:
                case DbExpressionKind.LeftOuterJoin: 
                    result.From.Append(" ON ");
                    isParentAJoinStack.Push(false); 
                    result.From.Append(joinCondition.Accept(this)); 
                    isParentAJoinStack.Pop();
                    break; 
            }

            symbolTable.ExitScope();
 
            if (!IsParentAJoin)
            { 
                selectStatementStack.Pop(); 
            }
 
            return result;
        }

        ///  
        /// This is called from .
        /// 
        /// This is responsible for maintaining the symbol table after visiting 
        /// a child of a join expression.
        /// 
        /// The child's sql statement may need to be completed.
        ///
        /// The child's result could be one of
        ///  
        /// The same as the parent's - this is treated specially.
        /// A sql select statement, which may need to be completed 
        /// An extent - just copy it to the from clause 
        /// Anything else (from a collection-valued expression) -
        /// unnest and copy it. 
        /// 
        ///
        /// If the input was a Join, we need to create a new join symbol,
        /// otherwise, we create a normal symbol. 
        ///
        /// We then call AddFromSymbol to add the AS clause, and update the symbol table. 
        /// 
        ///
        /// 
        /// If the child's result was the same as the parent's, we have to clean up
        /// the list of symbols in the FromExtents list, since this contains symbols from
        /// the children of both the parent and the child.
        /// The happens when the child visited is a Join, and is the leftmost child of 
        /// the parent.
        ///  
        ///  
        /// 
        ///  
        /// 
        private void ProcessJoinInputResult(ISqlFragment fromExtentFragment, SqlSelectStatement result,
            DbExpressionBinding input, int fromSymbolStart)
        { 
            Symbol fromSymbol = null;
 
            if (result != fromExtentFragment) 
            {
                // The child has its own select statement, and is not reusing 
                // our select statement.
                // This should look a lot like VisitInputExpression().
                SqlSelectStatement sqlSelectStatement = fromExtentFragment as SqlSelectStatement;
                if (sqlSelectStatement != null) 
                {
                    if (sqlSelectStatement.Select.IsEmpty) 
                    { 
                        List columns = AddDefaultColumns(sqlSelectStatement);
 
                        if (IsJoinExpression(input.Expression)
                            || IsApplyExpression(input.Expression))
                        {
                            List extents = sqlSelectStatement.FromExtents; 
                            JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
                            newJoinSymbol.IsNestedJoin = true; 
                            newJoinSymbol.ColumnList = columns; 

                            fromSymbol = newJoinSymbol; 
                        }
                        else
                        {
                            // this is a copy of the code in CreateNewSelectStatement. 

                            // if the oldStatement has a join as its input, ... 
                            // clone the join symbol, so that we "reuse" the 
                            // join symbol.  Normally, we create a new symbol - see the next block
                            // of code. 
                            JoinSymbol oldJoinSymbol = sqlSelectStatement.FromExtents[0] as JoinSymbol;
                            if (oldJoinSymbol != null)
                            {
                                // Note: sqlSelectStatement.FromExtents will not do, since it might 
                                // just be an alias of joinSymbol, and we want an actual JoinSymbol.
                                JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, oldJoinSymbol.ExtentList); 
                                // This indicates that the sqlSelectStatement is a blocking scope 
                                // i.e. it hides/renames extent columns
                                newJoinSymbol.IsNestedJoin = true; 
                                newJoinSymbol.ColumnList = columns;
                                newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;

                                fromSymbol = newJoinSymbol; 
                            }
                            else if (sqlSelectStatement.FromExtents[0].OutputColumnsRenamed) 
                            { 
                                fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.FromExtents[0].Columns);
                            } 
                        }

                    }
                    else if (sqlSelectStatement.OutputColumnsRenamed) 
                    {
                        fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.OutputColumns); 
                    } 
                    result.From.Append(" (");
                    result.From.Append(sqlSelectStatement); 
                    result.From.Append(" )");
                }
                else if (input.Expression is DbScanExpression)
                { 
                    result.From.Append(fromExtentFragment);
                } 
                else // bracket it 
                {
                    WrapNonQueryExtent(result, fromExtentFragment, input.Expression.ExpressionKind); 
                }

                if (fromSymbol == null) // i.e. not a join symbol
                { 
                    fromSymbol = new Symbol(input.VariableName, input.VariableType);
                } 
 

                AddFromSymbol(result, input.VariableName, fromSymbol); 
                result.AllJoinExtents.Add(fromSymbol);
            }
            else // result == fromExtentFragment.  The child extents have been merged into the parent's.
            { 
                // we are adding extents to the current sql statement via flattening.
                // We are replacing the child's extents with a single Join symbol. 
                // The child's extents are all those following the index fromSymbolStart. 
                //
                List extents = new List(); 

                // We cannot call extents.AddRange, since the is no simple way to
                // get the range of symbols fromSymbolStart..result.FromExtents.Count
                // from result.FromExtents. 
                // We copy these symbols to create the JoinSymbol later.
                for (int i = fromSymbolStart; i < result.FromExtents.Count; ++i) 
                { 
                    extents.Add(result.FromExtents[i]);
                } 
                result.FromExtents.RemoveRange(fromSymbolStart, result.FromExtents.Count - fromSymbolStart);
                fromSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
                result.FromExtents.Add(fromSymbol);
                // this Join Symbol does not have its own select statement, so we 
                // do not set IsNestedJoin
 
 
                // We do not call AddFromSymbol(), since we do not want to add
                // "AS alias" to the FROM clause- it has been done when the extent was added earlier. 
                symbolTable.Add(input.VariableName, fromSymbol);
            }
        }
 
        /// 
        /// We assume that this is only called as a child of a Project. 
        /// 
        /// This replaces , since
        /// we do not allow DbNewInstanceExpression as a child of any node other than 
        /// DbProjectExpression.
        ///
        /// We write out the translation of each of the columns in the record.
        ///  
        /// 
        ///  
        ///  
        /// A 
        private ISqlFragment VisitNewInstanceExpression(DbNewInstanceExpression e, bool aliasesNeedRenaming, out Dictionary newColumns) 
        {
            SqlBuilder result = new SqlBuilder();
            RowType rowType = e.ResultType.EdmType as RowType;
 
            if (null != rowType)
            { 
                if (aliasesNeedRenaming) 
                {
                    newColumns = new Dictionary(e.Arguments.Count); 
                }
                else
                {
                    newColumns = null; 
                }
 
                ReadOnlyMetadataCollection members = rowType.Properties; 
                string separator = "";
                for(int i = 0; i < e.Arguments.Count; ++i) 
                {
                    DbExpression argument = e.Arguments[i];
                    if (TypeSemantics.IsRowType(argument.ResultType))
                    { 
                        // We do not support nested records or other complex objects.
                        throw EntityUtil.NotSupported(); 
                    } 

                    EdmProperty member = members[i]; 
                    result.Append(separator);
                    result.AppendLine();
                    result.Append(argument.Accept(this));
                    result.Append(" AS "); 
                    if (aliasesNeedRenaming)
                    { 
                        Symbol column = new Symbol(member.Name, member.TypeUsage); 
                        column.NeedsRenaming = true;
                        column.NewName = String.Concat("Internal_", member.Name); 
                        result.Append(column);
                        newColumns.Add(member.Name, column);
                    }
                    else 
                    {
                        result.Append(QuoteIdentifier(member.Name)); 
                    } 
                    separator = ", ";
                } 
            }
            else
            {
                // 
                //
 
 
                throw EntityUtil.NotSupported();
            } 

            return result;
        }
 
        /// 
        /// Handler for set operations 
        /// It generates left separator right. 
        /// Only for SQL 8.0 it may need to create a new select statement
        /// above the set operation if the left child's output columns got renamed 
        /// 
        /// 
        /// 
        ///  
        /// 
        private ISqlFragment VisitSetOpExpression(DbExpression left, DbExpression right, string separator) 
        { 

            SqlSelectStatement leftSelectStatement = VisitExpressionEnsureSqlStatement(left); 
            SqlSelectStatement rightSelectStatement = VisitExpressionEnsureSqlStatement(right);

            SqlBuilder setStatement = new SqlBuilder();
            setStatement.Append(leftSelectStatement); 
            setStatement.AppendLine();
            setStatement.Append(separator); // e.g. UNION ALL 
            setStatement.AppendLine(); 
            setStatement.Append(rightSelectStatement);
 

            //This is the common scenario
            if (!leftSelectStatement.OutputColumnsRenamed)
            { 
                return setStatement;
            } 
 
            else
            { 
                // This is case only for SQL 8.0 when the left child has order by in it
                // If the output columns of the left child got renamed,
                // then the output of the union all is renamed
                // All this currenlty only happens for UNION ALL, because INTERSECT and 
                // EXCEPT get translated for SQL 8.0 before SqlGen.
                SqlSelectStatement selectStatement = new SqlSelectStatement(); 
                selectStatement.From.Append("( "); 
                selectStatement.From.Append(setStatement);
                selectStatement.From.AppendLine(); 
                selectStatement.From.Append(") ");

                Symbol fromSymbol = new Symbol("X", left.ResultType, leftSelectStatement.OutputColumns);
                AddFromSymbol(selectStatement, null, fromSymbol, false); 

                return selectStatement; 
            } 
        }
 
        #endregion

        #region Function Handling Helpers
        ///  
        /// Determines whether the given function is a store function that
        /// requires special handling 
        ///  
        /// 
        ///  
        private static bool IsSpecialStoreFunction(DbFunctionExpression e)
        {
            return IsStoreFunction(e.Function)
                && _storeFunctionHandlers.ContainsKey(e.Function.Name); 
        }
 
        ///  
        /// Determines whether the given function is a canonical function that
        /// requires special handling 
        /// 
        /// 
        /// 
        private static bool IsSpecialCanonicalFunction(DbFunctionExpression e) 
        {
            return TypeHelpers.IsCanonicalFunction(e.Function) 
            && _canonicalFunctionHandlers.ContainsKey(e.Function.Name); 
        }
 
        /// 
        /// Default handling for functions.
        /// Translates them to FunctionName(arg1, arg2, ..., argn)
        ///  
        /// 
        ///  
        private ISqlFragment HandleFunctionDefault(DbFunctionExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
            bool requiresCast = CastReturnTypeToInt32(e);
            if (requiresCast)
            {
                result.Append(" CAST("); 
            }
            WriteFunctionName(result, e.Function); 
            HandleFunctionArgumentsDefault(e, result); 
            if (requiresCast)
            { 
                result.Append(" AS int)");
            }
            return result;
        } 

        ///  
        /// Default handling for functions with a given name. 
        /// Translates them to FunctionName(arg1, arg2, ..., argn)
        ///  
        /// 
        /// 
        /// 
        private ISqlFragment HandleFunctionDefaultGivenName(DbFunctionExpression e, string functionName) 
        {
            SqlBuilder result = new SqlBuilder(); 
            bool needsCast = CastReturnTypeToInt32(e); 
            if (needsCast)
            { 
                result.Append("CAST(");
            }
            result.Append(functionName);
            HandleFunctionArgumentsDefault(e, result); 
            if (needsCast)
            { 
                result.Append(" AS int)"); 
            }
            return result; 
        }

        /// 
        /// Default handling on function arguments. 
        /// Appends the list of arguemnts to the given result
        /// If the function is niladic it does not append anything, 
        /// otherwise it appends (arg1, arg2, .., argn) 
        /// 
        ///  
        /// 
        private void HandleFunctionArgumentsDefault(DbFunctionExpression e, SqlBuilder result)
        {
            bool isNiladicFunction = e.Function.NiladicFunctionAttribute; 
            Debug.Assert(!(isNiladicFunction && (0 < e.Arguments.Count)), "function attributed as NiladicFunction='true' in the provider manifest cannot have arguments");
            if (isNiladicFunction && e.Arguments.Count > 0) 
            { 
                EntityUtil.Metadata(System.Data.Entity.Strings.NiladicFunctionsCannotHaveParameters);
            } 

            if (!isNiladicFunction)
            {
                result.Append("("); 
                string separator = "";
                foreach (DbExpression arg in e.Arguments) 
                { 
                    result.Append(separator);
                    result.Append(arg.Accept(this)); 
                    separator = ", ";
                }
                result.Append(")");
            } 
        }
 
        ///  
        /// Handler for functions that need to be translated to different store function based on version
        ///  
        /// 
        /// 
        /// 
        ///  
        private ISqlFragment HandleFunctionGivenNameBasedOnVersion(DbFunctionExpression e, string preKatmaiName, string katmaiName)
        { 
            if (this.IsPreKatmai) 
            {
                return HandleFunctionDefaultGivenName(e, preKatmaiName); 
            }
            return HandleFunctionDefaultGivenName(e, katmaiName);
        }
 
        /// 
        /// Handler for special build in functions 
        ///  
        /// 
        ///  
        private ISqlFragment HandleSpecialStoreFunction(DbFunctionExpression e)
        {
            return HandleSpecialFunction(_storeFunctionHandlers, e);
        } 

        ///  
        /// Handler for special canonical functions 
        /// 
        ///  
        /// 
        private ISqlFragment HandleSpecialCanonicalFunction(DbFunctionExpression e)
        {
            return HandleSpecialFunction(_canonicalFunctionHandlers, e); 
        }
 
        ///  
        /// Dispatches the special function processing to the appropriate handler
        ///  
        /// 
        /// 
        /// 
        private ISqlFragment HandleSpecialFunction(Dictionary handlers, DbFunctionExpression e) 
        {
            Debug.Assert(handlers.ContainsKey(e.Function.Name), "Special handling should be called only for functions in the list of special functions"); 
            return handlers[e.Function.Name](this, e); 
        }
 
        /// 
        /// Handles functions that are translated into TSQL operators.
        /// The given function should have one or two arguments.
        /// Functions with one arguemnt are translated into 
        ///     op arg
        /// Functions with two arguments are translated into 
        ///     arg0 op arg1 
        /// Also, the arguments can be optionaly enclosed in parethesis
        ///  
        /// 
        /// Whether the arguments should be enclosed in parethesis
        /// 
        private ISqlFragment HandleSpecialFunctionToOperator(DbFunctionExpression e, bool parenthesiseArguments) 
        {
            SqlBuilder result = new SqlBuilder(); 
            Debug.Assert(e.Arguments.Count > 0 && e.Arguments.Count <= 2, "There should be 1 or 2 arguments for operator"); 

            if (e.Arguments.Count > 1) 
            {
                if (parenthesiseArguments)
                {
                    result.Append("("); 
                }
                result.Append(e.Arguments[0].Accept(this)); 
                if (parenthesiseArguments) 
                {
                    result.Append(")"); 
                }
            }
            result.Append(" ");
            Debug.Assert(_functionNameToOperatorDictionary.ContainsKey(e.Function.Name), "The function can not be mapped to an operator"); 
            result.Append(_functionNameToOperatorDictionary[e.Function.Name]);
            result.Append(" "); 
 
            if (parenthesiseArguments)
            { 
                result.Append("(");
            }
            result.Append(e.Arguments[e.Arguments.Count - 1].Accept(this));
            if (parenthesiseArguments) 
            {
                result.Append(")"); 
            } 
            return result;
        } 

        /// 
        /// 
        ///  
        /// 
        ///  
        ///  
        private static ISqlFragment HandleConcatFunction(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            return sqlgen.HandleSpecialFunctionToOperator(e, false);
        }

        ///  
        /// 
        ///  
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionBitwise(SqlGenerator sqlgen, DbFunctionExpression e)
        {
            return sqlgen.HandleSpecialFunctionToOperator(e, true);
        } 

        ///  
        /// Handles special case in which datapart 'type' parameter is present. all the functions 
        /// handles here have *only* the 1st parameter as datepart. datepart value is passed along
        /// the QP as string and has to be expanded as TSQL keyword. 
        /// 
        /// 
        /// 
        ///  
        private static ISqlFragment HandleDatepartDateFunction(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            Debug.Assert(e.Arguments.Count > 0, "e.Arguments.Count > 0"); 

            DbConstantExpression constExpr = e.Arguments[0] as DbConstantExpression; 
            if (null == constExpr)
            {
                throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
            } 

            string datepart = constExpr.Value as string; 
            if (null == datepart) 
            {
                throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name)); 
            }

            SqlBuilder result = new SqlBuilder();
 
            //
            // check if datepart value is valid 
            // 
            if (!_datepartKeywords.Contains(datepart))
            { 
                throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.InvalidDatePartArgumentValue(datepart, e.Function.NamespaceName, e.Function.Name));
            }

            // 
            // finaly, expand the function name
            // 
            WriteFunctionName(result, e.Function); 
            result.Append("(");
 
            // expand the datepart literal as tsql kword
            result.Append(datepart);
            string separator = ", ";
 
            // expand remaining arguments
            for (int i = 1; i < e.Arguments.Count; i++) 
            { 
                result.Append(separator);
                result.Append(e.Arguments[i].Accept(sqlgen)); 
            }

            result.Append(")");
 
            return result;
        } 
 
        /// 
        /// Handler for canonical funcitons for extracting date parts. 
        /// For example:
        ///     Year(date) -> DATEPART( year, date)
        /// 
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleCanonicalFunctionDatepart(e.Function.Name.ToLowerInvariant(), e); 
        }

        /// 
        /// Handler for canonical funcitons for GetTotalOffsetMinutes. 
        /// GetTotalOffsetMinutes(e) --> Datepart(tzoffset, e)
        ///  
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionGetTotalOffsetMinutes(SqlGenerator sqlgen, DbFunctionExpression e)
        {
            return sqlgen.HandleCanonicalFunctionDatepart("tzoffset", e);
        } 

        ///  
        /// Handler for turning a canonical function into DATEPART 
        /// Results in DATEPART(datepart, e)
        ///  
        /// 
        /// 
        /// 
        private ISqlFragment HandleCanonicalFunctionDatepart(string datepart, DbFunctionExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
            result.Append("DATEPART ("); 
            result.Append(datepart);
            result.Append(", "); 

            Debug.Assert(e.Arguments.Count == 1, "Canonical datepart functions should have exactly one argument");
            result.Append(e.Arguments[0].Accept(this));
 
            result.Append(")");
 
            return result; 
        }
 
        /// 
        /// Handler for the canonical function CurrentDate
        /// For Sql8 and Sql9:  CurrentDate() -> GetDate()
        /// For Sql10:          CurrentDate() -> SysDateTime() 
        /// 
        ///  
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionCurrentDateTime(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionGivenNameBasedOnVersion(e, "GetDate", "SysDateTime");
        }
 
        /// 
        /// Handler for the canonical function CurrentUtcDateTime 
        /// For Sql8 and Sql9:  CurrentUtcDateTime() -> GetUtcDate() 
        /// For Sql10:          CurrentUtcDateTime() -> SysUtcDateTime()
        ///  
        /// 
        /// 
        /// 
        private static ISqlFragment HandleCanonicalFunctionCurrentUtcDateTime(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionGivenNameBasedOnVersion(e, "GetUtcDate", "SysUtcDateTime"); 
        } 

        ///  
        /// Handler for the canonical function CurrentDateTimeOffset
        /// For Sql8 and Sql9:  throw
        /// For Sql10: CurrentDateTimeOffset() -> SysDateTimeOffset()
        ///  
        /// 
        ///  
        ///  
        private static ISqlFragment HandleCanonicalFunctionCurrentDateTimeOffset(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            sqlgen.AssertKatmaiOrNewer(e);
            return sqlgen.HandleFunctionDefaultGivenName(e, "SysDateTimeOffset");
        }
 
        /// 
        ///  Function rename IndexOf -> CHARINDEX 
        ///  
        /// 
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionIndexOf(SqlGenerator sqlgen, DbFunctionExpression e)
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "CHARINDEX"); 
        }
 
        ///  
        ///  Function rename NewGuid -> NEWID
        ///  
        /// 
        /// 
        /// 
        private static ISqlFragment HandleCanonicalFunctionNewGuid(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "NEWID"); 
        } 

        ///  
        ///  Function rename Length -> LEN
        /// 
        /// 
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionLength(SqlGenerator sqlgen, DbFunctionExpression e) 
        { 
            // We are aware of SQL Server's trimming of trailing spaces. We disclaim that behavior in general.
            // It's up to the user to decide whether to trim them explicitly or to append a non-blank space char explicitly. 
            // Once SQL Server implements a function that computes Length correctly, we'll use it here instead of LEN,
            // and we'll drop the disclaimer.
            return sqlgen.HandleFunctionDefaultGivenName(e, "LEN");
        } 

        ///  
        /// Round(numericExpression) -> Round(numericExpression, 0); 
        /// 
        ///  
        /// 
        /// 
        private static ISqlFragment HandleCanonicalFunctionRound(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            SqlBuilder result = new SqlBuilder();
 
            result.Append("ROUND("); 

            Debug.Assert(e.Arguments.Count == 1, "Round should have one argument"); 
            result.Append(e.Arguments[0].Accept(sqlgen));

            result.Append(", 0)");
 
            return result;
        } 
 
        /// 
        /// TRIM(string) -> LTRIM(RTRIM(string)) 
        /// 
        /// 
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionTrim(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            SqlBuilder result = new SqlBuilder(); 

            result.Append("LTRIM(RTRIM("); 

            Debug.Assert(e.Arguments.Count == 1, "Trim should have one argument");
            result.Append(e.Arguments[0].Accept(sqlgen));
 
            result.Append("))");
 
            return result; 
        }
 
        /// 
        ///  Function rename ToLower -> LOWER
        /// 
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionToLower(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "LOWER"); 
        }

        /// 
        ///  Function rename ToUpper -> UPPER 
        /// 
        ///  
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionToUpper(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "UPPER");
        }
 
        /// 
        /// Writes the function name to the given SqlBuilder. 
        ///  
        /// 
        ///  
        private static void WriteFunctionName(SqlBuilder result, EdmFunction function)
        {
            string storeFunctionName;
 
            if (null != function.StoreFunctionNameAttribute)
            { 
                storeFunctionName = function.StoreFunctionNameAttribute; 
            }
            else 
            {
                storeFunctionName = function.Name;
            }
 
            // If the function is a builtin (i.e. the BuiltIn attribute has been
            // specified, both store and canonical functions have this attribute), 
            // then the function name should not be quoted; 
            // additionally, no namespace should be used.
            if (TypeHelpers.IsCanonicalFunction(function)) 
            {
                result.Append(storeFunctionName.ToUpperInvariant());
            }
            else if (IsStoreFunction(function)) 
            {
                result.Append(storeFunctionName); 
            } 
            else
            { 
                // Should we actually support this?
                if (String.IsNullOrEmpty(function.Schema))
                {
                    result.Append(QuoteIdentifier(function.NamespaceName)); 
                }
                else 
                { 
                    result.Append(QuoteIdentifier(function.Schema));
                } 
                result.Append(".");
                result.Append(QuoteIdentifier(storeFunctionName));
            }
        } 
        #endregion
 
        #region Other Helpers 
        /// 
        ///  
        /// Add the column names from the referenced extent/join to the
        /// select statement.
        ///
        /// If the symbol is a JoinSymbol, we recursively visit all the extents, 
        /// halting at real extents and JoinSymbols that have an associated SqlSelectStatement.
        /// 
        /// The column names for a real extent can be derived from its type. 
        /// The column names for a Join Select statement can be got from the
        /// list of columns that was created when the Join's select statement 
        /// was created.
        ///
        /// We do the following for each column.
        ///  
        /// Add the SQL string for each column to the SELECT clause
        /// Add the column to the list of columns - so that it can 
        /// become part of the "type" of a JoinSymbol 
        /// Check if the column name collides with a previous column added
        /// to the same select statement.  Flag both the columns for renaming if true. 
        /// Add the column to a name lookup dictionary for collision detection.
        /// 
        /// 
        /// The select statement that started off as SELECT * 
        /// The symbol containing the type information for
        /// the columns to be added. 
        /// Columns that have been added to the Select statement. 
        /// This is created in .
        /// A dictionary of the columns above. 
        /// Comma or nothing, depending on whether the SELECT
        /// clause is empty.
        private void AddColumns(SqlSelectStatement selectStatement, Symbol symbol,
            List columnList, Dictionary columnDictionary, ref string separator) 
        {
            JoinSymbol joinSymbol = symbol as JoinSymbol; 
            if (joinSymbol != null) 
            {
                if (!joinSymbol.IsNestedJoin) 
                {
                    // Recurse if the join symbol is a collection of flattened extents
                    foreach (Symbol sym in joinSymbol.ExtentList)
                    { 
                        // if sym is ScalarType means we are at base case in the
                        // recursion and there are not columns to add, just skip 
                        if ((sym.Type == null) || TypeSemantics.IsPrimitiveType(sym.Type)) 
                        {
                            continue; 
                        }

                        AddColumns(selectStatement, sym, columnList, columnDictionary, ref separator);
                    } 
                }
                else 
                { 
                    foreach (Symbol joinColumn in joinSymbol.ColumnList)
                    { 
                        // we write tableName.columnName
                        // rather than tableName.columnName as alias
                        // since the column name is unique (by the way we generate new column names)
                        // 
                        // We use the symbols for both the table and the column,
                        // since they are subject to renaming. 
                        selectStatement.Select.Append(separator); 
                        selectStatement.Select.Append(symbol);
                        selectStatement.Select.Append("."); 
                        selectStatement.Select.Append(joinColumn);

                        // check for name collisions.  If there is,
                        // flag both the colliding symbols. 
                        if (columnDictionary.ContainsKey(joinColumn.Name))
                        { 
                            columnDictionary[joinColumn.Name].NeedsRenaming = true; // the original symbol 
                            joinColumn.NeedsRenaming = true; // the current symbol.
                        } 
                        else
                        {
                            columnDictionary[joinColumn.Name] = joinColumn;
                        } 

                        columnList.Add(joinColumn); 
 
                        separator = ", ";
                    } 
                }
            }
            else
            { 
                // This is a non-join extent/select statement, and the CQT type has
                // the relevant column information. 
 
                // The type could be a record type(e.g. Project(...),
                // or an entity type ( e.g. EntityExpression(...) 
                // so, we check whether it is a structuralType.

                // Consider an expression of the form J(a, b=P(E))
                // The inner P(E) would have been translated to a SQL statement 
                // We should not use the raw names from the type, but the equivalent
                // symbols (they are present in symbol.Columns) if they exist. 
                // 
                // We add the new columns to the symbol's columns if they do
                // not already exist. 
                //
                // If the symbol represents a SqlStatement with renamed output columns,
                // we should use these instead of the rawnames and we should also mark
                // this selectStatement as one with renamed columns 

                if (symbol.OutputColumnsRenamed) 
                { 
                    selectStatement.OutputColumnsRenamed = true;
                    selectStatement.OutputColumns = new Dictionary(); 
                }

                if ((symbol.Type == null) || TypeSemantics.IsPrimitiveType(symbol.Type))
                { 
                    AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, "X");
                } 
                else 
                {
                    foreach (EdmProperty property in TypeHelpers.GetProperties(symbol.Type)) 
                    {
                        AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, property.Name);
                    }
               } 
            }
        } 
 
        /// 
        /// Helper method for AddColumns. Adds a column with the given column name 
        /// to the Select list of the given select statement.
        /// 
        /// The select statement to whose SELECT part the column should be added
        /// The symbol from which the column to be added originated 
        /// Columns that have been added to the Select statement.
        /// This is created in . 
        /// A dictionary of the columns above. 
        /// Comma or nothing, depending on whether the SELECT
        /// clause is empty. 
        /// The name of the column to be added.
        private void AddColumn(SqlSelectStatement selectStatement, Symbol symbol,
            List columnList, Dictionary columnDictionary, ref string separator, string columnName)
        { 
            // Since all renaming happens in the second phase
            // we lose nothing by setting the next column name index to 0 
            // many times. 
            allColumnNames[columnName] = 0;
 
            // Create a new symbol/reuse existing symbol for the column
            Symbol columnSymbol;
            if (!symbol.Columns.TryGetValue(columnName, out columnSymbol))
            { 
                // we do not care about the types of columns, so we pass null
                // when construction the symbol. 
                columnSymbol = new Symbol(columnName, null); 
                symbol.Columns.Add(columnName, columnSymbol);
            } 

            selectStatement.Select.Append(separator);
            selectStatement.Select.Append(symbol);
            selectStatement.Select.Append("."); 

            if (symbol.OutputColumnsRenamed) 
            { 
                selectStatement.Select.Append(columnSymbol);
                selectStatement.OutputColumns.Add(columnSymbol.Name, columnSymbol); 
            }

            // We use the actual name before the "AS", the new name goes
            // after the AS. 
            else
            { 
                selectStatement.Select.Append(QuoteIdentifier(columnName)); 
            }
 
            selectStatement.Select.Append(" AS ");
            selectStatement.Select.Append(columnSymbol);

            // Check for column name collisions. 
            if (columnDictionary.ContainsKey(columnName))
            { 
                columnDictionary[columnName].NeedsRenaming = true; 
                columnSymbol.NeedsRenaming = true;
            } 
            else
            {
                columnDictionary[columnName] = symbol.Columns[columnName];
            } 

            columnList.Add(columnSymbol); 
            separator = ", "; 
        }
 
        /// 
        /// Expands Select * to "select the_list_of_columns"
        /// If the columns are taken from an extent, they are written as
        /// {original_column_name AS Symbol(original_column)} to allow renaming. 
        ///
        /// If the columns are taken from a Join, they are written as just 
        /// {original_column_name}, since there cannot be a name collision. 
        ///
        /// We concatenate the columns from each of the inputs to the select statement. 
        /// Since the inputs may be joins that are flattened, we need to recurse.
        /// The inputs are inferred from the symbols in FromExtents.
        /// 
        ///  
        /// 
        private List AddDefaultColumns(SqlSelectStatement selectStatement) 
        { 
            // This is the list of columns added in this select statement
            // This forms the "type" of the Select statement, if it has to 
            // be expanded in another SELECT *
            List columnList = new List();

            // A lookup for the previous set of columns to aid column name 
            // collision detection.
            Dictionary columnDictionary = new Dictionary(StringComparer.OrdinalIgnoreCase); 
 
            string separator = "";
            // The Select should usually be empty before we are called, 
            // but we do not mind if it is not.
            if (!selectStatement.Select.IsEmpty)
            {
                separator = ", "; 
            }
 
            foreach (Symbol symbol in selectStatement.FromExtents) 
            {
                AddColumns(selectStatement, symbol, columnList, columnDictionary, ref separator); 
            }

            return columnList;
        } 

        ///  
        ///  
        /// 
        ///  
        /// 
        /// 
        private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol)
        { 
            AddFromSymbol(selectStatement, inputVarName, fromSymbol, true);
        } 
 
        /// 
        /// This method is called after the input to a relational node is visited. 
        ///  and 
        /// There are 2 scenarios
        /// 
        /// The fromSymbol is new i.e. the select statement has just been 
        /// created, or a join extent has been added.
        /// The fromSymbol is old i.e. we are reusing a select statement. 
        ///  
        ///
        /// If we are not reusing the select statement, we have to complete the 
        /// FROM clause with the alias
        /// 
        /// -- if the input was an extent
        /// FROM = [SchemaName].[TableName] 
        /// -- if the input was a Project
        /// FROM = (SELECT ... FROM ... WHERE ...) 
        ///  
        ///
        /// These become 
        /// 
        /// -- if the input was an extent
        /// FROM = [SchemaName].[TableName] AS alias
        /// -- if the input was a Project 
        /// FROM = (SELECT ... FROM ... WHERE ...) AS alias
        ///  
        /// and look like valid FROM clauses. 
        ///
        /// Finally, we have to add the alias to the global list of aliases used, 
        /// and also to the current symbol table.
        /// 
        /// 
        /// The alias to be used. 
        /// 
        ///  
        private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol, bool addToSymbolTable) 
        {
            // the first check is true if this is a new statement 
            // the second check is true if we are in a join - we do not
            // check if we are in a join context.
            // We do not want to add "AS alias" if it has been done already
            // e.g. when we are reusing the Sql statement. 
            if (selectStatement.FromExtents.Count == 0 || fromSymbol != selectStatement.FromExtents[0])
            { 
                selectStatement.FromExtents.Add(fromSymbol); 
                selectStatement.From.Append(" AS ");
                selectStatement.From.Append(fromSymbol); 

                // We have this inside the if statement, since
                // we only want to add extents that are actually used.
                allExtentNames[fromSymbol.Name] = 0; 
            }
 
            if (addToSymbolTable) 
            {
                symbolTable.Add(inputVarName, fromSymbol); 
            }
        }

        ///  
        /// Translates a list of SortClauses.
        /// Used in the translation of OrderBy 
        ///  
        /// The SqlBuilder to which the sort keys should be appended
        ///  
        private void AddSortKeys(SqlBuilder orderByClause, IList sortKeys)
        {
            string separator = "";
            foreach (DbSortClause sortClause in sortKeys) 
            {
                orderByClause.Append(separator); 
                orderByClause.Append(sortClause.Expression.Accept(this)); 
                // Bug 431021: COLLATE clause must precede ASC/DESC
                Debug.Assert(sortClause.Collation != null); 
                if (!String.IsNullOrEmpty(sortClause.Collation))
                {
                    orderByClause.Append(" COLLATE ");
                    orderByClause.Append(sortClause.Collation); 
                }
 
                orderByClause.Append(sortClause.Ascending ? " ASC" : " DESC"); 

                separator = ", "; 
            }
        }

        ///  
        /// 
        ///  
        ///  
        /// 
        ///  
        /// 
        /// 
        private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
            string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol) 
        {
            return CreateNewSelectStatement(oldStatement, inputVarName, inputVarType, true, out fromSymbol); 
        } 

        ///  
        /// This is called after a relational node's input has been visited, and the
        /// input's sql statement cannot be reused.  
        ///
        /// When the input's sql statement cannot be reused, we create a new sql 
        /// statement, with the old one as the from clause of the new statement.
        /// 
        /// The old statement must be completed i.e. if it has an empty select list, 
        /// the list of columns must be projected out.
        /// 
        /// If the old statement being completed has a join symbol as its from extent,
        /// the new statement must have a clone of the join symbol as its extent.
        /// We cannot reuse the old symbol, but the new select statement must behave
        /// as though it is working over the "join" record. 
        /// 
        ///  
        ///  
        /// 
        ///  
        /// 
        /// A new select statement, with the old one as the from clause.
        private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
            string inputVarName, TypeUsage inputVarType, bool finalizeOldStatement, out Symbol fromSymbol) 
        {
            fromSymbol = null; 
 
            // Finalize the old statement
            if (finalizeOldStatement && oldStatement.Select.IsEmpty) 
            {
                List columns = AddDefaultColumns(oldStatement);

                // Thid could not have been called from a join node. 
                Debug.Assert(oldStatement.FromExtents.Count == 1);
 
                // if the oldStatement has a join as its input, ... 
                // clone the join symbol, so that we "reuse" the
                // join symbol.  Normally, we create a new symbol - see the next block 
                // of code.
                JoinSymbol oldJoinSymbol = oldStatement.FromExtents[0] as JoinSymbol;
                if (oldJoinSymbol != null)
                { 
                    // Note: oldStatement.FromExtents will not do, since it might
                    // just be an alias of joinSymbol, and we want an actual JoinSymbol. 
                    JoinSymbol newJoinSymbol = new JoinSymbol(inputVarName, inputVarType, oldJoinSymbol.ExtentList); 
                    // This indicates that the oldStatement is a blocking scope
                    // i.e. it hides/renames extent columns 
                    newJoinSymbol.IsNestedJoin = true;
                    newJoinSymbol.ColumnList = columns;
                    newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
 
                    fromSymbol = newJoinSymbol;
                } 
            } 

            if (fromSymbol == null) 
            {
                if (oldStatement.OutputColumnsRenamed)
                {
                    fromSymbol = new Symbol(inputVarName, inputVarType, oldStatement.OutputColumns); 
                }
                else 
                { 
                    // This is just a simple extent/SqlSelectStatement,
                    // and we can get the column list from the type. 
                    fromSymbol = new Symbol(inputVarName, inputVarType);
                }
            }
 
            // Observe that the following looks like the body of Visit(ExtentExpression).
            SqlSelectStatement selectStatement = new SqlSelectStatement(); 
            selectStatement.From.Append("( "); 
            selectStatement.From.Append(oldStatement);
            selectStatement.From.AppendLine(); 
            selectStatement.From.Append(") ");


            return selectStatement; 
        }
 
        ///  
        /// Before we embed a string literal in a SQL string, we should
        /// convert all ' to '', and enclose the whole string in single quotes. 
        /// 
        /// 
        /// 
        /// The escaped sql string. 
        private static string EscapeSingleQuote(string s, bool isUnicode)
        { 
            return (isUnicode ? "N'" : "'") + s.Replace("'", "''") + "'"; 
        }
 
        /// 
        /// Returns the sql primitive/native type name.
        /// It will include size, precision or scale depending on type information present in the
        /// type facets 
        /// 
        ///  
        ///  
        private string GetSqlPrimitiveType( TypeUsage type )
        { 
            Debug.Assert(Helper.IsPrimitiveType(type.EdmType), "Type must be primitive type");
            Debug.Assert(type.EdmType.DataSpace == DataSpace.CSpace, "Type must be in cSpace");

            TypeUsage storeTypeUsage = this._storeItemCollection.StoreProviderManifest.GetStoreType(type); 

            string typeName = storeTypeUsage.EdmType.Name; 
            bool hasFacet = false; 
            int maxLength = 0;
            byte decimalPrecision = 0; 
            byte decimalScale = 0;

            PrimitiveTypeKind primitiveTypeKind = ((PrimitiveType)storeTypeUsage.EdmType).PrimitiveTypeKind;
 
            switch (primitiveTypeKind)
            { 
                case PrimitiveTypeKind.Binary: 
                    if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.MaxLengthFacetName))
                    { 
                        hasFacet = TypeHelpers.TryGetMaxLength(storeTypeUsage, out maxLength);
                        Debug.Assert(hasFacet, "Binary type did not have MaxLength facet");
                        typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
                    } 
                    break;
 
                case PrimitiveTypeKind.String: 
                    if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.MaxLengthFacetName))
                    { 
                        hasFacet = TypeHelpers.TryGetMaxLength(storeTypeUsage, out maxLength);
                        Debug.Assert(hasFacet, "String type did not have MaxLength facet");
                        typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
                    } 
                    break;
 
                case PrimitiveTypeKind.DateTime: 
                    typeName = this.IsPreKatmai ? "datetime" : "datetime2";
                    break; 
                case PrimitiveTypeKind.Time:
                    AssertKatmaiOrNewer(primitiveTypeKind);
                    typeName = "time";
                    break; 
                case PrimitiveTypeKind.DateTimeOffset:
                    AssertKatmaiOrNewer(primitiveTypeKind); 
                    typeName = "datetimeoffset"; 
                    break;
 
                case PrimitiveTypeKind.Decimal:
                    if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.PrecisionFacetName))
                    {
                        hasFacet = TypeHelpers.TryGetPrecision(storeTypeUsage, out decimalPrecision); 
                        Debug.Assert(hasFacet, "decimal must have precision facet");
                        Debug.Assert(decimalPrecision > 0, "decimal precision must be greater than zero"); 
                        hasFacet = TypeHelpers.TryGetScale(storeTypeUsage, out decimalScale); 
                        Debug.Assert(hasFacet, "decimal must have scale facet");
                        Debug.Assert(decimalPrecision >= decimalScale, "decimalPrecision must be greater or equal to decimalScale"); 
                        typeName = typeName + "(" + decimalPrecision + "," + decimalScale + ")";
                    }
                    break;
 
                default:
                    break; 
            } 

            return typeName; 
        }

        /// 
        /// Handles the expression represending DbLimitExpression.Limit and DbSkipExpression.Count. 
        /// If it is a constant expression, it simply does to string thus avoiding casting it to the specific value
        /// (which would be done if  is called) 
        ///  
        /// 
        ///  
        private ISqlFragment HandleCountExpression(DbExpression e)
        {
            ISqlFragment result;
 
            if (e.ExpressionKind == DbExpressionKind.Constant)
            { 
                //For constant expression we should not cast the value, 
                // thus we don't go throught the default DbConstantExpression handling
                SqlBuilder sqlBuilder = new SqlBuilder(); 
                sqlBuilder.Append(((DbConstantExpression)e).Value.ToString());
                result = sqlBuilder;
            }
            else 
            {
                result = e.Accept(this); 
            } 

            return result; 
        }

        /// 
        /// This is used to determine if a particular expression is an Apply operation. 
        /// This is only the case when the DbExpressionKind is CrossApply or OuterApply.
        ///  
        ///  
        /// 
        static bool IsApplyExpression(DbExpression e) 
        {
            return (DbExpressionKind.CrossApply == e.ExpressionKind || DbExpressionKind.OuterApply == e.ExpressionKind);
        }
 
        /// 
        /// This is used to determine if a particular expression is a Join operation. 
        /// This is true for DbCrossJoinExpression and DbJoinExpression, the 
        /// latter of which may have one of several different ExpressionKinds.
        ///  
        /// 
        /// 
        static bool IsJoinExpression(DbExpression e)
        { 
            return (DbExpressionKind.CrossJoin == e.ExpressionKind ||
                    DbExpressionKind.FullOuterJoin == e.ExpressionKind || 
                    DbExpressionKind.InnerJoin == e.ExpressionKind || 
                    DbExpressionKind.LeftOuterJoin == e.ExpressionKind);
        } 

        /// 
        /// This is used to determine if a calling expression needs to place
        /// round brackets around the translation of the expression e. 
        ///
        /// Constants, parameters and properties do not require brackets, 
        /// everything else does. 
        /// 
        ///  
        /// true, if the expression needs brackets 
        private static bool IsComplexExpression(DbExpression e)
        {
            switch (e.ExpressionKind) 
            {
                case DbExpressionKind.Constant: 
                case DbExpressionKind.ParameterReference: 
                case DbExpressionKind.Property:
                    return false; 

                default:
                    return true;
            } 
        }
 
        ///  
        /// Determine if the owner expression can add its unique sql to the input's
        /// SqlSelectStatement 
        /// 
        /// The SqlSelectStatement of the input to the relational node.
        /// The kind of the expression node(not the input's)
        ///  
        private static bool IsCompatible(SqlSelectStatement result, DbExpressionKind expressionKind)
        { 
            switch (expressionKind) 
            {
                case DbExpressionKind.Distinct: 
                    return result.Top == null
                        // #494803: The projection after distinct may not project all
                        // columns used in the Order By
                        // Improvement: Consider getting rid of the Order By instead 
                        && result.OrderBy.IsEmpty;
 
                case DbExpressionKind.Filter: 
                    return result.Select.IsEmpty
                            && result.Where.IsEmpty 
                            && result.GroupBy.IsEmpty
                            && result.Top == null;

                case DbExpressionKind.GroupBy: 
                    return result.Select.IsEmpty
                            && result.GroupBy.IsEmpty 
                            && result.OrderBy.IsEmpty 
                            && result.Top == null;
 
                case DbExpressionKind.Limit:
                case DbExpressionKind.Element:
                    return result.Top == null;
 
                case DbExpressionKind.Project:
                    // SQLBUDT #427998: Allow a Project to be compatible with an OrderBy 
                    // Otherwise we won't be able to sort an input, and project out only 
                    // a subset of the input columns
                    return result.Select.IsEmpty 
                            && result.GroupBy.IsEmpty
                            // SQLBUDT #513640 - If distinct is specified, the projection may affect
                            // the cardinality of the results, thus a new statement must be started.
                            && !result.IsDistinct; 

                case DbExpressionKind.Skip: 
                    return result.Select.IsEmpty 
                            && result.GroupBy.IsEmpty
                            && result.OrderBy.IsEmpty 
                            && !result.IsDistinct;

                case DbExpressionKind.Sort:
                    return result.Select.IsEmpty 
                            && result.GroupBy.IsEmpty
                            && result.OrderBy.IsEmpty 
                            // SQLBUDT #513640 - A Project may be on the top of the Sort, and if so, it would need 
                            // to be in the same statement as the Sort (see comment above for the Project case).
                            // A Distinct in the same statement would prevent that, and therefore if Distinct is present, 
                            // we need to start a new statement.
                            && !result.IsDistinct;

                default: 
                    Debug.Assert(false);
                    throw EntityUtil.InvalidOperation(String.Empty); 
            } 

        } 

        /// 
        /// We use the normal box quotes for SQL server.  We do not deal with ANSI quotes
        /// i.e. double quotes. 
        /// 
        ///  
        ///  
        internal static string QuoteIdentifier(string name)
        { 
            Debug.Assert(!String.IsNullOrEmpty(name));
            // We assume that the names are not quoted to begin with.
            return "[" + name.Replace("]", "]]") + "]";
        } 

        ///  
        /// Simply calls  
        /// with addDefaultColumns set to true
        ///  
        /// 
        /// 
        private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e)
        { 
            return VisitExpressionEnsureSqlStatement(e, true);
        } 
 
        /// 
        /// This is called from  and nodes which require a 
        /// select statement as an argument e.g. ,
        /// .
        ///
        /// SqlGenerator needs its child to have a proper alias if the child is 
        /// just an extent or a join.
        /// 
        /// The normal relational nodes result in complete valid SQL statements. 
        /// For the rest, we need to treat them as there was a dummy
        ///  
        /// -- originally {expression}
        /// -- change that to
        /// SELECT *
        /// FROM {expression} as c 
        /// 
        /// 
        /// DbLimitExpression needs to start the statement but not add the default columns 
        /// 
        ///  
        /// 
        /// 
        private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e, bool addDefaultColumns)
        { 
            Debug.Assert(TypeSemantics.IsCollectionType(e.ResultType));
 
            SqlSelectStatement result; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.Project:
                case DbExpressionKind.Filter:
                case DbExpressionKind.GroupBy:
                case DbExpressionKind.Sort: 
                    result = e.Accept(this) as SqlSelectStatement;
                    break; 
 
                default:
                    Symbol fromSymbol; 
                    string inputVarName = "c";  // any name will do - this is my random choice.
                    symbolTable.EnterScope();

                    TypeUsage type = null; 
                    switch (e.ExpressionKind)
                    { 
                        case DbExpressionKind.Scan: 
                        case DbExpressionKind.CrossJoin:
                        case DbExpressionKind.FullOuterJoin: 
                        case DbExpressionKind.InnerJoin:
                        case DbExpressionKind.LeftOuterJoin:
                        case DbExpressionKind.CrossApply:
                        case DbExpressionKind.OuterApply: 
                            // #490026: It used to be type = e.ResultType.
                            type = TypeHelpers.GetElementTypeUsage(e.ResultType); 
                            break; 

                        default: 
                            Debug.Assert(TypeSemantics.IsCollectionType(e.ResultType));
                            type = TypeHelpers.GetEdmType(e.ResultType).TypeUsage;
                            break;
                    } 

 
                    result = VisitInputExpression(e, inputVarName, type, out fromSymbol); 
                    AddFromSymbol(result, inputVarName, fromSymbol);
                    symbolTable.ExitScope(); 
                    break;
            }

            if (addDefaultColumns && result.Select.IsEmpty) 
            {
                AddDefaultColumns(result); 
            } 

            return result; 
        }

        /// 
        /// This method is called by  and 
        /// 
        /// 
        ///  
        /// 
        ///  
        /// This is passed from 
        /// in the All(...) case.
        /// 
        private SqlSelectStatement VisitFilterExpression(DbExpressionBinding input, DbExpression predicate, bool negatePredicate) 
        {
            Symbol fromSymbol; 
            SqlSelectStatement result = VisitInputExpression(input.Expression, 
                input.VariableName, input.VariableType, out fromSymbol);
 
            // Filter is compatible with OrderBy
            // but not with Project, another Filter or GroupBy
            if (!IsCompatible(result, DbExpressionKind.Filter))
            { 
                result = CreateNewSelectStatement(result, input.VariableName, input.VariableType, out fromSymbol);
            } 
 
            selectStatementStack.Push(result);
            symbolTable.EnterScope(); 

            AddFromSymbol(result, input.VariableName, fromSymbol);

            if (negatePredicate) 
            {
                result.Where.Append("NOT ("); 
            } 
            result.Where.Append(predicate.Accept(this));
            if (negatePredicate) 
            {
                result.Where.Append(")");
            }
 
            symbolTable.ExitScope();
            selectStatementStack.Pop(); 
 
            return result;
        } 

        /// 
        /// If the sql fragment for an input expression is not a SqlSelect statement
        /// or other acceptable form (e.g. an extent as a SqlBuilder), we need 
        /// to wrap it in a form acceptable in a FROM clause.  These are
        /// primarily the 
        ///  
        /// The set operation expressions - union all, intersect, except
        /// TVFs, which are conceptually similar to tables 
        /// 
        /// 
        /// 
        ///  
        /// 
        private static void WrapNonQueryExtent(SqlSelectStatement result, ISqlFragment sqlFragment, DbExpressionKind expressionKind) 
        { 
            switch (expressionKind)
            { 
                case DbExpressionKind.Function:
                    // TVF
                    result.From.Append(sqlFragment);
                    break; 

                default: 
                    result.From.Append(" ("); 
                    result.From.Append(sqlFragment);
                    result.From.Append(")"); 
                    break;
            }
        }
 
        /// 
        /// Is this a Store function (ie) does it have the builtinAttribute specified and it is not a canonical function? 
        ///  
        /// 
        ///  
        private static bool IsStoreFunction(EdmFunction function)
        {
            return function.BuiltInAttribute && !TypeHelpers.IsCanonicalFunction(function);
        } 

        private static string ByteArrayToBinaryString( Byte[] binaryArray ) 
        { 
            StringBuilder sb = new StringBuilder( binaryArray.Length * 2 );
            for (int i = 0 ; i < binaryArray.Length ; i++) 
            {
                sb.Append(hexDigits[(binaryArray[i]&0xF0) >>4]).Append(hexDigits[binaryArray[i]&0x0F]);
            }
            return sb.ToString(); 
        }
 
        private TypeUsage GetPrimitiveType(PrimitiveTypeKind modelType) 
        {
            TypeUsage type = null; 
            PrimitiveType mappedType = this._storeItemCollection.GetMappedPrimitiveType(modelType);

            Debug.Assert(mappedType != null, "Could not get type usage for primitive type");
 
            type = TypeUsage.CreateDefaultTypeUsage(mappedType);
            return type; 
        } 

        ///  
        /// Helper method for the Group By visitor
        /// Returns true if at least one of the aggregates in the given list
        /// has an argument that is not a  and is not
        /// a  over , 
        /// either potentially capped with a 
        /// 
        /// This is really due to the following two limitations of Sql Server: 
        /// 
        /// If an expression being aggregated contains an outer reference, then that outer 
        /// reference must be the only column referenced in the expression (SQLBUDT #488741)
        /// Sql Server cannot perform an aggregate function on an expression containing
        /// an aggregate or a subquery. (SQLBUDT #504600)
        ///  
        /// Potentially, we could furhter optimize this.
        ///  
        ///  
        /// 
        ///  
        static bool GroupByAggregatesNeedInnerQuery(IList aggregates, string inputVarRefName)
        {
            foreach (DbAggregate aggregate in aggregates)
            { 
                Debug.Assert(aggregate.Arguments.Count == 1);
                if (GroupByAggregateNeedsInnerQuery(aggregate.Arguments[0], inputVarRefName)) 
                { 
                    return true;
                } 
            }
            return false;
        }
 
        /// 
        /// Returns true if the given expression is not a  or a 
        ///  over  a  
        /// referencing the given inputVarRefName, either
        /// potentially capped with a . 
        /// 
        /// 
        /// 
        ///  
        private static bool GroupByAggregateNeedsInnerQuery(DbExpression expression, string inputVarRefName)
        { 
            return GroupByExpressionNeedsInnerQuery(expression, inputVarRefName, true); 
        }
 
        /// 
        /// Helper method for the Group By visitor
        /// Returns true if at least one of the expressions in the given list
        /// is not  over  
        /// referencing the given inputVarRefName potentially capped with a .
        /// 
        /// This is really due to the following limitation: Sql Server requires each GROUP BY expression 
        /// (key) to contain at least one column that is not an outer reference. (SQLBUDT #616523)
        /// Potentially, we could further optimize this. 
        /// 
        /// 
        /// 
        ///  
        static bool GroupByKeysNeedInnerQuery(IList keys, string inputVarRefName)
        { 
            foreach (DbExpression key in keys) 
            {
                if (GroupByKeyNeedsInnerQuery(key, inputVarRefName)) 
                {
                    return true;
                }
            } 
            return false;
        } 
 
        /// 
        /// Returns true if the given expression is not  over 
        ///  referencing the given inputVarRefName
        /// potentially capped with a .
        /// This is really due to the following limitation: Sql Server requires each GROUP BY expression
        /// (key) to contain at least one column that is not an outer reference. (SQLBUDT #616523) 
        /// Potentially, we could further optimize this.
        ///  
        ///  
        /// 
        ///  
        private static bool GroupByKeyNeedsInnerQuery(DbExpression expression, string inputVarRefName)
        {
            return GroupByExpressionNeedsInnerQuery(expression, inputVarRefName, false);
        } 

        ///  
        /// Helper method for processing Group By keys and aggregates. 
        /// Returns true if the given expression is not a 
        /// (and allowConstants is specified)or a  over 
        /// a  referencing the given inputVarRefName,
        /// either potentially capped with a .
        /// 
        ///  
        /// 
        ///  
        ///  
        private static bool GroupByExpressionNeedsInnerQuery(DbExpression expression, string inputVarRefName, bool allowConstants)
        { 
            //Skip a constant if constants are allowed
            if (allowConstants && (expression.ExpressionKind == DbExpressionKind.Constant))
            {
                return false; 
            }
 
            //Skip a cast expression 
            if (expression.ExpressionKind == DbExpressionKind.Cast)
            { 
                DbCastExpression castExpression = (DbCastExpression)expression;
                return GroupByExpressionNeedsInnerQuery(castExpression.Argument, inputVarRefName, allowConstants);
            }
 
            //Allow Property(Property(...)), needed when the input is a join
            if (expression.ExpressionKind == DbExpressionKind.Property) 
            { 
                DbPropertyExpression propertyExpression = (DbPropertyExpression)expression;
                return GroupByExpressionNeedsInnerQuery(propertyExpression.Instance, inputVarRefName, allowConstants); 
            }

            if (expression.ExpressionKind == DbExpressionKind.VariableReference)
            { 
                DbVariableReferenceExpression varRefExpression = expression as DbVariableReferenceExpression;
                return !varRefExpression.VariableName.Equals(inputVarRefName); 
            } 

            return true; 
        }

        /// 
        /// determines if the function requires the return type be enforeced by use of a cast expression 
        /// 
        ///  
        ///  
        private bool CastReturnTypeToInt32(DbFunctionExpression e)
        { 
            if (!_functionRequiresReturnTypeCast.Contains(e.Function.FullName))
            {
                return false;
            } 

            for (int i = 0; i < e.Arguments.Count; i++) 
            { 
                TypeUsage storeType = _storeItemCollection.StoreProviderManifest.GetStoreType(e.Arguments[i].ResultType);
                if (_maxTypeNames.Contains(storeType.EdmType.Name)) 
                {
                    return true;
                }
            } 
            return false;
        } 
 
        /// 
        /// Throws not supported exception if the server is pre-katmai 
        /// 
        /// 
        private void AssertKatmaiOrNewer(PrimitiveTypeKind primitiveTypeKind)
        { 
            if (this.IsPreKatmai)
            { 
                throw EntityUtil.NotSupported(System.Data.Entity.Strings.PrimitiveTypeNotSupportedPriorSql10(primitiveTypeKind)); 
            }
        } 

        /// 
        /// Throws not supported exception if the server is pre-katmai
        ///  
        /// 
        private void AssertKatmaiOrNewer(DbFunctionExpression e) 
        { 
            if (this.IsPreKatmai)
            { 
                throw EntityUtil.NotSupported(System.Data.Entity.Strings.CanonicalFunctionNotSupportedPriorSql10(e.Function.Name));
            }
        }
 
        #endregion
 
        #endregion 
    }
} 


// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
//---------------------------------------------------------------------- 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// 
// @owner  [....], [....]
//--------------------------------------------------------------------- 
 
using System;
using System.Collections.Generic; 
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text; 
using System.Data.Common;
using System.Data.Common.CommandTrees; 
using System.Data.Common.CommandTrees.Internal; 
using System.Data.Common.Utils;
using System.Data.Metadata.Edm; 
using System.Data.SqlClient;

namespace System.Data.SqlClient.SqlGen
{ 
    /// 
    /// Translates the command object into a SQL string that can be executed on 
    /// SQL Server 2000 and SQL Server 2005. 
    /// 
    ///  
    /// The translation is implemented as a visitor 
    /// over the query tree.  It makes a single pass over the tree, collecting the sql
    /// fragments for the various nodes in the tree .
    /// 
    /// The major operations are
    ///  
    /// Select statement minimization.  Multiple nodes in the query tree 
    /// that can be part of a single SQL select statement are merged. e.g. a
    /// Filter node that is the input of a Project node can typically share the 
    /// same SQL statement.
    /// Alpha-renaming.  As a result of the statement minimization above, there
    /// could be name collisions when using correlated subqueries
    ///  
    /// 
    /// Filter( 
    ///     b = Project( c.x 
    ///         c = Extent(foo)
    ///         ) 
    ///     exists (
    ///         Filter(
    ///             c = Extent(foo)
    ///             b.x = c.x 
    ///             )
    ///     ) 
    /// ) 
    /// 
    /// The first Filter, Project and Extent will share the same SQL select statement. 
    /// The alias for the Project i.e. b, will be replaced with c.
    /// If the alias c for the Filter within the exists clause is not renamed,
    /// we will get c.x = c.x, which is incorrect.
    /// Instead, the alias c within the second filter should be renamed to c1, to give 
    /// c.x = c1.x i.e. b is renamed to c, and c is renamed to c1.
    ///  
    ///  
    /// Join flattening.  In the query tree, a list of join nodes is typically
    /// represented as a tree of Join nodes, each with 2 children. e.g. 
    /// 
    /// 
    /// a = Join(InnerJoin
    ///     b = Join(CrossJoin 
    ///         c = Extent(foo)
    ///         d = Extent(foo) 
    ///         ) 
    ///     e = Extent(foo)
    ///     on b.c.x = e.x 
    ///     )
    /// 
    /// If translated directly, this will be translated to
    ///  
    /// FROM ( SELECT c.*, d.*
    ///         FROM foo as c 
    ///         CROSS JOIN foo as d) as b 
    /// INNER JOIN foo as e on b.x' = e.x
    ///  
    /// It would be better to translate this as
    /// 
    /// FROM foo as c
    /// CROSS JOIN foo as d 
    /// INNER JOIN foo as e on c.x = e.x
    ///  
    /// This allows the optimizer to choose an appropriate join ordering for evaluation. 
    /// 
    ///  
    /// Select * and column renaming.  In the example above, we noticed that
    /// in some cases we add SELECT * FROM ... to complete the SQL
    /// statement. i.e. there is no explicit PROJECT list.
    /// In this case, we enumerate all the columns available in the FROM clause 
    /// This is particularly problematic in the case of Join trees, since the columns
    /// from the extents joined might have the same name - this is illegal.  To solve 
    /// this problem, we will have to rename columns if they are part of a SELECT * 
    /// for a JOIN node - we do not need renaming in any other situation.
    /// . 
    /// 
    /// 
    ///
    ///  
    /// Renaming issues.
    /// When rows or columns are renamed, we produce names that are unique globally 
    /// with respect to the query.  The names are derived from the original names, 
    /// with an integer as a suffix. e.g. CustomerId will be renamed to CustomerId1,
    /// CustomerId2 etc. 
    ///
    /// Since the names generated are globally unique, they will not conflict when the
    /// columns of a JOIN SELECT statement are joined with another JOIN.
    /// 
    /// 
    /// 
    ///  
    /// Record flattening.
    /// SQL server does not have the concept of records.  However, a join statement 
    /// produces records.  We have to flatten the record accesses into a simple
    /// alias.column form.  
    /// 
    /// 
    /// 
    /// Building the SQL. 
    /// There are 2 phases 
    /// 
    /// Traverse the tree, producing a sql builder  
    /// Write the SqlBuilder into a string, renaming the aliases and columns
    /// as needed.
    /// 
    /// 
    /// In the first phase, we traverse the tree.  We cannot generate the SQL string
    /// right away, since 
    ///  
    /// The WHERE clause has to be visited before the from clause.
    /// extent aliases and column aliases need to be renamed.  To minimize 
    /// renaming collisions, all the names used must be known, before any renaming
    /// choice is made.
    /// 
    /// To defer the renaming choices, we use symbols .  These 
    /// are renamed in the second phase.
    /// 
    /// Since visitor methods cannot transfer information to child nodes through 
    /// parameters, we use some global stacks,
    ///  
    /// A stack for the current SQL select statement.  This is needed by
    ///  to create a
    /// list of free variables used by a select statement.  This is needed for
    /// alias renaming. 
    /// 
    /// A stack for the join context.  When visiting an extent, 
    /// we need to know whether we are inside a join or not.  If we are inside 
    /// a join, we do not create a new SELECT statement.
    ///  
    /// 
    ///
    /// 
    /// Global state. 
    /// To enable renaming, we maintain
    ///  
    /// The set of all extent aliases used. 
    /// The set of all parameter names.
    /// The set of all column names that may need to be renamed. 
    /// 
    ///
    /// Finally, we have a symbol table to lookup variable references.  All references
    /// to the same extent have the same symbol. 
    /// 
    /// 
    ///  
    /// Sql select statement sharing.
    /// 
    /// Each of the relational operator nodes
    /// 
    /// Project
    /// Filter 
    /// GroupBy
    /// Sort/OrderBy 
    ///  
    /// can add its non-input (e.g. project, predicate, sort order etc.) to
    /// the SQL statement for the input, or create a new SQL statement. 
    /// If it chooses to reuse the input's SQL statement, we play the following
    /// symbol table trick to accomplish renaming.  The symbol table entry for
    /// the alias of the current node points to the symbol for the input in
    /// the input's SQL statement. 
    /// 
    ///  
    /// Project(b.x 
    ///     b = Filter(
    ///         c = Extent(foo) 
    ///         c.x = 5)
    ///     )
    /// 
    /// The Extent node creates a new SqlSelectStatement.  This is added to the 
    /// symbol table by the Filter as {c, Symbol(c)}.  Thus, c.x is resolved to
    /// Symbol(c).x. 
    /// Looking at the project node, we add {b, Symbol(c)} to the symbol table if the 
    /// SQL statement is reused, and {b, Symbol(b)}, if there is no reuse.
    /// 
    /// Thus, b.x is resolved to Symbol(c).x if there is reuse, and to
    /// Symbol(b).x if there is no reuse.
    /// 
    ///  
    /// 
    internal sealed class SqlGenerator : DbExpressionVisitor 
    { 
        #region Visitor parameter stacks
        ///  
        /// Every relational node has to pass its SELECT statement to its children
        /// This allows them (DbVariableReferenceExpression eventually) to update the list of
        /// outer extents (free variables) used by this select statement.
        ///  
        private Stack selectStatementStack;
 
        ///  
        /// The top of the stack
        ///  
        private SqlSelectStatement CurrentSelectStatement
        {
            // There is always something on the stack, so we can always Peek.
            get { return selectStatementStack.Peek(); } 
        }
 
        ///  
        /// Nested joins and extents need to know whether they should create
        /// a new Select statement, or reuse the parent's.  This flag 
        /// indicates whether the parent is a join or not.
        /// 
        private Stack isParentAJoinStack;
 
        /// 
        /// The top of the stack 
        ///  
        private bool IsParentAJoin
        { 
            // There might be no entry on the stack if a Join node has never
            // been seen, so we return false in that case.
            get { return isParentAJoinStack.Count == 0 ? false : isParentAJoinStack.Peek(); }
        } 

        #endregion 
 
        #region Global lists and state
        Dictionary allExtentNames; 
        internal Dictionary AllExtentNames
        {
            get { return allExtentNames; }
        } 

        // For each column name, we store the last integer suffix that 
        // was added to produce a unique column name.  This speeds up 
        // the creation of the next unique name for this column name.
        Dictionary allColumnNames; 
        internal Dictionary AllColumnNames
        {
            get { return allColumnNames; }
        } 

        SymbolTable symbolTable = new SymbolTable(); 
 
        /// 
        /// VariableReferenceExpressions are allowed only as children of DbPropertyExpression 
        /// or MethodExpression.  The cheapest way to ensure this is to set the following
        /// property in DbVariableReferenceExpression and reset it in the allowed parent expressions.
        /// 
        private bool isVarRefSingle; 

        #endregion 
 
        #region Statics
        static private readonly Dictionary _storeFunctionHandlers = InitializeStoreFunctionHandlers(); 
        static private readonly Dictionary _canonicalFunctionHandlers = InitializeCanonicalFunctionHandlers();
        static private readonly Dictionary _functionNameToOperatorDictionary = InitializeFunctionNameToOperatorDictionary();
        static private readonly Set _datepartKeywords = new Set(new string[] {  "year", "yy", "yyyy",
                                                                                                 "quarter", "qq", "q", 
                                                                                                 "month", "mm", "m",
                                                                                                 "dayofyear", "dy", "y", 
                                                                                                 "day", "dd", "d", 
                                                                                                 "week", "wk", "ww",
                                                                                                 "weekday", "dw", "w", 
                                                                                                 "hour", "hh",
                                                                                                 "minute", "mi", "n",
                                                                                                 "second", "ss", "s",
                                                                                                 "millisecond", "ms", 
                                                                                                 "microsecond", "mcs",
                                                                                                 "nanosecond", "ns", 
                                                                                                 "tzoffset", "tz", 
                                                                                                 "iso_week", "isoww", "isowk"},
                                                                                        StringComparer.OrdinalIgnoreCase).MakeReadOnly(); 
        static private readonly char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
        static private readonly Set _functionRequiresReturnTypeCast = new Set(new string[] { "SqlServer.LEN"         ,
                                                                                                                 "SqlServer.PATINDEX"    ,
                                                                                                                 "SqlServer.CHARINDEX"   , 
                                                                                                                 "SqlServer.DATALENGTH"  ,
                                                                                                                 "Edm.IndexOf"           , 
                                                                                                                 "Edm.Length"            }, 
                                                                                                      StringComparer.Ordinal).MakeReadOnly();
        static private readonly Set _maxTypeNames = new Set(new string[] { "varchar(max)"    , 
                                                                                           "nvarchar(max)"   ,
                                                                                           "text"            ,
                                                                                           "ntext"           ,
                                                                                           "varbinary(max)"  , 
                                                                                           "image"           ,
                                                                                           "xml"             }, 
                                                                                StringComparer.Ordinal).MakeReadOnly(); 
        const byte defaultDecimalPrecision = 18;
 
        private delegate ISqlFragment FunctionHandler(SqlGenerator sqlgen, DbFunctionExpression functionExpr);

        /// 
        /// All special store functions and their handlers 
        /// 
        ///  
        private static Dictionary InitializeStoreFunctionHandlers() 
        {
            Dictionary functionHandlers = new Dictionary(5, StringComparer.Ordinal); 
            functionHandlers.Add("CONCAT", HandleConcatFunction);
            functionHandlers.Add("DATEADD", HandleDatepartDateFunction);
            functionHandlers.Add("DATEDIFF", HandleDatepartDateFunction);
            functionHandlers.Add("DATENAME", HandleDatepartDateFunction); 
            functionHandlers.Add("DATEPART", HandleDatepartDateFunction);
            return functionHandlers; 
        } 

        ///  
        /// All special non-aggregate canonical functions and their handlers
        /// 
        /// 
        private static Dictionary InitializeCanonicalFunctionHandlers() 
        {
            Dictionary functionHandlers = new Dictionary(16, StringComparer.Ordinal); 
            functionHandlers.Add("IndexOf", HandleCanonicalFunctionIndexOf); 
            functionHandlers.Add("Length", HandleCanonicalFunctionLength);
            functionHandlers.Add("NewGuid", HandleCanonicalFunctionNewGuid); 
            functionHandlers.Add("Round", HandleCanonicalFunctionRound);
            functionHandlers.Add("ToLower", HandleCanonicalFunctionToLower);
            functionHandlers.Add("ToUpper", HandleCanonicalFunctionToUpper);
            functionHandlers.Add("Trim", HandleCanonicalFunctionTrim); 

            //DateTime Functions 
            functionHandlers.Add("Year", HandleCanonicalFunctionDatepart); 
            functionHandlers.Add("Month", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Day", HandleCanonicalFunctionDatepart); 
            functionHandlers.Add("Hour", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Minute", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Second", HandleCanonicalFunctionDatepart);
            functionHandlers.Add("Millisecond", HandleCanonicalFunctionDatepart); 
            functionHandlers.Add("CurrentDateTime", HandleCanonicalFunctionCurrentDateTime);
            functionHandlers.Add("CurrentUtcDateTime", HandleCanonicalFunctionCurrentUtcDateTime); 
            functionHandlers.Add("CurrentDateTimeOffset", HandleCanonicalFunctionCurrentDateTimeOffset); 
            functionHandlers.Add("GetTotalOffsetMinutes", HandleCanonicalFunctionGetTotalOffsetMinutes);
 
            //Functions that translate to operators
            functionHandlers.Add("Concat", HandleConcatFunction);
            functionHandlers.Add("BitwiseAnd", HandleCanonicalFunctionBitwise);
            functionHandlers.Add("BitwiseNot", HandleCanonicalFunctionBitwise); 
            functionHandlers.Add("BitwiseOr", HandleCanonicalFunctionBitwise);
            functionHandlers.Add("BitwiseXor", HandleCanonicalFunctionBitwise); 
 
            return functionHandlers;
        } 

        /// 
        /// Initalizes the mapping from functions to TSql operators
        /// for all functions that translate to TSql operators 
        /// 
        ///  
        private static Dictionary InitializeFunctionNameToOperatorDictionary() 
        {
            Dictionary functionNameToOperatorDictionary = new Dictionary(5, StringComparer.Ordinal); 
            functionNameToOperatorDictionary.Add("Concat", "+");    //canonical
            functionNameToOperatorDictionary.Add("CONCAT", "+");    //store
            functionNameToOperatorDictionary.Add("BitwiseAnd", "&");
            functionNameToOperatorDictionary.Add("BitwiseNot", "~"); 
            functionNameToOperatorDictionary.Add("BitwiseOr", "|");
            functionNameToOperatorDictionary.Add("BitwiseXor", "^"); 
            return functionNameToOperatorDictionary; 
        }
 
        #endregion

        #region SqlVersion, Metadata, ...
 
        /// 
        /// The current SQL Server version 
        ///  
        private SqlVersion sqlVersion;
        internal SqlVersion SqlVersion 
        {
            get { return sqlVersion; }
        }
        internal bool IsPreKatmai 
        {
            get { return SqlVersionUtils.IsPreKatmai(this.SqlVersion); } 
        } 

        private MetadataWorkspace metadataWorkspace; 
        internal MetadataWorkspace Workspace
        {
            get { return metadataWorkspace; }
        } 

        private TypeUsage integerType = null; 
        internal TypeUsage IntegerType 
        {
            get 
            {
                if (integerType == null)
                {
                    integerType = GetPrimitiveType(PrimitiveTypeKind.Int64); 
                }
                return integerType; 
            } 
        }
        private StoreItemCollection _storeItemCollection; 
        #endregion

        #region Constructor
        ///  
        /// Basic constructor.
        ///  
        /// server version 
        private SqlGenerator(SqlVersion sqlVersion)
        { 
            this.sqlVersion = sqlVersion;
        }
        #endregion
 
        #region Entry points
        ///  
        /// General purpose static function that can be called from System.Data assembly 
        /// 
        /// Server version 
        /// command tree
        /// Parameters to add to the command tree corresponding
        /// to constants in the command tree. Used only in ModificationCommandTrees.
        /// CommandType for generated command. 
        /// The string representing the SQL to be executed.
        internal static string GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, out List parameters, out CommandType commandType) 
        { 
            SqlGenerator sqlGen;
            commandType = CommandType.Text; 
            parameters = null;

            switch (tree.CommandTreeKind)
            { 
                case DbCommandTreeKind.Query:
                    sqlGen = new SqlGenerator(sqlVersion); 
                    return sqlGen.GenerateSql((DbQueryCommandTree)tree); 

                case DbCommandTreeKind.Insert: 
                    return DmlSqlGenerator.GenerateInsertSql((DbInsertCommandTree)tree, sqlVersion, out parameters);

                case DbCommandTreeKind.Delete:
                    return DmlSqlGenerator.GenerateDeleteSql((DbDeleteCommandTree)tree, sqlVersion, out parameters); 

                case DbCommandTreeKind.Update: 
                    return DmlSqlGenerator.GenerateUpdateSql((DbUpdateCommandTree)tree, sqlVersion, out parameters); 

                case DbCommandTreeKind.Function: 
                    sqlGen = new SqlGenerator(sqlVersion);
                    return GenerateFunctionSql((DbFunctionCommandTree)tree, out commandType);

                default: 
                    //We have covered all command tree kinds
                    Debug.Assert(false, "Unknown command tree kind"); 
                    parameters = null; 
                    return null;
            } 
        }

        private static string GenerateFunctionSql(DbFunctionCommandTree tree, out CommandType commandType)
        { 
            tree.Validate();
 
            EdmFunction function = tree.EdmFunction; 

            if (String.IsNullOrEmpty(function.CommandTextAttribute)) 
            {
                // build a quoted description of the function
                commandType = CommandType.StoredProcedure;
 
                // if the schema name is not explicitly given, it is assumed to be the metadata namespace
                string schemaName = String.IsNullOrEmpty(function.Schema) ? 
                    function.NamespaceName : function.Schema; 

                // if the function store name is not explicitly given, it is assumed to be the metadata name 
                string functionName = String.IsNullOrEmpty(function.StoreFunctionNameAttribute) ?
                    function.Name : function.StoreFunctionNameAttribute;

                // quote elements of function text 
                string quotedSchemaName = QuoteIdentifier(schemaName);
                string quotedFunctionName = QuoteIdentifier(functionName); 
 
                // separator
                const string schemaSeparator = "."; 

                // concatenate elements of function text
                string quotedFunctionText = quotedSchemaName + schemaSeparator + quotedFunctionName;
 
                return quotedFunctionText;
            } 
            else 
            {
                // if the user has specified the command text, pass it through verbatim and choose CommandType.Text 
                commandType = CommandType.Text;
                return function.CommandTextAttribute;
            }
        } 
        #endregion
 
        #region Driver Methods 
        /// 
        /// Translate a command tree to a SQL string. 
        ///
        /// The input tree could be translated to either a SQL SELECT statement
        /// or a SELECT expression.  This choice is made based on the return type
        /// of the expression 
        /// CollectionType => select statement
        /// non collection type => select expression 
        ///  
        /// 
        /// The string representing the SQL to be executed. 
        private string GenerateSql(DbQueryCommandTree tree)
        {
            tree.Validate();
 
            DbQueryCommandTree targetTree = tree;
 
            //If we are on Sql 8.0 rewrite the tree if needed 
            if (this.SqlVersion == SqlVersion.Sql8)
            { 
                if (Sql8ConformanceChecker.NeedsRewrite(tree.Query))
                {
                    targetTree = Sql8ExpressionRewriter.Rewrite(tree);
                } 
            }
 
            this.metadataWorkspace = targetTree.MetadataWorkspace; 
            // needed in Private Type Helpers section bellow
            _storeItemCollection = (StoreItemCollection)this.Workspace.GetItemCollection(DataSpace.SSpace); 

            selectStatementStack = new Stack();
            isParentAJoinStack = new Stack();
 
            allExtentNames = new Dictionary(StringComparer.OrdinalIgnoreCase);
            allColumnNames = new Dictionary(StringComparer.OrdinalIgnoreCase); 
 
            // Literals will not be converted to parameters.
 
            ISqlFragment result;
            if (TypeSemantics.IsCollectionType(targetTree.Query.ResultType))
            {
                SqlSelectStatement sqlStatement = VisitExpressionEnsureSqlStatement(targetTree.Query); 
                Debug.Assert(sqlStatement != null, "The outer most sql statment is null");
                sqlStatement.IsTopMost = true; 
                result = sqlStatement; 

            } 
            else
            {
                SqlBuilder sqlBuilder = new SqlBuilder();
                sqlBuilder.Append("SELECT "); 
                sqlBuilder.Append(targetTree.Query.Accept(this));
 
                result = sqlBuilder; 
            }
 
            if (isVarRefSingle)
            {
                throw EntityUtil.NotSupported();
                // A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression 
            }
 
            // Check that the parameter stacks are not leaking. 
            Debug.Assert(selectStatementStack.Count == 0);
            Debug.Assert(isParentAJoinStack.Count == 0); 

            return WriteSql(result);
        }
 
        /// 
        /// Convert the SQL fragments to a string. 
        /// We have to setup the Stream for writing. 
        /// 
        ///  
        /// A string representing the SQL to be executed.
        private string WriteSql(ISqlFragment sqlStatement)
        {
            StringBuilder builder = new StringBuilder(1024); 
            using (SqlWriter writer = new SqlWriter(builder))
            { 
                sqlStatement.WriteSql(writer, this); 
            }
 
            return builder.ToString();
        }
        #endregion
 
        #region IExpressionVisitor Members
 
        ///  
        /// Translate(left) AND Translate(right)
        ///  
        /// 
        /// A .
        public override ISqlFragment Visit(DbAndExpression e)
        { 
            return VisitBinaryExpression(" AND ", DbExpressionKind.And, e.Left, e.Right);
        } 
 
        /// 
        /// An apply is just like a join, so it shares the common join processing 
        /// in 
        /// 
        /// 
        /// A . 
        public override ISqlFragment Visit(DbApplyExpression e)
        { 
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbApplyExpression when translating for SQL Server 2000."); 

            List inputs = new List(); 
            inputs.Add(e.Input);
            inputs.Add(e.Apply);

            string joinString; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.CrossApply: 
                    joinString = "CROSS APPLY";
                    break; 

                case DbExpressionKind.OuterApply:
                    joinString = "OUTER APPLY";
                    break; 

                default: 
                    Debug.Assert(false); 
                    throw EntityUtil.InvalidOperation(String.Empty);
            } 

            // The join condition does not exist in this case, so we use null.
            // WE do not have a on clause, so we use JoinType.CrossJoin.
            return VisitJoinExpression(inputs, DbExpressionKind.CrossJoin, joinString, null); 
        }
 
        ///  
        /// For binary expressions, we delegate to .
        /// We handle the other expressions directly. 
        /// 
        /// 
        /// A 
        public override ISqlFragment Visit(DbArithmeticExpression e) 
        {
            SqlBuilder result; 
 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.Divide:
                    result = VisitBinaryExpression(" / ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break;
                case DbExpressionKind.Minus: 
                    result = VisitBinaryExpression(" - ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break; 
                case DbExpressionKind.Modulo: 
                    result = VisitBinaryExpression(" % ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break; 
                case DbExpressionKind.Multiply:
                    result = VisitBinaryExpression(" * ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break;
                case DbExpressionKind.Plus: 
                    result = VisitBinaryExpression(" + ", e.ExpressionKind, e.Arguments[0], e.Arguments[1]);
                    break; 
 
                case DbExpressionKind.UnaryMinus:
                    result = new SqlBuilder(); 
                    result.Append(" -(");
                    result.Append(e.Arguments[0].Accept(this));
                    result.Append(")");
                    break; 

                default: 
                    Debug.Assert(false); 
                    throw EntityUtil.InvalidOperation(String.Empty);
            } 

            return result;
        }
 
        /// 
        /// If the ELSE clause is null, we do not write it out. 
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbCaseExpression e)
        {
            SqlBuilder result = new SqlBuilder();
 
            Debug.Assert(e.When.Count == e.Then.Count);
 
            result.Append("CASE"); 
            for (int i = 0; i < e.When.Count; ++i)
            { 
                result.Append(" WHEN (");
                result.Append(e.When[i].Accept(this));
                result.Append(") THEN ");
                result.Append(e.Then[i].Accept(this)); 
            }
            // 
 
            if (e.Else != null && !(e.Else is DbNullExpression))
            { 
                result.Append(" ELSE ");
                result.Append(e.Else.Accept(this));
            }
 
            result.Append(" END");
 
            return result; 
        }
 
        /// 
        ///
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbCastExpression e) 
        { 
            SqlBuilder result = new SqlBuilder();
            result.Append(" CAST( "); 
            result.Append(e.Argument.Accept(this));
            result.Append(" AS ");
            result.Append(GetSqlPrimitiveType(e.ResultType));
            result.Append(")"); 

            return result; 
        } 

        ///  
        /// The parser generates Not(Equals(...)) for <>.
        /// 
        /// 
        /// A . 
        public override ISqlFragment Visit(DbComparisonExpression e)
        { 
            SqlBuilder result; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.Equals:
                    result = VisitComparisonExpression(" = ", e.Left, e.Right);
                    break;
                case DbExpressionKind.LessThan: 
                    result = VisitComparisonExpression(" < ", e.Left, e.Right);
                    break; 
                case DbExpressionKind.LessThanOrEquals: 
                    result = VisitComparisonExpression(" <= ", e.Left, e.Right);
                    break; 
                case DbExpressionKind.GreaterThan:
                    result = VisitComparisonExpression(" > ", e.Left, e.Right);
                    break;
                case DbExpressionKind.GreaterThanOrEquals: 
                    result = VisitComparisonExpression(" >= ", e.Left, e.Right);
                    break; 
                    // The parser does not generate the expression kind below. 
                case DbExpressionKind.NotEquals:
                    result = VisitComparisonExpression(" <> ", e.Left, e.Right); 
                    break;

                default:
                    Debug.Assert(false);  // The constructor should have prevented this 
                    throw EntityUtil.InvalidOperation(String.Empty);
            } 
 
            return result;
        } 

        /// 
        /// Generate tsql for a constant. Avoid the explicit cast (if possible) when
        /// the isCastOptional parameter is set 
        /// 
        /// the constant expression 
        /// can we avoid the CAST 
        /// the tsql fragment
        private ISqlFragment VisitConstant(DbConstantExpression e, bool isCastOptional) 
        {
            // Constants will be send to the store as part of the generated TSQL, not as parameters

            SqlBuilder result = new SqlBuilder(); 

            PrimitiveTypeKind typeKind; 
            // Model Types can be (at the time of this implementation): 
            //      Binary, Boolean, Byte, Date, DateTime, DateTimeOffset, Decimal, Double, Guid, Int16, Int32, Int64, Single, String, Time
            if (TypeHelpers.TryGetPrimitiveTypeKind(e.ResultType, out typeKind)) 
            {
                switch (typeKind)
                {
                    case PrimitiveTypeKind.Int32: 
                        // default sql server type for integral values.
                        result.Append(e.Value.ToString()); 
                        break; 

                    case PrimitiveTypeKind.Binary: 
                        result.Append(" 0x");
                        result.Append(ByteArrayToBinaryString((Byte[])e.Value));
                        result.Append(" ");
                        break; 

                    case PrimitiveTypeKind.Boolean: 
                        // Bugs 450277, 430294: Need to preserve the boolean type-ness of 
                        // this value for round-trippability
                        WrapWithCastIfNeeded(!isCastOptional, (bool)e.Value ? "1" : "0", "bit", result); 
                        break;

                    case PrimitiveTypeKind.Byte:
                        WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "tinyint", result); 
                        break;
 
                    case PrimitiveTypeKind.DateTime: 
                        result.Append("convert(");
                        result.Append(this.IsPreKatmai ? "datetime" : "datetime2"); 
                        result.Append(", ");
                        result.Append(EscapeSingleQuote(((System.DateTime)e.Value).ToString(this.IsPreKatmai ? "yyyy-MM-dd HH:mm:ss.fff" : "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture), false /* IsUnicode */));
                        result.Append(", 121)");
                        break; 

                    case PrimitiveTypeKind.Time: 
                        AssertKatmaiOrNewer(typeKind); 
                        result.Append("convert(");
                        result.Append(e.ResultType.EdmType.Name); 
                        result.Append(", ");
                        result.Append(EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */));
                        result.Append(", 121)");
                        break; 

                    case PrimitiveTypeKind.DateTimeOffset: 
                        AssertKatmaiOrNewer(typeKind); 
                        result.Append("convert(");
                        result.Append(e.ResultType.EdmType.Name); 
                        result.Append(", ");
                        result.Append(EscapeSingleQuote(((System.DateTimeOffset)e.Value).ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz", CultureInfo.InvariantCulture), false /* IsUnicode */));
                        result.Append(", 121)");
                        break; 

                    case PrimitiveTypeKind.Decimal: 
                        string strDecimal = ((Decimal)e.Value).ToString(CultureInfo.InvariantCulture); 
                        // if the decimal value has no decimal part, cast as decimal to preserve type
                        // if the number has precision > int64 max precision, it will be handled as decimal by sql server 
                        // and does not need cast. if precision is lest then 20, then cast using Max(literal precision, sql default precision)
                        bool needsCast = -1 == strDecimal.IndexOf('.') && (strDecimal.TrimStart(new char[] { '-' }).Length < 20);

                        byte precision = (byte)Math.Max((Byte)strDecimal.Length, defaultDecimalPrecision); 
                        Debug.Assert(precision > 0, "Precision must be greater than zero");
 
                        string decimalType = "decimal(" + precision.ToString(CultureInfo.InvariantCulture) + ")"; 

                        WrapWithCastIfNeeded(needsCast, strDecimal, decimalType, result); 
                        break;

                    case PrimitiveTypeKind.Double:
                        WrapWithCastIfNeeded(true, ((Double)e.Value).ToString("R", CultureInfo.InvariantCulture), "float(53)", result); 
                        break;
 
                    case PrimitiveTypeKind.Guid: 
                        WrapWithCastIfNeeded(true, EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */), "uniqueidentifier", result);
                        break; 

                    case PrimitiveTypeKind.Int16:
                        WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "smallint", result);
                        break; 

                    case PrimitiveTypeKind.Int64: 
                        WrapWithCastIfNeeded(!isCastOptional, e.Value.ToString(), "bigint", result); 
                        break;
 
                    case PrimitiveTypeKind.Single:
                        WrapWithCastIfNeeded(true, ((Single)e.Value).ToString("R", CultureInfo.InvariantCulture), "real", result);
                        break;
 
                    case PrimitiveTypeKind.String:
                        bool isUnicode; 
                        if (!TypeHelpers.TryGetIsUnicode(e.ResultType, out isUnicode)) 
                        {
                            isUnicode = true; 
                        }
                        result.Append(EscapeSingleQuote(e.Value as string, isUnicode));
                        break;
 
                    default:
                        // all known scalar types should been handled already. 
                        throw EntityUtil.NotSupported(); 
                }
            } 
            else
            {
                throw EntityUtil.NotSupported();
                //if/when Enum types are supported, then handle appropriately, for now is not a valid type for constants. 
                //result.Append(e.Value.ToString());
            } 
 
            return result;
        } 

        /// 
        /// Helper function for 
        /// Appneds the given constant value to the result either 'as is' or wrapped with a cast to the given type. 
        /// 
        ///  
        ///  
        /// 
        ///  
        private static void WrapWithCastIfNeeded(bool cast, string value, string typeName, SqlBuilder result)
        {
            if (!cast)
            { 
                result.Append(value);
            } 
            else 
            {
                result.Append("cast("); 
                result.Append(value);
                result.Append(" as ");
                result.Append(typeName);
                result.Append(")"); 
            }
        } 
 
        /// 
        /// We do not pass constants as parameters. 
        /// 
        /// 
        /// A .  Strings are wrapped in single
        /// quotes and escaped.  Numbers are written literally. 
        public override ISqlFragment Visit(DbConstantExpression e)
        { 
            return VisitConstant(e, false /* isCastOptional */); 
        }
 
        /// 
        ///
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbDerefExpression e) 
        { 
            throw EntityUtil.NotSupported();
        } 

        /// 
        /// The DISTINCT has to be added to the beginning of SqlSelectStatement.Select,
        /// but it might be too late for that.  So, we use a flag on SqlSelectStatement 
        /// instead, and add the "DISTINCT" in the second phase.
        ///  
        ///  
        /// A 
        public override ISqlFragment Visit(DbDistinctExpression e) 
        {
            SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument);

            if (!IsCompatible(result, e.ExpressionKind)) 
            {
                Symbol fromSymbol; 
                TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType); 
                result = CreateNewSelectStatement(result, "distinct", inputType, out fromSymbol);
                AddFromSymbol(result, "distinct", fromSymbol, false); 
            }

            result.IsDistinct = true;
            return result; 
        }
 
        ///  
        /// An element expression returns a scalar - so it is translated to
        /// ( Select ... ) 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbElementExpression e) 
        {
            // ISSUE: What happens if the DbElementExpression is used as an input expression? 
            // i.e. adding the '('  might not be right in all cases. 
            SqlBuilder result = new SqlBuilder();
            result.Append("("); 
            result.Append(VisitExpressionEnsureSqlStatement(e.Argument));
            result.Append(")");

            return result; 
        }
 
        ///  
        /// 
        ///  
        /// 
        /// 
        public override ISqlFragment Visit(DbExceptExpression e)
        { 
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbExceptExpression when translating for SQL Server 2000.");
 
            return VisitSetOpExpression(e.Left, e.Right, "EXCEPT"); 
        }
 
        /// 
        /// Only concrete expression types will be visited.
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbExpression e) 
        { 
            throw EntityUtil.InvalidOperation(String.Empty);
        } 

        /// 
        ///
        ///  
        /// 
        /// If we are in a Join context, returns a  
        /// with the extent name, otherwise, a new  
        /// with the From field set.
        public override ISqlFragment Visit(DbScanExpression e) 
        {
            EntitySetBase target = e.Target;

            // ISSUE: Should we just return a string all the time, and let 
            // VisitInputExpression create the SqlSelectStatement?
 
            if (IsParentAJoin) 
            {
                SqlBuilder result = new SqlBuilder(); 
                result.Append(GetTargetTSql(target));

                return result;
            } 
            else
            { 
                SqlSelectStatement result = new SqlSelectStatement(); 
                result.From.Append(GetTargetTSql(target));
 
                return result;
            }
        }
 
        /// 
        /// Gets escaped TSql identifier describing this entity set. 
        ///  
        /// 
        internal static string GetTargetTSql(EntitySetBase entitySetBase) 
        {
            if (null == entitySetBase.CachedProviderSql)
            {
                if (null == entitySetBase.DefiningQuery) 
                {
                    // construct escaped T-SQL referencing entity set 
                    StringBuilder builder = new StringBuilder(50); 
                    if (!string.IsNullOrEmpty(entitySetBase.Schema))
                    { 
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Schema));
                        builder.Append(".");
                    }
                    else 
                    {
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.EntityContainer.Name)); 
                        builder.Append("."); 
                    }
 
                    if (!string.IsNullOrEmpty(entitySetBase.Table))
                    {
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Table));
                    } 
                    else
                    { 
                        builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Name)); 
                    }
                    entitySetBase.CachedProviderSql = builder.ToString(); 
                }
                else
                {
                    entitySetBase.CachedProviderSql = "(" + entitySetBase.DefiningQuery + ")"; 
                }
            } 
            return entitySetBase.CachedProviderSql; 
        }
 
        /// 
        /// The bodies of , ,
        /// ,  are similar.
        /// Each does the following. 
        /// 
        ///  Visit the input expression 
        ///  Determine if the input's SQL statement can be reused, or a new 
        /// one must be created.
        /// Create a new symbol table scope 
        /// Push the Sql statement onto a stack, so that children can
        /// update the free variable list.
        /// Visit the non-input expression.
        /// Cleanup 
        /// 
        ///  
        ///  
        /// A 
        public override ISqlFragment Visit(DbFilterExpression e) 
        {
            return VisitFilterExpression(e.Input, e.Predicate, false);
        }
 
        /// 
        /// Lambda functions are not supported. 
        /// The functions supported are: 
        /// 
        /// Canonical Functions - We recognize these by their dataspace, it is DataSpace.CSpace 
        /// Store Functions - We recognize these by the BuiltInAttribute and not being Canonical
        /// User-defined Functions - All the rest except for Lambda functions
        /// 
        /// We handle Canonical and Store functions the same way: If they are in the list of functions 
        /// that need special handling, we invoke the appropriate handler, otherwise we translate them to
        /// FunctionName(arg1, arg2, ..., argn). 
        /// We translate user-defined functions to NamespaceName.FunctionName(arg1, arg2, ..., argn). 
        /// 
        ///  
        /// A 
        public override ISqlFragment Visit(DbFunctionExpression e)
        {
            if (e.IsLambda) 
            {
                throw EntityUtil.NotSupported(); 
            } 

            // 
            // check if function requires special case processing, if so, delegates to it
            //
            if (IsSpecialCanonicalFunction(e))
            { 
                return HandleSpecialCanonicalFunction(e);
            } 
 
            if (IsSpecialStoreFunction(e))
            { 
                return HandleSpecialStoreFunction(e);
            }

            return HandleFunctionDefault(e); 
        }
 
        ///  
        ///
        ///  
        /// 
        /// 
        public override ISqlFragment Visit(DbEntityRefExpression e)
        { 
            throw EntityUtil.NotSupported();
        } 
 
        /// 
        /// 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbRefKeyExpression e) 
        {
            throw EntityUtil.NotSupported(); 
        } 

        ///  
        ///  for general details.
        /// We modify both the GroupBy and the Select fields of the SqlSelectStatement.
        /// GroupBy gets just the keys without aliases,
        /// and Select gets the keys and the aggregates with aliases. 
        ///
        /// Sql Server does not support arbitrarily complex expressions inside aggregates, 
        /// and requires keys to have reference to the input scope, 
        /// so in some cases we create a nested query in which we alias the arguments to the aggregates.
        /// The exact limitations of Sql Server are: 
        /// 
        /// If an expression being aggregated contains an outer reference, then that outer
        /// reference must be the only column referenced in the expression (SQLBUDT #488741)
        /// Sql Server cannot perform an aggregate function on an expression containing 
        /// an aggregate or a subquery. (SQLBUDT #504600)
        ///Sql Server requries each GROUP BY expression (key) to contain at least one column 
        /// that is not an outer reference. (SQLBUDT #616523) 
        /// Aggregates on the right side of an APPLY cannot reference columns from the left side.
        /// (SQLBUDT #617683)  
        /// 
        ///
        /// The default translation, without inner query is:
        /// 
        ///     SELECT
        ///         kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn, 
        ///         aggf1(aexpr1) AS agg1, .. aggfn(aexprn) AS aggn 
        ///     FROM input AS a
        ///     GROUP BY kexp1, kexp2, .. kexpn 
        ///
        /// When we inject an innner query, the equivalent translation is:
        ///
        ///     SELECT 
        ///         key1 AS key1, key2 AS key2, .. keyn AS keys,
        ///         aggf1(agg1) AS agg1, aggfn(aggn) AS aggn 
        ///     FROM ( 
        ///             SELECT
        ///                 kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn, 
        ///                 aexpr1 AS agg1, .. aexprn AS aggn
        ///             FROM input AS a
        ///         ) as a
        ///     GROUP BY key1, key2, keyn 
        ///
        ///  
        ///  
        /// A 
        public override ISqlFragment Visit(DbGroupByExpression e) 
        {
            Symbol fromSymbol;
            SqlSelectStatement innerQuery = VisitInputExpression(e.Input.Expression,
                e.Input.VariableName, e.Input.VariableType, out fromSymbol); 

            // GroupBy is compatible with Filter and OrderBy 
            // but not with Project, GroupBy 
            if (!IsCompatible(innerQuery, e.ExpressionKind))
            { 
                innerQuery = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
            }

            selectStatementStack.Push(innerQuery); 
            symbolTable.EnterScope();
 
            AddFromSymbol(innerQuery, e.Input.VariableName, fromSymbol); 
            // This line is not present for other relational nodes.
            symbolTable.Add(e.Input.GroupVariableName, fromSymbol); 

            // The enumerator is shared by both the keys and the aggregates,
            // so, we do not close it in between.
            RowType groupByType = TypeHelpers.GetEdmType(TypeHelpers.GetEdmType(e.ResultType).TypeUsage); 

            //SQL Server does not support arbitrarily complex expressions inside aggregates, 
            // and requires keys to have reference to the input scope, 
            // so we check for the specific restrictions and if need we inject an inner query.
            bool needsInnerQuery = GroupByAggregatesNeedInnerQuery(e.Aggregates, e.Input.GroupVariableName) || GroupByKeysNeedInnerQuery(e.Keys, e.Input.VariableName); 

            SqlSelectStatement result;
            if (needsInnerQuery)
            { 
                //Create the inner query
                result = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, false, out fromSymbol); 
                AddFromSymbol(result, e.Input.VariableName, fromSymbol, false); 
            }
            else 
            {
                result = innerQuery;
            }
 
            using (IEnumerator members = groupByType.Properties.GetEnumerator())
            { 
                members.MoveNext(); 
                Debug.Assert(result.Select.IsEmpty);
 
                string separator = "";

                foreach (DbExpression key in e.Keys)
                { 
                    EdmProperty member = members.Current;
                    string alias = QuoteIdentifier(member.Name); 
 
                    result.GroupBy.Append(separator);
 
                    ISqlFragment keySql = key.Accept(this);

                    if (!needsInnerQuery)
                    { 
                        //Default translation: Key AS Alias
                        result.Select.Append(separator); 
                        result.Select.AppendLine(); 
                        result.Select.Append(keySql);
                        result.Select.Append(" AS "); 
                        result.Select.Append(alias);

                        result.GroupBy.Append(keySql);
                    } 
                    else
                    { 
                        // The inner query contains the default translation Key AS Alias 
                        innerQuery.Select.Append(separator);
                        innerQuery.Select.AppendLine(); 
                        innerQuery.Select.Append(keySql);
                        innerQuery.Select.Append(" AS ");
                        innerQuery.Select.Append(alias);
 
                        //The outer resulting query projects over the key aliased in the inner query:
                        //  fromSymbol.Alias AS Alias 
                        result.Select.Append(separator); 
                        result.Select.AppendLine();
                        result.Select.Append(fromSymbol); 
                        result.Select.Append(".");
                        result.Select.Append(alias);
                        result.Select.Append(" AS ");
                        result.Select.Append(alias); 

                        result.GroupBy.Append(alias); 
                    } 

                    separator = ", "; 
                    members.MoveNext();
                }

                foreach (DbAggregate aggregate in e.Aggregates) 
                {
                    EdmProperty member = members.Current; 
                    string alias = QuoteIdentifier(member.Name); 

                    Debug.Assert(aggregate.Arguments.Count == 1); 
                    ISqlFragment translatedAggregateArgument = aggregate.Arguments[0].Accept(this);

                    object aggregateArgument;
 
                    if (needsInnerQuery)
                    { 
                        //In this case the argument to the aggratete is reference to the one projected out by the 
                        // inner query
                        SqlBuilder wrappingAggregateArgument = new SqlBuilder(); 
                        wrappingAggregateArgument.Append(fromSymbol);
                        wrappingAggregateArgument.Append(".");
                        wrappingAggregateArgument.Append(alias);
                        aggregateArgument = wrappingAggregateArgument; 

                        innerQuery.Select.Append(separator); 
                        innerQuery.Select.AppendLine(); 
                        innerQuery.Select.Append(translatedAggregateArgument);
                        innerQuery.Select.Append(" AS "); 
                        innerQuery.Select.Append(alias);
                    }
                    else
                    { 
                        aggregateArgument = translatedAggregateArgument;
                    } 
 
                    ISqlFragment aggregateResult = VisitAggregate(aggregate, aggregateArgument);
 
                    result.Select.Append(separator);
                    result.Select.AppendLine();
                    result.Select.Append(aggregateResult);
                    result.Select.Append(" AS "); 
                    result.Select.Append(alias);
 
                    separator = ", "; 
                    members.MoveNext();
                } 
            }

            symbolTable.ExitScope();
            selectStatementStack.Pop(); 

            return result; 
        } 

        ///  
        /// 
        /// 
        /// 
        ///  
        public override ISqlFragment Visit(DbIntersectExpression e)
        { 
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbIntersectExpression when translating for SQL Server 2000."); 

            return VisitSetOpExpression(e.Left, e.Right, "INTERSECT"); 
        }

        /// 
        /// Not(IsEmpty) has to be handled specially, so we delegate to 
        /// .
        /// 
        ///  
        /// 
        /// A . 
        /// [NOT] EXISTS( ... )
        /// 
        public override ISqlFragment Visit(DbIsEmptyExpression e)
        { 
            return VisitIsEmptyExpression(e, false);
        } 
 
        /// 
        /// Not(IsNull) is handled specially, so we delegate to 
        /// 
        /// 
        /// 
        /// A  
        /// IS [NOT] NULL
        ///  
        public override ISqlFragment Visit(DbIsNullExpression e) 
        {
            return VisitIsNullExpression(e, false); 
        }

        /// 
        /// No error is raised if the store cannot support this. 
        /// 
        ///  
        /// A  
        public override ISqlFragment Visit(DbIsOfExpression e)
        { 
            throw EntityUtil.NotSupported();
        }

        ///  
        /// 
        ///  
        ///  
        /// A .
        public override ISqlFragment Visit(DbCrossJoinExpression e) 
        {
            return VisitJoinExpression(e.Inputs, e.ExpressionKind, "CROSS JOIN", null);
        }
 
        /// 
        ///  
        ///  
        /// 
        /// A . 
        public override ISqlFragment Visit(DbJoinExpression e)
        {
            #region Map join type to a string
            string joinString; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.FullOuterJoin: 
                    joinString = "FULL OUTER JOIN";
                    break; 

                case DbExpressionKind.InnerJoin:
                    joinString = "INNER JOIN";
                    break; 

                case DbExpressionKind.LeftOuterJoin: 
                    joinString = "LEFT OUTER JOIN"; 
                    break;
 
                default:
                    Debug.Assert(false);
                    joinString = null;
                    break; 
            }
            #endregion 
 
            List inputs = new List(2);
            inputs.Add(e.Left); 
            inputs.Add(e.Right);

            return VisitJoinExpression(inputs, e.ExpressionKind, joinString, e.JoinCondition);
        } 

        ///  
        /// 
        /// 
        ///  
        /// A 
        public override ISqlFragment Visit(DbLikeExpression e)
        {
            SqlBuilder result = new SqlBuilder(); 
            result.Append(e.Argument.Accept(this));
            result.Append(" LIKE "); 
            result.Append(e.Pattern.Accept(this)); 

            // if the ESCAPE expression is a DbNullExpression, then that's tantamount to 
            // not having an ESCAPE at all
            if (e.Escape.ExpressionKind != DbExpressionKind.Null)
            {
                result.Append(" ESCAPE "); 
                result.Append(e.Escape.Accept(this));
            } 
 
            return result;
        } 

        /// 
        ///  Translates to TOP expression. For Sql8, limit can only be a constant expression
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbLimitExpression e) 
        {
            Debug.Assert(e.Limit is DbConstantExpression || e.Limit is DbParameterReferenceExpression, "DbLimitExpression.Limit is of invalid expression type"); 
            Debug.Assert(!((this.SqlVersion == SqlVersion.Sql8) && (e.Limit is DbParameterReferenceExpression)), "DbLimitExpression.Limit is DbParameterReferenceExpression for SQL Server 2000.");

            SqlSelectStatement result = VisitExpressionEnsureSqlStatement(e.Argument, false);
            Symbol fromSymbol; 

            if (!IsCompatible(result, e.ExpressionKind)) 
            { 
                TypeUsage inputType = TypeHelpers.GetElementTypeUsage(e.Argument.ResultType);
 
                result = CreateNewSelectStatement(result, "top", inputType, out fromSymbol);
                AddFromSymbol(result, "top", fromSymbol, false);
            }
 
            ISqlFragment topCount = HandleCountExpression(e.Limit) ;
 
            result.Top = new TopClause(topCount, e.WithTies); 
            return result;
        } 

#if METHOD_EXPRESSION
        /// 
        /// 
        /// 
        ///  
        /// A  
        public override ISqlFragment Visit(MethodExpression e)
        { 
            SqlBuilder result = new SqlBuilder();

            result.Append(e.Instance.Accept(this));
            result.Append("."); 
            result.Append(QuoteIdentifier(e.Method.Name));
            result.Append("("); 
 
            // Since the VariableReferenceExpression is a proper child of ours, we can reset
            // isVarSingle. 
            VariableReferenceExpression VariableReferenceExpression = e.Instance as VariableReferenceExpression;
            if (VariableReferenceExpression != null)
            {
                isVarRefSingle = false; 
            }
 
            string separator = ""; 
            foreach (Expression argument in e.Arguments)
            { 
                result.Append(separator);
                result.Append(argument.Accept(this));
                separator = ", ";
            } 
            result.Append(")");
 
            return result; 
        }
#endif 

        /// 
        /// DbNewInstanceExpression is allowed as a child of DbProjectExpression only.
        /// If anyone else is the parent, we throw. 
        /// We also perform special casing for collections - where we could convert
        /// them into Unions 
        /// 
        ///  for the actual implementation.
        /// 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbNewInstanceExpression e) 
        {
            if (TypeSemantics.IsCollectionType(e.ResultType)) 
            { 
                return VisitCollectionConstructor(e);
            } 
            throw EntityUtil.NotSupported();
        }

        ///  
        /// The Not expression may cause the translation of its child to change.
        /// These children are 
        ///  
        /// NOT(Not(x)) becomes x
        /// NOT EXISTS becomes EXISTS 
        /// IS NULL becomes IS NOT NULL
        /// = becomes<> 
        /// 
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbNotExpression e) 
        {
            // Flatten Not(Not(x)) to x. 
            DbNotExpression notExpression = e.Argument as DbNotExpression;
            if (notExpression != null)
            {
                return notExpression.Argument.Accept(this); 
            }
 
            DbIsEmptyExpression isEmptyExpression = e.Argument as DbIsEmptyExpression; 
            if (isEmptyExpression != null)
            { 
                return VisitIsEmptyExpression(isEmptyExpression, true);
            }

            DbIsNullExpression isNullExpression = e.Argument as DbIsNullExpression; 
            if (isNullExpression != null)
            { 
                return VisitIsNullExpression(isNullExpression, true); 
            }
 
            DbComparisonExpression comparisonExpression = e.Argument as DbComparisonExpression;
            if (comparisonExpression != null)
            {
                if (comparisonExpression.ExpressionKind == DbExpressionKind.Equals) 
                {
                    return VisitBinaryExpression(" <> ", DbExpressionKind.NotEquals, comparisonExpression.Left, comparisonExpression.Right); 
                } 
            }
 
            SqlBuilder result = new SqlBuilder();
            result.Append(" NOT (");
            result.Append(e.Argument.Accept(this));
            result.Append(")"); 

            return result; 
        } 

        ///  
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbNullExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
 
            // always cast nulls - sqlserver doesn't like case expressions where the "then" clause is null
            result.Append("CAST(NULL AS "); 
            TypeUsage type = e.ResultType;

            //
            // Use the narrowest type possible - especially for strings where we don't want 
            // to produce unicode strings always.
            // 
            Debug.Assert(Helper.IsPrimitiveType(type.EdmType), "Type must be primitive type"); 
            PrimitiveType primitiveType = type.EdmType as PrimitiveType;
            switch(primitiveType.PrimitiveTypeKind) 
            {
                case PrimitiveTypeKind.String:
                    result.Append("varchar(1)");
                    break; 
                case PrimitiveTypeKind.Binary:
                    result.Append("varbinary(1)"); 
                    break; 
                default:
                    result.Append(GetSqlPrimitiveType(type)); 
                    break;
            }

            result.Append(")"); 
            return result;
        } 
 
        /// 
        /// 
        /// 
        /// 
        /// A 
        public override ISqlFragment Visit(DbOfTypeExpression e) 
        {
            throw EntityUtil.NotSupported(); 
        } 

        ///  
        ///
        /// 
        /// 
        /// A  
        /// 
        public override ISqlFragment Visit(DbOrExpression e) 
        { 
            return VisitBinaryExpression(" OR ", e.ExpressionKind, e.Left, e.Right);
        } 

        /// 
        ///
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbParameterReferenceExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
            // Do not quote this name.
            // ISSUE: We are not checking that e.Name has no illegal characters. e.g. space
            result.Append("@" + e.ParameterName);
 
            return result;
        } 
 
        /// 
        ///  for the general ideas. 
        /// 
        /// 
        /// A 
        ///  
        public override ISqlFragment Visit(DbProjectExpression e)
        { 
            Symbol fromSymbol; 
            SqlSelectStatement result = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
 
            //#444002 Aliases need renaming only for Sql8 when there is Order By
            bool aliasesNeedRenaming = false;

            // Project is compatible with Filter 
            // but not with Project, GroupBy
            if (!IsCompatible(result, e.ExpressionKind)) 
            { 
                result = CreateNewSelectStatement(result, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
            } 
            else if ((this.SqlVersion == SqlVersion.Sql8) && !result.OrderBy.IsEmpty)
            {
                aliasesNeedRenaming = true;
            } 

            selectStatementStack.Push(result); 
            symbolTable.EnterScope(); 

            AddFromSymbol(result, e.Input.VariableName, fromSymbol); 

            // Project is the only node that can have DbNewInstanceExpression as a child
            // so we have to check it here.
            // We call VisitNewInstanceExpression instead of Visit(DbNewInstanceExpression), since 
            // the latter throws.
            DbNewInstanceExpression newInstanceExpression = e.Projection as DbNewInstanceExpression; 
            if (newInstanceExpression != null) 
            {
                Dictionary newColumns; 
                result.Select.Append(VisitNewInstanceExpression(newInstanceExpression, aliasesNeedRenaming, out newColumns));
                if (aliasesNeedRenaming)
                {
                    result.OutputColumnsRenamed = true; 
                    result.OutputColumns = newColumns;
                } 
            } 
            else
            { 
                result.Select.Append(e.Projection.Accept(this));
            }

            symbolTable.ExitScope(); 
            selectStatementStack.Pop();
 
            return result; 
        }
 
        /// 
        /// This method handles record flattening, which works as follows.
        /// consider an expression Prop(y, Prop(x, Prop(d, Prop(c, Prop(b, Var(a)))))
        /// where a,b,c are joins, d is an extent and x and y are fields. 
        /// b has been flattened into a, and has its own SELECT statement.
        /// c has been flattened into b. 
        /// d has been flattened into c. 
        ///
        /// We visit the instance, so we reach Var(a) first.  This gives us a (join)symbol. 
        /// Symbol(a).b gives us a join symbol, with a SELECT statement i.e. Symbol(b).
        /// From this point on , we need to remember Symbol(b) as the source alias,
        /// and then try to find the column.  So, we use a SymbolPair.
        /// 
        /// We have reached the end when the symbol no longer points to a join symbol.
        ///  
        ///  
        /// A  if we have not reached the first
        /// Join node that has a SELECT statement. 
        /// A  if we have seen the JoinNode, and it has
        /// a SELECT statement.
        /// A  with {Input}.propertyName otherwise.
        ///  
        public override ISqlFragment Visit(DbPropertyExpression e)
        { 
            SqlBuilder result; 

            ISqlFragment instanceSql = e.Instance.Accept(this); 

            // Since the DbVariableReferenceExpression is a proper child of ours, we can reset
            // isVarSingle.
            DbVariableReferenceExpression VariableReferenceExpression = e.Instance as DbVariableReferenceExpression; 
            if (VariableReferenceExpression != null)
            { 
                isVarRefSingle = false; 
            }
 
            // We need to flatten, and have not yet seen the first nested SELECT statement.
            JoinSymbol joinSymbol = instanceSql as JoinSymbol;
            if (joinSymbol != null)
            { 
                Debug.Assert(joinSymbol.NameToExtent.ContainsKey(e.Property.Name));
                if (joinSymbol.IsNestedJoin) 
                { 
                    return new SymbolPair(joinSymbol, joinSymbol.NameToExtent[e.Property.Name]);
                } 
                else
                {
                    return joinSymbol.NameToExtent[e.Property.Name];
                } 
            }
 
            // --------------------------------------- 
            // We have seen the first nested SELECT statement, but not the column.
            SymbolPair symbolPair = instanceSql as SymbolPair; 
            if (symbolPair != null)
            {
                JoinSymbol columnJoinSymbol = symbolPair.Column as JoinSymbol;
                if (columnJoinSymbol != null) 
                {
                    symbolPair.Column = columnJoinSymbol.NameToExtent[e.Property.Name]; 
                    return symbolPair; 
                }
                else 
                {
                    // symbolPair.Column has the base extent.
                    // we need the symbol for the column, since it might have been renamed
                    // when handling a JOIN. 
                    if (symbolPair.Column.Columns.ContainsKey(e.Property.Name))
                    { 
                        result = new SqlBuilder(); 
                        result.Append(symbolPair.Source);
                        result.Append("."); 
                        result.Append(symbolPair.Column.Columns[e.Property.Name]);
                        return result;
                    }
                } 
            }
            // --------------------------------------- 
 
            result = new SqlBuilder();
            result.Append(instanceSql); 
            result.Append(".");

            Symbol symbol = instanceSql as Symbol;
 
            if (symbol != null && symbol.OutputColumnsRenamed)
            { 
                result.Append(symbol.Columns[e.Property.Name]); 
            }
            else 
            {
                // At this point the column name cannot be renamed, so we do
                // not use a symbol.
                result.Append(QuoteIdentifier(e.Property.Name)); 
            }
            return result; 
        } 

        ///  
        /// Any(input, x) => Exists(Filter(input,x))
        /// All(input, x) => Not Exists(Filter(input, not(x))
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbQuantifierExpression e) 
        { 
            SqlBuilder result = new SqlBuilder();
 
            bool negatePredicate = (e.ExpressionKind == DbExpressionKind.All);
            if (e.ExpressionKind == DbExpressionKind.Any)
            {
                result.Append("EXISTS ("); 
            }
            else 
            { 
                Debug.Assert(e.ExpressionKind == DbExpressionKind.All);
                result.Append("NOT EXISTS ("); 
            }

            SqlSelectStatement filter = VisitFilterExpression(e.Input, e.Predicate, negatePredicate);
            if (filter.Select.IsEmpty) 
            {
                AddDefaultColumns(filter); 
            } 

            result.Append(filter); 
            result.Append(")");

            return result;
        } 

        ///  
        /// 
        /// 
        ///  
        /// 
        public override ISqlFragment Visit(DbRefExpression e)
        {
            throw EntityUtil.NotSupported(); 
        }
 
        ///  
        ///
        ///  
        /// 
        /// 
        public override ISqlFragment Visit(DbRelationshipNavigationExpression e)
        { 
            throw EntityUtil.NotSupported();
        } 
 
        /// 
        /// For Sql9 it translates to: 
        /// SELECT Y.x1, Y.x2, ..., Y.xn
        /// FROM (
        ///     SELECT X.x1, X.x2, ..., X.xn, row_number() OVER (ORDER BY sk1, sk2, ...) AS [row_number]
        ///     FROM input as X 
        ///     ) as Y
        /// WHERE Y.[row_number] > count 
        /// ORDER BY sk1, sk2, ... 
        /// 
        ///  
        /// A 
        public override ISqlFragment Visit(DbSkipExpression e)
        {
            Debug.Assert(this.SqlVersion != SqlVersion.Sql8, "DbSkipExpression when translating for SQL Server 2000."); 

            Debug.Assert(e.Count is DbConstantExpression || e.Count is DbParameterReferenceExpression, "DbSkipExpression.Count is of invalid expression type"); 
 
            //Visit the input
            Symbol fromSymbol; 
            SqlSelectStatement input = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);

            // Skip is not compatible with anything that OrderBy is not compatible with, as well as with distinct
            if (!IsCompatible(input, e.ExpressionKind)) 
            {
                input = CreateNewSelectStatement(input, e.Input.VariableName, e.Input.VariableType, out fromSymbol); 
            } 

            selectStatementStack.Push(input); 
            symbolTable.EnterScope();

            AddFromSymbol(input, e.Input.VariableName, fromSymbol);
 
            //Add the default columns
            Debug.Assert(input.Select.IsEmpty); 
            List inputColumns = AddDefaultColumns(input); 

            input.Select.Append(", row_number() OVER (ORDER BY "); 
            AddSortKeys(input.Select, e.SortOrder);
            input.Select.Append(") AS ");

            Symbol row_numberSymbol = new Symbol("row_number", IntegerType); 

            input.Select.Append(row_numberSymbol); 
 
            //The inner statement is complete, its scopes need not be valid any longer
            symbolTable.ExitScope(); 
            selectStatementStack.Pop();

            //Create the resulting statement
            //See CreateNewSelectStatement, it is very similar 
            //Future Enhancement ([....]): Refactor to avoid duplication with CreateNewSelectStatement if we
            // don't switch to using ExtensionExpression here 
            SqlSelectStatement result = new SqlSelectStatement(); 
            result.From.Append("( ");
            result.From.Append(input); 
            result.From.AppendLine();
            result.From.Append(") ");

            //Create a symbol for the input 
            Symbol resultFromSymbol = null;
 
            if (input.FromExtents.Count == 1) 
            {
                JoinSymbol oldJoinSymbol = input.FromExtents[0] as JoinSymbol; 
                if (oldJoinSymbol != null)
                {
                    // Note: input.FromExtents will not do, since it might
                    // just be an alias of joinSymbol, and we want an actual JoinSymbol. 
                    JoinSymbol newJoinSymbol = new JoinSymbol(e.Input.VariableName, e.Input.VariableType, oldJoinSymbol.ExtentList);
                    // This indicates that the oldStatement is a blocking scope 
                    // i.e. it hides/renames extent columns 
                    newJoinSymbol.IsNestedJoin = true;
                    newJoinSymbol.ColumnList = inputColumns; 
                    newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;

                    resultFromSymbol = newJoinSymbol;
                } 
            }
 
            if (resultFromSymbol == null) 
            {
                // This is just a simple extent/SqlSelectStatement, 
                // and we can get the column list from the type.
                resultFromSymbol = new Symbol(e.Input.VariableName, e.Input.VariableType);
            }
            //Add the ORDER BY part 
            selectStatementStack.Push(result);
            symbolTable.EnterScope(); 
 
            AddFromSymbol(result, e.Input.VariableName, resultFromSymbol);
 
            //Add the predicate
            result.Where.Append(resultFromSymbol);
            result.Where.Append(".");
            result.Where.Append(row_numberSymbol); 
            result.Where.Append(" > ");
            result.Where.Append(HandleCountExpression(e.Count)); 
 
            AddSortKeys(result.OrderBy, e.SortOrder);
 
            symbolTable.ExitScope();
            selectStatementStack.Pop();

            return result; 
        }
 
        ///  
        /// 
        ///  
        /// 
        /// A 
        /// 
        public override ISqlFragment Visit(DbSortExpression e) 
        {
            Symbol fromSymbol; 
            SqlSelectStatement result = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol); 

            // OrderBy is compatible with Filter 
            // and nothing else
            if (!IsCompatible(result, e.ExpressionKind))
            {
                result = CreateNewSelectStatement(result, e.Input.VariableName, e.Input.VariableType, out fromSymbol); 
            }
 
            selectStatementStack.Push(result); 
            symbolTable.EnterScope();
 
            AddFromSymbol(result, e.Input.VariableName, fromSymbol);

            AddSortKeys(result.OrderBy, e.SortOrder);
 
            symbolTable.ExitScope();
            selectStatementStack.Pop(); 
 
            return result;
        } 

        /// 
        ///
        ///  
        /// 
        /// A  
        public override ISqlFragment Visit(DbTreatExpression e) 
        {
            throw EntityUtil.NotSupported(); 
        }

        /// 
        /// This code is shared by  
        /// and 
        /// 
        ///  
        /// Since the left and right expression may not be Sql select statements,
        /// we must wrap them up to look like SQL select statements. 
        /// 
        /// 
        /// 
        public override ISqlFragment Visit(DbUnionAllExpression e) 
        {
            return VisitSetOpExpression(e.Left, e.Right, "UNION ALL"); 
        } 

        ///  
        /// This method determines whether an extent from an outer scope(free variable)
        /// is used in the CurrentSelectStatement.
        ///
        /// An extent in an outer scope, if its symbol is not in the FromExtents 
        /// of the CurrentSelectStatement.
        ///  
        ///  
        /// A .
        public override ISqlFragment Visit(DbVariableReferenceExpression e) 
        {
            if (isVarRefSingle)
            {
                throw EntityUtil.NotSupported(); 
                // A DbVariableReferenceExpression has to be a child of DbPropertyExpression or MethodExpression
                // This is also checked in GenerateSql(...) at the end of the visiting. 
            } 
            isVarRefSingle = true; // This will be reset by DbPropertyExpression or MethodExpression
 
            Symbol result = symbolTable.Lookup(e.VariableName);
            if (!CurrentSelectStatement.FromExtents.Contains(result))
            {
                CurrentSelectStatement.OuterExtents[result] = true; 
            }
 
            return result; 
        }
        #endregion 

        #region Visitor Helper Methods

        #region 'Visitor' methods - Shared visitors and methods that do most of the visiting 

        ///  
        /// Aggregates are not visited by the normal visitor walk. 
        /// 
        /// The aggreate go be translated 
        /// The translated aggregate argument
        /// 
        private static SqlBuilder VisitAggregate(DbAggregate aggregate, object aggregateArgument)
        { 
            SqlBuilder aggregateResult = new SqlBuilder();
            DbFunctionAggregate functionAggregate = aggregate as DbFunctionAggregate; 
 
            if (functionAggregate == null)
            { 
                throw EntityUtil.NotSupported();
            }

            //The only aggregate function with different name is Big_Count 
            //Note: If another such function is to be added, a dictionary should be created
            if (TypeHelpers.IsCanonicalFunction(functionAggregate.Function) 
                && String.Equals(functionAggregate.Function.Name, "BigCount", StringComparison.Ordinal)) 
            {
                aggregateResult.Append("COUNT_BIG"); 
            }
            else
            {
                WriteFunctionName(aggregateResult, functionAggregate.Function); 
            }
 
            aggregateResult.Append("("); 

            DbFunctionAggregate fnAggr = functionAggregate; 
            if ((null != fnAggr) && (fnAggr.Distinct))
            {
                aggregateResult.Append("DISTINCT ");
            } 

            aggregateResult.Append(aggregateArgument); 
 
            aggregateResult.Append(")");
            return aggregateResult; 
        }

        /// 
        /// Dump out an expression - optionally wrap it with parantheses if possible 
        /// 
        ///  
        ///  
        private void ParanthesizeExpressionIfNeeded(DbExpression e, SqlBuilder result)
        { 
            if (IsComplexExpression(e))
            {
                result.Append("(");
                result.Append(e.Accept(this)); 
                result.Append(")");
            } 
            else 
            {
                result.Append(e.Accept(this)); 
            }
        }

        ///  
        /// Handler for inline binary expressions.
        /// Produces left op right. 
        /// For associative operations does flattening. 
        /// Puts parenthesis around the arguments if needed.
        ///  
        /// 
        /// 
        /// 
        ///  
        /// 
        private SqlBuilder VisitBinaryExpression(string op, DbExpressionKind expressionKind, DbExpression left, DbExpression right) 
        { 
            SqlBuilder result = new SqlBuilder();
 
            bool isFirst = true;
            foreach (DbExpression argument in CommandTreeUtils.FlattenAssociativeExpression(expressionKind, left, right))
            {
                if (isFirst) 
                {
                    isFirst = false; 
                } 
                else
                { 
                    result.Append(op);
                }
                ParanthesizeExpressionIfNeeded(argument, result);
            } 
            return result;
        } 
 
        /// 
        /// Private handler for comparison expressions - almost identical to VisitBinaryExpression. 
        /// We special case constants, so that we don't emit unnecessary casts
        /// 
        /// the comparison op
        /// the left-side expression 
        /// the right-side expression
        ///  
        private SqlBuilder VisitComparisonExpression(string op, DbExpression left, DbExpression right) 
        {
            SqlBuilder result = new SqlBuilder(); 

            bool isCastOptional = left.ResultType.EdmType == right.ResultType.EdmType;

            if (left.ExpressionKind == DbExpressionKind.Constant) 
            {
                result.Append(VisitConstant((DbConstantExpression)left, isCastOptional)); 
            } 
            else
            { 
                ParanthesizeExpressionIfNeeded(left, result);
            }

            result.Append(op); 

            if (right.ExpressionKind == DbExpressionKind.Constant) 
            { 
                result.Append(VisitConstant((DbConstantExpression)right, isCastOptional));
            } 
            else
            {
                ParanthesizeExpressionIfNeeded(right, result);
            } 

            return result; 
        } 

        ///  
        /// This is called by the relational nodes.  It does the following
        /// 
        /// If the input is not a SqlSelectStatement, it assumes that the input
        /// is a collection expression, and creates a new SqlSelectStatement  
        /// 
        ///  
        ///  
        /// 
        ///  
        /// 
        /// A  and the main fromSymbol
        /// for this select statement.
        private SqlSelectStatement VisitInputExpression(DbExpression inputExpression, 
            string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol)
        { 
            SqlSelectStatement result; 
            ISqlFragment sqlFragment = inputExpression.Accept(this);
            result = sqlFragment as SqlSelectStatement; 

            if (result == null)
            {
                result = new SqlSelectStatement(); 
                WrapNonQueryExtent(result, sqlFragment, inputExpression.ExpressionKind);
            } 
 
            if (result.FromExtents.Count == 0)
            { 
                // input was an extent
                fromSymbol = new Symbol(inputVarName, inputVarType);
            }
            else if (result.FromExtents.Count == 1) 
            {
                // input was Filter/GroupBy/Project/OrderBy 
                // we are likely to reuse this statement. 
                fromSymbol = result.FromExtents[0];
            } 
            else
            {
                // input was a join.
                // we are reusing the select statement produced by a Join node 
                // we need to remove the original extents, and replace them with a
                // new extent with just the Join symbol. 
                JoinSymbol joinSymbol = new JoinSymbol(inputVarName, inputVarType, result.FromExtents); 
                joinSymbol.FlattenedExtentList = result.AllJoinExtents;
 
                fromSymbol = joinSymbol;
                result.FromExtents.Clear();
                result.FromExtents.Add(fromSymbol);
            } 

            return result; 
        } 

        ///  
        /// 
        /// 
        /// 
        /// Was the parent a DbNotExpression? 
        /// 
        SqlBuilder VisitIsEmptyExpression(DbIsEmptyExpression e, bool negate) 
        { 
            SqlBuilder result = new SqlBuilder();
            if (!negate) 
            {
                result.Append(" NOT");
            }
            result.Append(" EXISTS ("); 
            result.Append(VisitExpressionEnsureSqlStatement(e.Argument));
            result.AppendLine(); 
            result.Append(")"); 

            return result; 
        }


        ///  
        /// Translate a NewInstance(Element(X)) expression into
        ///   "select top(1) * from X" 
        ///  
        /// 
        ///  
        private ISqlFragment VisitCollectionConstructor(DbNewInstanceExpression e)
        {
            Debug.Assert(e.Arguments.Count <= 1);
 
            if (e.Arguments.Count == 1 && e.Arguments[0].ExpressionKind == DbExpressionKind.Element)
            { 
                DbElementExpression elementExpr = e.Arguments[0] as DbElementExpression; 
                SqlSelectStatement result = VisitExpressionEnsureSqlStatement(elementExpr.Argument);
 
                if (!IsCompatible(result, DbExpressionKind.Element))
                {
                    Symbol fromSymbol;
                    TypeUsage inputType = TypeHelpers.GetElementTypeUsage(elementExpr.Argument.ResultType); 

                    result = CreateNewSelectStatement(result, "element", inputType, out fromSymbol); 
                    AddFromSymbol(result, "element", fromSymbol, false); 
                }
                result.Top = new TopClause(1, false); 
                return result;
            }

 
            // Otherwise simply build this out as a union-all ladder
            CollectionType collectionType = TypeHelpers.GetEdmType(e.ResultType); 
            Debug.Assert(collectionType != null); 
            bool isScalarElement = TypeSemantics.IsPrimitiveType(collectionType.TypeUsage);
 
            SqlBuilder resultSql = new SqlBuilder();
            string separator = "";

            // handle empty table 
            if (e.Arguments.Count == 0)
            { 
                Debug.Assert(isScalarElement); 
                resultSql.Append(" SELECT CAST(null as ");
                resultSql.Append(GetSqlPrimitiveType(collectionType.TypeUsage)); 
                resultSql.Append(") AS X FROM (SELECT 1) AS Y WHERE 1=0");
            }

            foreach (DbExpression arg in e.Arguments) 
            {
                resultSql.Append(separator); 
                resultSql.Append(" SELECT "); 
                resultSql.Append(arg.Accept(this));
                // For scalar elements, no alias is appended yet. Add this. 
                if (isScalarElement)
                {
                    resultSql.Append(" AS X ");
                } 
                separator = " UNION ALL ";
            } 
 
            return resultSql;
        } 

        /// 
        /// 
        ///  
        /// 
        /// Was the parent a DbNotExpression? 
        ///  
        private SqlBuilder VisitIsNullExpression(DbIsNullExpression e, bool negate)
        { 
            SqlBuilder result = new SqlBuilder();
            result.Append(e.Argument.Accept(this));
            if (!negate)
            { 
                result.Append(" IS NULL");
            } 
            else 
            {
                result.Append(" IS NOT NULL"); 
            }

            return result;
        } 

        ///  
        /// This handles the processing of join expressions. 
        /// The extents on a left spine are flattened, while joins
        /// not on the left spine give rise to new nested sub queries. 
        ///
        /// Joins work differently from the rest of the visiting, in that
        /// the parent (i.e. the join node) creates the SqlSelectStatement
        /// for the children to use. 
        ///
        /// The "parameter" IsInJoinContext indicates whether a child extent should 
        /// add its stuff to the existing SqlSelectStatement, or create a new SqlSelectStatement 
        /// By passing true, we ask the children to add themselves to the parent join,
        /// by passing false, we ask the children to create new Select statements for 
        /// themselves.
        ///
        /// This method is called from  and
        /// . 
        /// 
        ///  
        ///  
        /// 
        ///  
        ///  A 
        private ISqlFragment VisitJoinExpression(IList inputs, DbExpressionKind joinKind,
            string joinString, DbExpression joinCondition)
        { 
            SqlSelectStatement result;
            // If the parent is not a join( or says that it is not), 
            // we should create a new SqlSelectStatement. 
            // otherwise, we add our child extents to the parent's FROM clause.
            if (!IsParentAJoin) 
            {
                result = new SqlSelectStatement();
                result.AllJoinExtents = new List();
                selectStatementStack.Push(result); 
            }
            else 
            { 
                result = CurrentSelectStatement;
            } 

            // Process each of the inputs, and then the joinCondition if it exists.
            // It would be nice if we could call VisitInputExpression - that would
            // avoid some code duplication 
            // but the Join postprocessing is messy and prevents this reuse.
            symbolTable.EnterScope(); 
 
            string separator = "";
            bool isLeftMostInput = true; 
            int inputCount = inputs.Count;
            for(int idx = 0; idx < inputCount; idx++)
            {
                DbExpressionBinding input = inputs[idx]; 

                if (separator.Length != 0) 
                { 
                    result.From.AppendLine();
                } 
                result.From.Append(separator + " ");
                // Change this if other conditions are required
                // to force the child to produce a nested SqlStatement.
                bool needsJoinContext = (input.Expression.ExpressionKind == DbExpressionKind.Scan) 
                                        || (isLeftMostInput &&
                                            (IsJoinExpression(input.Expression) 
                                             || IsApplyExpression(input.Expression))) 
                                        ;
 
                isParentAJoinStack.Push(needsJoinContext ? true : false);
                // if the child reuses our select statement, it will append the from
                // symbols to our FromExtents list.  So, we need to remember the
                // start of the child's entries. 
                int fromSymbolStart = result.FromExtents.Count;
 
                ISqlFragment fromExtentFragment = input.Expression.Accept(this); 

                isParentAJoinStack.Pop(); 

                ProcessJoinInputResult(fromExtentFragment, result, input, fromSymbolStart);
                separator = joinString;
 
                isLeftMostInput = false;
            } 
 
            // Visit the on clause/join condition.
            switch (joinKind) 
            {
                case DbExpressionKind.FullOuterJoin:
                case DbExpressionKind.InnerJoin:
                case DbExpressionKind.LeftOuterJoin: 
                    result.From.Append(" ON ");
                    isParentAJoinStack.Push(false); 
                    result.From.Append(joinCondition.Accept(this)); 
                    isParentAJoinStack.Pop();
                    break; 
            }

            symbolTable.ExitScope();
 
            if (!IsParentAJoin)
            { 
                selectStatementStack.Pop(); 
            }
 
            return result;
        }

        ///  
        /// This is called from .
        /// 
        /// This is responsible for maintaining the symbol table after visiting 
        /// a child of a join expression.
        /// 
        /// The child's sql statement may need to be completed.
        ///
        /// The child's result could be one of
        ///  
        /// The same as the parent's - this is treated specially.
        /// A sql select statement, which may need to be completed 
        /// An extent - just copy it to the from clause 
        /// Anything else (from a collection-valued expression) -
        /// unnest and copy it. 
        /// 
        ///
        /// If the input was a Join, we need to create a new join symbol,
        /// otherwise, we create a normal symbol. 
        ///
        /// We then call AddFromSymbol to add the AS clause, and update the symbol table. 
        /// 
        ///
        /// 
        /// If the child's result was the same as the parent's, we have to clean up
        /// the list of symbols in the FromExtents list, since this contains symbols from
        /// the children of both the parent and the child.
        /// The happens when the child visited is a Join, and is the leftmost child of 
        /// the parent.
        ///  
        ///  
        /// 
        ///  
        /// 
        private void ProcessJoinInputResult(ISqlFragment fromExtentFragment, SqlSelectStatement result,
            DbExpressionBinding input, int fromSymbolStart)
        { 
            Symbol fromSymbol = null;
 
            if (result != fromExtentFragment) 
            {
                // The child has its own select statement, and is not reusing 
                // our select statement.
                // This should look a lot like VisitInputExpression().
                SqlSelectStatement sqlSelectStatement = fromExtentFragment as SqlSelectStatement;
                if (sqlSelectStatement != null) 
                {
                    if (sqlSelectStatement.Select.IsEmpty) 
                    { 
                        List columns = AddDefaultColumns(sqlSelectStatement);
 
                        if (IsJoinExpression(input.Expression)
                            || IsApplyExpression(input.Expression))
                        {
                            List extents = sqlSelectStatement.FromExtents; 
                            JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
                            newJoinSymbol.IsNestedJoin = true; 
                            newJoinSymbol.ColumnList = columns; 

                            fromSymbol = newJoinSymbol; 
                        }
                        else
                        {
                            // this is a copy of the code in CreateNewSelectStatement. 

                            // if the oldStatement has a join as its input, ... 
                            // clone the join symbol, so that we "reuse" the 
                            // join symbol.  Normally, we create a new symbol - see the next block
                            // of code. 
                            JoinSymbol oldJoinSymbol = sqlSelectStatement.FromExtents[0] as JoinSymbol;
                            if (oldJoinSymbol != null)
                            {
                                // Note: sqlSelectStatement.FromExtents will not do, since it might 
                                // just be an alias of joinSymbol, and we want an actual JoinSymbol.
                                JoinSymbol newJoinSymbol = new JoinSymbol(input.VariableName, input.VariableType, oldJoinSymbol.ExtentList); 
                                // This indicates that the sqlSelectStatement is a blocking scope 
                                // i.e. it hides/renames extent columns
                                newJoinSymbol.IsNestedJoin = true; 
                                newJoinSymbol.ColumnList = columns;
                                newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;

                                fromSymbol = newJoinSymbol; 
                            }
                            else if (sqlSelectStatement.FromExtents[0].OutputColumnsRenamed) 
                            { 
                                fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.FromExtents[0].Columns);
                            } 
                        }

                    }
                    else if (sqlSelectStatement.OutputColumnsRenamed) 
                    {
                        fromSymbol = new Symbol(input.VariableName, input.VariableType, sqlSelectStatement.OutputColumns); 
                    } 
                    result.From.Append(" (");
                    result.From.Append(sqlSelectStatement); 
                    result.From.Append(" )");
                }
                else if (input.Expression is DbScanExpression)
                { 
                    result.From.Append(fromExtentFragment);
                } 
                else // bracket it 
                {
                    WrapNonQueryExtent(result, fromExtentFragment, input.Expression.ExpressionKind); 
                }

                if (fromSymbol == null) // i.e. not a join symbol
                { 
                    fromSymbol = new Symbol(input.VariableName, input.VariableType);
                } 
 

                AddFromSymbol(result, input.VariableName, fromSymbol); 
                result.AllJoinExtents.Add(fromSymbol);
            }
            else // result == fromExtentFragment.  The child extents have been merged into the parent's.
            { 
                // we are adding extents to the current sql statement via flattening.
                // We are replacing the child's extents with a single Join symbol. 
                // The child's extents are all those following the index fromSymbolStart. 
                //
                List extents = new List(); 

                // We cannot call extents.AddRange, since the is no simple way to
                // get the range of symbols fromSymbolStart..result.FromExtents.Count
                // from result.FromExtents. 
                // We copy these symbols to create the JoinSymbol later.
                for (int i = fromSymbolStart; i < result.FromExtents.Count; ++i) 
                { 
                    extents.Add(result.FromExtents[i]);
                } 
                result.FromExtents.RemoveRange(fromSymbolStart, result.FromExtents.Count - fromSymbolStart);
                fromSymbol = new JoinSymbol(input.VariableName, input.VariableType, extents);
                result.FromExtents.Add(fromSymbol);
                // this Join Symbol does not have its own select statement, so we 
                // do not set IsNestedJoin
 
 
                // We do not call AddFromSymbol(), since we do not want to add
                // "AS alias" to the FROM clause- it has been done when the extent was added earlier. 
                symbolTable.Add(input.VariableName, fromSymbol);
            }
        }
 
        /// 
        /// We assume that this is only called as a child of a Project. 
        /// 
        /// This replaces , since
        /// we do not allow DbNewInstanceExpression as a child of any node other than 
        /// DbProjectExpression.
        ///
        /// We write out the translation of each of the columns in the record.
        ///  
        /// 
        ///  
        ///  
        /// A 
        private ISqlFragment VisitNewInstanceExpression(DbNewInstanceExpression e, bool aliasesNeedRenaming, out Dictionary newColumns) 
        {
            SqlBuilder result = new SqlBuilder();
            RowType rowType = e.ResultType.EdmType as RowType;
 
            if (null != rowType)
            { 
                if (aliasesNeedRenaming) 
                {
                    newColumns = new Dictionary(e.Arguments.Count); 
                }
                else
                {
                    newColumns = null; 
                }
 
                ReadOnlyMetadataCollection members = rowType.Properties; 
                string separator = "";
                for(int i = 0; i < e.Arguments.Count; ++i) 
                {
                    DbExpression argument = e.Arguments[i];
                    if (TypeSemantics.IsRowType(argument.ResultType))
                    { 
                        // We do not support nested records or other complex objects.
                        throw EntityUtil.NotSupported(); 
                    } 

                    EdmProperty member = members[i]; 
                    result.Append(separator);
                    result.AppendLine();
                    result.Append(argument.Accept(this));
                    result.Append(" AS "); 
                    if (aliasesNeedRenaming)
                    { 
                        Symbol column = new Symbol(member.Name, member.TypeUsage); 
                        column.NeedsRenaming = true;
                        column.NewName = String.Concat("Internal_", member.Name); 
                        result.Append(column);
                        newColumns.Add(member.Name, column);
                    }
                    else 
                    {
                        result.Append(QuoteIdentifier(member.Name)); 
                    } 
                    separator = ", ";
                } 
            }
            else
            {
                // 
                //
 
 
                throw EntityUtil.NotSupported();
            } 

            return result;
        }
 
        /// 
        /// Handler for set operations 
        /// It generates left separator right. 
        /// Only for SQL 8.0 it may need to create a new select statement
        /// above the set operation if the left child's output columns got renamed 
        /// 
        /// 
        /// 
        ///  
        /// 
        private ISqlFragment VisitSetOpExpression(DbExpression left, DbExpression right, string separator) 
        { 

            SqlSelectStatement leftSelectStatement = VisitExpressionEnsureSqlStatement(left); 
            SqlSelectStatement rightSelectStatement = VisitExpressionEnsureSqlStatement(right);

            SqlBuilder setStatement = new SqlBuilder();
            setStatement.Append(leftSelectStatement); 
            setStatement.AppendLine();
            setStatement.Append(separator); // e.g. UNION ALL 
            setStatement.AppendLine(); 
            setStatement.Append(rightSelectStatement);
 

            //This is the common scenario
            if (!leftSelectStatement.OutputColumnsRenamed)
            { 
                return setStatement;
            } 
 
            else
            { 
                // This is case only for SQL 8.0 when the left child has order by in it
                // If the output columns of the left child got renamed,
                // then the output of the union all is renamed
                // All this currenlty only happens for UNION ALL, because INTERSECT and 
                // EXCEPT get translated for SQL 8.0 before SqlGen.
                SqlSelectStatement selectStatement = new SqlSelectStatement(); 
                selectStatement.From.Append("( "); 
                selectStatement.From.Append(setStatement);
                selectStatement.From.AppendLine(); 
                selectStatement.From.Append(") ");

                Symbol fromSymbol = new Symbol("X", left.ResultType, leftSelectStatement.OutputColumns);
                AddFromSymbol(selectStatement, null, fromSymbol, false); 

                return selectStatement; 
            } 
        }
 
        #endregion

        #region Function Handling Helpers
        ///  
        /// Determines whether the given function is a store function that
        /// requires special handling 
        ///  
        /// 
        ///  
        private static bool IsSpecialStoreFunction(DbFunctionExpression e)
        {
            return IsStoreFunction(e.Function)
                && _storeFunctionHandlers.ContainsKey(e.Function.Name); 
        }
 
        ///  
        /// Determines whether the given function is a canonical function that
        /// requires special handling 
        /// 
        /// 
        /// 
        private static bool IsSpecialCanonicalFunction(DbFunctionExpression e) 
        {
            return TypeHelpers.IsCanonicalFunction(e.Function) 
            && _canonicalFunctionHandlers.ContainsKey(e.Function.Name); 
        }
 
        /// 
        /// Default handling for functions.
        /// Translates them to FunctionName(arg1, arg2, ..., argn)
        ///  
        /// 
        ///  
        private ISqlFragment HandleFunctionDefault(DbFunctionExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
            bool requiresCast = CastReturnTypeToInt32(e);
            if (requiresCast)
            {
                result.Append(" CAST("); 
            }
            WriteFunctionName(result, e.Function); 
            HandleFunctionArgumentsDefault(e, result); 
            if (requiresCast)
            { 
                result.Append(" AS int)");
            }
            return result;
        } 

        ///  
        /// Default handling for functions with a given name. 
        /// Translates them to FunctionName(arg1, arg2, ..., argn)
        ///  
        /// 
        /// 
        /// 
        private ISqlFragment HandleFunctionDefaultGivenName(DbFunctionExpression e, string functionName) 
        {
            SqlBuilder result = new SqlBuilder(); 
            bool needsCast = CastReturnTypeToInt32(e); 
            if (needsCast)
            { 
                result.Append("CAST(");
            }
            result.Append(functionName);
            HandleFunctionArgumentsDefault(e, result); 
            if (needsCast)
            { 
                result.Append(" AS int)"); 
            }
            return result; 
        }

        /// 
        /// Default handling on function arguments. 
        /// Appends the list of arguemnts to the given result
        /// If the function is niladic it does not append anything, 
        /// otherwise it appends (arg1, arg2, .., argn) 
        /// 
        ///  
        /// 
        private void HandleFunctionArgumentsDefault(DbFunctionExpression e, SqlBuilder result)
        {
            bool isNiladicFunction = e.Function.NiladicFunctionAttribute; 
            Debug.Assert(!(isNiladicFunction && (0 < e.Arguments.Count)), "function attributed as NiladicFunction='true' in the provider manifest cannot have arguments");
            if (isNiladicFunction && e.Arguments.Count > 0) 
            { 
                EntityUtil.Metadata(System.Data.Entity.Strings.NiladicFunctionsCannotHaveParameters);
            } 

            if (!isNiladicFunction)
            {
                result.Append("("); 
                string separator = "";
                foreach (DbExpression arg in e.Arguments) 
                { 
                    result.Append(separator);
                    result.Append(arg.Accept(this)); 
                    separator = ", ";
                }
                result.Append(")");
            } 
        }
 
        ///  
        /// Handler for functions that need to be translated to different store function based on version
        ///  
        /// 
        /// 
        /// 
        ///  
        private ISqlFragment HandleFunctionGivenNameBasedOnVersion(DbFunctionExpression e, string preKatmaiName, string katmaiName)
        { 
            if (this.IsPreKatmai) 
            {
                return HandleFunctionDefaultGivenName(e, preKatmaiName); 
            }
            return HandleFunctionDefaultGivenName(e, katmaiName);
        }
 
        /// 
        /// Handler for special build in functions 
        ///  
        /// 
        ///  
        private ISqlFragment HandleSpecialStoreFunction(DbFunctionExpression e)
        {
            return HandleSpecialFunction(_storeFunctionHandlers, e);
        } 

        ///  
        /// Handler for special canonical functions 
        /// 
        ///  
        /// 
        private ISqlFragment HandleSpecialCanonicalFunction(DbFunctionExpression e)
        {
            return HandleSpecialFunction(_canonicalFunctionHandlers, e); 
        }
 
        ///  
        /// Dispatches the special function processing to the appropriate handler
        ///  
        /// 
        /// 
        /// 
        private ISqlFragment HandleSpecialFunction(Dictionary handlers, DbFunctionExpression e) 
        {
            Debug.Assert(handlers.ContainsKey(e.Function.Name), "Special handling should be called only for functions in the list of special functions"); 
            return handlers[e.Function.Name](this, e); 
        }
 
        /// 
        /// Handles functions that are translated into TSQL operators.
        /// The given function should have one or two arguments.
        /// Functions with one arguemnt are translated into 
        ///     op arg
        /// Functions with two arguments are translated into 
        ///     arg0 op arg1 
        /// Also, the arguments can be optionaly enclosed in parethesis
        ///  
        /// 
        /// Whether the arguments should be enclosed in parethesis
        /// 
        private ISqlFragment HandleSpecialFunctionToOperator(DbFunctionExpression e, bool parenthesiseArguments) 
        {
            SqlBuilder result = new SqlBuilder(); 
            Debug.Assert(e.Arguments.Count > 0 && e.Arguments.Count <= 2, "There should be 1 or 2 arguments for operator"); 

            if (e.Arguments.Count > 1) 
            {
                if (parenthesiseArguments)
                {
                    result.Append("("); 
                }
                result.Append(e.Arguments[0].Accept(this)); 
                if (parenthesiseArguments) 
                {
                    result.Append(")"); 
                }
            }
            result.Append(" ");
            Debug.Assert(_functionNameToOperatorDictionary.ContainsKey(e.Function.Name), "The function can not be mapped to an operator"); 
            result.Append(_functionNameToOperatorDictionary[e.Function.Name]);
            result.Append(" "); 
 
            if (parenthesiseArguments)
            { 
                result.Append("(");
            }
            result.Append(e.Arguments[e.Arguments.Count - 1].Accept(this));
            if (parenthesiseArguments) 
            {
                result.Append(")"); 
            } 
            return result;
        } 

        /// 
        /// 
        ///  
        /// 
        ///  
        ///  
        private static ISqlFragment HandleConcatFunction(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            return sqlgen.HandleSpecialFunctionToOperator(e, false);
        }

        ///  
        /// 
        ///  
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionBitwise(SqlGenerator sqlgen, DbFunctionExpression e)
        {
            return sqlgen.HandleSpecialFunctionToOperator(e, true);
        } 

        ///  
        /// Handles special case in which datapart 'type' parameter is present. all the functions 
        /// handles here have *only* the 1st parameter as datepart. datepart value is passed along
        /// the QP as string and has to be expanded as TSQL keyword. 
        /// 
        /// 
        /// 
        ///  
        private static ISqlFragment HandleDatepartDateFunction(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            Debug.Assert(e.Arguments.Count > 0, "e.Arguments.Count > 0"); 

            DbConstantExpression constExpr = e.Arguments[0] as DbConstantExpression; 
            if (null == constExpr)
            {
                throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name));
            } 

            string datepart = constExpr.Value as string; 
            if (null == datepart) 
            {
                throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name)); 
            }

            SqlBuilder result = new SqlBuilder();
 
            //
            // check if datepart value is valid 
            // 
            if (!_datepartKeywords.Contains(datepart))
            { 
                throw EntityUtil.InvalidOperation(System.Data.Entity.Strings.InvalidDatePartArgumentValue(datepart, e.Function.NamespaceName, e.Function.Name));
            }

            // 
            // finaly, expand the function name
            // 
            WriteFunctionName(result, e.Function); 
            result.Append("(");
 
            // expand the datepart literal as tsql kword
            result.Append(datepart);
            string separator = ", ";
 
            // expand remaining arguments
            for (int i = 1; i < e.Arguments.Count; i++) 
            { 
                result.Append(separator);
                result.Append(e.Arguments[i].Accept(sqlgen)); 
            }

            result.Append(")");
 
            return result;
        } 
 
        /// 
        /// Handler for canonical funcitons for extracting date parts. 
        /// For example:
        ///     Year(date) -> DATEPART( year, date)
        /// 
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleCanonicalFunctionDatepart(e.Function.Name.ToLowerInvariant(), e); 
        }

        /// 
        /// Handler for canonical funcitons for GetTotalOffsetMinutes. 
        /// GetTotalOffsetMinutes(e) --> Datepart(tzoffset, e)
        ///  
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionGetTotalOffsetMinutes(SqlGenerator sqlgen, DbFunctionExpression e)
        {
            return sqlgen.HandleCanonicalFunctionDatepart("tzoffset", e);
        } 

        ///  
        /// Handler for turning a canonical function into DATEPART 
        /// Results in DATEPART(datepart, e)
        ///  
        /// 
        /// 
        /// 
        private ISqlFragment HandleCanonicalFunctionDatepart(string datepart, DbFunctionExpression e) 
        {
            SqlBuilder result = new SqlBuilder(); 
            result.Append("DATEPART ("); 
            result.Append(datepart);
            result.Append(", "); 

            Debug.Assert(e.Arguments.Count == 1, "Canonical datepart functions should have exactly one argument");
            result.Append(e.Arguments[0].Accept(this));
 
            result.Append(")");
 
            return result; 
        }
 
        /// 
        /// Handler for the canonical function CurrentDate
        /// For Sql8 and Sql9:  CurrentDate() -> GetDate()
        /// For Sql10:          CurrentDate() -> SysDateTime() 
        /// 
        ///  
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionCurrentDateTime(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionGivenNameBasedOnVersion(e, "GetDate", "SysDateTime");
        }
 
        /// 
        /// Handler for the canonical function CurrentUtcDateTime 
        /// For Sql8 and Sql9:  CurrentUtcDateTime() -> GetUtcDate() 
        /// For Sql10:          CurrentUtcDateTime() -> SysUtcDateTime()
        ///  
        /// 
        /// 
        /// 
        private static ISqlFragment HandleCanonicalFunctionCurrentUtcDateTime(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionGivenNameBasedOnVersion(e, "GetUtcDate", "SysUtcDateTime"); 
        } 

        ///  
        /// Handler for the canonical function CurrentDateTimeOffset
        /// For Sql8 and Sql9:  throw
        /// For Sql10: CurrentDateTimeOffset() -> SysDateTimeOffset()
        ///  
        /// 
        ///  
        ///  
        private static ISqlFragment HandleCanonicalFunctionCurrentDateTimeOffset(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            sqlgen.AssertKatmaiOrNewer(e);
            return sqlgen.HandleFunctionDefaultGivenName(e, "SysDateTimeOffset");
        }
 
        /// 
        ///  Function rename IndexOf -> CHARINDEX 
        ///  
        /// 
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionIndexOf(SqlGenerator sqlgen, DbFunctionExpression e)
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "CHARINDEX"); 
        }
 
        ///  
        ///  Function rename NewGuid -> NEWID
        ///  
        /// 
        /// 
        /// 
        private static ISqlFragment HandleCanonicalFunctionNewGuid(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "NEWID"); 
        } 

        ///  
        ///  Function rename Length -> LEN
        /// 
        /// 
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionLength(SqlGenerator sqlgen, DbFunctionExpression e) 
        { 
            // We are aware of SQL Server's trimming of trailing spaces. We disclaim that behavior in general.
            // It's up to the user to decide whether to trim them explicitly or to append a non-blank space char explicitly. 
            // Once SQL Server implements a function that computes Length correctly, we'll use it here instead of LEN,
            // and we'll drop the disclaimer.
            return sqlgen.HandleFunctionDefaultGivenName(e, "LEN");
        } 

        ///  
        /// Round(numericExpression) -> Round(numericExpression, 0); 
        /// 
        ///  
        /// 
        /// 
        private static ISqlFragment HandleCanonicalFunctionRound(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            SqlBuilder result = new SqlBuilder();
 
            result.Append("ROUND("); 

            Debug.Assert(e.Arguments.Count == 1, "Round should have one argument"); 
            result.Append(e.Arguments[0].Accept(sqlgen));

            result.Append(", 0)");
 
            return result;
        } 
 
        /// 
        /// TRIM(string) -> LTRIM(RTRIM(string)) 
        /// 
        /// 
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionTrim(SqlGenerator sqlgen, DbFunctionExpression e)
        { 
            SqlBuilder result = new SqlBuilder(); 

            result.Append("LTRIM(RTRIM("); 

            Debug.Assert(e.Arguments.Count == 1, "Trim should have one argument");
            result.Append(e.Arguments[0].Accept(sqlgen));
 
            result.Append("))");
 
            return result; 
        }
 
        /// 
        ///  Function rename ToLower -> LOWER
        /// 
        ///  
        /// 
        ///  
        private static ISqlFragment HandleCanonicalFunctionToLower(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "LOWER"); 
        }

        /// 
        ///  Function rename ToUpper -> UPPER 
        /// 
        ///  
        ///  
        /// 
        private static ISqlFragment HandleCanonicalFunctionToUpper(SqlGenerator sqlgen, DbFunctionExpression e) 
        {
            return sqlgen.HandleFunctionDefaultGivenName(e, "UPPER");
        }
 
        /// 
        /// Writes the function name to the given SqlBuilder. 
        ///  
        /// 
        ///  
        private static void WriteFunctionName(SqlBuilder result, EdmFunction function)
        {
            string storeFunctionName;
 
            if (null != function.StoreFunctionNameAttribute)
            { 
                storeFunctionName = function.StoreFunctionNameAttribute; 
            }
            else 
            {
                storeFunctionName = function.Name;
            }
 
            // If the function is a builtin (i.e. the BuiltIn attribute has been
            // specified, both store and canonical functions have this attribute), 
            // then the function name should not be quoted; 
            // additionally, no namespace should be used.
            if (TypeHelpers.IsCanonicalFunction(function)) 
            {
                result.Append(storeFunctionName.ToUpperInvariant());
            }
            else if (IsStoreFunction(function)) 
            {
                result.Append(storeFunctionName); 
            } 
            else
            { 
                // Should we actually support this?
                if (String.IsNullOrEmpty(function.Schema))
                {
                    result.Append(QuoteIdentifier(function.NamespaceName)); 
                }
                else 
                { 
                    result.Append(QuoteIdentifier(function.Schema));
                } 
                result.Append(".");
                result.Append(QuoteIdentifier(storeFunctionName));
            }
        } 
        #endregion
 
        #region Other Helpers 
        /// 
        ///  
        /// Add the column names from the referenced extent/join to the
        /// select statement.
        ///
        /// If the symbol is a JoinSymbol, we recursively visit all the extents, 
        /// halting at real extents and JoinSymbols that have an associated SqlSelectStatement.
        /// 
        /// The column names for a real extent can be derived from its type. 
        /// The column names for a Join Select statement can be got from the
        /// list of columns that was created when the Join's select statement 
        /// was created.
        ///
        /// We do the following for each column.
        ///  
        /// Add the SQL string for each column to the SELECT clause
        /// Add the column to the list of columns - so that it can 
        /// become part of the "type" of a JoinSymbol 
        /// Check if the column name collides with a previous column added
        /// to the same select statement.  Flag both the columns for renaming if true. 
        /// Add the column to a name lookup dictionary for collision detection.
        /// 
        /// 
        /// The select statement that started off as SELECT * 
        /// The symbol containing the type information for
        /// the columns to be added. 
        /// Columns that have been added to the Select statement. 
        /// This is created in .
        /// A dictionary of the columns above. 
        /// Comma or nothing, depending on whether the SELECT
        /// clause is empty.
        private void AddColumns(SqlSelectStatement selectStatement, Symbol symbol,
            List columnList, Dictionary columnDictionary, ref string separator) 
        {
            JoinSymbol joinSymbol = symbol as JoinSymbol; 
            if (joinSymbol != null) 
            {
                if (!joinSymbol.IsNestedJoin) 
                {
                    // Recurse if the join symbol is a collection of flattened extents
                    foreach (Symbol sym in joinSymbol.ExtentList)
                    { 
                        // if sym is ScalarType means we are at base case in the
                        // recursion and there are not columns to add, just skip 
                        if ((sym.Type == null) || TypeSemantics.IsPrimitiveType(sym.Type)) 
                        {
                            continue; 
                        }

                        AddColumns(selectStatement, sym, columnList, columnDictionary, ref separator);
                    } 
                }
                else 
                { 
                    foreach (Symbol joinColumn in joinSymbol.ColumnList)
                    { 
                        // we write tableName.columnName
                        // rather than tableName.columnName as alias
                        // since the column name is unique (by the way we generate new column names)
                        // 
                        // We use the symbols for both the table and the column,
                        // since they are subject to renaming. 
                        selectStatement.Select.Append(separator); 
                        selectStatement.Select.Append(symbol);
                        selectStatement.Select.Append("."); 
                        selectStatement.Select.Append(joinColumn);

                        // check for name collisions.  If there is,
                        // flag both the colliding symbols. 
                        if (columnDictionary.ContainsKey(joinColumn.Name))
                        { 
                            columnDictionary[joinColumn.Name].NeedsRenaming = true; // the original symbol 
                            joinColumn.NeedsRenaming = true; // the current symbol.
                        } 
                        else
                        {
                            columnDictionary[joinColumn.Name] = joinColumn;
                        } 

                        columnList.Add(joinColumn); 
 
                        separator = ", ";
                    } 
                }
            }
            else
            { 
                // This is a non-join extent/select statement, and the CQT type has
                // the relevant column information. 
 
                // The type could be a record type(e.g. Project(...),
                // or an entity type ( e.g. EntityExpression(...) 
                // so, we check whether it is a structuralType.

                // Consider an expression of the form J(a, b=P(E))
                // The inner P(E) would have been translated to a SQL statement 
                // We should not use the raw names from the type, but the equivalent
                // symbols (they are present in symbol.Columns) if they exist. 
                // 
                // We add the new columns to the symbol's columns if they do
                // not already exist. 
                //
                // If the symbol represents a SqlStatement with renamed output columns,
                // we should use these instead of the rawnames and we should also mark
                // this selectStatement as one with renamed columns 

                if (symbol.OutputColumnsRenamed) 
                { 
                    selectStatement.OutputColumnsRenamed = true;
                    selectStatement.OutputColumns = new Dictionary(); 
                }

                if ((symbol.Type == null) || TypeSemantics.IsPrimitiveType(symbol.Type))
                { 
                    AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, "X");
                } 
                else 
                {
                    foreach (EdmProperty property in TypeHelpers.GetProperties(symbol.Type)) 
                    {
                        AddColumn(selectStatement, symbol, columnList, columnDictionary, ref separator, property.Name);
                    }
               } 
            }
        } 
 
        /// 
        /// Helper method for AddColumns. Adds a column with the given column name 
        /// to the Select list of the given select statement.
        /// 
        /// The select statement to whose SELECT part the column should be added
        /// The symbol from which the column to be added originated 
        /// Columns that have been added to the Select statement.
        /// This is created in . 
        /// A dictionary of the columns above. 
        /// Comma or nothing, depending on whether the SELECT
        /// clause is empty. 
        /// The name of the column to be added.
        private void AddColumn(SqlSelectStatement selectStatement, Symbol symbol,
            List columnList, Dictionary columnDictionary, ref string separator, string columnName)
        { 
            // Since all renaming happens in the second phase
            // we lose nothing by setting the next column name index to 0 
            // many times. 
            allColumnNames[columnName] = 0;
 
            // Create a new symbol/reuse existing symbol for the column
            Symbol columnSymbol;
            if (!symbol.Columns.TryGetValue(columnName, out columnSymbol))
            { 
                // we do not care about the types of columns, so we pass null
                // when construction the symbol. 
                columnSymbol = new Symbol(columnName, null); 
                symbol.Columns.Add(columnName, columnSymbol);
            } 

            selectStatement.Select.Append(separator);
            selectStatement.Select.Append(symbol);
            selectStatement.Select.Append("."); 

            if (symbol.OutputColumnsRenamed) 
            { 
                selectStatement.Select.Append(columnSymbol);
                selectStatement.OutputColumns.Add(columnSymbol.Name, columnSymbol); 
            }

            // We use the actual name before the "AS", the new name goes
            // after the AS. 
            else
            { 
                selectStatement.Select.Append(QuoteIdentifier(columnName)); 
            }
 
            selectStatement.Select.Append(" AS ");
            selectStatement.Select.Append(columnSymbol);

            // Check for column name collisions. 
            if (columnDictionary.ContainsKey(columnName))
            { 
                columnDictionary[columnName].NeedsRenaming = true; 
                columnSymbol.NeedsRenaming = true;
            } 
            else
            {
                columnDictionary[columnName] = symbol.Columns[columnName];
            } 

            columnList.Add(columnSymbol); 
            separator = ", "; 
        }
 
        /// 
        /// Expands Select * to "select the_list_of_columns"
        /// If the columns are taken from an extent, they are written as
        /// {original_column_name AS Symbol(original_column)} to allow renaming. 
        ///
        /// If the columns are taken from a Join, they are written as just 
        /// {original_column_name}, since there cannot be a name collision. 
        ///
        /// We concatenate the columns from each of the inputs to the select statement. 
        /// Since the inputs may be joins that are flattened, we need to recurse.
        /// The inputs are inferred from the symbols in FromExtents.
        /// 
        ///  
        /// 
        private List AddDefaultColumns(SqlSelectStatement selectStatement) 
        { 
            // This is the list of columns added in this select statement
            // This forms the "type" of the Select statement, if it has to 
            // be expanded in another SELECT *
            List columnList = new List();

            // A lookup for the previous set of columns to aid column name 
            // collision detection.
            Dictionary columnDictionary = new Dictionary(StringComparer.OrdinalIgnoreCase); 
 
            string separator = "";
            // The Select should usually be empty before we are called, 
            // but we do not mind if it is not.
            if (!selectStatement.Select.IsEmpty)
            {
                separator = ", "; 
            }
 
            foreach (Symbol symbol in selectStatement.FromExtents) 
            {
                AddColumns(selectStatement, symbol, columnList, columnDictionary, ref separator); 
            }

            return columnList;
        } 

        ///  
        ///  
        /// 
        ///  
        /// 
        /// 
        private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol)
        { 
            AddFromSymbol(selectStatement, inputVarName, fromSymbol, true);
        } 
 
        /// 
        /// This method is called after the input to a relational node is visited. 
        ///  and 
        /// There are 2 scenarios
        /// 
        /// The fromSymbol is new i.e. the select statement has just been 
        /// created, or a join extent has been added.
        /// The fromSymbol is old i.e. we are reusing a select statement. 
        ///  
        ///
        /// If we are not reusing the select statement, we have to complete the 
        /// FROM clause with the alias
        /// 
        /// -- if the input was an extent
        /// FROM = [SchemaName].[TableName] 
        /// -- if the input was a Project
        /// FROM = (SELECT ... FROM ... WHERE ...) 
        ///  
        ///
        /// These become 
        /// 
        /// -- if the input was an extent
        /// FROM = [SchemaName].[TableName] AS alias
        /// -- if the input was a Project 
        /// FROM = (SELECT ... FROM ... WHERE ...) AS alias
        ///  
        /// and look like valid FROM clauses. 
        ///
        /// Finally, we have to add the alias to the global list of aliases used, 
        /// and also to the current symbol table.
        /// 
        /// 
        /// The alias to be used. 
        /// 
        ///  
        private void AddFromSymbol(SqlSelectStatement selectStatement, string inputVarName, Symbol fromSymbol, bool addToSymbolTable) 
        {
            // the first check is true if this is a new statement 
            // the second check is true if we are in a join - we do not
            // check if we are in a join context.
            // We do not want to add "AS alias" if it has been done already
            // e.g. when we are reusing the Sql statement. 
            if (selectStatement.FromExtents.Count == 0 || fromSymbol != selectStatement.FromExtents[0])
            { 
                selectStatement.FromExtents.Add(fromSymbol); 
                selectStatement.From.Append(" AS ");
                selectStatement.From.Append(fromSymbol); 

                // We have this inside the if statement, since
                // we only want to add extents that are actually used.
                allExtentNames[fromSymbol.Name] = 0; 
            }
 
            if (addToSymbolTable) 
            {
                symbolTable.Add(inputVarName, fromSymbol); 
            }
        }

        ///  
        /// Translates a list of SortClauses.
        /// Used in the translation of OrderBy 
        ///  
        /// The SqlBuilder to which the sort keys should be appended
        ///  
        private void AddSortKeys(SqlBuilder orderByClause, IList sortKeys)
        {
            string separator = "";
            foreach (DbSortClause sortClause in sortKeys) 
            {
                orderByClause.Append(separator); 
                orderByClause.Append(sortClause.Expression.Accept(this)); 
                // Bug 431021: COLLATE clause must precede ASC/DESC
                Debug.Assert(sortClause.Collation != null); 
                if (!String.IsNullOrEmpty(sortClause.Collation))
                {
                    orderByClause.Append(" COLLATE ");
                    orderByClause.Append(sortClause.Collation); 
                }
 
                orderByClause.Append(sortClause.Ascending ? " ASC" : " DESC"); 

                separator = ", "; 
            }
        }

        ///  
        /// 
        ///  
        ///  
        /// 
        ///  
        /// 
        /// 
        private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
            string inputVarName, TypeUsage inputVarType, out Symbol fromSymbol) 
        {
            return CreateNewSelectStatement(oldStatement, inputVarName, inputVarType, true, out fromSymbol); 
        } 

        ///  
        /// This is called after a relational node's input has been visited, and the
        /// input's sql statement cannot be reused.  
        ///
        /// When the input's sql statement cannot be reused, we create a new sql 
        /// statement, with the old one as the from clause of the new statement.
        /// 
        /// The old statement must be completed i.e. if it has an empty select list, 
        /// the list of columns must be projected out.
        /// 
        /// If the old statement being completed has a join symbol as its from extent,
        /// the new statement must have a clone of the join symbol as its extent.
        /// We cannot reuse the old symbol, but the new select statement must behave
        /// as though it is working over the "join" record. 
        /// 
        ///  
        ///  
        /// 
        ///  
        /// 
        /// A new select statement, with the old one as the from clause.
        private SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
            string inputVarName, TypeUsage inputVarType, bool finalizeOldStatement, out Symbol fromSymbol) 
        {
            fromSymbol = null; 
 
            // Finalize the old statement
            if (finalizeOldStatement && oldStatement.Select.IsEmpty) 
            {
                List columns = AddDefaultColumns(oldStatement);

                // Thid could not have been called from a join node. 
                Debug.Assert(oldStatement.FromExtents.Count == 1);
 
                // if the oldStatement has a join as its input, ... 
                // clone the join symbol, so that we "reuse" the
                // join symbol.  Normally, we create a new symbol - see the next block 
                // of code.
                JoinSymbol oldJoinSymbol = oldStatement.FromExtents[0] as JoinSymbol;
                if (oldJoinSymbol != null)
                { 
                    // Note: oldStatement.FromExtents will not do, since it might
                    // just be an alias of joinSymbol, and we want an actual JoinSymbol. 
                    JoinSymbol newJoinSymbol = new JoinSymbol(inputVarName, inputVarType, oldJoinSymbol.ExtentList); 
                    // This indicates that the oldStatement is a blocking scope
                    // i.e. it hides/renames extent columns 
                    newJoinSymbol.IsNestedJoin = true;
                    newJoinSymbol.ColumnList = columns;
                    newJoinSymbol.FlattenedExtentList = oldJoinSymbol.FlattenedExtentList;
 
                    fromSymbol = newJoinSymbol;
                } 
            } 

            if (fromSymbol == null) 
            {
                if (oldStatement.OutputColumnsRenamed)
                {
                    fromSymbol = new Symbol(inputVarName, inputVarType, oldStatement.OutputColumns); 
                }
                else 
                { 
                    // This is just a simple extent/SqlSelectStatement,
                    // and we can get the column list from the type. 
                    fromSymbol = new Symbol(inputVarName, inputVarType);
                }
            }
 
            // Observe that the following looks like the body of Visit(ExtentExpression).
            SqlSelectStatement selectStatement = new SqlSelectStatement(); 
            selectStatement.From.Append("( "); 
            selectStatement.From.Append(oldStatement);
            selectStatement.From.AppendLine(); 
            selectStatement.From.Append(") ");


            return selectStatement; 
        }
 
        ///  
        /// Before we embed a string literal in a SQL string, we should
        /// convert all ' to '', and enclose the whole string in single quotes. 
        /// 
        /// 
        /// 
        /// The escaped sql string. 
        private static string EscapeSingleQuote(string s, bool isUnicode)
        { 
            return (isUnicode ? "N'" : "'") + s.Replace("'", "''") + "'"; 
        }
 
        /// 
        /// Returns the sql primitive/native type name.
        /// It will include size, precision or scale depending on type information present in the
        /// type facets 
        /// 
        ///  
        ///  
        private string GetSqlPrimitiveType( TypeUsage type )
        { 
            Debug.Assert(Helper.IsPrimitiveType(type.EdmType), "Type must be primitive type");
            Debug.Assert(type.EdmType.DataSpace == DataSpace.CSpace, "Type must be in cSpace");

            TypeUsage storeTypeUsage = this._storeItemCollection.StoreProviderManifest.GetStoreType(type); 

            string typeName = storeTypeUsage.EdmType.Name; 
            bool hasFacet = false; 
            int maxLength = 0;
            byte decimalPrecision = 0; 
            byte decimalScale = 0;

            PrimitiveTypeKind primitiveTypeKind = ((PrimitiveType)storeTypeUsage.EdmType).PrimitiveTypeKind;
 
            switch (primitiveTypeKind)
            { 
                case PrimitiveTypeKind.Binary: 
                    if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.MaxLengthFacetName))
                    { 
                        hasFacet = TypeHelpers.TryGetMaxLength(storeTypeUsage, out maxLength);
                        Debug.Assert(hasFacet, "Binary type did not have MaxLength facet");
                        typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
                    } 
                    break;
 
                case PrimitiveTypeKind.String: 
                    if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.MaxLengthFacetName))
                    { 
                        hasFacet = TypeHelpers.TryGetMaxLength(storeTypeUsage, out maxLength);
                        Debug.Assert(hasFacet, "String type did not have MaxLength facet");
                        typeName = typeName + "(" + maxLength.ToString(CultureInfo.InvariantCulture) + ")";
                    } 
                    break;
 
                case PrimitiveTypeKind.DateTime: 
                    typeName = this.IsPreKatmai ? "datetime" : "datetime2";
                    break; 
                case PrimitiveTypeKind.Time:
                    AssertKatmaiOrNewer(primitiveTypeKind);
                    typeName = "time";
                    break; 
                case PrimitiveTypeKind.DateTimeOffset:
                    AssertKatmaiOrNewer(primitiveTypeKind); 
                    typeName = "datetimeoffset"; 
                    break;
 
                case PrimitiveTypeKind.Decimal:
                    if (!TypeHelpers.IsFacetValueConstant(storeTypeUsage, DbProviderManifest.PrecisionFacetName))
                    {
                        hasFacet = TypeHelpers.TryGetPrecision(storeTypeUsage, out decimalPrecision); 
                        Debug.Assert(hasFacet, "decimal must have precision facet");
                        Debug.Assert(decimalPrecision > 0, "decimal precision must be greater than zero"); 
                        hasFacet = TypeHelpers.TryGetScale(storeTypeUsage, out decimalScale); 
                        Debug.Assert(hasFacet, "decimal must have scale facet");
                        Debug.Assert(decimalPrecision >= decimalScale, "decimalPrecision must be greater or equal to decimalScale"); 
                        typeName = typeName + "(" + decimalPrecision + "," + decimalScale + ")";
                    }
                    break;
 
                default:
                    break; 
            } 

            return typeName; 
        }

        /// 
        /// Handles the expression represending DbLimitExpression.Limit and DbSkipExpression.Count. 
        /// If it is a constant expression, it simply does to string thus avoiding casting it to the specific value
        /// (which would be done if  is called) 
        ///  
        /// 
        ///  
        private ISqlFragment HandleCountExpression(DbExpression e)
        {
            ISqlFragment result;
 
            if (e.ExpressionKind == DbExpressionKind.Constant)
            { 
                //For constant expression we should not cast the value, 
                // thus we don't go throught the default DbConstantExpression handling
                SqlBuilder sqlBuilder = new SqlBuilder(); 
                sqlBuilder.Append(((DbConstantExpression)e).Value.ToString());
                result = sqlBuilder;
            }
            else 
            {
                result = e.Accept(this); 
            } 

            return result; 
        }

        /// 
        /// This is used to determine if a particular expression is an Apply operation. 
        /// This is only the case when the DbExpressionKind is CrossApply or OuterApply.
        ///  
        ///  
        /// 
        static bool IsApplyExpression(DbExpression e) 
        {
            return (DbExpressionKind.CrossApply == e.ExpressionKind || DbExpressionKind.OuterApply == e.ExpressionKind);
        }
 
        /// 
        /// This is used to determine if a particular expression is a Join operation. 
        /// This is true for DbCrossJoinExpression and DbJoinExpression, the 
        /// latter of which may have one of several different ExpressionKinds.
        ///  
        /// 
        /// 
        static bool IsJoinExpression(DbExpression e)
        { 
            return (DbExpressionKind.CrossJoin == e.ExpressionKind ||
                    DbExpressionKind.FullOuterJoin == e.ExpressionKind || 
                    DbExpressionKind.InnerJoin == e.ExpressionKind || 
                    DbExpressionKind.LeftOuterJoin == e.ExpressionKind);
        } 

        /// 
        /// This is used to determine if a calling expression needs to place
        /// round brackets around the translation of the expression e. 
        ///
        /// Constants, parameters and properties do not require brackets, 
        /// everything else does. 
        /// 
        ///  
        /// true, if the expression needs brackets 
        private static bool IsComplexExpression(DbExpression e)
        {
            switch (e.ExpressionKind) 
            {
                case DbExpressionKind.Constant: 
                case DbExpressionKind.ParameterReference: 
                case DbExpressionKind.Property:
                    return false; 

                default:
                    return true;
            } 
        }
 
        ///  
        /// Determine if the owner expression can add its unique sql to the input's
        /// SqlSelectStatement 
        /// 
        /// The SqlSelectStatement of the input to the relational node.
        /// The kind of the expression node(not the input's)
        ///  
        private static bool IsCompatible(SqlSelectStatement result, DbExpressionKind expressionKind)
        { 
            switch (expressionKind) 
            {
                case DbExpressionKind.Distinct: 
                    return result.Top == null
                        // #494803: The projection after distinct may not project all
                        // columns used in the Order By
                        // Improvement: Consider getting rid of the Order By instead 
                        && result.OrderBy.IsEmpty;
 
                case DbExpressionKind.Filter: 
                    return result.Select.IsEmpty
                            && result.Where.IsEmpty 
                            && result.GroupBy.IsEmpty
                            && result.Top == null;

                case DbExpressionKind.GroupBy: 
                    return result.Select.IsEmpty
                            && result.GroupBy.IsEmpty 
                            && result.OrderBy.IsEmpty 
                            && result.Top == null;
 
                case DbExpressionKind.Limit:
                case DbExpressionKind.Element:
                    return result.Top == null;
 
                case DbExpressionKind.Project:
                    // SQLBUDT #427998: Allow a Project to be compatible with an OrderBy 
                    // Otherwise we won't be able to sort an input, and project out only 
                    // a subset of the input columns
                    return result.Select.IsEmpty 
                            && result.GroupBy.IsEmpty
                            // SQLBUDT #513640 - If distinct is specified, the projection may affect
                            // the cardinality of the results, thus a new statement must be started.
                            && !result.IsDistinct; 

                case DbExpressionKind.Skip: 
                    return result.Select.IsEmpty 
                            && result.GroupBy.IsEmpty
                            && result.OrderBy.IsEmpty 
                            && !result.IsDistinct;

                case DbExpressionKind.Sort:
                    return result.Select.IsEmpty 
                            && result.GroupBy.IsEmpty
                            && result.OrderBy.IsEmpty 
                            // SQLBUDT #513640 - A Project may be on the top of the Sort, and if so, it would need 
                            // to be in the same statement as the Sort (see comment above for the Project case).
                            // A Distinct in the same statement would prevent that, and therefore if Distinct is present, 
                            // we need to start a new statement.
                            && !result.IsDistinct;

                default: 
                    Debug.Assert(false);
                    throw EntityUtil.InvalidOperation(String.Empty); 
            } 

        } 

        /// 
        /// We use the normal box quotes for SQL server.  We do not deal with ANSI quotes
        /// i.e. double quotes. 
        /// 
        ///  
        ///  
        internal static string QuoteIdentifier(string name)
        { 
            Debug.Assert(!String.IsNullOrEmpty(name));
            // We assume that the names are not quoted to begin with.
            return "[" + name.Replace("]", "]]") + "]";
        } 

        ///  
        /// Simply calls  
        /// with addDefaultColumns set to true
        ///  
        /// 
        /// 
        private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e)
        { 
            return VisitExpressionEnsureSqlStatement(e, true);
        } 
 
        /// 
        /// This is called from  and nodes which require a 
        /// select statement as an argument e.g. ,
        /// .
        ///
        /// SqlGenerator needs its child to have a proper alias if the child is 
        /// just an extent or a join.
        /// 
        /// The normal relational nodes result in complete valid SQL statements. 
        /// For the rest, we need to treat them as there was a dummy
        ///  
        /// -- originally {expression}
        /// -- change that to
        /// SELECT *
        /// FROM {expression} as c 
        /// 
        /// 
        /// DbLimitExpression needs to start the statement but not add the default columns 
        /// 
        ///  
        /// 
        /// 
        private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e, bool addDefaultColumns)
        { 
            Debug.Assert(TypeSemantics.IsCollectionType(e.ResultType));
 
            SqlSelectStatement result; 
            switch (e.ExpressionKind)
            { 
                case DbExpressionKind.Project:
                case DbExpressionKind.Filter:
                case DbExpressionKind.GroupBy:
                case DbExpressionKind.Sort: 
                    result = e.Accept(this) as SqlSelectStatement;
                    break; 
 
                default:
                    Symbol fromSymbol; 
                    string inputVarName = "c";  // any name will do - this is my random choice.
                    symbolTable.EnterScope();

                    TypeUsage type = null; 
                    switch (e.ExpressionKind)
                    { 
                        case DbExpressionKind.Scan: 
                        case DbExpressionKind.CrossJoin:
                        case DbExpressionKind.FullOuterJoin: 
                        case DbExpressionKind.InnerJoin:
                        case DbExpressionKind.LeftOuterJoin:
                        case DbExpressionKind.CrossApply:
                        case DbExpressionKind.OuterApply: 
                            // #490026: It used to be type = e.ResultType.
                            type = TypeHelpers.GetElementTypeUsage(e.ResultType); 
                            break; 

                        default: 
                            Debug.Assert(TypeSemantics.IsCollectionType(e.ResultType));
                            type = TypeHelpers.GetEdmType(e.ResultType).TypeUsage;
                            break;
                    } 

 
                    result = VisitInputExpression(e, inputVarName, type, out fromSymbol); 
                    AddFromSymbol(result, inputVarName, fromSymbol);
                    symbolTable.ExitScope(); 
                    break;
            }

            if (addDefaultColumns && result.Select.IsEmpty) 
            {
                AddDefaultColumns(result); 
            } 

            return result; 
        }

        /// 
        /// This method is called by  and 
        /// 
        /// 
        ///  
        /// 
        ///  
        /// This is passed from 
        /// in the All(...) case.
        /// 
        private SqlSelectStatement VisitFilterExpression(DbExpressionBinding input, DbExpression predicate, bool negatePredicate) 
        {
            Symbol fromSymbol; 
            SqlSelectStatement result = VisitInputExpression(input.Expression, 
                input.VariableName, input.VariableType, out fromSymbol);
 
            // Filter is compatible with OrderBy
            // but not with Project, another Filter or GroupBy
            if (!IsCompatible(result, DbExpressionKind.Filter))
            { 
                result = CreateNewSelectStatement(result, input.VariableName, input.VariableType, out fromSymbol);
            } 
 
            selectStatementStack.Push(result);
            symbolTable.EnterScope(); 

            AddFromSymbol(result, input.VariableName, fromSymbol);

            if (negatePredicate) 
            {
                result.Where.Append("NOT ("); 
            } 
            result.Where.Append(predicate.Accept(this));
            if (negatePredicate) 
            {
                result.Where.Append(")");
            }
 
            symbolTable.ExitScope();
            selectStatementStack.Pop(); 
 
            return result;
        } 

        /// 
        /// If the sql fragment for an input expression is not a SqlSelect statement
        /// or other acceptable form (e.g. an extent as a SqlBuilder), we need 
        /// to wrap it in a form acceptable in a FROM clause.  These are
        /// primarily the 
        ///  
        /// The set operation expressions - union all, intersect, except
        /// TVFs, which are conceptually similar to tables 
        /// 
        /// 
        /// 
        ///  
        /// 
        private static void WrapNonQueryExtent(SqlSelectStatement result, ISqlFragment sqlFragment, DbExpressionKind expressionKind) 
        { 
            switch (expressionKind)
            { 
                case DbExpressionKind.Function:
                    // TVF
                    result.From.Append(sqlFragment);
                    break; 

                default: 
                    result.From.Append(" ("); 
                    result.From.Append(sqlFragment);
                    result.From.Append(")"); 
                    break;
            }
        }
 
        /// 
        /// Is this a Store function (ie) does it have the builtinAttribute specified and it is not a canonical function? 
        ///  
        /// 
        ///  
        private static bool IsStoreFunction(EdmFunction function)
        {
            return function.BuiltInAttribute && !TypeHelpers.IsCanonicalFunction(function);
        } 

        private static string ByteArrayToBinaryString( Byte[] binaryArray ) 
        { 
            StringBuilder sb = new StringBuilder( binaryArray.Length * 2 );
            for (int i = 0 ; i < binaryArray.Length ; i++) 
            {
                sb.Append(hexDigits[(binaryArray[i]&0xF0) >>4]).Append(hexDigits[binaryArray[i]&0x0F]);
            }
            return sb.ToString(); 
        }
 
        private TypeUsage GetPrimitiveType(PrimitiveTypeKind modelType) 
        {
            TypeUsage type = null; 
            PrimitiveType mappedType = this._storeItemCollection.GetMappedPrimitiveType(modelType);

            Debug.Assert(mappedType != null, "Could not get type usage for primitive type");
 
            type = TypeUsage.CreateDefaultTypeUsage(mappedType);
            return type; 
        } 

        ///  
        /// Helper method for the Group By visitor
        /// Returns true if at least one of the aggregates in the given list
        /// has an argument that is not a  and is not
        /// a  over , 
        /// either potentially capped with a 
        /// 
        /// This is really due to the following two limitations of Sql Server: 
        /// 
        /// If an expression being aggregated contains an outer reference, then that outer 
        /// reference must be the only column referenced in the expression (SQLBUDT #488741)
        /// Sql Server cannot perform an aggregate function on an expression containing
        /// an aggregate or a subquery. (SQLBUDT #504600)
        ///  
        /// Potentially, we could furhter optimize this.
        ///  
        ///  
        /// 
        ///  
        static bool GroupByAggregatesNeedInnerQuery(IList aggregates, string inputVarRefName)
        {
            foreach (DbAggregate aggregate in aggregates)
            { 
                Debug.Assert(aggregate.Arguments.Count == 1);
                if (GroupByAggregateNeedsInnerQuery(aggregate.Arguments[0], inputVarRefName)) 
                { 
                    return true;
                } 
            }
            return false;
        }
 
        /// 
        /// Returns true if the given expression is not a  or a 
        ///  over  a  
        /// referencing the given inputVarRefName, either
        /// potentially capped with a . 
        /// 
        /// 
        /// 
        ///  
        private static bool GroupByAggregateNeedsInnerQuery(DbExpression expression, string inputVarRefName)
        { 
            return GroupByExpressionNeedsInnerQuery(expression, inputVarRefName, true); 
        }
 
        /// 
        /// Helper method for the Group By visitor
        /// Returns true if at least one of the expressions in the given list
        /// is not  over  
        /// referencing the given inputVarRefName potentially capped with a .
        /// 
        /// This is really due to the following limitation: Sql Server requires each GROUP BY expression 
        /// (key) to contain at least one column that is not an outer reference. (SQLBUDT #616523)
        /// Potentially, we could further optimize this. 
        /// 
        /// 
        /// 
        ///  
        static bool GroupByKeysNeedInnerQuery(IList keys, string inputVarRefName)
        { 
            foreach (DbExpression key in keys) 
            {
                if (GroupByKeyNeedsInnerQuery(key, inputVarRefName)) 
                {
                    return true;
                }
            } 
            return false;
        } 
 
        /// 
        /// Returns true if the given expression is not  over 
        ///  referencing the given inputVarRefName
        /// potentially capped with a .
        /// This is really due to the following limitation: Sql Server requires each GROUP BY expression
        /// (key) to contain at least one column that is not an outer reference. (SQLBUDT #616523) 
        /// Potentially, we could further optimize this.
        ///  
        ///  
        /// 
        ///  
        private static bool GroupByKeyNeedsInnerQuery(DbExpression expression, string inputVarRefName)
        {
            return GroupByExpressionNeedsInnerQuery(expression, inputVarRefName, false);
        } 

        ///  
        /// Helper method for processing Group By keys and aggregates. 
        /// Returns true if the given expression is not a 
        /// (and allowConstants is specified)or a  over 
        /// a  referencing the given inputVarRefName,
        /// either potentially capped with a .
        /// 
        ///  
        /// 
        ///  
        ///  
        private static bool GroupByExpressionNeedsInnerQuery(DbExpression expression, string inputVarRefName, bool allowConstants)
        { 
            //Skip a constant if constants are allowed
            if (allowConstants && (expression.ExpressionKind == DbExpressionKind.Constant))
            {
                return false; 
            }
 
            //Skip a cast expression 
            if (expression.ExpressionKind == DbExpressionKind.Cast)
            { 
                DbCastExpression castExpression = (DbCastExpression)expression;
                return GroupByExpressionNeedsInnerQuery(castExpression.Argument, inputVarRefName, allowConstants);
            }
 
            //Allow Property(Property(...)), needed when the input is a join
            if (expression.ExpressionKind == DbExpressionKind.Property) 
            { 
                DbPropertyExpression propertyExpression = (DbPropertyExpression)expression;
                return GroupByExpressionNeedsInnerQuery(propertyExpression.Instance, inputVarRefName, allowConstants); 
            }

            if (expression.ExpressionKind == DbExpressionKind.VariableReference)
            { 
                DbVariableReferenceExpression varRefExpression = expression as DbVariableReferenceExpression;
                return !varRefExpression.VariableName.Equals(inputVarRefName); 
            } 

            return true; 
        }

        /// 
        /// determines if the function requires the return type be enforeced by use of a cast expression 
        /// 
        ///  
        ///  
        private bool CastReturnTypeToInt32(DbFunctionExpression e)
        { 
            if (!_functionRequiresReturnTypeCast.Contains(e.Function.FullName))
            {
                return false;
            } 

            for (int i = 0; i < e.Arguments.Count; i++) 
            { 
                TypeUsage storeType = _storeItemCollection.StoreProviderManifest.GetStoreType(e.Arguments[i].ResultType);
                if (_maxTypeNames.Contains(storeType.EdmType.Name)) 
                {
                    return true;
                }
            } 
            return false;
        } 
 
        /// 
        /// Throws not supported exception if the server is pre-katmai 
        /// 
        /// 
        private void AssertKatmaiOrNewer(PrimitiveTypeKind primitiveTypeKind)
        { 
            if (this.IsPreKatmai)
            { 
                throw EntityUtil.NotSupported(System.Data.Entity.Strings.PrimitiveTypeNotSupportedPriorSql10(primitiveTypeKind)); 
            }
        } 

        /// 
        /// Throws not supported exception if the server is pre-katmai
        ///  
        /// 
        private void AssertKatmaiOrNewer(DbFunctionExpression e) 
        { 
            if (this.IsPreKatmai)
            { 
                throw EntityUtil.NotSupported(System.Data.Entity.Strings.CanonicalFunctionNotSupportedPriorSql10(e.Function.Name));
            }
        }
 
        #endregion
 
        #endregion 
    }
} 


// File provided for Reference Use Only by Microsoft Corporation (c) 2007.

                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK