/// <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)); }