Use IPagedList with multiple results

2

I'm using the IPagedList to do results listing in my application. But I'm trying to solve a problem: When I have many results, going to the controller function to read the results of another page gets heavy. That is, when I am loading my View I am sending a list of data that I am handling with the IPagedList to do pagination, now when I change the page I do not want to go to the controller again to load the list again when I change from page (gets computationally heavy ...).

What have I tried?

When doing the query to get the list I put the Skip and the Take to search only the results to present on the page (always comes to the controller every time I change the page):

int from = Convert.ToInt32(page) * pageSize;
int to = from + pageSize;

terceiros = terceiros.Where(k => !(fornComCert.Any(l => l.IdForn == k.IdFornecedor && l.IdFilialForn == k.IdFilial))).Skip(from).Take(to).ToList();

This solution does not calculate the total page number and instead of putting all the results pages it only has 1 page (which is the one I'm sending up).

How can I get around this?

Full function:

public ActionResult listaFornSemCertISCC(int? page) {
        //Paginação 
        int pageSize = 20;
        int pageNumber = (page ?? 1);

        ARTSOFT.dal.ViewModels.GetDadosComerciais dbArt = new ARTSOFT.dal.ViewModels.GetDadosComerciais();
        var terceiros = dbArt.getListaFornecedoresDados();
        var fornComCert = db.AutoDeclaracoesCertISCC;


        int from = Convert.ToInt32(page) * pageSize;
        int to = from + pageSize;

        terceiros = terceiros.Where(k => !(fornComCert.Any(l => l.IdForn == k.IdFornecedor && l.IdFilialForn == k.IdFilial))).Skip(from).Take(to).ToList();

        return PartialView(terceiros.ToPagedList(pageNumber, pageSize));
    }

getListaFornecedoresDados is a list:

public List<FornecedorCViewModel> getListaFornecedoresDados()
        { db.Open();
        //string SQLstr = "select fornumero,ternome,terfilial from TERFCH where clinumero >0";
        string SQLstr = "select fornumero,ternome,terfilial, TerNrIDFisc, DivNrFicha, TerMorada, TerLocalid, TerCPPais, TerTelef, TerTlmBip, TerFax, TerCPPais, TerTelef, TerEmail, ForCodZona, paiscod from TERFCH where terfilial = 0 AND ForNumero > 0 AND DivNrFicha != 1 AND DivNrFicha != 2 AND fornumero<>0 and Forsituac=0";
        DataTable _ret = new DataTable();
        PsqlCommand cmd = new PsqlCommand(SQLstr, db);
        PsqlDataAdapter _oDataAdapter = new PsqlDataAdapter(cmd);
        _oDataAdapter.Fill(_ret);
        db.Close();
        List<FornecedorCViewModel> _lista = new List<FornecedorCViewModel>();
        foreach (DataRow row in _ret.Rows)
        {
            _lista.Add(new FornecedorCViewModel(int.Parse(row["fornumero"].ToString()), row["ternome"].ToString(), int.Parse(row["terfilial"].ToString()),
                row["TerNrIDFisc"].ToString(), int.Parse(row["DivNrFicha"].ToString()), row["TerMorada"].ToString(),
                row["TerLocalid"].ToString(), row["TerCPPais"].ToString(), row["TerTelef"].ToString(), row["TerTlmBip"].ToString(),
                row["TerFax"].ToString(), row["TerEmail"].ToString(), int.Parse(row["ForCodZona"].ToString()), int.Parse(row["paiscod"].ToString())
                ));
        }
        return _lista; }
    
asked by anonymous 03.07.2014 / 18:01

1 answer

3

Do this optimization:

It's like this:

terceiros = terceiros
     .Where(k => !(fornComCert.Any(l => l.IdForn == k.IdFornecedor && l.IdFilialForn == k.IdFilial)))
     .Skip(from)
     .Take(to)
     .ToList();

So

terceiros = terceiros
      .Where(k => !(fornComCert.Any(l => l.IdForn == k.IdFornecedor && l.IdFilialForn == k.IdFilial)));

Or

public ActionResult listaFornSemCertISCC(int? page) 
{
    //Paginação 
    int pageSize = 20;
    int pageNumber = (page ?? 1);

    ARTSOFT.dal.ViewModels.GetDadosComerciais dbArt = new ARTSOFT.dal.ViewModels.GetDadosComerciais();
    var terceiros = dbArt.getListaFornecedoresDados();
    var fornComCert = db.AutoDeclaracoesCertISCC;


    int from = Convert.ToInt32(page) * pageSize;
    int to = from + pageSize;

    terceiros = terceiros
        .Where(k => !(fornComCert.Any(l => l.IdForn == k.IdFornecedor && l.IdFilialForn == k.IdFilial)))            
        .ToPagedList(pageNumber, pageSize);

    return PartialView(terceiros);
}

Why:

If you give ToList() and keep working in memory with the data, this will degrade considerably, then send SQL to generate everything and then show.

A Note relevant to your code

This line does what dbArt.getListaFornecedoresDados(); , it generates a ToList() if it is also degrades, it already starts the error there. And that fornComCert = db.AutoDeclaracoesCertISCC . Here are the problems of your generation !!! Another thing when using ToPageList does not have to use Skip nor Take it does it for you in SQL inclusive

    
03.07.2014 / 18:04