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