Sortable JqGrid using LINQ to MySQL (DbLinq) and Dynamic LINQ - Orderby doesn't work
Try with the following
public ActionResult All(string sidx, string sord, int page, int rows)
{
IQueryable<Ticket> repository = ZTRepository.GetAllTickets();
int totalRecords = repository.Count();
// first sorting the data as IQueryable<Ticket> without converting ToList()
IQueryable<Ticket> orderdData = repository;
System.Reflection.PropertyInfo propertyInfo =
typeof(Ticket).GetProperty (sidx);
if (propertyInfo != null) {
orderdData = String.Compare(sord,"desc",StringComparison.Ordinal) == 0 ?
(from x in repository
orderby propertyInfo.GetValue (x, null) descending
select x) :
(from x in repository
orderby propertyInfo.GetValue (x, null)
select x);
}
// if you use fields instead of properties, then one can modify the code above
// to the following
// System.Reflection.FieldInfo fieldInfo =
// typeof(Ticket).GetField (sidx);
// if (fieldInfo != null) {
// orderdData = String.Compare(sord,"desc",StringComparison.Ordinal) == 0 ?
// (from x in repository
// orderby fieldInfo.GetValue (x, null) descending
// select x) :
// (from x in repository
// orderby fieldInfo.GetValue (x, null)
// select x);
//}
// paging of the results
IQueryable<Ticket> pagedData = orderdData
.Skip ((page > 0? page - 1: 0) * rows)
.Take (rows);
// now the select statement with both sorting and paging is prepared
// and we can get the data
var rowdata = ( from ticket in tickets
select new {
id = ticket.ID,
cell = new String[] {
ticket.ID.ToString(), ticket.Hardware, ticket.Issue,
ticket.IssueDetails, ticket.RequestedBy,
ticket.AssignedTo, ticket.Priority.ToString(),
ticket.State
}
}).ToList();
var jsonData = new {
total = page,
records = totalRecords,
total = (totalRecords + rows - 1) / rows,
rows = pagedData
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
Here I suppose that the type of your ticket object is Ticket.
Difficulty sorting Linq expression with jqGrid
I spent too much time to resolve this.
Please check Order LINQ by "string" name.
Linq To Entities - OrderBy
I recommed you to use PropertyInfo, GetProperty or FieldInfo, GetField depend on your data model. In the case you can implement OrderBy operation without any extensions. See the answer for details.
UPDATED: I reread your question carefully one more time. It seems to me, that your problem is inside of GetAllEquipment method. It returns List<Equipment> and not IQueryable<Equipment>. So the GetAllEquipment method get "SELECT * FROM it.Equipment" and return the data as a List which are no more Entity. With equipService.GetAllEquipment().AsQueryable() you will have IQueryable<Equipment> object, but you will not more use LINQ to Entity and so you should not use "it." prefix before the names.
jqGrid events not firing
If you use datatype: 'json' then the server is responsible for the data sorting and paging. Your current server code (GetRateTypes) don't do this. Look this old answer for example which shows how the sorting and paging can be implemented.
jqGrid does not populate with data
First of all if the server send back the data which you posted, the jqGrid will do displayed the results (see http://www.ok-soft-gmbh.com/jqGrid/jsonfromsvc.htm). Of cause jqGrid will works not really good, because you use StationId as the id, but all rows in your JSON data has the same value 50130 as the id. So, for example, if you select one row all rows will be selected.
The DateTime is not a standard JSON type and it is not supported currently by jqGrid (see this answer and this feature request). To fix the problem you have to make at least some small changes in both data and the jqGrid.
The current JSON data has a lot of data with null value. To reduce the size of empty data send from the server consider to use EmitDefaultValue attribute.
Moreover I find strange, that you not use parameters like
ajaxGridOptions: { contentType: "application/json" },
serializeRowData: function (data) {return JSON.stringify(data);}
(see another old answer). Probably your WFC don't receive currently any input parameters like int page, int rows, string sidx, string sord and so on). If you post at least prototype of your server method which you call.
UPDATED: How I promised before I created a small WCF application and a HTML page which call the WCF service.
Your current data has no id. The field StationId along is not a key because it is the same in different data rows. If you include id in your data you can include in the column definition the option key:true and jqGrid will use the data as the id. Because the example will be used only to display the data without data editing I included no id in the data send from the server. In the case jqGrid use integer counter starting with 1 as the row ids. If you decide to include editing features in the grid you will have to include as id in the data.
Now we go to the code. Because You wrote that you use Visual Studio 2010 and answer nothing about the version of .NET I created an application in .NET 4.0. The web.config:
<?xml version="1.0"?>
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
<system.serviceModel>
<standardEndpoints>
<webHttpEndpoint>
<standardEndpoint helpEnabled="true"
automaticFormatSelectionEnabled="true"/>
</webHttpEndpoint>
</standardEndpoints>
<behaviors>
<serviceBehaviors>
<behavior name="">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
</behavior>
</serviceBehaviors>
</behaviors>
<serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
</system.serviceModel>
</configuration>
File WeatherDataService.svc:
<%@ ServiceHost Factory="System.ServiceModel.Activation.WebServiceHostFactory"
Service="WfcToJqGrid.WeatherDataService" %>
File IWeatherDataService.cs:
using System;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
namespace WfcToJqGrid {
[ServiceContract]
public interface IWeatherDataService {
[OperationContract,
WebGet (RequestFormat = WebMessageFormat.Json,
ResponseFormat = WebMessageFormat.Json,
UriTemplate = "GetWeatherData?page={page}&rows={rows}" +
"&sidx={sortIndex}&sord={sortDirection}")]
WeatherDataForJqGrid GetDataForjqGrid (int page, int rows,
string sortIndex,
SortDirection sortDirection);
}
[DataContract]
public enum SortDirection {
[EnumMember (Value = "asc")]
Asc,
[EnumMember (Value = "desc")]
Desc
}
// jsonReader: { repeatitems: false }
[DataContract]
public class WeatherDataForJqGrid {
[DataMember (Order=0, Name = "total")]
public int Total { get; set; } // total number of pages
[DataMember (Order = 1, Name = "page")]
public int Page { get; set; } // current zero based page number
[DataMember (Order = 2, Name = "records")]
public int Records { get; set; } // total number of records
[DataMember (Order = 3, Name = "rows")]
public IEnumerable<WeatherData> Rows { get; set; }
}
[DataContract]
public class WeatherData {
[DataMember (Order=0)]
public int StationId { get; set; }
[DataMember (Order = 1)]
public string StationName { get; set; }
[DataMember (Order = 2)]
public DateTime Timestamp { get; set; }
[DataMember (Order = 3, EmitDefaultValue = false)]
public string MaxTemperature { get; set; }
[DataMember (Order = 4, EmitDefaultValue = false)]
public string MinTemperature { get; set; }
[DataMember (Order = 5, EmitDefaultValue = false)]
public string Precipitation { get; set; }
[DataMember (Order = 6, EmitDefaultValue = false)]
public string Snowfall { get; set; }
[DataMember (Order = 7, EmitDefaultValue = false)]
public string SnowDepth { get; set; }
}
}
File WeatherDataService.svc.sc:
using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Web;
using System.Net;
namespace WfcToJqGrid {
public class WeatherDataService : IWeatherDataService {
// we use very simple database model to simulate a real data
private static IQueryable<WeatherData> _repository = new List<WeatherData>{
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,1,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,2,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,3,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,4,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,5,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,6,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,7,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,8,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,9,8,0,0)},
new WeatherData { StationId = 50130, StationName = "ALAMOSA WSO AP",
Timestamp = new DateTime(1993,1,10,8,0,0)}
}.AsQueryable ();
public WeatherDataForJqGrid GetDataForjqGrid (int page, int rows,
string sortIndex,
SortDirection sortDirection){
int totalRecords = _repository.Count();
// sorting of data
IQueryable<WeatherData> orderdData = _repository;
System.Reflection.PropertyInfo propertyInfo =
typeof(WeatherData).GetProperty (sortIndex);
if (propertyInfo != null) {
orderdData = sortDirection == SortDirection.Desc ?
(from x in _repository
orderby propertyInfo.GetValue (x, null) descending
select x) :
(from x in _repository
orderby propertyInfo.GetValue (x, null)
select x);
}
// paging of the results
IEnumerable<WeatherData> pagedData = orderdData
.Skip ((page > 0? page - 1: 0) * rows)
.Take (rows);
// force revalidate data on the server on every request
if (WebOperationContext.Current != null)
WebOperationContext.Current.OutgoingResponse.Headers.Set (
HttpResponseHeader.CacheControl, "max-age=0");
return new WeatherDataForJqGrid {
Page = page,
Records = totalRecords,
Total = (totalRecords + rows - 1) / rows,
Rows = pagedData
};
}
}
}
(read more about caching "jqGrid data stored in browser cache?")
and default.htm:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Demonstration how use jqGrid to call WFC service</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/themes/redmond/jquery-ui.css" />
<link rel="stylesheet" type="text/css" href="http://www.ok-soft-gmbh.com/jqGrid/jquery.jqGrid-3.8/css/ui.jqgrid.css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="http://www.ok-soft-gmbh.com/jqGrid/jquery.jqGrid-3.8/js/i18n/grid.locale-en.js"></script>
<script type="text/javascript" src="http://www.ok-soft-gmbh.com/jqGrid/jquery.jqGrid-3.8/js/jquery.jqGrid.min.js"></script>
<script type="text/javascript" src="http://www.ok-soft-gmbh.com/jqGrid/json2.js"></script>
<script type="text/javascript">
//<![CDATA[
jQuery(document).ready(function () {
$("#list").jqGrid({
datatype: 'json',
url: 'WeatherDataService.svc/GetWeatherData',
jsonReader: { repeatitems: false },
loadui: "block",
mtype: 'GET',
rowNum: 5,
rowList: [5, 10, 20, 30],
viewrecords: true,
colNames: ['Station ID', 'Station Name', 'Timestamp', 'Max Temp',
'Min Temp', 'Precipitation', 'Snowfall', 'SnowDepth'],
colModel: [
{ name: 'StationId', index: 'StationId', width: 100 },
{ name: 'StationName', index: 'StationName', width: 150 },
{ name: 'Timestamp', index: 'Timestamp', align: 'right', width: 250,
formatter: function (cellvalue, options, rowObject) {
// possible characters like "+0100" at the end of string will be ignored
return new Date(parseInt(cellvalue.substr(6, cellvalue.length - 8), 10));
}
},
{ name: 'MaxTemperature', index: 'MaxTemperature', align: 'right', width: 100 },
{ name: 'MinTemperature', index: 'MinTemperature', align: 'right', width: 100 },
{ name: 'Precipitation', index: 'Precipitation', align: 'right', width: 100 },
{ name: 'Snowfall', index: 'Snowfall', align: 'right', width: 100 },
{ name: 'SnowDepth', index: 'SnowDepth', align: 'right', width: 100 },
],
pager: '#pager',
sortname: 'Timestamp',
sortorder: 'asc',
height: "100%",
width: "100%",
prmNames: { nd: null, search: null }, // we switch of data caching on the server
// and not use _search parameter
caption: 'Weather Records'
});
});
//]]>
</script>
</head>
<body>
<table id="list"><tr><td/></tr></table>
<div id="pager"></div>
</body>
</html>
You can download the full code here.
using jquery jqgrid
You can find some examples here, here and here.
ASP.Net MVC 3 JQGrid
EF doesn't support ToString method, you must retrieve the data without ToString and format
this should work
public ActionResult LinqGridData(string sidx, string sord, int page, int rows)
{
AssetEntities context = new AssetEntities();
var query = from e in context.Equipments
select e;
var count = query.Count();
var result = new
{
total = 1,
page = page,
records = count,
rows = query.Select(x => new { x.equipamentID, x.Category.categoryTitle,x.Department.title })
.ToList() // .AsEnumerable() whatever
.Select(x => new {
id = x.equipamentID,
cell = new string[] {
x.equipamentID.ToString(),
x.categoryTitle,
x.title
}})
.ToArray(),
};
return Json(result, JsonRequestBehavior.AllowGet);
}
Does DbLinq supports SQLite3?
The 'official' SQLite ADO.NET provider supports LINQ.
JSON results are returned in a different order than expected
I found the solution here: linq to entities orderby strange issue
The issue ultimately stems from the fact that Linq to Entities has trouble handling strings. When I was using the SqlFunctions.StringConvert method, this was incorrectly performing the conversion (although, I must admit that I don't fully understand why the order was then switched around).
In either case, per the above post, the solution for fixing the problem was to do the selection locally so that I could "force" Linq to Entities to work with strings properly. From this, my final code is:
var people = repository.FindAllPeople()
.OrderBy(sidx + " " + sord)
.Skip(pageIndex * pageSize)
.Take(pageSize);
// Due to a problem with Linq to Entities working with strings,
// all string work has to be done locally.
var local = people.AsEnumerable();
var rowData = local.Select(person => new
{
id = person.PersonID,
cell = new List<string> {
person.PersonID.ToString(),
person.PersonName
}
}
).ToArray();
var jsonData = new
{
total = totalPages,
page = page,
records = totalRecords,
rows = rowData
};
return Json(jsonData);
C# MVC2 Jqgrid - what is the correct way to do server side paging?
You should post more full code. The model.ToGridData is not defined in the current code for example. How you caclulate index from the imput patrameters and so on are also unclear. Only having model.ToGridData() one can say whether the output which your program produce are correspond to the jsonReader which you define.
I recommend you to look this old answer, where both paging and sorting are used. In one more answer you will find more references to code examples.
Related Topics
Server.Mappath - Physical Path Given, Virtual Path Expected
Where Are Clr-Defined Methods Like [Delegate].Begininvoke Documented
Return All Enumerables with Yield Return at Once; Without Looping Through
How to Add My New User Control to the Toolbox or a New Winform
Top Level Domain from Url in C#
No Access to the Session Information Through Signalr Hub. Is My Design Is Wrong
Why am I Getting 'One or More Types Required to Compile a Dynamic Expression Cannot Be Found.'
Resharper Formatting: Align Equal Operands
How to Simply 'Read' a File That Is in Use
No Itemchecked Event in a Checkedlistbox
Why am I Getting Sehexception When Calling Roleenvironment.Getconfigurationsettingvalue("Mykey")
Opening a "Known File Type" into Running Instance of Custom App - .Net
Image.Fromstream() Method Returns Invalid Argument Exception
Specified Key Is Not a Valid Size for This Algorithm
Mocking Static Methods Using Rhino.Mocks
Selecting Attribute Values with HTML Agility Pack
Caching the Result from a [N Async] Factory Method Iff It Doesn't Throw
Dynamically Cross-Join Multiple Different-Size Collections Together in Linq (C#)