Sorting jqGrid in ASP.NET MVC client view with jQuery and LINQ-to-Entities
Solution 1
There are two basic ways to handle this. The grid can sort the data itself. I can't remember how to turn this on, because I never use this option. Generally, I work with datasets which are too large to return to the page, so I use the grid's paging features. This requires doing this sort on the server, since the grid will not see the entire dataset.
To do the paging on the server, add a sidx and a sord (both strings) argument to your action. sidx will be the name of the column to sort on. sord will be the direction, asc or desc.
I have a demo project which shows how to do this (using LINQ to Objects). But using LINQ to Entities is almost identical; I use LINQ to Entities in my production/non-demo code. Download the demo solution and look for yourself.
Solution 2
OK, I should have posted this when I figured it out, but I ended up getting caught up in other tasks. Here is what I did that worked with LINQ to Entities, implemented for a report entity. First, the code to load the jqGrid with a default search was simple (once I figured out what I had missed):
$(document).ready(function() {
// Set up jqGrid for the report search results table.
// This is displayed in a tab in the bottom section of the master page.
$("#searchResultList").jqGrid({
url: '/Report/GetResultsL2E/',
datatype: 'json',
mtype: 'GET',
colNames: ['', 'ID', 'Title', 'Post_Date', 'Start_Date', 'End_Date', 'Summary'],
colModel: [
{ name: 'act', index: 'act', width: 75, sortable: false },
{ name: 'ID', index: 'ID', width: 40, align: 'left', hidden: true },
{ name: 'Title', index: 'Title', width: 150, align: 'left' },
{ name: 'Post_Date', index: 'Post_Date', width: 80, align: 'left', formatter: 'date' },
{ name: 'Start_Date', index: 'Start_Date', width: 80, align: 'left', formatter: 'date' },
{ name: 'End_Date', index: 'End_Date', width: 80, align: 'left', formatter: 'date' },
{ name: 'Summary', index: 'Summary', width: 240, align: 'left' }
],
pager: jQuery('#searchResultPager'),
rowNum: 10,
rowList: [5, 10, 20, 50],
sortname: 'Title',
sortorder: "asc",
viewrecords: true,
imgpath: '/Scripts/jqGrid/themes/green/images',
caption: 'Report Search Results',
editurl: "/Report/Edit",
scroll: true,
height: 'auto',
recordtext: ' Reports',
pgtext: ' of ',
multiselect: true,
multiboxonly: true, //adds check box column
altRows: true,
loadComplete: function() {
var ids = jQuery("#searchResultList").getDataIDs();
for (var i = 0; i ";
se = "";
ce = "";
jQuery("#searchResultList").setRowData(ids[i], { act: be + se + ce })
}
}
}).navGrid('#searchResultPager',
{ edit: false, add: false, del: false, search: false }, //options
{height: 280, reloadAfterSubmit: false }, // edit options
{height: 280, reloadAfterSubmit: false }, // add options
{reloadAfterSubmit: false }, // del options
{} // search options
);
});
The method to load the default search set returns the first page of the total set of reports available:
/// /// Query the ReportSet to return a paged, sorted set of Report entity properties in response to a call from a view. /// /// The name of the column to use for sorting. /// The order of the sort (ascending or descending). /// The number of the page to return to the calling process. /// The number of rows to return for the page. /// This ActionResult returns a JSON result to be used by a jqGrid using the jQuery library. /// jQuery requires a script tag linking the jQuery.js script. /// jqGrid requires stylesheet links to the following scripts and stylesheets: /// /// jQuery/themes/base/ui.all.css /// jqGrid/themes/green/grid.css (or other theme CSS file) /// jqGrid/jquery.jqGrid.js /// jqGrid/grid.base.js /// jqGrid/js/jqModal.js /// jqGrid/js/jqDnR.js /// public ActionResult GetResultsL2E(string sidx, string sord, int page, int rows) { int pageIndex = Convert.ToInt32(page) - 1; int pageSize = rows; int totalRecords = _db.ReportSet.Count(); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize); int startRecord = pageIndex * pageSize; List rowStrings = new List(); // Get all of the reports in the model in a fixed order (for comparison). //var reports = from item in _db.ReportSet // orderby item.Start_Date, item.Title // select new { item.ID, item.Title, item.Post_Date, // item.Start_Date, item.End_Date, item.Summary }; // Get all of the reports in the model in a dynamic order passed from jqGrid. string orderBytext = ""; orderBytext = string.Format("it.{0} {1}", sidx, sord); var reports = _db.ReportSet .OrderBy(orderBytext); List stringList = new List(); int counter = reports.Count(); foreach (var report in reports) { var rowString = new { id = report.ID, cell = new[] { "", report.ID.ToString(), report.Title, report.Post_Date.ToShortDateString(), report.Start_Date.ToShortDateString(), report.End_Date.ToString(), report.Summary.ToString()} }; stringList.Add(rowString); } var rowsOut = new object[counter]; for (int i = 0; i
I later added another method to respond to the user selecting columns to sort, using the PredicateBuilder discussed from the Albaharis' book C# in a Nutshell's section on Dynamically Composing Expression Predicates. I discussed my solution to that in a question I started on MSDN at PredicateBuilder fails on nested predicates with LINQ to Entities
Tim Rourke
Updated on June 24, 2022Comments
-
Tim Rourke almost 2 years
I'm a jQuery noob, so I'm sure I'm missing something simple here.
I've got the jqGrid working with an action that creates JSON data from a LINQ-to-Entities operation. But when I click on the column headers in the browser, the rows don't sort. The ascending/descending indicator shows up, but nothing else happens.
The necessary JavaScript and CSS links are in the master page header:
<script src="/Scripts/jquery-1.3.2.min.js" type="text/javascript"></script> <!-- CSS and JavaScript files for jqGrid to display on Details pages --> <link rel="stylesheet" type="text/css" href="/scripts/jQuery/jqGrid-3.4.4/themes/green/grid.css" title="green" media="screen" /> <script src="/Scripts/jQuery/jqGrid-3.4.4/jquery.jqGrid.js" type="text/javascript"></script> <script src="/Scripts/jQuery/jqGrid-3.4.4/js/jqModal.js" type="text/javascript"></script> <script src="/Scripts/jQuery/jqGrid-3.4.4/js/jqDnR.js" type="text/javascript"></script>
Here's my initialization code:
// jqGrid setup. $("#gridlist").jqGrid({ url: '/Contact/GridData', datatype: 'json', mtype: 'GET', colNames: ['ID', 'First Name', 'Last Name', 'Organization'], colModel: [ { name: 'id', index: 'id', width: 40, align: 'left', resizable: true }, { name: 'first_name', index: 'first_name', width: 200, align: 'left', resizable: true, sortable: true, sorttype: "text" }, { name: 'last_name', index: 'last_name', width: 200, align: 'left', resizable: true, sortable: true, sorttype: "text" }, { name: 'organization', index: 'organization', width: 300, align: 'left', resizable: true, sortable: true, sorttype: "text"}], pager: jQuery('#pager'), rowNum: 5, rowList: [5, 10, 20, 50], repeatitems: false, viewrecords: true, imgpath: '/scripts/jQuery/jqGrid-3.4.4/themes/green/images', caption: 'Contacts' });
And here's the HTML:
<h3>My Grid Data</h3> <table id="gridlist" class="scroll" cellpadding="0" cellspacing="0"> </table> <div id="pager" class="scroll" style="text-align:center;"> </div>
And, just for completeness, the action method:
public ActionResult GridData() { var page = new { page = 1 }; IEnumerable contacts = _db.ContactSet; int i = 0; var rows = new object[contacts.Count()]; foreach (Contact contact in contacts) { rows[i] = new { id = contact.ID, cell = new[] { contact.ID.ToString(), contact.First_Name, contact.Last_Name, contact.Organization } }; i++; } var result = new JsonResult(); result.Data = new { page = 1, records = 2, rows, total = 1 }; return result; }
Any ideas what obvious setting I'm missing here?