Dapper.SqlBuilderで膨大な数のレコードのページ表示を適応的にする

膨大な数のレコードをページ表示する際に、アプリケーション側にページサイズよりも大きい数のデータを読み込むことは効率的ではありません。
今回はDapper.SqlBuilderを用いてページ表示を効率化させてみます。

Dapper.SqlBuilderではWhere句やOrderBy句などの組み立てを簡素化できます。
以下の例では各ページに表示させるレコード(todos)と、条件に一致するレコードの総数(count)を取得しています。

int? userId = 1;
bool? isDone = null;
bool orderDesc = false;

var page = 3;
var pageSize = 10;

var start = (page - 1) * pageSize + 1;
var end = page * pageSize;

var pageSql =
$@"
SELECT
  todo.*
FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER (/**orderby**/) AS [RowNumber]
  FROM TodoItems as t
  /**where**/
) AS todo
WHERE todo.[RowNumber] BETWEEN @{nameof(start)} AND @{nameof(end)};
";

var countSql =
@"
SELECT
  COUNT(*)
FROM TodoItems as t
/**where**/
";

var builder = new SqlBuilder();

var pageTemplate = builder.AddTemplate(pageSql);
var countTemplate = builder.AddTemplate(countSql);

if (userId.HasValue)
{
  builder.Where($"t.[UserId] = @{nameof(userId)}");
}

if(isDone.HasValue)
{
  builder.Where($"t.[Done] = @{nameof(isDone)}");
}

builder.OrderBy(string.Format("t.[Id] {0}", orderDesc ? "desc" : "asc"));

using (var conn = new SqlConnection(connectionstring))
{
  var todos = conn.Query<TodoItem>(pageTemplate.RawSql, new { start, end, userId, isDone });
  var count = conn.QueryFirst<int>(countTemplate.RawSql, new { userId, isDone });
}

追記

mrgchr.hatenablog.com