Pagination C# MVC Asp . NET

Question

I have a problem with paging performance with Entity Framework and SQL SERVER, first it does getAll and then list the pagination

var bdPergunta = new PerguntaRepositorioEF(contexto);
var pergunta = bdPergunta.GetAll().OrderByDescending(x => x.data).ToPagedList(page ?? 1, 200);
return View(pergunta);

But I have over 60,000 records in this table and this getAll() is taking too long, how can I make this pagination without the getAll()?

In Mysql when working with PHP I used Limit(x, y) to page, but now I’m having this difficulty.

Answer 1

The approach is incorrect. Use a repository in your case is incorrect. I’ve explained it a few times.

If you want to use the PagedList, abandon this type of approach:

var bdPergunta = new PerguntaRepositorioEF(contexto);
var pergunta = bdPergunta.GetAll().OrderByDescending(x => x.data).ToPagedList(page ?? 1, 200);

One of the reasons for never use repository with Entity Framework is that:

var pergunta = bdPergunta.GetAll().OrderByDescending(x => x.data).ToPagedList(page ?? 1, 200);

It’s different from this:

var pergunta = contexto.Perguntas.OrderByDescending(x => x.data).ToPagedList(page ?? 1, 200);

The first makes a FULL TABLE SCAN in the bank, bringing the 60 thousand records. The second inserts a TOP 200 in query (is the same thing as LIMIT Mysql, only pro SQL Server). That is, you do not bring the 60 thousand records: it brings only the first 200 in memory.

Abandon the repository approach that will work quickly.

Answer 2

You can use the Pagedlist MVC. It will help you a lot! You can do this step by step here, that will teach you to use.

Install, open the Package Manager Console and install via the Nuget command:

Install-Package PagedList.Mvc

In his Controller, you define how many records will appear per page:

public ActionResult Index(int? pagina)
{
   var contexto = new CadastroEntities();
   var listaAlunos = contexto.Alunos.ToList();
   int paginaTamanho = 4;
   int paginaNumero = (pagina ?? 1);

   return View(listaAlunos.ToPagedList(paginaNumero, paginaTamanho));
}

and in the View:

<div>
    Página @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber)
    de @Model.PageCount

    @if (Model.HasPreviousPage)
    {
        @Html.ActionLink("<<", "Index", new { pagina = 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })
        @Html.Raw(" ");
        @Html.ActionLink("< Anterior", "Index", new { pagina = Model.PageNumber - 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })
    }
    else
    {
        @:<<
        @Html.Raw(" ");
        @:< Anterior
    }

    @if (Model.HasNextPage)
    {
        @Html.ActionLink("Próxima >", "Index", new { pagina = Model.PageNumber + 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })
        @Html.Raw(" ");
        @Html.ActionLink(">>", "Index", new { pagina = Model.PageCount, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })
    }
    else
    {
        @:Próxima >
        @Html.Raw(" ")
        @:>>
    }
</div>