/// <summary>
/// Gets escaped TSql identifier describing this entity set.
/// </summary>
/// <returns></returns>
internal static string GetTargetTSql(EntitySetBase entitySetBase)
{
// construct escaped T-SQL referencing entity set
StringBuilder builder = new StringBuilder(50);
string definingQuery = MetadataHelpers.TryGetValueForMetadataProperty <string>(entitySetBase, "DefiningQuery");
if (!string.IsNullOrEmpty(definingQuery))
{
builder.Append("(");
builder.Append(definingQuery);
builder.Append(")");
}
else
{
string schemaName = MetadataHelpers.TryGetValueForMetadataProperty <string>(entitySetBase, "Schema");
if (!string.IsNullOrEmpty(schemaName))
{
builder.Append(SqlGenerator.QuoteIdentifier(schemaName));
builder.Append(".");
}
else
{
builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.EntityContainer.Name));
builder.Append(".");
}
string tableName = MetadataHelpers.TryGetValueForMetadataProperty <string>(entitySetBase, "Table");
if (!string.IsNullOrEmpty(tableName))
{
builder.Append(SqlGenerator.QuoteIdentifier(tableName));
}
else
{
builder.Append(SqlGenerator.QuoteIdentifier(entitySetBase.Name));
}
}
return(builder.ToString());
}
/// <summary>
/// Write this symbol out as a string for sql. This is just
/// the new name of the symbol (which could be the same as the old name).
///
/// We rename columns here if necessary.
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
if (NeedsRenaming)
{
string newName;
int i = sqlGenerator.AllColumnNames[NewName];
do
{
++i;
newName = SqlGenerator.Format("{0}{1}", Name, i);
} while (sqlGenerator.AllColumnNames.ContainsKey(newName));
sqlGenerator.AllColumnNames[NewName] = i;
// Prevent it from being renamed repeatedly.
NeedsRenaming = false;
NewName = newName;
// Add this column name to list of known names so that there are no subsequent
// collisions
sqlGenerator.AllColumnNames[newName] = 0;
}
writer.Write(SqlGenerator.QuoteIdentifier(NewName));
}
/// <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>
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 = MetadataHelpers.GetEdmType <RowType>(MetadataHelpers.GetEdmType <CollectionType>(e.ResultType).TypeUsage);
//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.
bool needsInnerQuery = NeedsInnerQuery(e.Aggregates);
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 <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)
{
//Default translation: Alias = Key
result.Select.Append(new SelectColumn(alias, keySql));
result.GroupBy.Append(keySql);
}
else
{
// The inner query contains the default translation Alias = Key
result.Select.Append(new SelectColumn(alias, keySql));
//The outer resulting query projects over the key aliased in the inner query:
// fromSymbol.Alias AS Alias
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)
{
//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(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);
}
// Generates T-SQL describing a member
// Requires: member must belong to an entity type (a safe requirement for DML
// SQL gen, where we only access table columns)
private static string GenerateMemberTSql(EdmMember member)
{
return(SqlGenerator.QuoteIdentifier(member.Name));
}
