ASP.NET MVC Webgrid Efficient Paging

问题:

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 = " ")

回答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());
    }

回答2:

It shouldn't execute the query right away as long as itemqry is an IEnumerable or IQueryable type. Can you cast it as an IQueryable as follows?

public ActionResult Index()
{
    IQueryable<vvItem> itemQry = db.vvItem;
    return View(itemQry.OrderBy(s => s.name).Skip(10).Take(20).ToList());
}

If the itemqry is the correct type it won't get executed until the .ToList() is called which converts it to an IList type. Check the SQL that is being produced to be sure.

回答3:

public ActionResult Index(int? pageIndex)
{
   int pageSize = 20;
   var itemIndex = ((pageIndex??1) -1) * pageSize;

    return View(db.vvItem.OrderBy(s => s.name).Skip(itemIndex).Take(pageSize).ToList()); 
}

回答4:

Thanks for this.

I tweaked this to work with some input parameters in my SP to control the PageSize, PageNumber, SortIndex and SortOrder:

declare @sql nvarchar(1000) = '
select * from
(
    select *, row_number() over(order by '+@SortIndex+' '+@SortOrder+') as RowNumber from #Results
) as T
where T.RowNumber between ('+@PageNumber+' - 1) * '+@PageSize+' + 1 and '+@PageNumber+' * '+@PageSize

exec sp_executesql @sql