Click here to Skip to main content
13,454,296 members (55,162 online)
Click here to Skip to main content
Add your own
alternative version


13 bookmarked
Posted 13 Aug 2014

Paging In MVC 5 with Entity Framework

, 13 Aug 2014
Rate this:
Please Sign up or sign in to vote.
One more example of how to do Paging in MVC

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

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.

    public partial class DimEmployee
        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]
/****** Object:  StoredProcedure [dbo].[getEmployeeByPageNumber]    
Script Date: 08/12/2014 14:24:29 ******/
-- =============================================
-- 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
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
SELECT @TotalCount=COUNT(*) FROM dbo.DimEmployee
SELECT [EmployeeKey]
      ,[DepartmentName] FROM 
 ) AS K

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) })
			<a href="#">« Prev</a>
    @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) })
           <a href="#">Next »</a>

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)

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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

raju melveetilpurayil
Software Developer (Senior)
United Kingdom United Kingdom
Microsoft Certified Professional Developer.

You may also be interested in...

Comments and Discussions

Questionpaging Pin
thas0228-Apr-15 15:50
memberthas0228-Apr-15 15:50 
GeneralRe: paging Pin
raju melveetilpurayil10-Apr-15 0:29
memberraju melveetilpurayil10-Apr-15 0:29 
QuestionVery nice job. Pin
macupryk10-Mar-15 16:06
membermacupryk10-Mar-15 16:06 
AnswerRe: Very nice job. Pin
raju melveetilpurayil10-Apr-15 0:34
memberraju melveetilpurayil10-Apr-15 0:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04-2016 | 2.8.180321.1 | Last Updated 13 Aug 2014
Article Copyright 2014 by raju melveetilpurayil
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid