private SqlString PageByLimitAndOffset(SqlString offset, SqlString limit)
{
var queryParser = new MsSqlSelectParser(_sourceQuery);
if (queryParser.SelectIndex < 0) return null;
var result = new SqlStringBuilder();
BuildSelectClauseForPagingQuery(queryParser, limit, result);
if (queryParser.IsDistinct)
{
BuildFromClauseForPagingDistinctQuery(queryParser, result);
}
else
{
BuildFromClauseForPagingQuery(queryParser, result);
}
BuildWhereAndOrderClausesForPagingQuery(offset, result);
return result.ToSqlString();
}
private static void BuildSelectClauseForPagingQuery(MsSqlSelectParser sqlQuery, SqlString limit, SqlStringBuilder result)
{
result.Add(sqlQuery.Sql.Substring(0, sqlQuery.SelectIndex));
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 + ") ");
}
var sb = new StringBuilder();
foreach (var column in sqlQuery.SelectColumns)
{
if (sb.Length > 0) sb.Append(", ");
sb.Append(column.Alias);
}
result.Add(sb.ToString());
}
private static void BuildFromClauseForPagingQuery(MsSqlSelectParser sqlQuery, SqlStringBuilder result)
{
result.Add(" FROM (")
.Add(sqlQuery.SelectClause)
.Add(", ROW_NUMBER() OVER(ORDER BY ");
var orderIndex = 0;
foreach (var order in sqlQuery.Orders)
{
if (orderIndex++ > 0) result.Add(", ");
if (order.Column.Name != null)
{
result.Add(order.Column.Name);
}
else
{
result.Add(sqlQuery.Sql.Substring(order.Column.SqlIndex, order.Column.SqlLength).Trim());
}
if (order.IsDescending) result.Add(" DESC");
}
if (orderIndex == 0)
{
result.Add("CURRENT_TIMESTAMP");
}
result.Add(") as __hibernate_sort_row ")
.Add(sqlQuery.FromAndWhereClause)
.Add(") as query");
}
private static void BuildFromClauseForPagingDistinctQuery(MsSqlSelectParser sqlQuery, SqlStringBuilder result)
{
result.Add(" FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ");
int orderIndex = 0;
foreach (var order in sqlQuery.Orders)
{
if (orderIndex++ > 0) result.Add(", ");
if (!order.Column.InSelectClause)
{
throw new HibernateException(
"The dialect was unable to perform paging of a statement that requires distinct results, and " +
"is ordered by a column that is not included in the result set of the query.");
}
result.Add("q_.").Add(order.Column.Alias);
if (order.IsDescending) result.Add(" DESC");
}
if (orderIndex == 0)
{
result.Add("CURRENT_TIMESTAMP");
}
result.Add(") as __hibernate_sort_row FROM (")
.Add(sqlQuery.SelectClause)
.Add(" ")
.Add(sqlQuery.FromAndWhereClause)
.Add(") as q_) as query");
}
