private SqlString PageByLimitAndOffset(SqlString offset, SqlString limit) { int fromIndex = GetFromIndex(); SqlString select = _sourceQuery.Substring(0, fromIndex); List <SqlString> columnsOrAliases; Dictionary <SqlString, SqlString> aliasToColumn; Dictionary <SqlString, SqlString> columnToAlias; Dialect.ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn, out columnToAlias); int orderIndex = _sourceQuery.LastIndexOfCaseInsensitive(" order by "); SqlString fromAndWhere; SqlString[] sortExpressions; //don't use the order index if it is contained within a larger statement(assuming //a statement with non matching parenthesis is part of a larger block) if (orderIndex > 0 && HasMatchingParens(_sourceQuery.Substring(orderIndex).ToString())) { fromAndWhere = _sourceQuery.Substring(fromIndex, orderIndex - fromIndex).Trim(); SqlString orderBy = _sourceQuery.Substring(orderIndex).Trim().Substring(9); sortExpressions = orderBy.SplitWithRegex(@"(?<!\([^\)]*),{1}"); } else { fromAndWhere = _sourceQuery.Substring(fromIndex).Trim(); // Use dummy sort to avoid errors sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP") }; } var result = new SqlStringBuilder(); result.Add("SELECT "); if (limit != null) { result.Add("TOP (").Add(limit).Add(") "); } else { // ORDER BY can only be used in subqueries if TOP is also specified. result.Add("TOP (" + int.MaxValue + ") "); } if (IsDistinct()) { result .Add(StringHelper.Join(", ", columnsOrAliases)) .Add(" FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY "); AppendSortExpressionsForDistinct(columnToAlias, sortExpressions, result); result.Add(") as __hibernate_sort_row ") .Add(" FROM (") .Add(select) .Add(" ") .Add(fromAndWhere) .Add(") as q_) as query WHERE query.__hibernate_sort_row > ") .Add(offset) .Add(" ORDER BY query.__hibernate_sort_row"); } else { result .Add(StringHelper.Join(", ", columnsOrAliases)) .Add(" FROM (") .Add(select) .Add(", ROW_NUMBER() OVER(ORDER BY "); AppendSortExpressions(aliasToColumn, sortExpressions, result); result .Add(") as __hibernate_sort_row ") .Add(fromAndWhere) .Add(") as query WHERE query.__hibernate_sort_row > ") .Add(offset) .Add(" ORDER BY query.__hibernate_sort_row"); } return(result.ToSqlString()); }