Paging using Linq-To-Sql based on two parameters in asp.net mvc...
- by Pandiya Chendur
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);
