/// <summary>
/// Recursively builds the table join tree from the structure query tree.
/// </summary>
/// <param name="query">Input StructuredQuery.</param>
/// <param name="entity">StructuredQuery node being processed.</param>
/// <param name="parentTable">Parent table that this table may be joining to. May be null.</param>
/// <param name="sqlQuery">Query to be receiving this structure.</param>
/// <returns></returns>
private EntityTables BuildEntityTableJoinTree(StructuredQuery query, Entity entity, SqlTable parentTable, SqlQuery sqlQuery)
{
// Note: for the most part we aren't interested in the returned result, as the tables will get built inside the sqlQuery
// Only return the result so we can capture the topmost table.
EntityTables entityTables = CreateEntityPrimaryTable(query, entity, parentTable, sqlQuery);
// The EntityNode should generally not already be set, but in the case of a downcast, the existing parentTable may be returned
// In that case, we don't want to override the entitynode, because the parent node security rules can be safely applied to the child, but not vice-versa.
if (entityTables.EntityTable.EntityNode == null)
{
entityTables.EntityTable.EntityNode = entity;
}
sqlQuery.References.RegisterEntity(entity, entityTables);
if (entity.RelatedEntities != null)
{
foreach (Entity relatedEntity in entity.RelatedEntities)
{
var relatedResourceEntity = relatedEntity as RelatedResource;
if (relatedResourceEntity != null &&
relatedResourceEntity.ExcludeIfNotReferenced &&
!IsEntityReferenced(query, relatedResourceEntity))
{
// Check to see if this resource entity is used anywhere else in the query
// If not continue ignore the join.
continue;
}
BuildEntityTableJoinTree(query, relatedEntity, entityTables.EntityTable, sqlQuery);
}
}
// Add any conditions that are explicitly assigned to this node
if (entity.Conditions != null)
{
foreach (var expr in entity.Conditions)
{
SqlExpression sqlExpr = ConvertExpression(expr, sqlQuery);
entityTables.EntityTable.Conditions.Add(sqlExpr.BoolSql);
entityTables.EntityTable.HasCustomConditions = true;
}
}
return(entityTables);
}
public void RegisterEntity(Entity entity, EntityTables tables)
{
_entityNodeByNodeId[entity.NodeId] = entity;
_tablesByEntity[entity] = tables;
}
/// <summary>
/// Generates SqlTable and sub query for an aggregation.
/// </summary>
/// <param name="structuredQuery">The structured query.</param>
/// <param name="entity">The AggregateEntity that represents the aggregation in the 'from' tree.</param>
/// <param name="parentTable">The table that this aggregation joins up to. Can be null if the aggregation is being performed at the top level.</param>
/// <param name="sqlQuery">The query object.</param>
/// <returns>
/// The table
/// </returns>
private EntityTables RegisterAggregateEntity(StructuredQuery structuredQuery, AggregateEntity entity, SqlTable parentTable, SqlQuery sqlQuery)
{
if (entity.GroupedEntity == null)
{
throw new Exception("Aggregate entity must have GroupedEntity set.");
}
if (entity.RelatedEntities.Count > 0)
{
throw new Exception("Aggregate entity should not have related entities."); // note: no logical reason not to, however it's likely indicative of a bug elsewhere
}
// Establish sub query
// Note that sub query has its distinct reference manager for tracking tables and other elements.
SqlQuery subquery = sqlQuery.CreateSubQuery( );
subquery.ParentQuery = sqlQuery;
sqlQuery.Subqueries.Add(subquery);
// Get joining table for the entity being grouped
EntityTables childTables =
BuildEntityTableJoinTree(structuredQuery, entity.GroupedEntity, parentTable, subquery); //hmm
SqlTable childTable = childTables.HeadTable;
subquery.FromClause.RootTable = childTable;
// Note: we passed in parentTable above so that the child can learn about the joining column
// However, it will actually try to join to the table when we don't want it to, so need to manually unlink it afterwards.
childTable.Parent = null;
if (parentTable != null)
{
parentTable.Children.Remove(childTable);
}
// Create the proxy table
// Note: parentTable may be null for root aggregate
SqlTable proxyTable = sqlQuery.CreateJoinedTable("", // not applicable
"ag", parentTable, JoinHint.Unspecified, childTable.JoinColumn, parentTable?.IdColumn);
//if the used condition under current aggregate grouped entity or related entities, set joinhint to requried
if (structuredQuery.Conditions.Any(c => c.Operator != ConditionType.Unspecified &&
c.Operator != ConditionType.IsNull &&
ConditionContainArgument(c) &&
c.Expression is ResourceDataColumn &&
MatchGroupedEntityNode(((ResourceDataColumn)c.Expression).NodeId, entity.GroupedEntity)
))
{
proxyTable.JoinHint = JoinHint.Required;
}
//TODO, this hacky code is resolved a special sql query issue (bug 24406), both Pete and me agree with this is not the best solution, but can fix current bug
//TODO, we can remove this hacky code when report builder allows set the aggregated field in analyzer.
ConvertConditionExpressionToAggregate(structuredQuery.Conditions, entity);
proxyTable.SubQuery = new SqlUnion(subquery);
subquery.ProxyTable = proxyTable;
// Proxy the join column through the sub query select statement
// (If it actually joins to something)
if (childTable.JoinColumn != null)
{
string joinColumnAlias = sqlQuery.AliasManager.CreateAlias("aggCol");
string joinColumnSql = GetColumnSql(childTable, childTable.JoinColumn);
var joinColumn = new SqlSelectItem
{
Expression = new SqlExpression
{
Sql = joinColumnSql
},
Alias = joinColumnAlias
};
subquery.SelectClause.Items.Add(joinColumn);
subquery.GroupByClause.Expressions.Add(joinColumn.Expression);
proxyTable.JoinColumn = joinColumnAlias;
var idExpr = new IdExpression
{
NodeId = entity.GroupedEntity.NodeId
};
sqlQuery.References.RegisterMappedExpression(idExpr, joinColumn.Expression);
}
// Add additional grouping columns
if (entity.GroupBy != null && entity.GroupBy.Count > 0)
{
proxyTable.GroupByMap = new Dictionary <ScalarExpression, SqlExpression>();
foreach (ScalarExpression expr in entity.GroupBy)
{
try
{
SqlExpression sqlExpr;
if (TryConvertExpression(expr, subquery, false, out sqlExpr))
{
if (!_querySettings.FullAggregateClustering)
{
// If we're not clustering everywhere, then explicitly cluster in summarize group-bys still.
sqlExpr = ApplyClusterOperation(sqlExpr, expr.ClusterOperation, sqlQuery);
}
proxyTable.GroupByMap[expr] = sqlExpr;
var mappedSqlExpr = CreateAggregateMappingExpression(sqlQuery, proxyTable, sqlExpr);
sqlQuery.References.RegisterMappedExpression(expr, mappedSqlExpr);
if (sqlExpr.OrderingSqlCallback != null && sqlExpr.OrderingSqlRequiresGrouping)
{
mappedSqlExpr.OrderingSqlCallback =
exprTmp =>
CreateAggregateOrderbyMappingExpression(sqlQuery, proxyTable, sqlExpr.OrderingSql).Sql;
}
}
}
catch
{
}
}
}
// Return proxy table
return(new EntityTables(proxyTable));
}
