static GenerateColumnResult GenerateColumn(EdmProperty property)
{
var column = new StringBuilder();
var columnComments = new Dictionary <string, string>();
column.Append(SqlGenerator.QuoteIdentifier(property.Name));
column.Append(" ");
column.Append(SqlGenerator.GetSqlPrimitiveType(property.TypeUsage));
switch (MetadataHelpers.GetEdmType <PrimitiveType>(property.TypeUsage).PrimitiveTypeKind)
{
case PrimitiveTypeKind.Boolean:
column.AppendFormat(" CHECK ({0} IN (1,0))", SqlGenerator.QuoteIdentifier(property.Name));
columnComments.Add(property.Name, "#BOOL#");
break;
case PrimitiveTypeKind.Guid:
columnComments.Add(property.Name, "#GUID#");
break;
}
if (!property.Nullable)
{
column.Append(" NOT NULL");
}
return(new GenerateColumnResult()
{
ColumnName = column.ToString(), ColumnComments = columnComments
});
}
/// <summary>
/// Translate a NewInstance(Element(X)) expression into
/// "select top(1) * from X"
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
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 = MetadataHelpers.GetElementTypeUsage(elementExpr.Argument.ResultType);
result = CreateNewSelectStatement(result, "element", inputType, out fromSymbol);
AddFromSymbol(result, "element", fromSymbol, false);
}
result.Top.SetTopCount(1);
return(result);
}
// Otherwise simply build this out as a union-all ladder
CollectionType collectionType = MetadataHelpers.GetEdmType <CollectionType>(e.ResultType);
Debug.Assert(collectionType != null);
bool isScalarElement = MetadataHelpers.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(MetadataHelpers.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);
}
/// <summary>
/// This is called from <see cref="GenerateSql(DbQueryCommandTree)"/> and nodes which require a
/// select statement as an argument e.g. <see cref="Visit(DbIsEmptyExpression)"/>,
/// <see cref="Visit(DbUnionAllExpression)"/>.
///
/// 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
/// <code>
/// -- originally {expression}
/// -- change that to
/// SELECT *
/// FROM {expression} as c
/// </code>
///
/// DbLimitExpression needs to start the statement but not add the default columns
/// </summary>
/// <param name="e"></param>
/// <param name="addDefaultColumns"></param>
/// <returns></returns>
private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e, bool addDefaultColumns)
{
Debug.Assert(MetadataHelpers.IsCollectionType(e.ResultType.EdmType));
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:
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:
// It used to be type = e.ResultType.
type = MetadataHelpers.GetElementTypeUsage(e.ResultType);
break;
default:
Debug.Assert(MetadataHelpers.IsCollectionType(e.ResultType.EdmType));
type = MetadataHelpers.GetEdmType <CollectionType>(e.ResultType).TypeUsage;
break;
}
Symbol fromSymbol;
result = VisitInputExpression(e, inputVarName, type, out fromSymbol);
AddFromSymbol(result, inputVarName, fromSymbol);
symbolTable.ExitScope();
break;
}
if (addDefaultColumns && result.Select.IsEmpty)
{
AddDefaultColumns(result);
}
return(result);
}
/// <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);
}