Paging with LINQ for objects

Question

How would you implement paging in a LINQ query? Actually for the time being, I would be satisfied if the sql TOP function could be imitated. However, I am sure that the need for full paging support comes up sooner later anyway.

var queryResult = from o in objects
                  where ...
                  select new
                      {
                         A = o.a,
                         B = o.b
                      }
                   ????????? TOP 10????????

Summertime

Answer 1

You're looking for the Skip and Take extension methods. Skip moves past the first N elements in the result, returning the remainder; Take returns the first N elements in the result, dropping any remaining elements.

See Microsoft's documentation for more information on how to use these methods:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/return-or-skip-elements-in-a-sequence

If your pageNumber starts at 0 (decrease per 1 as suggested in the comments), you could do it like this:

int numberOfObjectsPerPage = 10;
var queryResultPage = queryResult
  .Skip(numberOfObjectsPerPage * pageNumber)
  .Take(numberOfObjectsPerPage);

If pageNumber starts at 1 (as suggested by @Alvin), then you coudld o it like this:

int numberOfObjectsPerPage = 10;
var queryResultPage = queryResult
  .Skip(numberOfObjectsPerPage * (pageNumber - 1))
  .Take(numberOfObjectsPerPage);

Nardi

Should I use the same technique over SQL with a huge database, will it take the entire table into memory first and then throw away the the unwanted? – Summertime

If you're interested in what's going on under the hood, by the way, most LINQ database drivers provide a way to get debug output information for the actual SQL that is being executed. – Nardi

Answer 2

Using Skip and Take is definitely the way to go. If I were implementing this, I would probably write my own extension method to handle paging (to make the code more readable). The implementation can of course use Skip and Take:

static class PagingUtils {
  public static IEnumerable<T> Page<T>(this IEnumerable<T> en, int pageSize, int page) {
    return en.Skip(page * pageSize).Take(pageSize);
  }
  public static IQueryable<T> Page<T>(this IQueryable<T> en, int pageSize, int page) {
    return en.Skip(page * pageSize).Take(pageSize);
  }
}

The class defines two extension methods - one for IEnumerable and one for IQueryable, which means that you can use it with both LINQ to Objects and LINQ to SQL (when writing database query, the compiler will pick the IQueryable version).

Depending on your paging requirements, you could also add some additional behavior (for example to handle negative pageSize or page value). Here is an example how you would use this extension method in your query:

var q = (from p in products
         where p.Show == true
         select new { p.Name }).Page(10, pageIndex);

Neglectful

I believe this will return the entire result set, and then filter in-memory instead of on the server. Huge performance hit against a database if this is SQL. – Foxhole

@jvenema You're right. Since this is using the IEnumerable interface rather than IQueryable this will pull in the entire database table, which will be a major performance hit. – Nardi

You can of course easily add an overload for IQueryable to make it work with databse queries too (I editted the answer and added it). It is a bit unfortunate that you can't write the code in a fully generic way (in Haskell this would be possible with type classes). The original question mentioned LINQ to Objects, so I wrote only one overload. – Neglectful

I was just thinking about implementing this myself. I'm a little bit surprised that it isn't part of the standard implementation. Thanks for the sample code! – Deirdredeism

I think the example should be: public static IQueryable<T> Page<T>(...etc – Upcoming

Answer 3

Here is my performant approach to paging when using LINQ to objects:

public static IEnumerable<IEnumerable<T>> Page<T>(this IEnumerable<T> source, int pageSize)
{
    Contract.Requires(source != null);
    Contract.Requires(pageSize > 0);
    Contract.Ensures(Contract.Result<IEnumerable<IEnumerable<T>>>() != null);

    using (var enumerator = source.GetEnumerator())
    {
        while (enumerator.MoveNext())
        {
            var currentPage = new List<T>(pageSize)
            {
                enumerator.Current
            };

            while (currentPage.Count < pageSize && enumerator.MoveNext())
            {
                currentPage.Add(enumerator.Current);
            }
            yield return new ReadOnlyCollection<T>(currentPage);
        }
    }
}

This can then be used like so:

var items = Enumerable.Range(0, 12);

foreach(var page in items.Page(3))
{
    // Do something with each page
    foreach(var item in page)
    {
        // Do something with the item in the current page       
    }
}

None of this rubbish Skip and Take which will be highly inefficient if you are interested in multiple pages.

Epidaurus

It works in Entity Framework with Azure SQL Data Warehouse,that doesn't support Skip method(internally using OFFSET clause) – Padraic

This just had to be stolen and put in my common lib, thanks! I just renamed the method to Paginate to remove noun vs verb ambiguity. – Grattan

I am wondering if this statement holds true. Now that it's been quite a few years, have Skip and Take been optimized? – Afterguard

Now in .NET 6 we have the Chunk method that does the same as Page here: items.Chunk(3) – Plainspoken

Answer 4

   ( for o in objects
    where ...
    select new
   {
     A=o.a,
     B=o.b
   })
.Skip((page-1)*pageSize)
.Take(pageSize)

Gondolier

Answer 5

Don't know if this will help anyone, but I found it useful for my purposes:

private static IEnumerable<T> PagedIterator<T>(IEnumerable<T> objectList, int PageSize)
{
    var page = 0;
    var recordCount = objectList.Count();
    var pageCount = (int)((recordCount + PageSize)/PageSize);

    if (recordCount < 1)
    {
        yield break;
    }

    while (page < pageCount)
    {
        var pageData = objectList.Skip(PageSize*page).Take(PageSize).ToList();

        foreach (var rd in pageData)
        {
            yield return rd;
        }
        page++;
    }
}

To use this you would have some linq query, and pass the result along with the page size into a foreach loop:

var results = from a in dbContext.Authors
              where a.PublishDate > someDate
              orderby a.Publisher
              select a;

foreach(var author in PagedIterator(results, 100))
{
    // Do Stuff
}

So this will iterate over each author fetching 100 authors at a time.

Expositor

As Count() enumerates the collection, you can just as well convert it to List() and iterate with indexes. – Substitutive

Answer 6

var queryResult = (from o in objects where ...
    select new {
        A = o.a,
        B = o.b
    }).Take(10);

Cesaria

You might also need brackets around the query before calling Take. (from ... select ...).Take(10). I called the construct with selecting a string. Without brackets, the Take returned the first 10 chars of the string instead of limiting the query result :) – Summertime

Answer 7

Similar to Lukazoid's answer I've created an extension for IQueryable.

   public static IEnumerable<IEnumerable<T>> PageIterator<T>(this IQueryable<T> source, int pageSize)
            {
                Contract.Requires(source != null);
                Contract.Requires(pageSize > 0);
                Contract.Ensures(Contract.Result<IEnumerable<IQueryable<T>>>() != null);

                using (var enumerator = source.GetEnumerator())
                {
                    while (enumerator.MoveNext())
                    {
                        var currentPage = new List<T>(pageSize)
                        {
                            enumerator.Current
                        };

                        while (currentPage.Count < pageSize && enumerator.MoveNext())
                        {
                            currentPage.Add(enumerator.Current);
                        }
                        yield return new ReadOnlyCollection<T>(currentPage);
                    }
                }
            }

It is useful if Skip or Take are not supported.

Padraic

Answer 8

var pages = items.Select((item, index) => new { item, Page = index / batchSize }).GroupBy(g => g.Page);

Batchsize will obviously be an integer. This takes advantage of the fact that integers simply drop decimal places.

I'm half joking with this response, but it will do what you want it to, and because it's deferred, you won't incur a large performance penalty if you do

pages.First(p => p.Key == thePage)

This solution is not for LinqToEntities, I don't even know if it could turn this into a good query.

Tracey

Answer 9

I use this extension method:

public static IQueryable<T> Page<T, TResult>(this IQueryable<T> obj, int page, int pageSize, System.Linq.Expressions.Expression<Func<T, TResult>> keySelector, bool asc, out int rowsCount)
{
    rowsCount = obj.Count();
    int innerRows = rowsCount - (page * pageSize);
    if (innerRows < 0)
    {
        innerRows = 0;
    }
    if (asc)
        return obj.OrderByDescending(keySelector).Take(innerRows).OrderBy(keySelector).Take(pageSize).AsQueryable();
    else
        return obj.OrderBy(keySelector).Take(innerRows).OrderByDescending(keySelector).Take(pageSize).AsQueryable();
}

public IEnumerable<Data> GetAll(int RowIndex, int PageSize, string SortExpression)
{
    int totalRows;
    int pageIndex = RowIndex / PageSize;

    List<Data> data= new List<Data>();
    IEnumerable<Data> dataPage;

    bool asc = !SortExpression.Contains("DESC");
    switch (SortExpression.Split(' ')[0])
    {
        case "ColumnName":
            dataPage = DataContext.Data.Page(pageIndex, PageSize, p => p.ColumnName, asc, out totalRows);
            break;
        default:
            dataPage = DataContext.vwClientDetails1s.Page(pageIndex, PageSize, p => p.IdColumn, asc, out totalRows);
            break;
    }

    foreach (var d in dataPage)
    {
        clients.Add(d);
    }

    return data;
}
public int CountAll()
{
    return DataContext.Data.Count();
}

Catchweight

Answer 10

public LightDataTable PagerSelection(int pageNumber, int setsPerPage, Func<LightDataRow, bool> prection = null)
{
    this.setsPerPage = setsPerPage;
    this.pageNumber = pageNumber > 0 ? pageNumber - 1 : pageNumber;
    if (!ValidatePagerByPageNumber(pageNumber))
        return this;

    var rowList = rows.Cast<LightDataRow>();
    if (prection != null)
        rowList = rows.Where(prection).ToList();

    if (!rowList.Any())
        return new LightDataTable() { TablePrimaryKey = this.tablePrimaryKey };
    //if (rowList.Count() < (pageNumber * setsPerPage))
    //    return new LightDataTable(new LightDataRowCollection(rowList)) { TablePrimaryKey = this.tablePrimaryKey };

    return new LightDataTable(new LightDataRowCollection(rowList.Skip(this.pageNumber * setsPerPage).Take(setsPerPage).ToList())) { TablePrimaryKey = this.tablePrimaryKey };
}

Normally you start at 1 but in IList you start with 0. so if you have 152 rows that mean you have 8 paging but in IList you only have 7.

Vantassel

Answer 11

There are two main options:

.NET >= 4.0 Dynamic LINQ:

1. Add using System.Linq.Dynamic; at the top.
2. Use: var people = people.AsQueryable().OrderBy("Make ASC, Year DESC").ToList();

You can also get it by NuGet.

.NET < 4.0 Extension Methods:

private static readonly Hashtable accessors = new Hashtable();

private static readonly Hashtable callSites = new Hashtable();

private static CallSite<Func<CallSite, object, object>> GetCallSiteLocked(string name) {
    var callSite = (CallSite<Func<CallSite, object, object>>)callSites[name];
    if(callSite == null)
    {
        callSites[name] = callSite = CallSite<Func<CallSite, object, object>>.Create(
                    Binder.GetMember(CSharpBinderFlags.None, name, typeof(AccessorCache),
                new CSharpArgumentInfo[] { CSharpArgumentInfo.Create(CSharpArgumentInfoFlags.None, null) }));
    }
    return callSite;
}

internal static Func<dynamic,object> GetAccessor(string name)
{
    Func<dynamic, object> accessor = (Func<dynamic, object>)accessors[name];
    if (accessor == null)
    {
        lock (accessors )
        {
            accessor = (Func<dynamic, object>)accessors[name];
            if (accessor == null)
            {
                if(name.IndexOf('.') >= 0) {
                    string[] props = name.Split('.');
                    CallSite<Func<CallSite, object, object>>[] arr = Array.ConvertAll(props, GetCallSiteLocked);
                    accessor = target =>
                    {
                        object val = (object)target;
                        for (int i = 0; i < arr.Length; i++)
                        {
                            var cs = arr[i];
                            val = cs.Target(cs, val);
                        }
                        return val;
                    };
                } else {
                    var callSite = GetCallSiteLocked(name);
                    accessor = target =>
                    {
                        return callSite.Target(callSite, (object)target);
                    };
                }
                accessors[name] = accessor;
            }
        }
    }
    return accessor;
}
public static IOrderedEnumerable<dynamic> OrderBy(this IEnumerable<dynamic> source, string property)
{
    return Enumerable.OrderBy<dynamic, object>(source, AccessorCache.GetAccessor(property), Comparer<object>.Default);
}
public static IOrderedEnumerable<dynamic> OrderByDescending(this IEnumerable<dynamic> source, string property)
{
    return Enumerable.OrderByDescending<dynamic, object>(source, AccessorCache.GetAccessor(property), Comparer<object>.Default);
}
public static IOrderedEnumerable<dynamic> ThenBy(this IOrderedEnumerable<dynamic> source, string property)
{
    return Enumerable.ThenBy<dynamic, object>(source, AccessorCache.GetAccessor(property), Comparer<object>.Default);
}
public static IOrderedEnumerable<dynamic> ThenByDescending(this IOrderedEnumerable<dynamic> source, string property)
{
    return Enumerable.ThenByDescending<dynamic, object>(source, AccessorCache.GetAccessor(property), Comparer<object>.Default);
}

Quandary

Answer 12

The following one-liner takes a generic IEnumerable<T> collection and returns it paginated:

static class PaginationExtension
{
  internal static IEnumerable<IEnumerable<T>> Paginated<T>(
    this IEnumerable<T> xs,
    int pageSize) =>

    Enumerable.Range(0, (int)Math.Ceiling(decimal.Divide(xs.Length(), pageSize)))
      .Select(i =>
        xs
          .Skip(i * pageSize)
          .Take(pageSize));
}

Here's a unit test showing its use:

[Theory]
[InlineData(25, 100, 4)]
[InlineData(20, 20, 1)]
[InlineData(20, 10, 1)]
[InlineData(20, 1, 1)]
[InlineData(20, 0, 0)]
[InlineData(20, 21, 2)]
void it_paginates_items(int pageSize, int numberOfItems, int expectedPages)
{
    var items = Enumerable.Range(0, numberOfItems);
    
    var pages = items.Paginated(pageSize);

    Assert.Equal(expectedPages, pages.Length());
}

This is similar to Lukazoid's answer, but possibly simpler.

Pappose

Answer 13

var results = (medicineInfo.OrderBy(x=>x.id)
                       .Skip((pages -1) * 2)
                       .Take(2));

Mithras