1

I have one table on database with 10.000 records and I wanna paginate it using PagedList. The problem is that all records are loaded and after that the PagedList paginate them, it is become very slow. I want to make a real paginate for example returning each 20 records till 10.000 and showing them on view, but I cannot make it.

How could I do this ? Any example ?

Model Search

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

Model ViewUsuario

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 = 10;
    SearchUsuario search = new SearchUsuario();
    IQueryable<Usuario> lista = new UsuarioDAO().GetAll(); //returns all records
    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

@using PagedList.Mvc
@model SearchUsuario
....   
<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>
....
Pagina @Model.lista.PageNumber de @Model.lista.PageCount
@Html.PagedListPager(Model.lista, page => Url.Action("view", new{ page = page }))
FernandoPaiva
  • 4,410
  • 13
  • 59
  • 118
  • You need to adjust your db query to `.Skip()` and `.Take()`. You would calculate the pagination values based on the page number and page size. – Jasen Feb 06 '18 at 18:40
  • See here for an example https://stackoverflow.com/questions/2380413/paging-with-linq-for-objects – Jasen Feb 06 '18 at 18:42
  • i think your problem is in your `new UsuarioDAO().GetAll()` sentence: you need to return an `IQueryable` not a a materialized collection (like `List` or `Array`) if you use an IQueryable, you can narrow the search to only the items required in the page with the `ToPagedList`, so it would be helpful if you post the code of that class – Javier Gonzalez Feb 06 '18 at 18:55
  • @Jasen using as your link suggestion it works, but on `View` doesn't display the index of pages. for example: page 2, page 3, page 4...etc. I need to have 2 Lists or maybe a Count with numbers of records to create this ? – FernandoPaiva Feb 06 '18 at 19:27
  • @xavvvier `UsuarioDAO.GetAll()` returns a IQueryable, see this: ` public IQueryable GetAll(){ IQueryable query = context.Set(); return query; }` – FernandoPaiva Feb 06 '18 at 19:27
  • Have you checked the generated html to verify that the index links are missing? – Jasen Feb 06 '18 at 20:05
  • `PagedList` already uses `.Skip()` and `.Take()` internally to perform server side paging. But its designed to work with a `IQueryable` data model. If you have a need to create a view model (which does not seem necessary in your case), then look at using `StaticPagedList` –  Feb 06 '18 at 21:56
  • @StephenMuecke do you have any example about how to use `StaticPagedList` ? – FernandoPaiva Feb 06 '18 at 22:00
  • Refer [this answer](https://stackoverflow.com/questions/38373353/paging-net-mvc-without-download-all-records-from-webservice/38374805#38374805) for an example of where it might be relevant. But in you case, there seems no need to a view model and all you need is `IQueryable lista = new UsuarioDAO().GetAll().OrderBy(u => u.nome); search.lista = lista.ToPagedList(pageNumber, pageSize);` (and change the property to `public IPagedList lista { get; set; }` –  Feb 06 '18 at 22:04
  • I rolled back part of your last edit (where you added `.Skip()` and `.Take()` to the query based on comments because it then means you question makes no sense - it would not be 'slow' (but it would mean that you do not get page numbers in the view). I'll add an answer explaining the issue and solution a little later –  Feb 07 '18 at 02:59

1 Answers1

2

The .ToPagedList() method internally calls .Skip() and .Take() to limit the result set to the specified number of records. However it is designed to to take an IQueryable<T> of your data model, and is not designed for view models.

Your issue is that your taking your IQueryable<Usuario> and using a loop to generate a view model for every record in the database, so not only are you downloading 10,000 records to memory, your also creating 10,000 view models in memory (and then you throw away 9,990 of them)

Based on the code you have shown, you do not need a view model. Instead you can simply use

public ActionResult view(int? page)
{
    int pageNumber = page ?? 1;
    int pageSize = 10;
    IQueryable<Usuario> lista = new UsuarioDAO().GetAll();
    SearchUsuario search = new SearchUsuario
    {
        lista = lista.ToPagedList(pageNumber, pageSize);
    };
    return View(search);
}

and change the property in SearchUsuario to

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

The ToPagedList() method will now only download the 10 records you want in the view.

For cases where you do need a view model, then you need to use the StaticPagedList method and pass it the total number of records in order for the page number buttons in the view to be rendered correctly. It means a 2nd database call to get the .Count() of the records, but that is generally very fast

public ActionResult view(int? page)
{
    int pageNumber = page ?? 1;
    int pageSize = 10;
    // Get the count of all records
    int totalRecords = IQueryable<Usuario> lista = new UsuarioDAO().GetAll().Count();
    // Get just the records to be displayed using .Skip() and .Take()
    // and project into your view model
    IEnumerable<ViewUsuario> lista = new UsuarioDAO().GetAll()
        .OrderBy(u => u.nome).Skip(pageSize * pageNumber).Take(pageSize)
        .Select(x => new ViewUsuario
        {
            nome = x.nome,
            ....
        });
    SearchUsuario search = new SearchUsuario
    {
        lista = new StaticPagedList<ViewUsuario>(lista, pageNumber, pageSize, totalRecords);
    }
    return View(search);
}