How to Paginate Data with Pagedlist in Asp.Net MVC?

Question

I have a user table with 10,000 entries. I want to paginate these records using LIMIT and OFFSET with Pagedlist but I’m not able to do that. Currently the system searches all records to then paginate and do not want it to be like this, I want to paginate in a real way.

How to do this ?

I’m trying like this.

Model Search

public class SearchUsuario{

    public IPagedList<ViewUsuario> lista        { get; set; }

    public SearchUsuario(){
        lista = new List<ViewUsuario>().ToPagedList(1, 50);
    }
}

Model View

public class ViewUsuario {

    public String nome { get; set; }
}

Generic DAO

//returns all records 
 public IQueryable<T> GetAll(){
        IQueryable<T> query = context.Set<T>();
        return query;
    }

Controller

public ActionResult view(int? page){
            int pageNumber = page ?? 1;
            int pageSize = 20;

            SearchUsuario search = new SearchUsuario();
            IQueryable<Usuario> lista = new UsuarioDAO().GetAll().OrderBy(u => u.nome).Skip(pageSize * pageNumber).Take(pageSize); //retorna todos os usuarios
            List<ViewUsuario> listaModel = new List<ViewUsuario>();
            foreach(Usuario u in lista){
                Debug.WriteLine(u.nome);
                ViewUsuario view = new ViewUsuario();
                view.nome = u.nome;
                listaModel.Add(view);
            }
            search.lista = listaModel.ToPagedList(pageNumber, pageSize);
            return View(search);
        }

View HTML

@using PagedList.Mvc
@model SearchUsuario

@{
    ViewBag.Title = "view";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">@Language.Users</div>
            <div class="panel-body">
                <div class="table-responsive">
                    <table id="grid" class="table table-striped table-hover" cellspacing="0">
                        <thead>
                            <tr>
                                <th>Nome</th>
                            </tr>
                        </thead>
                        <tfoot>
                            <tr>
                                <th>Nome</th>
                            </tr>
                        </tfoot>
                        <tbody>  
                            @foreach (ViewUsuario m in Model.lista){
                                <tr>
                                    <td>@Html.DisplayFor(i => m.nome)</td>
                                </tr>
                            }                         
                        </tbody>
                    </table>
                </div><!--/div table-responsive-->
            </div><!--div panel-body -->
            <div class="panel-footer">
                Pagina @Model.lista.PageNumber de @Model.lista.PageCount
                @Html.PagedListPager(Model.lista, page => Url.Action("view", new{
                                                                                page = page                                                                                       
                                                                             }))
            </div><!--/panel-footer-->

        </div><!--/div panel-->
    </div><!--/div col-->
</div><!--/div row-->

Answer 1

One can do so, since the method GetAll() returns a IQueryable<T> has not yet been run by the bank and can quietly work with all the features outside:

public ActionResult view(int? page)
{
    int p = page ?? 1;
    int pageSize = 20;

    SearchUsuario search = new SearchUsuario();

    IQueryable<Usuario> lista = new UsuarioDAO()
        .GetAll();      

    search.lista = lista.Select(x => new ViewUsuario 
    {
        nome = x.nome
    })
    .ToPagedList(p, pageSize);

    return View(search);

}

this way the data will always come paginated and will not bring all the data of the base to later paginar.

Answer 2

As I use a Model to display the data, I ended up using the StaticPagedList that is part of the Pagedlist library, and that solved my problem, besides being very performatic.

I did so.

public ActionResult view(int? page){            
            int pageNumber = page ?? 1;
            int pageSize = 20;
            int totalRecords = dao.GetAll().Count(); //qtd de registros

            //lista paginando registros
            IEnumerable<ViewUsuario> lista = dao.GetAll()
                                            .OrderBy(u => u.id)
                                            .Skip(pageSize * pageNumber)
                                            .Take(pageSize)
                                            .Select(x => new ViewUsuario{
                                                nome = x.nome,            
                                            });

            //model
            SearchUsuario search = new SearchUsuario();
            search.lista = new StaticPagedList<ViewUsuario>(lista, pageNumber, pageSize, totalRecords);            

            return View(search);
        }