Get Paginated SQL Server Result AND total count in one query via Dapper?

dapper pagination sql-server

Question

If I had a User model that looks like this:

public class User
{
    public Guid Id { get; set; }
    public DateTime CreatedAtUtc { get; set; }
    public string Username { get; set; }
    public string Country { get; set; }
}

...and I'd perform a query which starts at a given row and fetches a limited amount of further rows (basically for paginating over the results) that looks like this:

var spaniards = connection.Query<User>(
                    "select * from Users where Country=@Country OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY",
                    new { Country = "Spain" }).ToList();

.. using Dapper(.Net), would it be possible to get that particular, limited result set AND the total count of rows in one single query and if so.. how?

Popular Answer

One way to do solve this is using the splitOn functionality, together with count(1) over()

Let's assume the following sql string:

var sql = "select *, overall_count = COUNT(1) OVER() from Users ORDER BY Id Asc OFFSET 5 ROWS;"

together with the following dapper.net call:

HashSet<int> hashSet = new HashSet<int>();
Func<User, int, User> map = (result, count) =>
{
   hashSet.Add(count);
   return result;
};
await connection.QueryAsync(sql, map, "overall_count").ConfigureAwait(false);

This will split the result of the dapper call into two parts (just like with joining different tables) and it will call the map functor each time, in this case we simply store the count in a hashset (you could then check if the hashSet.Count is 1)

Hope this helps

PS: I'm not certain that your OFFSET works without any ORDER BY




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why