internal static bool HasAggregates(SqlSelectExpression expression, bool ignoreInSubqueries)
{
var checker = new SqlAggregateChecker(ignoreInSubqueries);
checker.Visit(expression);
return(checker.hasAggregate);
}
internal static bool HasAggregates(SqlSelectExpression expression, bool ignoreInSubqueries)
{
var checker = new SqlAggregateChecker(ignoreInSubqueries);
checker.Visit(expression);
return checker.hasAggregate;
}
protected override Expression VisitSelect(SqlSelectExpression selectExpression)
{
if (selectExpression.Columns.Count == 1 &&
selectExpression.From.NodeType == (ExpressionType)SqlExpressionType.Select &&
selectExpression.Columns[0].Expression.NodeType == (ExpressionType)SqlExpressionType.Aggregate)
{
var from = (SqlSelectExpression)selectExpression.From;
var aggregateExpression = (SqlAggregateExpression)selectExpression.Columns[0].Expression;
if (from.Columns.Count > 1 ||
aggregateExpression.IsDistinct ||
from.Distinct ||
from.Take != null ||
from.Skip != null ||
from.GroupBy != null
/* Don't fold a from with an orderby into the outer select if it has a count or other aggregate */
|| from.OrderBy != null && from.OrderBy.Count > 0 && SqlAggregateChecker.HasAggregates(selectExpression))
{
return(base.VisitSelect(selectExpression));
}
var newColumns = new List <SqlColumnDeclaration>();
if (from.Columns.Count == 1)
{
foreach (var column in from.Columns)
{
if (column.Expression.NodeType != (ExpressionType)SqlExpressionType.Column)
{
return(base.VisitSelect(selectExpression));
}
var newAggregate = new SqlAggregateExpression
(
aggregateExpression.Type,
aggregateExpression.AggregateType,
column.Expression,
aggregateExpression.IsDistinct
);
newColumns.Add(new SqlColumnDeclaration(column.Name, newAggregate));
}
}
else
{
newColumns.Add(selectExpression.Columns[0]);
}
var where = this.Visit(from.Where);
return(from.ChangeWhereAndColumns(where, newColumns.ToReadOnlyList(), from.ForUpdate || selectExpression.ForUpdate));
}
return(base.VisitSelect(selectExpression));
}
private static bool CanMergeWithFrom(SqlSelectExpression select)
{
var fromSelect = select.From.GetLeftMostSelect();
if (fromSelect == null)
{
return(false);
}
if (fromSelect.Columns.Any(c => c.NoOptimise))
{
return(false);
}
if (!IsColumnProjection(fromSelect))
{
return(false);
}
var selHasNameMapProjection = SqlRedundantSubqueryFinder.IsNameMapProjection(select);
var selHasSkip = select.Skip != null;
var selHasWhere = select.Where != null;
var selHasOrderBy = select.OrderBy != null && select.OrderBy.Count > 0;
var selHasGroupBy = select.GroupBy != null && select.GroupBy.Count > 0;
var selHasAggregates = SqlAggregateChecker.HasAggregates(select);
var frmHasOrderBy = fromSelect.OrderBy != null && fromSelect.OrderBy.Count > 0;
var frmHasGroupBy = fromSelect.GroupBy != null && fromSelect.GroupBy.Count > 0;
// Both cannot have OrderBy
if (selHasOrderBy && frmHasOrderBy)
{
return(false);
}
// Both cannot have GroupBy
if (selHasGroupBy && frmHasGroupBy)
{
return(false);
}
// Cannot move forward OrderBy if outer has GroupBy
if (frmHasOrderBy && (selHasGroupBy || selHasAggregates || select.Distinct))
{
return(false);
}
// Cannot move forward GroupBy
if (frmHasGroupBy)
{
return(false);
}
// Cannot move forward a take if outer has take or skip or distinct
if (fromSelect.Take != null && (select.Take != null || selHasSkip || select.Distinct || selHasAggregates || selHasGroupBy))
{
return(false);
}
// Cannot move forward a skip if outer has skip or distinct or aggregates with accompanying groupby or where
if (fromSelect.Skip != null && ((selHasWhere || selHasGroupBy) && (select.Skip != null || select.Distinct || selHasAggregates)))
{
return(false);
}
// Cannot merge a distinct if the outer has a distinct or aggregates with accompanying groupby or where
if (fromSelect.Distinct && (!selHasNameMapProjection || (((selHasWhere || selHasGroupBy) && (selHasAggregates || selHasOrderBy)))))
{
return(false);
}
return(true);
}
protected override Expression VisitSelect(SqlSelectExpression select)
{
var canHaveOrderBy = select.Take != null || select.Skip != null;
var hasGroupBy = select.GroupBy?.Count > 0;
var canReceiveOrderings = canHaveOrderBy && !hasGroupBy && !select.Distinct && !SqlAggregateChecker.HasAggregates(select);
this.outerCanReceiveOrderings &= canReceiveOrderings;
select = (SqlSelectExpression)base.VisitSelect(select);
var hasOrderBy = select.OrderBy?.Count > 0;
if (hasOrderBy)
{
this.PrependOrderings(select.OrderBy);
}
var columns = select.Columns;
var orderings = canReceiveOrderings ? this.gatheredOrderings : select.OrderBy;
var canPassOnOrderings = this.outerCanReceiveOrderings && !hasGroupBy && !select.Distinct;
if (this.gatheredOrderings != null)
{
if (canPassOnOrderings)
{
var producedAliases = SqlAliasesProduced.Gather(select.From);
var project = this.RebindOrderings(this.gatheredOrderings, select.Alias, producedAliases, select.Columns);
this.gatheredOrderings = null;
this.PrependOrderings(project.Orderings);
columns = project.Columns;
}
else
{
this.gatheredOrderings = null;
}
}
if (orderings != select.OrderBy || columns != select.Columns)
{
select = new SqlSelectExpression(select.Type, select.Alias, columns, select.From, select.Where, orderings, select.GroupBy, select.Distinct, select.Skip, select.Take, select.ForUpdate);
}
return(select);
}
protected override Expression VisitJoin(SqlJoinExpression join)
{
join = (SqlJoinExpression)base.VisitJoin(join);
if (join.JoinType == SqlJoinType.CrossApply || join.JoinType == SqlJoinType.OuterApply)
{
if (join.Right is SqlTableExpression)
{
return(new SqlJoinExpression(join.Type, SqlJoinType.Cross, join.Left, join.Right, null));
}
else
{
if (@join.Right is SqlSelectExpression @select && select.Take == null && select.Skip == null && !SqlAggregateChecker.HasAggregates(select) && (select.GroupBy == null || select.GroupBy.Count == 0))
{
var selectWithoutWhere = select.ChangeWhere(null);
var referencedAliases = SqlReferencedAliasGatherer.Gather(selectWithoutWhere);
var declaredAliases = SqlDeclaredAliasGatherer.Gather(join.Left);
referencedAliases.IntersectWith(declaredAliases);
if (referencedAliases.Count == 0)
{
var where = select.Where;
select = selectWithoutWhere;
var pc = ColumnProjector.ProjectColumns(new Nominator(Nominator.CanBeColumn), where, select.Columns, select.Alias, SqlDeclaredAliasGatherer.Gather(select.From));
select = select.ChangeColumns(pc.Columns);
where = pc.Projector;
var joinType = (where == null) ? SqlJoinType.Cross : (join.JoinType == SqlJoinType.CrossApply ? SqlJoinType.Inner : SqlJoinType.Left);
return(new SqlJoinExpression(typeof(void), joinType, join.Left, select, where));
}
}
}
}
return(join);
}