Web API and paging

May 3, 2013 at 5:54 AM
Hey,

I want to start by telling that this Grid is awesome. I used it for most of my projects.

I have problem here with Web API and paging. I have two projects, first one is standard ASP.NET MVC4 website and the other one is Web API. I have Customer class on website and a method GetAll() to grab all customer data which will have more than 100.000 records. This GetAll() method is calling the Web API and the API will return all the data. The problem is, I have to make paging for the Web API because calling all the data at once is obviously really bad for bandwidth. But if I implement the paging on the API and only return let say 20 records for a page then Grid.MVC will showing there is only 1 page with 20 records instead of 5000 page with 100.000 records.

Could you please give advise how to solve this problem?

Sorry for my English. Thanks in advance
Coordinator
May 3, 2013 at 7:12 AM
Hi,

Can you provide some code how you use grid and web api method?
May 3, 2013 at 7:29 AM
Hi, this is my code

Website:

CustomerController:
        public virtual ActionResult Index()
        {
            List<Customer> list = Customer.FetchAll();
            ViewData.Model = list;
            ViewBag.Title = "Customer";
            return View();
        }
FetchAll()
        public static List<Data.Customer> FetchAll()
        {
            //call API URL ex: http://localhost:12345/api/Customer
            return API.List<Customer>("/Customer").OrderBy(x => x.FullName).ToList(); 
        }

        public static List<Data.Customer> FetchAll(int Page, int PageSize, string SortExp, string SortColumn)
        {
            //call API URL ex: http://localhost:12345/api/Customer?Page=1&PageSize=20&SortExp=Desc&SortColumn=CreatedDate
            return API.List<Customer>(string.Format("/Customer?Page={0}&PageSize={1}&SortExp={2}&SortColumn={3}", Page, PageSize, SortExp, SortColumn)).OrderBy(x => x.FullName).ToList(); 
        }
cshtml:
@model List<Customer>
@Html.Grid(Model).Columns(columns =>
{
    columns.Add(x => x.FullName)
        .Titled("Customer")
        .Sortable(true)
        .Filterable(true);

    columns.Add(x => x.Phone)
        .Titled("Phone Number")
        .Sortable(true)
        .Filterable(true);

    columns.Add(x => x.Email)
        .Titled("Email")
        .Sortable(true)
        .Filterable(true);

    columns.Add(x => x.Address)
        .Titled("Address")
        .Sortable(true)
        .Filterable(true);
}).WithPaging(20)

Web API:

CustomerController:
    public class CustomerController : ApiController
    {
        // GET api/values
        public IEnumerable<Customer> Get()
        {
            NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query);
            List<Customer> obj = null;
            if (nvc["phone"] != null)
            {
                obj = Customer.FetchByPhone(nvc["phone"]);
            }
            else
            {
                obj = Customer.FetchAll();
            }            
            return Customer.AsEnumerable(); 
        }

        public IEnumerable<Customer> Get(int Page, int PageSize, string SortExp, string SortColumn)
        {
            List<Customer> obj = Customer.FetchAll(Page, PageSize, SortExp, SortColumn);
            return Customer.AsEnumerable();
        }
    }
on Customer Class:
        public static List<Data.Customer> FetchAll()
        {
            return DB.Entities.Customers.OrderBy(x => x.ID).ToList();
        }

        public static List<Data.Customer> FetchAll(int Page, int PageSize, string SortExp, string SortColumn)
        {
            return DB.Entities.Customers.OrderBy(SortColumn + " " + SortExp).Skip(PageSize * (Page - 1)).Take(PageSize).ToList();
        }
Coordinator
May 3, 2013 at 8:16 AM
It's not trivial task to use built-in grid features with data source that has custom API, and here's why
As you now Grid.Mvc uses IQueryable<T> interface to construct query expressions to your data collection.
When you go to some page the grid invokes .Skip(N).Take(N) methods and when you sort data the grid invokes OrderBy or OrderByDescending methods etc.
In other words grid don't know how to query data from your data source. For example the initial collection can be sorted, filtered. Also to render total amount of pages grid need to invoke Count() of final modified collection;

There is some ways to solve this problem:
  1. The best way is to create custom IQueryable collection implementation that will translate grid queries to your data source: when user will sort the column -> grid will invoke OrderBy(x=>x.ColumnName) of your implementation -> you can parse this expression and translate it to "/Api/Customer?sortexpr=<column>" query. In this case you can use all grid features out of box.
    You can find additional infomation aout creating custom linq query provider: http://msdn.microsoft.com/en-us/library/bb546158.aspx
  2. The second way - render Pager separately. You can create custom implementation of IGridPager interface and render it using @Html.Partial("_GridPager", pager) html helper. Also in controller action you can query only selected page of data and pass it to the grid (view).
May 3, 2013 at 11:40 AM
Hi,

I can't see the first option as the solution. When I move to page 2 of the grid, grid queries will be translated as /Api/Customer?Page=2 and will be responded with only 20 records of data. Your Grid Pager won't draw any other page because the item count is only 20

But I managed to solve this with your second way, I made CustomGridPager implement IGridPager interface and render using @Html.Partial("_GridPager", pager) just like you said

My CustomGridPager is the same like your GridPager. The difference is only at this const
public const string DefaultPageQueryParameter = "grid-page";
I change it to "page" so your default GridPager won't read it and I can handle it at the action controller.
        public virtual ActionResult Index()
        {
            NameValueCollection nvc = HttpUtility.ParseQueryString(Request.QueryString.ToString());
            int Page = 1;
            if (nvc["page"] != null)
            {
                int.TryParse(Request.QueryString["page"].ToString(), out Page);
            }

            CustomGridPager pager = new CustomGridPager();
            pager.ItemsCount = Customer.Count();
            pager.PageSize = 20;
            ViewBag.Pager = pager;

            List<Customer> list = Customer.FetchAll(Page, 20, "DESC", "CreatedDate");
            ViewData.Model = list;
            ViewBag.Title = "Customer";
            return View();
        }
and then add @Html.Partial("_GridPager", ViewBag.Pager as CustomGridPager) at view right after grid render.


Thank You very much!!
Coordinator
May 4, 2013 at 5:27 AM
Hi,

I guess that grid Count() query should be translated to Customer.Count() method, like EntityFramework traslate to SELECT COUNT(*) query
Feb 26, 2014 at 9:06 AM
Hi Bukharin,

i have implemented the Custom Pager and it is working fine. But now whenever i select any other page in a pager it only select 1st page.
Coordinator
Mar 1, 2014 at 5:19 AM
Hi,

Try to debug you pager implementation. Inspect what model pager pass to _GridPager.cshtml view