how to apply paging on a list

Question

I have a function that retrieves data from database and add it into list. My list is ready and shows the data but i want paging on that list so that it shows limited records per page. but no idea about how to do that.
Here is my code of that data layer function.

 public List<demodto> executereader(List<demodto> Ldemo,SqlCommand cmdshow, string tablename)
    {
         SqlConnection cn;
         try
         {
             cn = this.getconnection();

             cmdshow.Connection = cn;
             cn.Open();

             SqlDataReader rd = cmdshow.ExecuteReader();
             while (rd.Read())
             {
                 demodto dtoobj1 = new demodto();
                 dtoobj1.ID = Convert.ToInt32(rd[0].ToString());
                 dtoobj1.Name = rd[1].ToString();
                 dtoobj1.PhNo = Convert.ToInt32(rd[2].ToString());
                 dtoobj1.Address = rd[3].ToString();
                 dtoobj1.Gender = rd[4].ToString();
                 dtoobj1.Email = rd[5].ToString();
                 dtoobj1.Emptype = rd[6].ToString();

                 Ldemo.Add(dtoobj1);

             }
             cn.Close();
             return Ldemo;
         }
         catch (Exception ex2)
         {
             throw new DataException("error....." + ex2.Message);

         }

    }

And this is for DTO class..

 public class demodto
{
    public Int32 ID{get;set;}
    public string Name{get;set;}
    public Int32 PhNo { get; set; }
    public string Address{get;set;}
    public string Gender { get; set; }
    public string Email { get; set; }
    public string Emptype { get; set; }
 }

please help me. Thanks.

Possible duplicate of [Paging with LINQ for objects](http://stackoverflow.com/questions/2380413/paging-with-linq-for-objects) — Michael Freidgeim, Aug 16 '16 at 14:34

score 45 · Accepted Answer · edited May 23 '17 at 11:54

45

You can page a list with LINQ, like this:

IList<demodto> GetPage(IList<demodto> list, int page, int pageSize) {
    return list.Skip(page*pageSize).Take(pageSize).ToList();
}

For example, suppose each page has 50 records. To get a third page, call

IList<demodto> thirdPage = GetPage(dataList, 3, 50);

Note, however, that applying paging to data in memory makes very little sense: the idea behind paging is to cut down on the time it takes to retrieve your data from the database, and to save some memory by keeping only a single page, which is not going to happen in your case, because all data is retrieved at once.

In order to make paging worth the effort, you need to move it into the database. Change your method to accept page size and number, and use them to change the SQL to retrieve the list for a single page. Don't forget to force ordering on your sql read, otherwise the same data might appear on different pages. Your SQL needs to be modified to support pagination. This is done differently depending on your database. MS SQL Server solution is described in this answer.

edited May 23 '17 at 11:54

Community

1
1

answered Jan 26 '14 at 10:16

Sergey Kalinichenko

697,062
78
1,055
1,465

2

Shouldn't it be `return list.Skip(page - 1 * pageSize).Take(pageSize).ToList();` ? – Saket Kumar Feb 02 '15 at 14:25
2

@Saket It depends on whether you consider `page` one-based or zero-based. I prefer zero-based numbering. – Sergey Kalinichenko Feb 02 '15 at 14:40
Okay. Thanks for the info. – Saket Kumar Feb 03 '15 at 05:48
@dasblinkenlight do you have any idea how to know the total page number of the given collection length via LINQ? – Roel Aug 16 '16 at 03:40
@Roel, i'm a little late to the party but you could use: `int result = Math.DivRem(int a, int b, out new int remainder);` If remainder is greater than 0 add one to the result (the quotient) and that is your total page count. [Math.DivRem()](https://docs.microsoft.com/en-us/dotnet/api/system.math.divrem?view=netframework-4.8) – Bryan Halterman May 15 '19 at 17:53

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

_context.skip(5).take(10)

Try to use this one. Better explanation in this question Paging with LINQ for objects

edited May 23 '17 at 12:18

Community

1
1

answered Jan 26 '14 at 10:14

BorHunter

881
2
18
41

score 0 · Answer 3 · answered Jan 26 '14 at 10:14

Skip and Take extension methods fits your needs.I don't know how is your page structure but you can use a simple for loop to get values like this:

int recordPerPage = 20;
for(int i=0; i<pageCount; i++)
{ 
   var values = list.Skip(recordPerPage*i).Take(recordPerPage).ToList();
   // add values to the page or display whatever..
}

score 0 · Answer 4 · answered Feb 10 '21 at 15:54

In new versions of SQL Server you can use the ROWNUMBER Function

rowStart    = ((Page - 1) * PageSize) + 1
rowEnd      = Page * PageSize

SELECT * From (SELECT ROW_NUMBER() {0} As RowNum, * FROM table WHERE condition) AS RowConstrainedResult Where RowNum >= rowStart And RowNum <= rowEnd Order By RowNum

This then makes the whole thing a lot quicker as you are not bringing back the whole data-set, just those records on the page you want

score 0 · Answer 5 · answered Apr 07 '21 at 20:12

int pagesize = 1000;
int countitens = list1.Count();
int pagecount = countitens % pagesize <= 0 ? countitens / pagesize : (countitens / pagesize) + 1; // for example 10001 itens and page size of 1000 retusn 2 pages
for (int page = 0; page < pagecount; page++)
{
    var itens = list1.Skip(page * pagesize).Take(pagesize).ToList();
}

Or just

var itens = list1.Skip(page-1 * pagesize).Take(pagesize).ToList(); // page-1 if value of page start with 1

how to apply paging on a list

5 Answers5

Linked