我试图使用MvcjQgrid实现jQgrid,我得到这个异常.
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'.
虽然OrdeyBy在Skip方法之前使用为什么会生成异常？怎么解决？
我在控制器中遇到了异常：
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方法和CountJobDescription方法
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);
}
最后FilterJobDescription和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;
}
解决方法
异常意味着如果您应用“跳过”,则您总是需要排序输入,也就是在用户不点击列进行排序的情况下.我可以想象,在用户甚至可以点击列标题之前首次打开网格视图时,没有指定排序列.为了抓住这种情况,我建议在没有给出其他排序条件时定义一些你想要的默认排序,例如：
switch (sortColumn)
{
case "JobDescriptionID":
return (sortOrder == "desc")
? jobdescriptions.OrderByDescending(j => j.JobDescriptionID)
: jobdescriptions.OrderBy(j => j.JobDescriptionID);
case "JobDescriptionTitle":
return (sortOrder == "desc")
? jobdescriptions.OrderByDescending(j => j.JobDescriptionTitle)
: jobdescriptions.OrderBy(j => j.JobDescriptionTitle);
// etc.
default:
return jobdescriptions.OrderBy(j => j.JobDescriptionID);
}
编辑
关于您的后续问题根据您的评论：您不能在LINQ to Entities查询中使用ToString().而下一个问题是您无法在查询中创建一个字符串数组.我建议从数据库中加载其原始类型的数据,然后将其转换为内存中的字符串(和字符串数组)：
rows = (from j in jobdescription
select new
{
JobDescriptionID = j.JobDescriptionID,JobTitle = j.JobTitle,JobTypeName = j.JobType.JobTypeName,JobPriorityName = j.JobPriority.JobPriorityName,Rate = j.JobType.Rate,CreationDate = j.CreationDate,JobDeadline = j.JobDeadline
})
.AsEnumerable() // DB query runs here,the rest is in memory
.Select(a => new
{
id = a.JobDescriptionID,cell = new[]
{
a.JobDescriptionID.ToString(),a.JobTitle,a.JobTypeName,a.JobPriorityName,a.Rate.ToString(),a.CreationDate.ToShortDateString(),a.JobDeadline.ToShortDateString()
}
})
.ToArray()
