Take the 2-minute tour ×

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.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 = " ")
share|improve this question
in the nerddinner example they would materialize the x in the view not itemqry. –  Mike Miller Jun 19 '12 at 12:43
I renamed x to pageditems. As you can see with Skip-Take it took 25 sec and without 88 sec –  Misi Jun 19 '12 at 14:10
For question 2 it would be managed in the controller as in the NerdDinner example and as user1439338 posted below. Your view would have to count the number of pages and manage the page links. A dedicated View Model is a good way as is shown at the bottom of the NerdDinner link. –  Turnkey Jun 20 '12 at 17:22
add comment

4 Answers

up vote 2 down vote accepted

I made a SQL Stored Procedure

    @pagNo int, 
    @pageSize int
    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());
share|improve this answer
This is the SP version. If you have other solutions please fill me in. –  Misi Jun 19 '12 at 15:58
How is the db object defined? –  Turnkey Jun 19 '12 at 16:04
I have a SQL table and a SQL view(the view displays rows from this table plus some unimportant data(~ 1000 records) from another table) with ~ 400.000 records. Do you want to see the SQL script for the view and from the table ? –  Misi Jun 19 '12 at 17:22
No you answered my question above and I think I understand it now. –  Turnkey Jun 19 '12 at 20:59
@Misi Can you explain how this was displayed in the view / How to display paged results in view (when we are doing it the SP way)?? –  stylojack_10 Sep 2 '13 at 12:19
add comment

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.

share|improve this answer
You have written what I have in my commented line from my return. I clocked without Skip() and Take() at 1 minute and a half and with Skip() and Take() at 25 seconds. In SQL, from the same table, if I write TOP 1000 it will return 1000 rows in 1 second –  Misi Jun 19 '12 at 13:52
What sort of variable is itemqry if you hover over it in Visual Studio? –  Turnkey Jun 19 '12 at 15:30
{System.Data.Objects.ObjectQuery<Comenzi.Models.vvItem>} –  Misi Jun 19 '12 at 17:20
I'm not as familiar with ObjectQuery but it sounds like it's not playing nicely with deferred execution. Which is surprising since it implements IQueryable interface. I'll update the answer with another suggestion. –  Turnkey Jun 19 '12 at 20:54
add comment
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()); 
share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer


