Server side paging and sorting Best practice?

Question

I am using the following method to get data for my jqgrid's subgrid and its working completely fine.

Note that this method is used to implement server-side sorting and paging.

Now the query I have is as you can see in the line

List<SomeEntity> myList = _service.GetSomeData(id); here a database call is made and all records are fetched.

So I was just not very sure, So I just wanted to know if this is in with the best practice to implement serverside paging and

public JsonResult GetData(string folderId, string sidx, string sord, int page, int rows) {
    int id = int.Parse(folderId);
    List < SomeEntity > myList = _service.GetSomeData(id);

    const int pageSize = 5;

    // total
    double totalPages = Math.Ceiling((double) myList.Count() / pageSize);

    // sort
    if (sord == "asc") {
        myList = myList.OrderBy(m = > m.Name).ToList();
    }
    else {
        myList = myList.OrderByDescending(m = > m.Name).ToList();
    }

    // paging
    myList = myList.Skip((page - 1) * pageSize).Take(pageSize).ToList();

    var jsonData = new {
        total = totalPages, records = domainList.Count, page,

        rows = myList
    };

    return Json(jsonData, JsonRequestBehavior.AllowGet);
}​

Answer 1

It looks like you're returning all the data from your service (_service) and then paging the results. Also, it looks like you are going to make the same request everytime a paging request is made. If true, then I think this is inefficient.

Your service (_service) should handle the Take and Skip functionality (pass as parameters), thereby reducing the number of records that are fetched and sent over the wire. You didn't post the code for your GetSomeData(id) method. Is it returning an IEnumerable or IQueryable? This will also have a bearing on performance/efficiency.

I hope I haven't misunderstood your code or question.

Answer 2

Your main problem is that you assign result of _service.GetSomeData(id) to List<SomeEntity>. In any way you should use ObjectQuery<SomeEntity> or IQueryable<SomeEntity> (depend on the used database technology) to be able to use sorting and paring on the SQL Server side. Your current code just get all the data, then sort it and get the required page in the next statement. I could recommend you to look at the code from the answer or more recent code from another answer more examples how sorting, paging and filtering of data can be implemented for jqGrid.