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