Paging using Linq-To-Sql based on two parameters in asp.net mvc

Question

As two parameters i say currentPage and pagesize .....I thus far used sql server stored procedures and implemented paging like this,

GO
ALTER PROCEDURE [dbo].[GetMaterialsInView]
    -- Add the parameters for the stored procedure here
    @CurrentPage INT,
    @PageSize INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

     SELECT *,ROW_NUMBER() OVER (ORDER BY Id) AS Row FROM    
     (
     SELECT
      *,ROW_NUMBER() OVER (ORDER BY Id) AS Row
      FROM InTimePagingView
     ) AS InTimePages
    WHERE  Row >= (@CurrentPage - 1) * @PageSize + 1 AND Row <= @CurrentPage*@PageSize

    SELECT COUNT(*) as TotalCount FROM InTimePagingView

    SELECT  CEILING(COUNT(*) / CAST(@PageSize AS FLOAT)) NumberOfPages
     FROM  InTimePagingView

END

Now i am using Linq-to-sql and i use this,

public IQueryable<MaterialsObj> FindAllMaterials()
        {
           var materials =  from m in db.Materials
                   join Mt in db.MeasurementTypes on m.MeasurementTypeId equals Mt.Id
                   where m.Is_Deleted == 0
                   select new MaterialsObj()
                   {
                       Id = Convert.ToInt64(m.Mat_id),
                       Mat_Name = m.Mat_Name,
                       Mes_Name = Mt.Name,
                   };
            return materials;

        }

Now i want to return the records,TotalCount where i use Total count to generate pagenumbers..... Is this possible... Any suggestion...

EDIT:

Just found this...

NorthWindDataContext db = new NorthWindDataContext();

var query = from c in db.Customers
            select c.CompanyName;

//Assuming Page Number = 2, Page Size = 10
int iPageNum = 2;
int iPageSize = 10;

var PagedData = query.Skip((iPageNum - 1) * iPageSize).Take(iPageSize);

ObjectDumper.Write(PagedData);

Iain Galloway · Accepted Answer · 2014-07-08 08:36:22Z

6

You can do it "by hand" with IQueryable.Skip(), .Take(), and .Count(), or you can use this handy pattern:-

public class PagedList<T> : List<T>
{
    public PagedList(IEnumerable<T> source, int index, int pageSize)
    {
        this.TotalCount = source.Count();
        this.PageSize = pageSize;
        this.PageIndex = index;
        this.AddRange(source.Skip(index * pageSize).Take(pageSize).ToList());
    }    

    public int TotalCount { get; set; }
    public int PageIndex { get; set; }
    public int PageSize { get; set; }

    public bool HasPreviousPage 
    { 
        get 
        {
            return (PageIndex > 0);
        }
    }

    public bool HasNextPage 
    { 
        get
        {
            return ((PageIndex + 1) * PageSize) < TotalCount;
        } 
    }     
 }

Use it like this:-

public ActionResult Materials(int page)
{
  var materials = MaterialsRepository.FindAllMaterials();
  var results = new PagedList<MaterialsObj>(materials, page - 1, 10);
  return new View(results)
}

There are a bunch of implementations around the internet with helpers and tests and all that jazz. I think it was ScottGu that came up with the original code, but don't quote me on that.

edited Jul 8, 2014 at 8:36

answered May 4, 2010 at 7:47

Iain Galloway

19.4k6 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Winward Over a year ago

If anyone else is having problems with the HasNextPage property becoming true too soon, or when there are no items, I changed it to the following logic, which seems to work better: ((PageIndex + 1) * PageSize) < TotalCount && TotalCount > PageSize;

Chris - Haddox Technologies Over a year ago

for HasNextPage try: (PageIndex <= Math.Ceiling(TotalCount / PageSize))

Collectives™ on Stack Overflow

Paging using Linq-To-Sql based on two parameters in asp.net mvc

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related