protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
base.Write("ROW_NUMBER() OVER(");
if ((rowNumber.OrderBy != null) && (rowNumber.OrderBy.Count > 0))
{
base.Write("ORDER BY ");
int num = 0;
int count = rowNumber.OrderBy.Count;
while (num < count)
{
OrderExpression expression = rowNumber.OrderBy[num];
if (num > 0)
{
base.Write(", ");
}
this.VisitValue(expression.Expression);
if (expression.OrderType != OrderType.Ascending)
{
base.Write(" DESC");
}
num++;
}
}
base.Write(")");
return(rowNumber);
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
Write("ROW_NUMBER() OVER(");
if (rowNumber.OrderBy != null && rowNumber.OrderBy.Count > 0)
{
Write("ORDER BY ");
for (int i = 0, n = rowNumber.OrderBy.Count; i < n; i++)
{
var exp = rowNumber.OrderBy[i];
if (i > 0)
{
Write(", ");
}
VisitValue(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
Write(" DESC");
}
}
}
else
{
Write("ORDER BY (SELECT 1)");
}
Write(")");
return(rowNumber);
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumberExpression)
{
Check.NotNull(rowNumberExpression, nameof(rowNumberExpression));
var canOptimize = _canOptimize;
_canOptimize = false;
var changed = false;
var partitions = new List <SqlExpression>();
foreach (var partition in rowNumberExpression.Partitions)
{
var newPartition = (SqlExpression)Visit(partition);
changed |= newPartition != partition;
partitions.Add(newPartition);
}
var orderings = new List <OrderingExpression>();
foreach (var ordering in rowNumberExpression.Orderings)
{
var newOrdering = (OrderingExpression)Visit(ordering);
changed |= newOrdering != ordering;
orderings.Add(newOrdering);
}
_canOptimize = canOptimize;
return(rowNumberExpression.Update(partitions, orderings));
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumberExpression)
{
Check.NotNull(rowNumberExpression, nameof(rowNumberExpression));
var parentSearchCondition = _isSearchCondition;
_isSearchCondition = false;
var changed = false;
var partitions = new List <SqlExpression>();
foreach (var partition in rowNumberExpression.Partitions)
{
var newPartition = (SqlExpression)Visit(partition);
changed |= newPartition != partition;
partitions.Add(newPartition);
}
var orderings = new List <OrderingExpression>();
foreach (var ordering in rowNumberExpression.Orderings)
{
var newOrdering = (OrderingExpression)Visit(ordering);
changed |= newOrdering != ordering;
orderings.Add(newOrdering);
}
_isSearchCondition = parentSearchCondition;
return(ApplyConversion(rowNumberExpression.Update(partitions, orderings), condition: false));
}
protected internal virtual Expression VisitRowNumber(RowNumberExpression rowNumber)
{
var orderBys = Visit(rowNumber.OrderBy, VisitOrderBy);
if (orderBys != rowNumber.OrderBy)
{
return(new RowNumberExpression(orderBys));
}
return(rowNumber);
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public virtual Expression VisitRowNumber(RowNumberExpression rowNumberExpression)
{
Check.NotNull(rowNumberExpression, nameof(rowNumberExpression));
Sql.Append("ROW_NUMBER() OVER(");
GenerateOrderBy(rowNumberExpression.Orderings);
Sql.Append(")");
return(rowNumberExpression);
}
public virtual Expression VisitRowNumber(RowNumberExpression rowNumberExpression)
{
Check.NotNull(rowNumberExpression, nameof(rowNumberExpression));
Sql.Append("ROW_NUMBER() OVER(");
GenerateOrderBy(rowNumberExpression.Orderings);
Sql.Append(") AS ").Append(SqlGenerator.DelimitIdentifier(rowNumberExpression.ColumnExpression.Name));
return(rowNumberExpression);
}
protected internal override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
var orderBys = RemoveDuplicates(Visit(rowNumber.OrderBy, VisitOrderBy));
if (orderBys != rowNumber.OrderBy)
{
return(new RowNumberExpression(orderBys));
}
return(rowNumber);
}
protected internal override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
var orderBys = Visit(rowNumber.OrderBy, o => IsConstant(o.Expression) ? null ! : Visit(o.Expression).Let(e => e == o.Expression ? o : new OrderExpression(o.OrderType, e)));;
if (orderBys != rowNumber.OrderBy)
{
return(new RowNumberExpression(orderBys));
}
return(rowNumber);
}
/// <summary>
/// 更新 Select 语句
/// </summary>
/// <param name="selectExpression"></param>
/// <returns></returns>
private Expression VisitSelect(SelectExpression selectExpression)
{
var oldOffset = selectExpression.Offset;
if (oldOffset == null)
{
return(selectExpression);
}
var oldLimit = selectExpression.Limit;
var oldOrderings = selectExpression.Orderings;
// 在子查询中 OrderBy 必须写 Top 数量
var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
? oldOrderings.ToList()
: new List <OrderingExpression>();
// 更新表达式
selectExpression = selectExpression.Update(selectExpression.Projection.ToList(),
selectExpression.Tables.ToList(),
selectExpression.Predicate,
selectExpression.GroupBy.ToList(),
selectExpression.Having,
orderings: newOrderings,
limit: null,
offset: null);
var rowOrderings = oldOrderings.Count != 0 ? oldOrderings
: new[] { new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true) };
_ = selectExpression.PushdownIntoSubquery(); // .NET 6 该方法已无返回值
var subQuery = (SelectExpression)selectExpression.Tables[0];
var projection = new RowNumberExpression(Array.Empty <SqlExpression>(), rowOrderings, oldOffset.TypeMapping);
var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left, oldOffset));
if (oldLimit != null)
{
if (oldOrderings.Count == 0)
{
selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
}
else
{
// 这里不支持子查询的 OrderBy 操作
selectExpression.ApplyLimit(oldLimit);
}
}
return(selectExpression);
}
private Expression VisitSelect(SelectExpression selectExpression)
{
var oldOffset = selectExpression.Offset;
if (oldOffset == null)
{
return(selectExpression);
}
var oldLimit = selectExpression.Limit;
var oldOrderings = selectExpression.Orderings;
//order by in subQuery without TOP N is invalid.
var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
? oldOrderings.ToList()
: new List <OrderingExpression>();
selectExpression = selectExpression.Update(selectExpression.Projection.ToList(),
selectExpression.Tables.ToList(),
selectExpression.Predicate,
selectExpression.GroupBy.ToList(),
selectExpression.Having,
orderings: newOrderings,
limit: null,
offset: null,
selectExpression.IsDistinct,
selectExpression.Alias);
var rowOrderings = oldOrderings.Count != 0 ? oldOrderings
: new[] { new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true) };
_ = selectExpression.PushdownIntoSubquery();
var subQuery = (SelectExpression)selectExpression.Tables[0];
var projection = new RowNumberExpression(Array.Empty <SqlExpression>(), rowOrderings, oldOffset.TypeMapping);
var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left, oldOffset));
if (oldLimit != null)
{
if (oldOrderings.Count == 0)
{
selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
}
else
{
//the above one not working when used as subQuery with orderBy
selectExpression.ApplyLimit(oldLimit);
}
}
return(selectExpression);
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumberExpression)
{
_relationalCommandBuilder.Append("ROW_NUMBER() OVER(");
if (rowNumberExpression.Partitions.Any())
{
_relationalCommandBuilder.Append("PARTITION BY ");
GenerateList(rowNumberExpression.Partitions, e => Visit(e));
_relationalCommandBuilder.Append(" ");
}
_relationalCommandBuilder.Append("ORDER BY ");
GenerateList(rowNumberExpression.Orderings, e => Visit(e));
_relationalCommandBuilder.Append(")");
return(rowNumberExpression);
}
protected internal override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
sb.Append("ROW_NUMBER() OVER(ORDER BY ");
for (int i = 0, n = rowNumber.OrderBy.Count; i < n; i++)
{
OrderExpression exp = rowNumber.OrderBy[i];
if (i > 0)
{
sb.Append(", ");
}
this.Visit(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
sb.Append(" DESC");
}
}
sb.Append(')');
return(rowNumber);
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
this.Append("ROW_NUMBER() OVER(");
if (rowNumber.OrderBy != null && rowNumber.OrderBy.Count > 0)
{
this.Append("ORDER BY ");
for (int i = 0, n = rowNumber.OrderBy.Count; i < n; i++)
{
OrderExpression exp = rowNumber.OrderBy[i];
if (i > 0)
{
this.Append(", ");
}
this.VisitValue(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
this.Append(" DESC");
}
}
}
this.Append(")");
return(rowNumber);
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
this.Append("ROW_NUMBER() OVER(");
if (rowNumber.OrderBy != null && rowNumber.OrderBy.Count > 0)
{
this.Append("ORDER BY ");
for (int i = 0, n = rowNumber.OrderBy.Count; i < n; i++)
{
OrderExpression exp = rowNumber.OrderBy[i];
if (i > 0)
{
this.Append(", ");
}
this.VisitValue(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
this.Append(" DESC");
}
}
}
this.Append(")");
return rowNumber;
}
private bool CompareRowNumber(RowNumberExpression a, RowNumberExpression b)
{
return this.CompareOrderList(a.OrderBy, b.OrderBy);
}
protected internal override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
var orderBys = Visit(rowNumber.OrderBy, o => IsConstant(o.Expression) ? null : Visit(o.Expression).Let(e => e == o.Expression ? o : new OrderExpression(o.OrderType, e))); ;
if (orderBys != rowNumber.OrderBy)
return new RowNumberExpression(orderBys);
return rowNumber;
}
protected virtual bool CompareRowNumber(RowNumberExpression a, RowNumberExpression b)
{
return(CompareOrderList(a.OrderBy, b.OrderBy));
}
protected abstract Expression VisitRowNumber(RowNumberExpression rowNumberExpression);
/// <summary>
/// Visits the row number.
/// </summary>
/// <param name="rowNumber">The row number.</param>
/// <returns></returns>
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
throw new NotSupportedException();
}
private Expression VisitSelectExpression(SelectExpression selectExpression)
{
base.Visit(selectExpression);
if (!RequiresRowNumberPaging(selectExpression))
{
return(selectExpression);
}
var projections = new List <Expression>();
projections.AddRange(selectExpression.Projection);
var subQuery = selectExpression.PushDownSubquery();
foreach (var projection in projections)
{
var alias = projection as AliasExpression;
var column = projection as ColumnExpression;
if (column != null)
{
column = new ColumnExpression(column.Name, column.Property, subQuery);
selectExpression.AddToProjection(column);
continue;
}
column = alias?.TryGetColumnExpression();
if (column != null)
{
column = new ColumnExpression(column.Name, column.Property, subQuery);
alias = new AliasExpression(alias.Alias, column);
selectExpression.AddToProjection(alias);
}
else
{
selectExpression.AddToProjection(projection);
}
}
if (subQuery.OrderBy.Count == 0)
{
subQuery.AddToOrderBy(
new Ordering(new SqlFunctionExpression("@@RowCount", typeof(int)), OrderingDirection.Asc));
}
var columnExpression = new ColumnExpression(RowNumberColumnName, typeof(int), subQuery);
var rowNumber = new RowNumberExpression(columnExpression, subQuery.OrderBy);
subQuery.ClearOrderBy();
subQuery.ClearProjection();
subQuery.AddToProjection(rowNumber);
subQuery.IsProjectStar = true;
Expression predicate = null;
var offset = subQuery.Offset ?? 0;
if (subQuery.Offset.HasValue)
{
predicate = Expression.GreaterThan(columnExpression, Expression.Constant(offset));
}
if (subQuery.Limit.HasValue)
{
var exp = Expression.LessThanOrEqual(columnExpression, Expression.Constant(offset + subQuery.Limit.Value));
if (predicate != null)
{
exp = Expression.AndAlso(predicate, exp);
}
predicate = exp;
}
selectExpression.Predicate = predicate;
return(selectExpression);
}
protected virtual Expression VisitRowNumber(RowNumberExpression rowNumberExpression) => CheckSupport(rowNumberExpression, true);
protected virtual Expression VisitRowNumber(RowNumberExpression rowNumber)
{
var orderby = this.VisitOrderBy(rowNumber.OrderBy);
if (orderby != rowNumber.OrderBy)
{
return new RowNumberExpression(orderby);
}
return rowNumber;
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
AppendKeys();
return new RowNumberExpression(gatheredOrderings);
}
protected virtual Expression VisitRowNumber(RowNumberExpression rowNumber)
{
var orderby = this.VisitOrderBy(rowNumber.OrderBy);
return this.UpdateRowNumber(rowNumber, orderby);
}
protected RowNumberExpression UpdateRowNumber(RowNumberExpression rowNumber, IEnumerable<OrderExpression> orderBy)
{
if (orderBy != rowNumber.OrderBy)
{
return new RowNumberExpression(orderBy);
}
return rowNumber;
}
protected virtual Expression VisitRowNumber(RowNumberExpression rowNumberExpression) => CheckSupport(rowNumberExpression, _connectionInfo.ServerVersion.SupportsWindowFunctions);
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
this.Write("RecNo()");
return(rowNumber);
}
protected virtual bool CompareRowNumber(RowNumberExpression a, RowNumberExpression b)
{
return this.CompareOrderList(a.OrderBy, b.OrderBy);
}
/// <summary> /// Visits the children of the row number expression. /// </summary> /// <param name="rowNumberExpression"> The expression to visit. </param> /// <returns> The modified expression, if it or any subexpression was modified; otherwise, returns the original expression. </returns> protected abstract Expression VisitRowNumber([NotNull] RowNumberExpression rowNumberExpression);
protected internal override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
AppendKeys();
return(new RowNumberExpression(gatheredOrderings));
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
this.Write("RecNo()");
return rowNumber;
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumberExpression)
{
return(rowNumberExpression?.Update(
rowNumberExpression.Partitions.VisitAll(this),
rowNumberExpression.Orderings.VisitAll(this)));
}
protected virtual Expression VisitRowNumber(RowNumberExpression rowNumber)
{
var orderBys = rowNumber.OrderBy.NewIfChange(VisitOrderBy);
if (orderBys != rowNumber.OrderBy)
return new RowNumberExpression(orderBys);
return rowNumber;
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
throw new NotSupportedException();
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumberExpression)
{
return(rowNumberExpression);
}
private Expression VisitSelectExpression(SelectExpression selectExpression)
{
base.Visit(selectExpression);
if (!RequiresRowNumberPaging(selectExpression))
{
return(selectExpression);
}
var subQuery = selectExpression.PushDownSubquery();
foreach (var projection in subQuery.Projection)
{
var alias = projection as AliasExpression;
var column = projection as ColumnExpression;
if (column != null)
{
column = new ColumnExpression(column.Name, column.Property, subQuery);
selectExpression.AddToProjection(column);
continue;
}
column = alias?.TryGetColumnExpression();
if (column != null)
{
column = new ColumnExpression(column.Name, column.Property, subQuery);
alias = new AliasExpression(alias.Alias, column);
selectExpression.AddToProjection(alias);
}
else
{
column = new ColumnExpression(alias.Alias, alias.Expression.Type, subQuery);
selectExpression.AddToProjection(column);
}
}
if (subQuery.OrderBy.Count == 0)
{
subQuery.AddToOrderBy(
new Ordering(new SqlFunctionExpression("@@RowCount", typeof(int)), OrderingDirection.Asc));
}
var columnExpression = new ColumnExpression(RowNumberColumnName, typeof(int), subQuery);
var rowNumber = new RowNumberExpression(columnExpression, subQuery.OrderBy);
subQuery.ClearOrderBy();
subQuery.AddToProjection(rowNumber, false);
Expression predicate = null;
var offset = subQuery.Offset ?? Expression.Constant(0);
if (subQuery.Offset != null)
{
predicate = Expression.GreaterThan(columnExpression, offset);
}
if (subQuery.Limit != null)
{
var constantValue = (subQuery.Limit as ConstantExpression)?.Value;
var offsetValue = (offset as ConstantExpression)?.Value;
var limitExpression
= constantValue != null &&
offsetValue != null
? (Expression)Expression.Constant((int)offsetValue + (int)constantValue)
: Expression.Add(offset, subQuery.Limit);
var expression = Expression.LessThanOrEqual(columnExpression, limitExpression);
if (predicate != null)
{
expression = Expression.AndAlso(predicate, expression);
}
predicate = expression;
}
selectExpression.Predicate = predicate;
return(selectExpression);
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
sb.Append("ROW_NUMBER() OVER(ORDER BY ");
for (int i = 0, n = rowNumber.OrderBy.Count; i < n; i++)
{
OrderExpression exp = rowNumber.OrderBy[i];
if (i > 0)
sb.Append(", ");
this.Visit(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
sb.Append(" DESC");
}
sb.Append(")");
return rowNumber;
}
