public override string SelectSql(SqlTableNameWithAlias tableName, IEnumerable <SqlExpressionWithAlias> columns, string sqlWhere, IEnumerable <SqlJoinDefinition> joins = null, string sqlSortExpression = null, int?start = null, int?numRecords = null, string afterSelect = null)
{
if (start == null && numRecords == null)
{
return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression));
}
if (start == null)
{
return(base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, afterSelect: "TOP " + numRecords));
}
string subQueryAlias = SqlNameGenerator.NextTableAlias();
string rowNumAlias = SqlNameGenerator.NextFieldAlias();
int end = (numRecords == null ? int.MaxValue : (numRecords.Value + start - 1)).Value;
SqlExpressionWithAlias rowNumExpression = new SqlExpressionWithAlias("row_number() over (order by " + sqlSortExpression + ")", rowNumAlias);
string[] parts =
{
"select",
subQueryAlias + ".*",
"from",
"(",
base.SelectSql(tableName,columns.Union(new[] { rowNumExpression }), sqlWhere, joins),
")",
"as",
subQueryAlias,
"where",
rowNumAlias,
"between",
start.ToString(),
"and",
end.ToString(),
"order by",
rowNumAlias
};
return(string.Join(" ", parts));
}
public void SetUp()
{
_sqlNameGenerator = new SqlNameGenerator();
}