C # MVC Asp .NET Paging

Question

C # MVC Asp .NET Paging

Navigation

#1 by (16 votes)
#2 by (1 votes)

8

I have a performance problem in paging with Entity Framework and SQL SERVER, first it does getAll to later list paging

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 a long time, how can I make this pagination without getAll() ?

In MySQL when working with PHP I used Limit(x, y) to page, but now I have this difficulty.

c# entity-framework asp.net-mvc

asked by anonymous 29.07.2015 / 15:41

2 answers

1

You can use PagedList MVC . It will help you a lot! You can do this step by step here , which will teach you how to use.

Installation, open the Package Manager Console and install through the NuGet command:

Install-Package PagedList.Mvc

In your 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 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>

29.07.2015 / 15:53

How to give split in a String that contains blanks in the beginning? How do I delete a character within a string?

score 16 · Accepted Answer

The approach is incorrect . Using a repository in your case is incorrect . I have explained this a few times .

If you want to correctly use PagedList , abandon this approach:

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

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

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

It's different than this:

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

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

Leave the repository approach that will work quickly.