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 virtual Expression VisitOver(SqlOverExpression expression) { var source = this.Visit(expression.Source); var orderBy = this.VisitExpressionList(expression.OrderBy); if (source != expression.Source || orderBy != expression.OrderBy) { return(new SqlOverExpression(source, orderBy)); } return(expression); }
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 Expression VisitOver(SqlOverExpression selectExpression) { Visit(selectExpression.Source); Write(" OVER (ORDER BY "); WriteDeliminatedListOfItems <Expression>(selectExpression.OrderBy, c => { Visit(c); }); Write(")"); return(selectExpression); }
protected override Expression VisitOver(SqlOverExpression selectExpression) { this.Visit(selectExpression.Source); this.Write(" OVER (ORDER BY "); this.WriteDeliminatedListOfItems <Expression>(selectExpression.OrderBy, c => { this.Visit(c); if (((SqlOrderByExpression)c).OrderType == OrderType.Descending) { this.Write(" DESC"); } }); this.Write(")"); return(selectExpression); }
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)); }