Linq and Pagination

LINQ and pagination

I always use the following code:

public static class PagingExtensions
{
    //used by LINQ to SQL
    public static IQueryable<TSource> Page<TSource>(this IQueryable<TSource> source, int page, int pageSize)
    {
        return source.Skip((page - 1) * pageSize).Take(pageSize);
    }

    //used by LINQ
    public static IEnumerable<TSource> Page<TSource>(this IEnumerable<TSource> source, int page, int pageSize)
    {
        return source.Skip((page - 1) * pageSize).Take(pageSize);
    }

}

That is a static class, which you can include in your sources.
After adding this class you can do the following:

MyQuery.Page(pageNumber, pageSize)

Paging with LINQ for objects

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 MSDN for more information on how to use these methods: http://msdn.microsoft.com/en-us/library/bb386988.aspx

Assuming you are already taking into account that the pageNumber should start 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);

Otherwise if pageNumber is 1-based (as suggested by @Alvin)

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

How to get the total page of LINQ pagination

Isn't it simple math?

Page count = Round(Total Records / Page Size)

in C#

int totalItemCount = myList.Count();
int pageSize = 10;

int pageCount = System.Convert.ToInt32(System.Math.Ceiling(totalItemCount / System.Convert.ToDouble(pageSize)));

.NET MVC Linq pagination with PagedList

The thing is you have to Skip items and then Take(pageSize)

var pagedNews = DB.News.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();

So let's say you have 5 items / page.

If you are on page 1

(1 - 1) * 5 = 0 so skip zero Items and take 5

If you are on page 2

(2 - 1) * 5 = 5 so skip 5 Items and take 5

Your parameters are Nullable so you might have to put a default condition on your parameters say if NULL then PageSize = 5 and PageNumber = 1

int pageSize, int? newsID, int? page

EDIT:

Instead of:

if (cacheNews == null)
{
                news = (from n in DB.News
                        select n).ToList();

...........
}

Use this:

// You will have to OrderBy() before doing the pagination:

// Read as Queryable()

var pagedNews = DB.News.AsQueryable();

// Apply OrderBy Logic
pagedNews = pagedNews.OrderBy(); 

//ApplyPagination
pagedNews = pagedNews.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();

ORDER BY

You don't need to pass the OrderBy columns as separate strings.

Pass one string e.g. selectedSortBy from View,

I have created a Helper method:

using System;
using System.Linq;
using System.Linq.Expressions;

namespace Common.Helpers
{
    public static class PaginationHelper
    {
        public static IQueryable<T> ApplyPagination<T>(IQueryable<T> source, Pagination pagination)
        {
            var sortDirection = pagination.SortDirection == SortDirectionEnum.Ascending ? "OrderBy" : "OrderByDescending";
            var orderBy = pagination.SortBy ?? pagination.DefaultSortBy;

            return source.OrderBy(orderBy, sortDirection).Skip((pagination.PageNumber - 1) * pagination.PageSize).Take(pagination.PageSize);
        }

        public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, string sortDirection, params object[] values)
        {
            var type = typeof(T);
            var property = type.GetProperty(ordering);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var orderByExp = Expression.Lambda(propertyAccess, parameter);
            var resultExp = Expression.Call(typeof(Queryable), sortDirection, new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExp));
            return source.Provider.CreateQuery<T>(resultExp);
        }
    }
}

Pagination Model + Enum:

namespace Common.Helpers
{
    public class Pagination
    {
        public SortDirectionEnum SortDirection { get; set; }
        public string SortBy { get; set; }
        public int TotalRecords { get; set; }
        public int NumberOfPages { get; set; }
        public int PageSize { get; set; }
        public int PageNumber { get; set; }

        public string DefaultSortBy { get; set; }

        public string ReloadUrl { get; set; }
        public string TargetDiv { get; set; }

        public Pagination()
        {

        }

        public Pagination(string reloadUrl, string targetDiv, int totalRecords, int numberOfPages)
        {
            ReloadUrl = reloadUrl;
            TargetDiv = targetDiv;
            PageSize = 10;
            PageNumber = 1;
        }
    }

    public enum SortDirectionEnum
    {
        Ascending = 1,
        Descending = 2
    }
}

Then call your Query like this:

var items = DB.News.AsQueryable();

items = PaginationHelper.ApplyPagination(items, PAGINATION_MODEL);

Efficiently paging large data sets with LINQ

The first statement does not execute the actual SQL query, it only builds part of the query you intend to run.

It is when you call query.Count() that the first will be executed

SELECT COUNT(*) FROM Table WHERE Something = something

On query.Skip().Take() won't execute the query either, it is only when you try to enumerate the results(doing a foreach over paged or calling .ToList() on it) that it will execute the appropriate SQL statement retrieving only the rows for the page (using ROW_NUMBER).

If watch this in the SQL Profiler you will see that exactly two queries are executed and at no point it will try to retrieve the full table.

---

Be careful when you are using the debugger, because if you step after the first statement and try to look at the contents of query that will execute the SQL query. Maybe that is the source of your misunderstanding.

---

Related Topics