Using Ipagedlist with multiple results

Question

I am using Ipagedlist to list results in my application. But I’m trying to solve a problem: When I get a lot of results, going to the controller function to read the results from another page gets heavy. In other words, when I am loading my View I am sending a list of data that I am dealing with with Ipagedlist to make paging, now when I change page I do not want you to go back to the controller to load the list again when I change page (gets computationally heavy...).

What I’ve already tried?

When doing the query to fetch the list I used the Skip and the Take to fetch only the results to be displayed on the page (it 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();

With this solution you don’t calculate the total number of pages and instead of placing all pages of results you only have 1 page (which is the one I’m sending on top).

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; }

Answer 1

Do this optimization:

Ta so:

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

Put it like this

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

Or Even

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);
}

Because:

If you give a ToList() and keeps working in memory with the data that degrades considerably, so send the SQL generate all and then show.

An Observation relevant to your code

That line does what dbArt.getListaFornecedoresDados();, generates a ToList() if it is also degrades, already begins the error there. And this fornComCert = db.AutoDeclaracoesCertISCC. Here are the problems of your generation!!! Something else when you wear ToPageList don’t need to use Skip nor Take it does this for you in SQL inclusive