Trying to paging with ViewModel in MVC4

Question

I dont want to get all items from database and convert them to a specific viewmodel then use PagedList library to page all these data.

For example I want to get 20 items at a page but I dont understand when should I convert my model to the viewmodel ?

This is the method

public ViewResult Index(ProductReviewRelationalFilterViewModel filterViewModel)
{
    var items =
        _productreviewService.GetItems(filterViewModel.ProductId, filterViewModel.UserId)
                             .Select(AutoMapper.Mapper.Map<ProductReviewListViewModel>);

    var result = items.ToPagedList(filterViewModel.PageIndex, 20);

    return View(result );
}

This method gets all the items then convert them to the ProductReviewListViewModel and then make paging for it.

What I want is getting only 20 items from db and get same result as above ?

Note: GetItems method return IQueryable

ADDED SOLUTION

I did GetItems method, also return total items count as out int parameter. In controller action, I use StaticPagedList method to create paging manually like this link says

Answer 1

I haven't tested the below but something to this affect:

public ViewResult Index(ProductReviewRelationalFilterViewModel filterViewModel)
{
    var pageSize = 20;
    var skipRecords = (filterViewModel.PageIndex > 1 ? (filterViewModel.PageIndex - 1) * pageSize + (filterViewModel.PageIndex - 2) : 0);
    var items =
        _productreviewService.GetItems(filterViewModel.ProductId, filterViewModel.UserId)
                            .Skip(skipRecords).Take(pageSize)
                            .Select(AutoMapper.Mapper.Map<ProductReviewListViewModel>);

    var result = items.ToPagedList(filterViewModel.PageIndex, 20);

    return View(result );
}

Formula: (pageIndex > 1 ? (pageIndex - 1) * pageSize + (pageIndex - 2) : 0)

page 1 = 0 = skip 0 take 20
page 2 = (2 - 1) 1 * 20 + (2 - 2) 0 = skip 20 take 20
page 3 = (3 - 1) 2 * 20 + (3 - 2) 1 = skip 41 take 20
page 4 = (4 - 1) 3 * 20 + (4 - 2) 2 = skip 62 take 20 
page 5 = (5 - 1) 4 * 20 + (5 - 2) 3 = skip 83 take 20 

Results:
page 1 - skip 0 take 20 (shows record 0 - 20)
page 2 - skip 20 take 20 (shows record 21 - 41)
page 3 - skip 41 take 20 (shows record 42 - 62)
page 4 - skip 62 take 20 (shows record 63 - 83)

Because its an IQueryable that means you should be using Take() and Skip() to grab the records out of the result set, it will only execute the query once you call .ToPagedList on it.

One caveat with the above is that I'm not sure what the ToPagedList() implementation looks like, I'd assume you'd need to tell it the total records that match your query so that it can calculate how many page links to output.