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