ASP.NET MVC Webgrid Efficient Paging

Question

I have a ASP.NET MVC 4 project and a SQL View (vvItem). ItemController

    MVCAppEntities db = new MVCAppEntities();
    public ActionResult Index()
    {
        var itemqry = db.vvItem.OrderBy(s => s.name);
        //var pageditems = itemqry.Skip(10).Take(20); // 25 seconds
        return View(itemqry.ToList()); // 88 seconds
    }

Index.cshtml View

@model IEnumerable<MVCApplication1.Models.vvItem>
@{
    var norows = 20;
    var grid = new WebGrid(Model, canPage: true, rowsPerPage: norows);
    grid.Pager(WebGridPagerModes.NextPrevious);  
    @grid.GetHtml(tableStyle: "table",  headerStyle: "header", columns: grid.Columns(
          grid.Column(columnName: "name", header: "Name", canSort: true),
          grid.Column(columnName: "quantity", header: "Quantity", canSort: true),  
          grid.Column(columnName: "code", header: "Code", canSort: true),
          grid.Column(columnName: "Price", header: "Price", canSort: true),
          ))}

In vvItem I have almost 400000 records. I thought that the webgrid Pager would load (Take()) only the displayed records and it would know to Skip() the first records if I would go to the next pages.

Q : How can I efficiently make a view to load only the displayed records ?

I found 2 solutions : JSON version and NerdDinner

I'm not so good at JSON so I tried the NerdDinner solution. And as in my commented line //var pageditems = itemqry.Skip(10).Take(20); itemqry is already loaded with all the records and it took a lot of time to load.

Q2 : How can I do paging now ? I need to modify the page no. from the Index method.

public ActionResult Index(int? page, string filter1 = " ", string filter2 = " ")

Answer 1

I made a SQL Stored Procedure

CREATE PROCEDURE SkipTake 
    @pagNo int, 
    @pageSize int
AS    
    select *
    from (select *, row_number() over (order by COD) as rn 
          from vvSTOC
         ) as T
    where T.rn between (@pagNo - 1) * @pageSize + 1 and @pagNo * @pageSize

I've added this sp in my EF model at Function Import so that it returns an entity (vvSTOC)

    public ActionResult Index(int? page)
    {
        const int pageSize = 20;
        return View(db.spSkipTake(page, pageSize).ToList());
    }