protected override Expression VisitSelect(SqlSelectExpression selectExpression) { if (selectExpression.Skip != null) { var rowNumber = new SqlFunctionCallExpression(typeof(int), "ROW_NUMBER"); var over = new SqlOverExpression(rowNumber, selectExpression.OrderBy ?? new ReadOnlyList <Expression>(new [] { new SqlOrderByExpression(OrderType.Ascending, selectExpression.Columns[0].Expression) })); var additionalColumn = new SqlColumnDeclaration(RowColumnName, over); var newAlias = selectExpression.Alias + "INNER"; var innerColumns = new ReadOnlyList <SqlColumnDeclaration>(selectExpression.Columns.Select(c => c).Concat(new[] { additionalColumn })); var innerSelect = new SqlSelectExpression(selectExpression.Type, newAlias, innerColumns, selectExpression.From, selectExpression.Where, null, selectExpression.GroupBy, selectExpression.Distinct, null, null, selectExpression.ForUpdate); var outerColumns = selectExpression.Columns.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, newAlias, c.Name))); Expression rowPredicate = Expression.GreaterThan(new SqlColumnExpression(typeof(int), newAlias, additionalColumn.Name), selectExpression.Skip); if (selectExpression.Take != null && !(selectExpression.Take is SqlTakeAllValueExpression)) { rowPredicate = Expression.And ( rowPredicate, Expression.LessThanOrEqual(new SqlColumnExpression(typeof(int), newAlias, additionalColumn.Name), Expression.Add(selectExpression.Skip, selectExpression.Take)) ); } return(new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, outerColumns, innerSelect, rowPredicate, null, selectExpression.ForUpdate)); } return(base.VisitSelect(selectExpression)); }
protected override Expression VisitSelect(SqlSelectExpression selectExpression) { var count = selectExpression.Columns.Count; List <SqlColumnDeclaration> newColumns = null; for (var i = 0; i < count; i++) { var column = selectExpression.Columns[i]; var visitedColumnExpression = this.Visit(column.Expression); if (visitedColumnExpression.Type.GetUnwrappedNullableType() == typeof(bool) && !(visitedColumnExpression is BitBooleanExpression)) { if (newColumns == null) { newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i)); } var newColumnExpression = BitBooleanExpression.Coerce(visitedColumnExpression); var newColumnDeclaration = new SqlColumnDeclaration(column.Name, newColumnExpression); newColumns.Add(newColumnDeclaration); } else if (visitedColumnExpression != column.Expression) { if (newColumns == null) { newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i)); } newColumns.Add(column.ReplaceExpression(visitedColumnExpression)); } else if (newColumns != null) { newColumns.Add(column); } } var from = this.VisitSource(selectExpression.From); var where = this.Visit(selectExpression.Where); var orderBy = this.VisitExpressionList(selectExpression.OrderBy); var groupBy = this.VisitExpressionList(selectExpression.GroupBy); var skip = this.Visit(selectExpression.Skip); var take = this.Visit(selectExpression.Take); if (where is BitBooleanExpression) { where = Expression.Equal(where, Expression.Constant(true, where.Type)); } if (from != selectExpression.From || where != selectExpression.Where || newColumns != selectExpression.Columns || orderBy != selectExpression.OrderBy || groupBy != selectExpression.GroupBy || take != selectExpression.Take || skip != selectExpression.Skip) { return(new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, newColumns ?? selectExpression.Columns, from, where, orderBy, groupBy, selectExpression.Distinct, skip, take, selectExpression.ForUpdate, selectExpression.Reverse, selectExpression.Into)); } return(base.VisitSelect(selectExpression)); }
protected virtual void VisitColumn(SqlSelectExpression selectExpression, SqlColumnDeclaration column) { var c = this.Visit(column.Expression) as SqlColumnExpression; if ((c == null || c.Name != column.Name) && !String.IsNullOrEmpty(column.Name)) { this.Write(" AS "); this.WriteQuotedIdentifier(column.Name); } }
protected virtual SqlColumnDeclaration VisitColumnDeclaration(SqlColumnDeclaration sqlColumnDeclaration) { var e = this.Visit(sqlColumnDeclaration.Expression); if (e != sqlColumnDeclaration.Expression) { return(new SqlColumnDeclaration(sqlColumnDeclaration.Name, e)); } return(sqlColumnDeclaration); }
protected override Expression VisitSelect(SqlSelectExpression selectExpression) { if (selectExpression.Skip != null) { var rowNumber = new SqlFunctionCallExpression(typeof(int), "ROW_NUMBER"); var oldAliases = (selectExpression.From as ISqlExposesAliases)?.Aliases; var innerSelectWithRowAlias = selectExpression.Alias + "_ROW"; var cols = selectExpression.Columns.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, selectExpression.Alias, c.Name))).ToList(); var over = new SqlOverExpression(rowNumber, selectExpression.OrderBy?.Cast <SqlOrderByExpression>().ToReadOnlyCollection() ?? cols.Select(c => new SqlOrderByExpression(OrderType.Ascending, c.Expression)).ToReadOnlyCollection()); if (oldAliases != null) { over = (SqlOverExpression)AliasReferenceReplacer.Replace(over, oldAliases.Contains, selectExpression.Alias); } var rowColumn = new SqlColumnDeclaration(RowColumnName, over); cols.Add(rowColumn); var innerSelectWithRowColumns = cols.ToReadOnlyCollection(); var innerSelectWithRow = new SqlSelectExpression(selectExpression.Type, innerSelectWithRowAlias, innerSelectWithRowColumns, selectExpression.ChangeOrderBy(null).ChangeSkipTake(null, null), null, null, null, false, null, null, false); var outerColumns = selectExpression.Columns.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, innerSelectWithRowAlias, c.Name))); Expression rowPredicate = Expression.GreaterThan(new SqlColumnExpression(typeof(int), innerSelectWithRowAlias, rowColumn.Name), selectExpression.Skip); if (selectExpression.Take != null && !(selectExpression.Take is SqlTakeAllValueExpression)) { rowPredicate = Expression.And ( rowPredicate, Expression.LessThanOrEqual(new SqlColumnExpression(typeof(int), innerSelectWithRowAlias, rowColumn.Name), Expression.Add(selectExpression.Skip, selectExpression.Take)) ); } var newOrderBy = selectExpression .OrderBy? .Select(c => oldAliases == null ? c : AliasReferenceReplacer.Replace(c, oldAliases.Contains, innerSelectWithRowAlias)) .Concat(new SqlOrderByExpression(OrderType.Ascending, new SqlColumnExpression(typeof(int), innerSelectWithRowAlias, rowColumn.Name))) .ToReadOnlyCollection(); var retval = new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, outerColumns, innerSelectWithRow, rowPredicate, newOrderBy, selectExpression.ForUpdate); return(retval); } return(base.VisitSelect(selectExpression)); }
protected override void VisitColumn(SqlSelectExpression selectExpression, SqlColumnDeclaration column) { if (column.Expression.Type == typeof(Decimal)) { this.Write("ROUND(CAST("); var c = this.Visit(column.Expression) as SqlColumnExpression; this.Write(" as NUMERIC)"); this.Write(", 20)"); if (!String.IsNullOrEmpty(column.Name)) { this.Write(" AS "); this.Write(this.identifierQuoteString); this.Write(column.Name); this.Write(this.identifierQuoteString); } } else { base.VisitColumn(selectExpression, column); } }
protected override Expression VisitSelect(SqlSelectExpression select) { select = (SqlSelectExpression)base.VisitSelect(select); if (this.aggregateSubqueriesBySelectAlias.Contains(select.Alias)) { var columnsIncludingAggregates = new List <SqlColumnDeclaration>(select.Columns); foreach (var aggregateSubqueryExpression in this.aggregateSubqueriesBySelectAlias[select.Alias]) { var name = "AGGR" + columnsIncludingAggregates.Count; var columnDeclaration = new SqlColumnDeclaration(name, aggregateSubqueryExpression.AggregateInGroupSelect); this.aggregateSubqueryInstances.Add(aggregateSubqueryExpression, new SqlColumnExpression(aggregateSubqueryExpression.Type, aggregateSubqueryExpression.GroupByAlias, name)); columnsIncludingAggregates.Add(columnDeclaration); } return(new SqlSelectExpression(select.Type, select.Alias, columnsIncludingAggregates.ToReadOnlyList(), select.From, select.Where, select.OrderBy, select.GroupBy, select.Distinct, select.Skip, select.Take, select.ForUpdate)); } return(select); }
protected override Expression VisitSelect(SqlSelectExpression select) { // Visit column projection first var columns = select.Columns; List <SqlColumnDeclaration> alternate = null; for (int i = 0, n = select.Columns.Count; i < n; i++) { var columnDeclaration = select.Columns[i]; if (select.Distinct || this.IsColumnUsed(select.Alias, columnDeclaration.Name)) { var expr = this.Visit(columnDeclaration.Expression); if (expr != columnDeclaration.Expression) { columnDeclaration = new SqlColumnDeclaration(columnDeclaration.Name, expr); } } else { columnDeclaration = null; // null means it gets omitted } if (columnDeclaration != select.Columns[i] && alternate == null) { alternate = new List <SqlColumnDeclaration>(); for (var j = 0; j < i; j++) { alternate.Add(select.Columns[j]); } } if (columnDeclaration != null && alternate != null) { alternate.Add(columnDeclaration); } } if (alternate != null) { columns = alternate.ToReadOnlyList(); } var take = this.Visit(select.Take); var skip = this.Visit(select.Skip); var groupbys = this.VisitExpressionList(select.GroupBy); var orderbys = this.VisitExpressionList(select.OrderBy); var where = this.Visit(select.Where); var from = this.Visit(select.From); this.ClearColumnsUsed(select.Alias); if (columns != select.Columns || orderbys != select.OrderBy || groupbys != select.GroupBy || where != select.Where || from != select.From || take != select.Take || skip != select.Skip) { select = new SqlSelectExpression(select.Type, select.Alias, columns, from, where, orderbys, groupbys, select.Distinct, skip, take, select.ForUpdate); } return(select); }
protected override Expression VisitSelect(SqlSelectExpression select) { // Visit column projection first var columns = select.Columns; List <SqlColumnDeclaration> alternate = null; for (int i = 0, n = select.Columns.Count; i < n; i++) { var columnDeclaration = select.Columns[i]; if (select.Distinct || IsColumnUsed(select.Alias, columnDeclaration.Name) || columnDeclaration.NoOptimise) { var expr = Visit(columnDeclaration.Expression); if (expr != columnDeclaration.Expression) { columnDeclaration = new SqlColumnDeclaration(columnDeclaration.Name, expr); } } else { // null means it gets omitted columnDeclaration = null; } if (columnDeclaration != select.Columns[i] && alternate == null) { alternate = new List <SqlColumnDeclaration>(); for (var j = 0; j < i; j++) { alternate.Add(select.Columns[j]); } } if (columnDeclaration != null) { alternate?.Add(columnDeclaration); } } if (alternate != null) { // Conservatively, don't remove any columns if it would mean removing all of them if (alternate.Count == 0) { // Visit all columns so that their references/expressions can be marked as used foreach (var c in select.Columns) { Visit(c.Expression); } } else { columns = alternate.ToReadOnlyCollection(); } } var take = Visit(select.Take); var skip = Visit(select.Skip); var groupbys = VisitExpressionList(select.GroupBy); var orderbys = VisitExpressionList(select.OrderBy); var where = Visit(select.Where); var from = Visit(select.From); ClearColumnsUsed(select.Alias); if (columns != select.Columns || orderbys != select.OrderBy || groupbys != select.GroupBy || where != select.Where || from != select.From || take != select.Take || skip != select.Skip) { select = new SqlSelectExpression(select.Type, select.Alias, columns, from, where, orderbys, groupbys, select.Distinct, skip, take, select.ForUpdate); } return(select); }
protected override Expression VisitSelect(SqlSelectExpression selectExpression) { if (selectExpression.Skip != null) { const string orderByColumnPrefix = "__$$ORDERBYCOL"; var rowNumber = new SqlFunctionCallExpression(typeof(int), "ROW_NUMBER"); var selectWithRowAlias = selectExpression.Alias + "_ROW"; var cols = selectExpression .Columns .Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, selectExpression.Alias, c.Name))) .ToList(); var aliasedNames = selectExpression.OrderBy == null ? null : new HashSet <string>(selectExpression .Columns .Select(c => c.Expression) .OfType <SqlColumnExpression>() .Select(c => c.AliasedName)); var orderByCols = selectExpression .OrderBy? .Where(c => !IsColumnAndAlreadyProjected(c.Expression, aliasedNames)) .Select((c, i) => new SqlColumnDeclaration(orderByColumnPrefix + i, c.Expression)) ?? Enumerable.Empty <SqlColumnDeclaration>(); SqlOverExpression over; if (selectExpression.OrderBy?.Any() == true) { var i = 0; over = new SqlOverExpression(rowNumber, selectExpression .OrderBy .Select(c => { if (IsColumnAndAlreadyProjected(c.Expression, aliasedNames)) { return(new SqlOrderByExpression(c.OrderType, new SqlColumnExpression(c.Type, selectExpression.Alias, ((SqlColumnExpression)c.Expression).Name))); } else { return(new SqlOrderByExpression(c.OrderType, new SqlColumnExpression(c.Type, selectExpression.Alias, orderByColumnPrefix + i++))); } }).ToReadOnlyCollection()); } else { over = new SqlOverExpression(rowNumber, cols.Select(c => new SqlOrderByExpression(OrderType.Ascending, c.Expression)).ToReadOnlyCollection()); } var innerSelect = orderByCols == null ? selectExpression : selectExpression.ChangeColumns(selectExpression.Columns.Concat(orderByCols)); var rowColumn = new SqlColumnDeclaration(RowColumnName, over); cols.Add(rowColumn); var selectWithRowColumns = cols.ToReadOnlyCollection(); var selectWithRow = new SqlSelectExpression(selectExpression.Type, selectWithRowAlias, selectWithRowColumns, Visit(innerSelect.ChangeOrderBy(null).ChangeSkipTake(null, null)), null, null, null, false, null, null, false); var outerColumns = selectExpression.Columns.Select(c => new SqlColumnDeclaration(c.Name, new SqlColumnExpression(c.Expression.Type, selectWithRowAlias, c.Name))); Expression rowPredicate = Expression.GreaterThan(new SqlColumnExpression(typeof(int), selectWithRowAlias, rowColumn.Name), selectExpression.Skip); if (selectExpression.Take != null && !(selectExpression.Take is SqlTakeAllValueExpression)) { rowPredicate = Expression.And ( rowPredicate, Expression.LessThanOrEqual(new SqlColumnExpression(typeof(int), selectWithRowAlias, rowColumn.Name), Expression.Add(selectExpression.Skip, selectExpression.Take)) ); } var retval = new SqlSelectExpression(selectExpression.Type, selectExpression.Alias, outerColumns.ToReadOnlyCollection(), selectWithRow, rowPredicate, null, null, selectExpression.Distinct, null, null, selectExpression.ForUpdate, selectExpression.Reverse, selectExpression.Into); return(retval); } return(base.VisitSelect(selectExpression)); }
protected override Expression VisitSelect(SqlSelectExpression selectExpression) { List <SqlColumnDeclaration> newColumns = null; for (var i = 0; i < selectExpression.Columns.Count; i++) { var column = selectExpression.Columns[i]; var visitedColumnExpression = this.Visit(column.Expression); if (visitedColumnExpression.Type.GetUnwrappedNullableType() == typeof(bool) && !(visitedColumnExpression is BitBooleanExpression)) { if (newColumns == null) { newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i)); } var newColumnExpression = BitBooleanExpression.Coerce(visitedColumnExpression); var newColumnDeclaration = new SqlColumnDeclaration(column.Name, newColumnExpression); newColumns.Add(newColumnDeclaration); } else if (visitedColumnExpression != column.Expression) { if (newColumns == null) { newColumns = new List <SqlColumnDeclaration>(selectExpression.Columns.Take(i)); } newColumns.Add(column.ReplaceExpression(visitedColumnExpression)); } else if (newColumns != null) { newColumns.Add(column); } } var where = this.Visit(selectExpression.Where); if ((where is BitBooleanExpression)) { where = Expression.Equal(where, Expression.Constant(true)); } if (where != selectExpression.Where) { if (newColumns != null) { return(selectExpression.ChangeWhereAndColumns(where, new ReadOnlyList <SqlColumnDeclaration>(newColumns))); } else { return(selectExpression.ChangeWhere(where)); } } else if (newColumns != null) { return(selectExpression.ChangeColumns(newColumns, true)); } else { return(base.VisitSelect(selectExpression)); } }