Generic pagination with the dapper

Question

Generic pagination with the dapper

asp.net-mvc dapper generics pagination

Question

I am using dapper and also dapper.contrib.My question is how can I create an generic pagination class. Here is the what I have tried so far.

 public class GenericRepository<T> :IGenericRepository<T> where T : class
    {
    public  async Task<IEnumerable<T>> GetAllPagedAsync(int limit,int offset)
        {
            var list = await Connection.GetAllAsync<T>();//but this return IEnumarable
            return list;

        }
}

What I am thinking is get the T name of the class which is the same as Table name,and write an sql string called sql_statement which is apply pagination.later apply this code.

 var list = await Connection.QueryAsync<T>("sql_statement")

Does this make sense? I s there any better way to achive that.

stackada

View more on Stack Overflow

corkington · Accepted Answer · 9/11/2015 3:26:07 PM

It looks currently as though you are planning to retrieve all of the rows in the table and then select from them the page of data you actually require. It would likely be quicker to just select the page you need straight from the database, unless you do actually need all of the rows for some reason.

Assuming that your table names are always going to match exactly with their respective class/entity names, the following will give you a paged result (using postgres):

public class GenericRepository<T> : IGenericRepository<T> where T : class
        {
        public async Task<IEnumerable<T>> GetAllPagedAsync(int limit, int offset)
            {
                var tableName = typeof(T).Name;             
                // assuming here you want the newest rows first, and column name is "created_date"
                // may also wish to specify the exact columns needed, rather than *
                var query = "SELECT * FROM @TableName ORDER BY created_date DESC Limit @Limit Offset @Offset";
                var results = Connection.QueryAsync<T>(query, new {Limit = limit, Offset = offset});
                return results;
            }
    }

A note regarding this. I am obviously not familiar with the structure or size of your database, however for most general purposes the limit/offset approach to paging shown here will most probably be sufficient. There are however some potential issues you may wish to consider:

When the offset value gets very large performance may suffer.
Paging tables with a high frequency of inserts in this fashion may cause results to be duplicated/ appear on multiple pages as the offset values does not take into account new rows added to the table since the last retrieval.

Whether or not these are likely to cause issues to your particular case, these potential drawbacks, as well as some alternatives solutions are outlined here.

Generic pagination with the dapper

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator