Paging In MVC 5 with Entity Framework

• Download sample - 17.9 MB
• My Dropbox download example

How to do Paging in MVC? You can find a lot of examples for paging in MVC with Entity Framework on the 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 Employees Following is ViewModel class, data that you need to pass to the view. Class constructor has IQueryable<t> Datasource. It holds the data and returns 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 have 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 returns 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. It's because 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 is only meant to display paging controls. The advantage of this control and model are generic, so we can pass any Datasource to do paging.

<ul class="pagination">
   @{
         double _pageCount = ((double)((Model.TotalCount-1) / Model.PageSize));
         int pageCount = _pageCount - (Math.Round(_pageCount)) != 0 ? 
                         (int)(_pageCount + 1) : (int)_pageCount;
         
         string classNameForNext = Model.CurrentPage == _pageCount ? "disabled" : string.Empty;
         string classNameForPrevious = Model.CurrentPage == 1 ? "disabled" : string.Empty;
    }
    <li class="@classNameForPrevious">
        @if (classNameForPrevious == string.Empty)
		{
         @Html.ActionLink("« Prev", "Index", new { page = (Model.CurrentPage - 1) })
		}
        else
		{
			<a href="#">« Prev</a>
		}
    </li>
    
    @for (int pages = 1; pages <= pageCount; pages++)

    {

        //

        //adding active class to current page

        string active = "";

		if (pages == Model.CurrentPage)

		{

			active = "active"; 

		}

        <li class="@active">@Html.ActionLink(pages.ToString(), "Index", new { page = pages })</li>
    }
    <li class="@classNameForNext">
        @if (classNameForNext == string.Empty)
		{
            @Html.ActionLink("Next »", "Index", new { page = (Model.CurrentPage + 1) })
		}
        else
		{
           <a href="#">Next »</a>
        }
    </li>
</ul>

So we can see PagingController to see how controller works:

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

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

        }
 }

Depending on page number, Controller creates pageno, pageSize and calls 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();

CodeProject