How to do data pagination with PagedList in Asp.Net MVC?

Navigation
0

I have a user table with 10,000 entries. I want to paginate these records using LIMIT and OFFSET with PagedList but I can not do that. Currently the system searches all the records to later paginate and I do not want it to be this way, 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-->
    
asked by anonymous 06.02.2018 / 18:17

2 answers

1

If you can do this, since the GetAll() method returns a IQueryable<T> has not yet been executed by the bank and can quietly work with all the features out: 

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);

}

In this way the data will always come in paged and will not fetch all the data from the database and then paginate.

    
06.02.2018 / 20:36
0

As I use a Model to display the data, I ended up using the StaticPagedList that is part of the PagedList library, and this solved my problem, in addition to 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);
        }
    
07.02.2018 / 13:51
White Screen when loading app in Android Studio How to export a SQL table to a .txt file?