I was trying to implement the jQgrid
using MvcjQgrid
and i got this exception.
System.NotSupportedException was unhandled by user code
Message=The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.
Though OrdeyBy is used before Skip method why it is generating the exception? How can it be solved?
I encountered the exception in the controller:
public ActionResult GridDataBasic(GridSettings gridSettings)
{
var jobdescription = sm.GetJobDescription(gridSettings);
var totalJobDescription = sm.CountJobDescription(gridSettings);
var jsonData = new
{
total = totalJobDescription / gridSettings.PageSize + 1,
page = gridSettings.PageIndex,
records = totalJobDescription,
rows = (
from j in jobdescription
select new
{
id = j.JobDescriptionID,
cell = new[]
{
j.JobDescriptionID.ToString(),
j.JobTitle,
j.JobType.JobTypeName,
j.JobPriority.JobPriorityName,
j.JobType.Rate.ToString(),
j.CreationDate.ToShortDateString(),
j.JobDeadline.ToShortDateString(),
}
}).ToArray()
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
GetJobDescription Method and CountJobDescription Method
public int CountJobDescription(GridSettings gridSettings)
{
var jobdescription = _dataContext.JobDescriptions.AsQueryable();
if (gridSettings.IsSearch)
{
jobdescription = gridSettings.Where.rules.Aggregate(jobdescription, FilterJobDescription);
}
return jobdescription.Count();
}
public IQueryable<JobDescription> GetJobDescription(GridSettings gridSettings)
{
var jobdescription = orderJobDescription(_dataContext.JobDescriptions.AsQueryable(), gridSettings.SortColumn, gridSettings.SortOrder);
if (gridSettings.IsSearch)
{
jobdescription = gridSettings.Where.rules.Aggregate(jobdescription, FilterJobDescription);
}
return jobdescription.Skip((gridSettings.PageIndex - 1) * gridSettings.PageSize).Take(gridSettings.PageSize);
}
And Finally FilterJobDescription and OrderJobDescription
private static IQueryable<JobDescription> FilterJobDescription(IQueryable<JobDescription> jobdescriptions, Rule rule)
{
if (rule.field == "JobDescriptionID")
{
int result;
if (!int.TryParse(rule.data, out result))
return jobdescriptions;
return jobdescriptions.Where(j => j.JobDescriptionID == Convert.ToInt32(rule.data));
}
// Similar Statements
return jobdescriptions;
}
private IQueryable<JobDescription> orderJobDescription(IQueryable<JobDescription> jobdescriptions, string sortColumn, string sortOrder)
{
if (sortColumn == "JobDescriptionID")
return (sortOrder == "desc") ? jobdescriptions.OrderByDescending(j => j.JobDescriptionID) : jobdescriptions.OrderBy(j => j.JobDescriptionID);
return jobdescriptions;
}
I had the same type of problem after sorting using some code from Adam Anderson that accepted a generic sort string in OrderBy.
After getting this excpetion, i did lots of research and found that very clever fix:
Hope that helps !
SP
The exception means that you always need a sorted input if you apply
Skip
, also in the case that the user doesn't click on a column to sort by. I could imagine that no sort column is specified when you open the grid view for the first time before the user can even click on a column header. To catch this case I would suggest to define some default sorting that you want when no other sorting criterion is given, for example:Edit
About your follow-up problems according to your comment: You cannot use
ToString()
in a LINQ to Entities query. And the next problem would be that you cannot create astring
array in a query. I would suggest to load the data from the DB with their native types and then convert afterwards to strings (and to the string array) in memory: