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