asp.net mvc 2 - C# MVC2 Jqgrid - what is the correct way to do server side paging? -

i have jqgrid database table has few thousand rows, jqrid shows 15 @ time.

it should displaying (it doesnt take long query 15 rows). instead takes 10 - 20 seconds, indicates retrieving entire table each time.

the grid defined this:

$("#products").jqgrid({ url: url, mtype: "get", datatype: "json", jsonreader: {     root: "rows", page: "page", total: "total", records: "records", repeatitems: false,     userdata: "userdata",id: "id"}, colnames: ["product id","product code", ... etc ], colmodel: [{ name: "id", ... etc}], viewrecords: true, height: 400, width: 800, pager: $("#jqgpager"), rownum: 15, rowlist: [50, 100, 200],  autowidth: true, multiselect: false 

and server side (mvc2 action) this:

var model = (from p in products     select new     {     p.id, p.productcode, p.productdescription,     allocatedqty = p.warehouseproducts.sum(wp => wp.allocatedquantity),     qtyonhand = p.warehouseproducts.sum(wp => wp.onhandquantity)     }).asqueryable();      jsonresult json = json(model.togriddata(         page, rows, orderby, "",          new[] { "id", "productcode", "productdescription", "allocatedqty", "qtyonhand" }),             jsonrequestbehavior.allowget); 

and model.togriddata extension method this:

var data =_service.getall();  var page = data.skip((index) * pagesize).take(pagesize); list.add(page.asenumerable); 

and i'm bit lost problem lies:

• have set jqgrid paging options incorrectly?
• have written bad linq pulls rows regardless? eg sum() cause rows read?
• have done .skip().take() incorrectly?
• have missed else entirely?

edit

when comparing code example posted oleg can see things in order:

1. getall
2. select model fields
3. page

wheras olegs sample seems in order:

1. getall
2. page
3. select model fields

so i've changed simpler implementation:

public actionresult getproductlist(int page, int rows, string sidx, string sord,  string searchoper, string searchfield, string searchstring) {         list<product> products = _productservice.getallproducts();         int totalrecords = products.count();          var pageddata = products.skip((page > 0 ? page - 1 : 0) * rows).take(rows);          var model = (from p in pageddata                     select new                     {                         p.id, p.productcode, p.productdescription,                         barcode = string.empty, unitofmeasure = string.empty,                         p.packsize, allocatedqty = string.empty,                         qtyonhand = string.empty }).tolist();          var jsondata = new         {             total = page, records = totalrecords,             page = (totalrecords + rows - 1) / rows, rows = model         };          return json(jsondata, jsonrequestbehavior.allowget); } 

however has new problem:

a circular reference detected while serializing object of type 'system.data.entity.dynamicproxies.product_fa935d3899e2... 

the difference can see oleg's sample getall returns iqueryable mine list.

you should post more full code. model.togriddata not defined in current code example. how caclulate index imput patrameters , on unclear. having model.togriddata() 1 can whether output program produce correspond jsonreader define.

i recommend this old answer, both paging , sorting used. in one more answer find more references code examples.