Paging In MVC 5 with Entity Framework

How to do Paging in MVC You can find lot of examples for paging in MVC with Entity Framework in internet. I am adding one more example. In this example you can see one partial page for paging control and ViewModel for called Pager. Here I am using AdventureWorks database Employee table to page Empoyees Following is ViewModel class, data that you need to pass to the view. Class constructor have IQueryable Datasource, It hold the data and return DataSource, TotalCount,PageSize currentpage and TotalPages. 

public class Pager<T>:List<T>
    {
        public int TotalPages { get; set; }
        public int CurrentPage { get; set; }
        public int PageSize { get; set; }
        public int TotalCount { get; set; }

        public Pager(IQueryable<T> dataSource, int pageIndex, int pageSize, int totalCount)
        {
            TotalCount = totalCount;
            CurrentPage = pageIndex;
            PageSize = pageSize;
            TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);
            this.AddRange(dataSource);
        }
    }

Model and DataContext class

 public partial class EmployeeContext : DbContext
    {
        public EmployeeContext()
            : base("name=AdventureWorksConnectionString")
        {
        }

        public virtual DbSet<DimEmployee> DimEmployees { get; set; }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
        }
    }

In Model class I am not included all the columns, Just added only columns I need..

[Table("DimEmployee")]
    public partial class DimEmployee
    {
        [Key]
        public int EmployeeKey { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Title { get; set; }
        public string EmailAddress { get; set; }
        public string DepartmentName { get; set; }
    }

EmployeeContext class return Data from database, I already Installed Entity Framework from Package Manager, But in this example I am calling one stored procedure to get the data using EF. Its for I am not taking the whole data from database.. Just taking what I want to display. Here we need to pass the limit of data we want.

USE [AdventureWorksDW2008R2]
GO
/****** Object:  StoredProcedure [dbo].[getEmployeeByPageNumber]    Script Date: 08/12/2014 14:24:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Raju Melveetilpurayil>
-- Create date: <Create Date,,>
-- Description: <Get rows by row between limit>
-- =============================================
ALTER PROCEDURE [dbo].[getEmployeeByPageNumber]
 -- Add the parameters for the stored procedure here
 @Start INT,
 @End INT,
 @TotalCount INT OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
SELECT @TotalCount=COUNT(*) FROM dbo.DimEmployee
SELECT [EmployeeKey]
      ,[FirstName]
      ,[LastName]
      ,[Title]
      ,[EmailAddress]
      ,[DepartmentName] FROM 
(
 SELECT ROW_NUMBER() OVER(ORDER BY EmployeeKey) ROW_NUM, * FROM DimEmployee
 ) AS K
 WHERE ROW_NUM >@Start AND ROW_NUM <=@End
  
END

Now we can see our MVC pages. Here is a partial page it only meant to display paging controls. The advantage of this control and model are generic, so we can pass any datasouce to do paging.

   @{
         double _pageCount = ((double)((Model.TotalCount-1) / Model.PageSize));
         int pageCount = _pageCount - (Math.Round(_pageCount)) != 0 ? (int)(_pageCount + 1) : (int)_pageCount;

         //
         // disable class for next and previous button if there is no previous and next
         string classNameForNext = Model.CurrentPage == _pageCount ? "disabled" : string.Empty;
         string classNameForPrevious = Model.CurrentPage == 1 ? "disabled" : string.Empty;
    }

        @if (classNameForPrevious == string.Empty){
         @Html.ActionLink("« Prev", "Index", new { page = (Model.CurrentPage - 1) })}
        else{
        « Prev}

    
@for (int pages = 1; pages <= pageCount; pages++)
    {
        //
        //adding active class to current page
        string active = "";
        if (pages == Model.CurrentPage) { active = "active"; }
@Html.ActionLink(pages.ToString(), "Index", new { page = pages })
}

        @if (classNameForNext == string.Empty){
            @Html.ActionLink("Next »", "Index", new { page = (Model.CurrentPage + 1) })}
        else{
           Next »
        }
    

form the Index.cshtml we pass Model to the partial page. From the Model partial page read class properties TotalPage,CurrentPage.. and generate page numbers and next and previous buttons Passing Model to partial Page

<tr>
     <td colspan="5">
        @Html.Partial("_PagingPartial",Model)
     </td>
</tr>

Ok Now we can see the whole Index.cshtml

@model EFMVCSample.ViewModel.Pager<EFMVCSample.Models.DimEmployee>
@{
    ViewBag.Title = "Employee";
}
<table class="table">
         <tr>
            <th>FirstName</th>
            <th>LastName</th>
            <th>Title</th>
            <th>EmailAddress</th>
            <th>DepartmentName</th>
        </tr>
            @foreach (EFMVCSample.Models.DimEmployee employee in Model.AsQueryable().ToList())
            {
                <tr>
                    <td>@employee.FirstName</td>
                    <td>@employee.LastName</td>
                    <td>@employee.Title</td>
                    <td>@employee.EmailAddress</td>
                    <td>@employee.DepartmentName</td>
                </tr>
            }
    <tr>
        <td colspan="5">
            @Html.Partial("_PagingPartial",Model)
        </td>
    </tr>
</table>

So we can see PagingController to see how controller works

public class PagingController : Controller
    {
        private List<DimEmployee> AllEmpoyees { get; set; }

        //
        // GET: /Paging/
        public ActionResult Index(int? page)
        {
            int pageno = 0;
            pageno = page == null ? 1 : int.Parse(page.ToString());

            int pageSize = 15;
            int totalCount = 0;



            using (var db = new EmployeeContext())
            {
                int limitEnd = pageno * pageSize;
                int limitStart = limitEnd - pageSize;

                //
                //calling stored procedure to get the total result count

                var start = new SqlParameter("@Start", limitStart);
                var end = new SqlParameter("@End", limitEnd);

                //
                // setting stored procedure OUTPUT value
                // This return total number of rows, and avoid two database call for data and total number of rows 
                var spOutput = new SqlParameter
                {
                    ParameterName = "@TotalCount",
                    SqlDbType = System.Data.SqlDbType.BigInt,
                    Direction = System.Data.ParameterDirection.Output
                };

                //
                //calling stored procedure to get paged data.
                AllEmpoyees = db.Database.SqlQuery<DimEmployee>(
                                                "getEmployeeByPageNumber @Start,@End,@TotalCount out",
                                                start, end, spOutput)
                                                .ToList();

                //
                // setting total number of records
                totalCount = int.Parse(spOutput.Value.ToString());

            }
            Pager<DimEmployee> pager = new Pager<DimEmployee>(AllEmpoyees.AsQueryable(), pageno, pageSize, totalCount);
            return View(pager);

        }
 }

Depend on page number, Controller create pageno,pageSize and call stored procedure to get data between limit.

 var spOutput = new SqlParameter
                {
                    ParameterName = "@TotalCount",
                    SqlDbType = System.Data.SqlDbType.BigInt,
                    Direction = System.Data.ParameterDirection.Output
                };

//
//calling stored procedure to get paged data.
AllEmpoyees = db.Database.SqlQuery<DimEmployee>("getEmployeeByPageNumber @Start,@End,@TotalCount out",start, end, spOutput).ToList();

Download example

CodeProject