public void ShouldApplyLimitUsingTop()
{
var sql = "select a,b,c from d where a = 1 order by c";
var expected = new[]{ "select top 5 a,b,c from d where a = 1 order by c"};
var pagedSql = new SqlQueryPager().ApplyLimit(sql, 5);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.IsTrue(expected.SequenceEqual(modified));
}
public void ShouldCopeWithAliasedColumns()
{
var sql = "select [dbo].[d].[a],[dbo].[d].[b] as [foo],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1";
var expected =new[]{
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[a]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
+ " select [dbo].[d].[a],[dbo].[d].[b] as [foo],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 21 and 25"};
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[]{"[dbo].[d].[a]"}, 20, 5);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
Assert.AreEqual(expected[0], modified[0]);
}
public void ShouldApplyPagingUsingOrderByKeysIfNotAlreadyOrdered()
{
var sql = "select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from [dbo].[d] where [dbo].[d].[a] = 1";
var expected = new[]{
"with __data as (select [dbo].[d].[a], row_number() over(order by [dbo].[d].[a]) as [_#_] from [dbo].[d] where [dbo].[d].[a] = 1)"
+ " select [dbo].[d].[a],[dbo].[d].[b],[dbo].[d].[c] from __data join [dbo].[d] on [dbo].[d].[a] = __data.[a] where [dbo].[d].[a] = 1 and [_#_] between 11 and 30"};
var pagedSql = new SqlQueryPager().ApplyPaging(sql, new[] {"[dbo].[d].[a]"}, 10, 20);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant()).ToArray();
Assert.AreEqual(expected[0], modified[0]);
}
public void ShouldCopeWithAliasedDefaultSortColumn()
{
var sql = "select [a] as [foo],[b],[c] from [d] where [a] = 1";
var expected = new[]{
"with __data as (select [a] as [foo],[b],[c], row_number() over(order by [a]) as [_#_] from [d] where [a] = 1)"
+ " select [foo],[b],[c] from __data where [_#_] between 31 and 40"};
var pagedSql = new SqlQueryPager().ApplyPaging(sql, 30, 10);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.IsTrue(expected.SequenceEqual(modified));
}
public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered()
{
var sql = "select a,b,c from d where a = 1";
var expected = new[]{
"with __data as (select a,b,c, row_number() over(order by a) as [_#_] from d where a = 1)"
+ " select a,b,c from __data where [_#_] between 11 and 30"};
var pagedSql = new SqlQueryPager().ApplyPaging(sql, 10, 20);
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
Assert.IsTrue(expected.SequenceEqual(modified));
}