Pagination C# MVC Asp . NET

Asked

Viewed 1,854 times

8

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.

2 answers

18


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.

  • 2

    It worked, thank you very much.

1

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>
  • I’m already using Pagelist MVC, the problem is this general loading of records that it does before it pages.

  • It won’t work not because the answer is incorrect, but because the author of the question is using a repository to select.

  • @Gypsy omorrisonmendez what is returned to var categoria when you urge new CadastroEntities();?

  • @tek6 I don’t understand. Where is categoria?

  • @Ciganomorrisonmendez Sorry for the mistake! The correct would be var contexto.

  • 2

    @tek6 If I understood his example, it returns something derived from DbContext. This answer has a correct explanation, but does not solve the main problem, which is an unnecessary encapsulation of repository within repository.

  • 1

    @Ciganomorrisonmendez Thank you! I’m reading some things of yours.

Show 2 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.