EFIngresProvider/SqlGen/SqlGenerator/SqlGeneratorDbExpressionVisitorMembers.cs
/// <summary>
/// <see cref="Visit(DbFilterExpression)"/> 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.
/// 
/// Whenever there exists at least one aggregate with an argument that is not is not a simple
/// <see cref="DbPropertyExpression"/>  over <see cref="DbVariableReferenceExpression"/>, 
/// we create a nested query in which we alias the arguments to the aggregates. 
/// That is due to the following two limitations of Sql Server:
/// <list type="number">
/// <item>If an expression being aggregated contains an outer reference, then that outer 
/// reference must be the only column referenced in the expression </item>
/// <item>Sql Server cannot perform an aggregate function on an expression containing 
/// an aggregate or a subquery. </item>
/// </list>
/// 
/// 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
/// 
/// </summary>
/// <param name="e"></param>
/// <returns>A <see cref="SqlSelectStatement"/></returns>
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.

Whenever there exists at least one aggregate with an argument that is not is not a simple
over ,
we create a nested query in which we alias the arguments to the aggregates.
That is due to the following two limitations of Sql Server.

The default translation, without inner query is.

SELECT
kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
aggf1(aexpr1) AS agg1, ..

When we inject an innner query, the equivalent translation is.

SELECT
key1 AS key1, key2 AS key2, ..

If an expression being aggregated contains an outer reference, then that outer
reference must be the only column referenced in the expression Sql Server cannot perform an aggregate function on an expression containing
an aggregate or a subquery.
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 . Whenever there exists at least one aggregate with an argument that is not is not a simple over we create a nested query in which we alias the arguments to the aggregates . That is due to the following two limitations of Sql Server . The default translation without inner query is . SELECT kexp1 AS key1 kexp2 AS key2 ... kexpn AS keyn aggf1 ( aexpr1 ) AS agg1 .. When we inject an innner query the equivalent translation is . SELECT key1 AS key1 key2 AS key2 .. If an expression being aggregated contains an outer reference then that outer reference must be the only column referenced in the expression Sql Server cannot perform an aggregate function on an expression containing an aggregate or a subquery .
public override ISqlFragment Visit(DbGroupByExpression e)
        {
            Symbol fromSymbol;
            SqlSelectStatement innerQuery = VisitInputExpression(e.Input.Expression,
                e.Input.VariableName, e.Input.VariableType, out fromSymbol);
            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);
            symbolTable.Add(e.Input.GroupVariableName, fromSymbol);
            RowType groupByType = MetadataHelpers.GetEdmType<RowType>(MetadataHelpers.GetEdmType<CollectionType>(e.ResultType).TypeUsage);
            bool needsInnerQuery = NeedsInnerQuery(e.Aggregates);
            SqlSelectStatement result;
            if (needsInnerQuery)
            {
                result = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, false, out fromSymbol);
                AddFromSymbol(result, e.Input.VariableName, fromSymbol, false);
            }
            else
            {
                result = innerQuery;
            }
            using (IEnumerator<EdmProperty> members = groupByType.Properties.GetEnumerator())
            {
                members.MoveNext();
                Debug.Assert(result.Select.IsEmpty);
                string separator = "";
                foreach (DbExpression key in e.Keys)
                {
                    EdmProperty member = members.Current;
                    var alias = new Symbol(member.Name);
                    result.GroupBy.Append(separator);
                    ISqlFragment keySql = key.Accept(this);
                    if (!needsInnerQuery)
                    {
                        result.Select.Append(new SelectColumn(alias, keySql));
                        result.GroupBy.Append(keySql);
                    }
                    else
                    {
                        result.Select.Append(new SelectColumn(alias, keySql));
                        result.Select.Append(new SelectColumn(alias, fromSymbol, alias));
                        result.GroupBy.Append(alias);
                    }
                    separator = ", ";
                    members.MoveNext();
                }
                foreach (DbAggregate aggregate in e.Aggregates)
                {
                    EdmProperty member = members.Current;
                    var alias = new Symbol(member.Name);
                    Debug.Assert(aggregate.Arguments.Count == 1);
                    ISqlFragment translatedAggregateArgument = aggregate.Arguments[0].Accept(this);
                    ISqlFragment aggregateArgument;
                    if (needsInnerQuery)
                    {
                        SqlBuilder wrappingAggregateArgument = new SqlBuilder();
                        wrappingAggregateArgument.Append(fromSymbol);
                        wrappingAggregateArgument.Append(".");
                        wrappingAggregateArgument.Append(SqlGenerator.QuoteIdentifier(alias.Name));
                        aggregateArgument = wrappingAggregateArgument;
                        innerQuery.Select.Append(new SelectColumn(alias, translatedAggregateArgument));
                    }
                    else
                    {
                        aggregateArgument = translatedAggregateArgument;
                    }
                    ISqlFragment aggregateResult = VisitAggregate(aggregate, aggregateArgument);
                    result.Select.Append(new SelectColumn(alias, aggregateResult));
                    separator = ", ";
                    members.MoveNext();
                }
            }
            symbolTable.ExitScope();
            selectStatementStack.Pop();
            return result;
        }
public override ISqlFragment Visit ( DbGroupByExpression e ) { Symbol fromSymbol ; SqlSelectStatement innerQuery = VisitInputExpression ( e . Input . Expression , e . Input . VariableName , e . Input . VariableType , out fromSymbol ) ; 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 ) ; symbolTable . Add ( e . Input . GroupVariableName , fromSymbol ) ; RowType groupByType = MetadataHelpers . GetEdmType < RowType > ( MetadataHelpers . GetEdmType < CollectionType > ( e . ResultType ) . TypeUsage ) ; bool needsInnerQuery = NeedsInnerQuery ( e . Aggregates ) ; SqlSelectStatement result ; if ( needsInnerQuery ) { result = CreateNewSelectStatement ( innerQuery , e . Input . VariableName , e . Input . VariableType , false , out fromSymbol ) ; AddFromSymbol ( result , e . Input . VariableName , fromSymbol , false ) ; } else { result = innerQuery ; } using ( IEnumerator < EdmProperty > members = groupByType . Properties . GetEnumerator ( ) ) { members . MoveNext ( ) ; Debug . Assert ( result . Select . IsEmpty ) ; string separator = " " ; foreach ( DbExpression key in e . Keys ) { EdmProperty member = members . Current ; var alias = new Symbol ( member . Name ) ; result . GroupBy . Append ( separator ) ; ISqlFragment keySql = key . Accept ( this ) ; if ( ! needsInnerQuery ) { result . Select . Append ( new SelectColumn ( alias , keySql ) ) ; result . GroupBy . Append ( keySql ) ; } else { result . Select . Append ( new SelectColumn ( alias , keySql ) ) ; result . Select . Append ( new SelectColumn ( alias , fromSymbol , alias ) ) ; result . GroupBy . Append ( alias ) ; } separator = " " ; members . MoveNext ( ) ; } foreach ( DbAggregate aggregate in e . Aggregates ) { EdmProperty member = members . Current ; var alias = new Symbol ( member . Name ) ; Debug . Assert ( aggregate . Arguments . Count == 1 ) ; ISqlFragment translatedAggregateArgument = aggregate . Arguments [ 0 ] . Accept ( this ) ; ISqlFragment aggregateArgument ; if ( needsInnerQuery ) { SqlBuilder wrappingAggregateArgument = new SqlBuilder ( ) ; wrappingAggregateArgument . Append ( fromSymbol ) ; wrappingAggregateArgument . Append ( " " ) ; wrappingAggregateArgument . Append ( SqlGenerator . QuoteIdentifier ( alias . Name ) ) ; aggregateArgument = wrappingAggregateArgument ; innerQuery . Select . Append ( new SelectColumn ( alias , translatedAggregateArgument ) ) ; } else { aggregateArgument = translatedAggregateArgument ; } ISqlFragment aggregateResult = VisitAggregate ( aggregate , aggregateArgument ) ; result . Select . Append ( new SelectColumn ( alias , aggregateResult ) ) ; separator = " " ; members . MoveNext ( ) ; } } symbolTable . ExitScope ( ) ; selectStatementStack . Pop ( ) ; return result ; }
for general details.
for general details .
// GroupBy is compatible with Filter and OrderBy // but not with Project, GroupBy // This line is not present for other relational nodes. // The enumerator is shared by both the keys and the aggregates, // so, we do not close it in between. //Whenever there exists at least one aggregate with an argument that is not simply a PropertyExpression  // over a VarRefExpression, we need a nested query in which we alias the arguments to the aggregates. //Create the inner query //Default translation: Alias = Key // The inner query contains the default translation Alias = Key //The outer resulting query projects over the key aliased in the inner query:  //  fromSymbol.Alias AS Alias //In this case the argument to the aggratete is reference to the one projected out by the // inner query
None
e
DbGroupByExpression
None