Retrieve paged lists from DAL theo codeutil.wordpress.com

Objective:

Create an easy way to retrieve paged lists from the data access layer when using Entity Framework.

Steps:

1. Create an interface that defines a paged list: IPagedList<T>.
2. Create a class PagedList<T> that implements IPagedList<T>.
3. Create a Linq extension method that can be applied to an Entity Framework query and returns a paged list.
4. Optional: If you are using the repository pattern you can use IPagedList<T> to return paged lists.

Code :)

1. Define an interface for the paged list.

1 2 3 4 5 6 7 8 9 10 11 12 13

using System.Collections.Generic;   namespace Demo.Paging {     public interface IPagedList<T>     {         int PageSize { get; }         int TotalNumberOfItems { get; }         IList<T> CurrentPage { get; }         int CurrentPageNumber { get; }         int TotalNumberOfPages { get; }            } }

2. Implement the class for the paged list.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

using System.Collections.Generic;   namespace Demo.Paging {     public class PagedList<T> : IPagedList<T>     {         public int PageSize { get; private set; }         public int TotalNumberOfItems { get; private set; }         public IList<T> CurrentPage { get; private set; }         public int CurrentPageNumber { get; private set; }           public int TotalNumberOfPages         {             get             {                 return TotalNumberOfItems == 0                            ? 1                            : (TotalNumberOfItems/PageSize + (TotalNumberOfItems%PageSize > 0 ? 1 : 0));             }         }           public PagedList(IList<T> currentPage, int currentPageNumber, int pageSize, int totalNumberOfItems)         {             PageSize = pageSize;             TotalNumberOfItems = totalNumberOfItems;             CurrentPage = currentPage;             CurrentPageNumber = currentPageNumber;         }     } }

3. Create a Linq extension method that is able to return paged lists from regular queries.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

using System.Linq;   namespace Demo.Paging {     public static class PagedListLinqExtentions     {         public static PagedList<T> ToPagedList<T>(this IQueryable<T> allItems, int currentPageNumber, int pageSize)         {             if (currentPageNumber < 1)                 currentPageNumber = 1;               var totalNumberOfItems = allItems.Count();             var totalNumberOfPages = totalNumberOfItems == 0 ? 1 : (totalNumberOfItems / pageSize + (totalNumberOfItems % pageSize > 0 ? 1 : 0));             if (currentPageNumber > totalNumberOfPages)                 currentPageNumber = totalNumberOfPages;               var itemsToSkip = (currentPageNumber - 1) * pageSize;             var pagedItems = allItems.Skip(itemsToSkip).Take(pageSize).ToList();               return new PagedList<T>(pagedItems, currentPageNumber, pageSize, totalNumberOfItems);         }     } }

4. Example of usage:

1 2 3 4 5 6 7

protected IPagedList GetPagedList() {     using (var context = new DemoEntities())     {         return context.TestTable.Where(c => condition).OrderBy(t => t.TableColumn).ToPagedList(1, 10);     } }

Note:
The extension method can be applied only on queries that are sorted, because of an Entity Framework restriction. Otherwise we will receive the error:

“The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.”

5. If you are using the repository pattern and you need to return a paged list, below is a handy example that will provide a default column to sort by in case no ordering is supplied. In this way you no longer have to worry about the error: The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.

Note: We are using System.Linq.Dynamic to create dynamic Linq queries. You can use NuGet to download it.

Base repository interface:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

using System; using System.Collections.Generic; using System.Linq.Expressions; using Demo.Paging;   namespace Demo.DataAccess {     /// <summary>     /// Base repository class     /// </summary>     /// <typeparam name="T"></typeparam>     public interface IRepository<T> where T:class     {         /// <summary>         /// Return a paged list.         /// </summary>         /// <param name="filters">A lambda expression used to filter the result.</param>         /// <param name="sorting">The sort expression, example: ColumnName desc</param>         /// <param name="includeList">The list of related entities to load.</param>         /// <param name="currentPageNumber">The page number to return.</param>         /// <param name="pageSize">The page size (the maximum number of elements to return in the list).</param>         /// <returns></returns>         IPagedList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList, int currentPageNumber, int pageSize);           /// <summary>         /// Return a list.         /// </summary>         /// <param name="filters">A lambda expression used to filter the result.</param>         /// <param name="sorting">The sort expression, example: ColumnName desc</param>         /// <param name="includeList">The list of related entities to load.</param>         /// <returns></returns>         IList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList);     } }

Base repository class:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

using System; using System.Collections.Generic; using System.Data.Objects; using System.Linq; using System.Linq.Expressions; using System.Linq.Dynamic; using using Demo.Paging;   namespace Demo.DataAccess {     /// <summary>     /// Base repository class     /// </summary>     /// <typeparam name="T"></typeparam>     public class Repository<T> : IRepository<T> where T : class     {         /// <summary>         /// Return a paged list.         /// </summary>         /// <param name="filters">A lambda expression used to filter the result.</param>         /// <param name="sorting">The sort expression, example: ColumnName desc</param>         /// <param name="includeList">The list of related entities to load.</param>         /// <param name="currentPageNumber">The page number to return.</param>         /// <param name="pageSize">The page size (the maximum number of elements to return in the list).</param>         /// <returns></returns>         public IPagedList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList, int currentPageNumber, int pageSize)         {             using (var context = new MvcDemoEntities())             {                 return GetQuery(context, filters, sorting, includeList).ToPagedList(currentPageNumber, pageSize);             }         }           /// <summary>         /// Return a list.         /// </summary>         /// <param name="filters">A lambda expression used to filter the result.</param>         /// <param name="sorting">The sort expression, example: ColumnName desc</param>         /// <param name="includeList">The list of related entities to load.</param>         /// <returns></returns>         public IList<T> Search(Expression<Func<T, bool>> filters, string sorting, List<string> includeList)         {             using (var context = new MvcDemoEntities())             {                 return GetQuery(context, filters, sorting, includeList).ToList();             }         }           /// <summary>         /// Method used to build the query that will reflect the filter conditions and the sort expression.         /// </summary>         /// <param name="context"></param>         /// <param name="filters"></param>         /// <param name="sorting"></param>         /// <param name="includeList"></param>         /// <returns></returns>         protected IQueryable<T> GetQuery(ObjectContext context, Expression<Func<T, bool>> filters, string sorting, List<string> includeList)         {             var objectSet = context.CreateObjectSet<T>();             if (string.IsNullOrEmpty(sorting))                 sorting =  objectSet.EntitySet.ElementType.KeyMembers.First().Name; //consider the PK as the default sort column               var objectQuery = (ObjectQuery<T>) objectSet;             if (includeList != null)             {                 foreach (var include in includeList)                 {                     objectQuery = objectQuery.Include(include);                 }             }               var query = objectQuery.Where(filters == null ? t => 1 == 1 : filters);             query = query.OrderBy(sorting);             return query;         }     } }

Repository usage example:

1 2	var userRepository = new Repository<User>(); var pagedList = userRepository.Search(u => u.FirstName.Contains("a"), "FirstName asc", new List<string>{"UserRole"}, 1, 5);