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