.NET MVC Linq pagination with PagedList

Question

In my MVC app I'm using LINQ to retrieve data from DB and PagedList for pagination. I have a couple of questions, after the code block, that I would like some help with.

Function where I retrieve data from cache or DB:

public NewsPagedListDTO GetNewsFromCacheOrDB(int pageSize, int? newsID, int? page, string newsTitle, int? categoryID, int? orderByTitle, int? orderByPublisher, int? orderByDate, int? orderByCategory)
            {
            DataCache cache = new DataCache("default");
            object cacheNews = cache.Get("cacheNews");

            List<News> news = new List<News>();


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

            //Only cache if no parameters was provided
            if (newsID == null && newsTitle == null && categoryID == null && orderByTitle == null && orderByPublisher == null &&
                orderByDate == null && orderByCategory == null)
                cache.Add("cacheNews", news);
            }
            }
            else
            {
                news = (List<News>)cacheNews;
            }

            if (newsID != null)
                news = news.Where(n => n.NewsID == newsID).ToList();

            if (categoryID != null)
                news = news.Where(n => n.CategoryID == categoryID).ToList();

            if (newsTitle != null)
                news = news.Where(n => n.Title == newsTitle).ToList();

            if (orderByTitle != null)
                if (orderByTitle == 0)
                    news = news.OrderBy(n => n.Title).ToList();
                else
                    news = news.OrderByDescending(n => n.Title).ToList();

            if (orderByPublisher != null)
                if (orderByPublisher == 0)
                    news = news.OrderBy(n => n.PublishedByFullName).ToList();
                else
                    news = news.OrderByDescending(n => n.PublishedByFullName).ToList();

            if (orderByDate != null)
                if (orderByDate == 0)
                    news = news.OrderByDescending(n => n.DatePublished).ToList();
                else
                    news = news.OrderBy(n => n.DatePublished).ToList();

            if (orderByCategory != null)
                if (orderByCategory == 0)
                    news = news.OrderBy(n => n.CategoryToString).ToList();
                else
                    news = news.OrderByDescending(n => n.CategoryToString).ToList();


            List<NewsDTO> newsDTO = new List<NewsDTO>();

            foreach (var item in news)
            {
                NewsDTO newsDTOtemp = new NewsDTO();

                newsDTOtemp.BlobName = item.BlobName;
                newsDTOtemp.DatePublished = item.DatePublished;
                newsDTOtemp.NewsID = item.NewsID;
                newsDTOtemp.PreviewText = item.PreviewText;
                newsDTOtemp.PublishedByEmail = item.PublishedByEmail;
                newsDTOtemp.PublishedByFullName = item.PublishedByFullName;
                newsDTOtemp.PublishedByID = item.PublishedByID;
                newsDTOtemp.Title = item.Title;
                newsDTOtemp.CategoryID = item.Category.CategoryID;
                newsDTOtemp.CategoryToString = item.Category.Name;

                newsDTO.Add(newsDTOtemp);
            }

            //Pagination
            NewsPagedListDTO newsResultDTO = new NewsPagedListDTO();
            newsResultDTO.NewsDTO = (PagedList<NewsDTO>)newsDTO.ToPagedList(page ?? 1, pageSize);

            return newsResultDTO;
        }

Pagination in my view:

@Html.PagedListPager(Model.NewsPagedListDTO.NewsDTO, page => Url.Action("News", new
   {
       page,
       newsTitle = Request.QueryString["NewsTitle"],
       categoryID = Request.QueryString["categoryID"],
       orderByTitle = Request.QueryString["orderByTitle"],
       orderByPublisher = Request.QueryString["orderByPublisher"],
       orderByDate = Request.QueryString["orderByDate"],
       orderByCategory = Request.QueryString["orderByCategory"]
   }),
    new PagedListRenderOptions()
    {
        Display = PagedListDisplayMode.IfNeeded,
        MaximumPageNumbersToDisplay = 5,
        DisplayEllipsesWhenNotShowingAllPageNumbers = false,
        DisplayLinkToPreviousPage = PagedListDisplayMode.Never,
        DisplayLinkToNextPage = PagedListDisplayMode.Never,
        LinkToFirstPageFormat = String.Format("«"),
        LinkToLastPageFormat = String.Format("»")
    })

Questions

1. It's the first time I'm using PagedList. What's the point in having postback for changing the page when the full results is retrieved? Isn't it better with client side pagination then? Currently I am retrieving all posts from DB with:

   news = (from n in DB.News select n).ToList();

   And after data is retrieved, sort with parameters..

   Sure the result is easy to cache but.. I rather only get data for just one page.

2. How would I only get data for the current page with my optional parameters? I have used stored procedures for this before but I don't think it's possible with PagedList.

3. How can I have cleaner code for optional parameters in my LINQ query? I don't like all those if statements..

Answer 1

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