Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Paging In MVC 5 with Entity Framework

, 13 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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 Emp
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.
<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("&laquo; Prev", "Index", new { page = (Model.CurrentPage - 1) })
		}
        else
		{
			<a href="#">&laquo; 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 &raquo;", "Index", new { page = (Model.CurrentPage + 1) })
		}
        else
		{
           <a href="#">Next &raquo;</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);

        }
 }
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();
My Dropbox download example

License

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

Share

About the Author

raju melveetilpurayil

United Kingdom United Kingdom
Microsoft Certified Professional Developer.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141220.1 | Last Updated 13 Aug 2014
Article Copyright 2014 by raju melveetilpurayil
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid