I use jqGrid and the examples from Greg here helped me a lot. However I have one problem I cannot solve:
My Linq statement uses joins to collect data from three tables:
var queryDetails = (from survey in pagedQuery
join subproj in db.t_onlinesubproject
on survey.SubPid equals subproj.datsubprojectid
join proj in db.t_onlineproject
on subproj.datprojectid equals proj.datprojectid
select new
{
survey.scriptID,
proj.projectnumber,
proj.projecttitlesap,
subproj.subprojectname,
survey.Projectname,
survey.platform,
survey.Status,
survey.Programmer
}).ToList();
Whenever I try to search or order by another field than from the table survey I get an EntitySqlException, e.g. projectnumber is not an element of the current loaded schema.
When I search or order fields from the main table (scriptID, Projectname, platform, Status, Programmer), everything works fine,
projectnumber, projecttitlesap and subprojectname leads to above mentioned error.
Any idea?
Here the important parts of my sources:
View:
$(function () {
$("#grid").jqGrid({
url: '@Url.Action("Projects", "Json")',
datatype: 'json',
mtype: 'POST',
colNames: ['id', 'Project number', 'Project title', 'Subproject title', 'Scripters Project title', 'Platform', 'Status', 'Scripter'],
colModel: [
{ name: 'scriptID', index: 'scriptID', width: 20, align: 'right', sorttype: 'int' },
{ name: 'projectnumber', index: 'projectnumber', width: 70, align: 'right' },
{ name: 'projecttitlesap', index: 'projecttitlesap', width: 150, align: 'left' },
{ name: 'subprojectname', index: 'subprojectname', width: 150, align: 'left' },
{ name: 'Projectname', index: 'Projectname', width: 150, align: 'left' },
{ name: 'platform', index: 'platform', width: 8, align: 'center', stype: 'select', surl: '@Url.Action("platformSelect", "Json")', searchoptions: { sopt: ['eq']} },
{ name: 'Status', index: 'Status', width: 60, align: 'left', stype: 'select', surl: '@Url.Action("statusSelect", "Json")', searchoptions: { sopt: ['eq']} },
{ name: 'Programmer', index: 'Programmer', width: 100, align: 'left', stype: 'select', surl: '@Url.Action("scripterSelect", "Json")', searchoptions: { sopt: ['eq']} },
],
pager: jQuery('#pager'),
rowNum: 50,
height: 500,
autowidth: true,
rowList: [5, 10, 20, 50, 100, 200],
ignoreCase: true,
viewrecords: true,
imgpath: '@Url.Content("~/Content/themes/sunny/images")',
caption: 'Projects',
hidegrid: false,
sortable: true,
sortname: $.cookie("grid_index_name") ? $.cookie("grid_index_name") : "scriptID",
sortorder: $.cookie("grid_sort_order") ? $.cookie("grid_sort_order") : "desc",
toolbar: [false, "top"]
});
Controller:
public JsonResult Projects(string sidx, string sord, int page, int rows, bool _search, string filters)
{
var serializer = new JavaScriptSerializer();
Filters f = (!_search || string.IsNullOrEmpty(filters)) ? null : serializer.Deserialize<Filters>(filters);
ObjectQuery<t_scripting> filteredQuery = (f == null ? db.t_scripting : f.FilterObjectSet(db.t_scripting));
filteredQuery.MergeOption = MergeOption.NoTracking; // we don't want to update the data
var totalRecords = filteredQuery.Count();
var pagedQuery = filteredQuery.Skip("it." + sidx + " " + sord, "@skip", new ObjectParameter("skip", (page - 1) * rows)).Top("@limit", new ObjectParameter("limit", rows));
// to be able to use ToString() below which is NOT exist in the LINQ to Entity
var queryDetails = (from survey in pagedQuery
join subproj in db.t_onlinesubproject on survey.SubPid equals subproj.datsubprojectid
join proj in db.t_onlineproject on subproj.datprojectid equals proj.datprojectid
select new
{
survey.scriptID,
proj.projectnumber,
proj.projecttitlesap,
subproj.subprojectname,
survey.Projectname,
survey.platform,
survey.Status,
survey.Programmer
}).ToList();
var jsonData = new
{
total = (totalRecords + rows - 1) / rows,
page,
records = totalRecords,
rows =
(from survey in queryDetails
select new
{
id = survey.scriptID,
cell = new string[] {
survey.scriptID.ToString(),
survey.projectnumber,
survey.projecttitlesap,
survey.subprojectname,
survey.Projectname,
survey.platform != null ? (from p in platforms where (p.id == survey.platform) select p.abbreviation).Single() : String.Empty,
survey.Status != null ? (from s in stati where s.id == survey.Status select s.status).Single() : String.Empty,
survey.Programmer != null ? (from p in programmerAndCommissioner where p.id == survey.Programmer select p.forename + " " + p.surname).Single() : String.Empty
}
}).ToArray()
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
Helpers:
public class Filters
{
public enum GroupOp
{
AND,
OR
}
public enum Operations
{
eq, // "equal"
ne, // "not equal"
lt, // "less"
le, // "less or equal"
gt, // "greater"
ge, // "greater or equal"
bw, // "begins with"
bn, // "does not begin with"
//in, // "in"
//ni, // "not in"
ew, // "ends with"
en, // "does not end with"
cn, // "contains"
nc // "does not contain"
}
public class Rule
{
public string field { get; set; }
public Operations op { get; set; }
public string data { get; set; }
}
public GroupOp groupOp { get; set; }
public List<Rule> rules { get; set; }
private static readonly string[] FormatMapping = {
"(it.{0} = @p{1})", // "eq" - equal
"(it.{0} <> @p{1})", // "ne" - not equal
"(it.{0} < @p{1})", // "lt" - less than
"(it.{0} <= @p{1})", // "le" - less than or equal to
"(it.{0} > @p{1})", // "gt" - greater than
"(it.{0} >= @p{1})", // "ge" - greater than or equal to
"(it.{0} LIKE (@p{1}+'%'))", // "bw" - begins with
"(it.{0} NOT LIKE (@p{1}+'%'))", // "bn" - does not begin with
"(it.{0} LIKE ('%'+@p{1}))", // "ew" - ends with
"(it.{0} NOT LIKE ('%'+@p{1}))", // "en" - does not end with
"(it.{0} LIKE ('%'+@p{1}+'%'))", // "cn" - contains
"(it.{0} NOT LIKE ('%'+@p{1}+'%'))" //" nc" - does not contain
};
internal ObjectQuery<T> FilterObjectSet<T>(ObjectQuery<T> inputQuery) where T : class
{
if (rules.Count <= 0) return inputQuery;
var sb = new StringBuilder();
var objParams = new List<ObjectParameter>(rules.Count);
foreach (Rule rule in rules)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(rule.field);
if (propertyInfo == null) continue; // skip wrong entries
if (sb.Length != 0) sb.Append(groupOp);
var iParam = objParams.Count;
sb.AppendFormat(FormatMapping[(int)rule.op], rule.field, iParam);
ObjectParameter param;
//nullable workaround
string type = "";
if (propertyInfo.PropertyType.FullName.StartsWith("System.Nullable"))
{
Match m = Regex.Match(propertyInfo.PropertyType.FullName, @"\[\[(.+?),");
if (m.Groups.Count < 2) type = propertyInfo.PropertyType.FullName;
type = m.Groups[1].ToString();
}
else type = propertyInfo.PropertyType.FullName;
//end nullable workaround
//switch (propertyInfo.PropertyType.FullName)
switch (type)
{
case "System.Int32": // int
param = new ObjectParameter("p" + iParam, Int32.Parse(rule.data));
break;
case "System.Int64": // bigint
param = new ObjectParameter("p" + iParam, Int64.Parse(rule.data));
break;
case "System.Int16": // smallint
param = new ObjectParameter("p" + iParam, Int16.Parse(rule.data));
break;
case "System.SByte": // tinyint
param = new ObjectParameter("p" + iParam, SByte.Parse(rule.data));
break;
case "System.Single": // Edm.Single, in SQL: float
param = new ObjectParameter("p" + iParam, Single.Parse(rule.data));
break;
case "System.Double": // float(53), double precision
param = new ObjectParameter("p" + iParam, Double.Parse(rule.data));
break;
case "System.Boolean": // Edm.Boolean, in SQL: bit
param = new ObjectParameter("p" + iParam,
String.Compare(rule.data, "1", StringComparison.Ordinal) == 0 ||
String.Compare(rule.data, "yes", StringComparison.OrdinalIgnoreCase) == 0 ||
String.Compare(rule.data, "true", StringComparison.OrdinalIgnoreCase) == 0 ?
true :
false);
break;
default:
// TO DO: Extend to other data types
// binary, date, datetimeoffset,
// decimal, numeric,
// money, smallmoney
// and so on
param = new ObjectParameter("p" + iParam, rule.data);
break;
}
objParams.Add(param);
}
ObjectQuery<T> filteredQuery = inputQuery.Where(sb.ToString());
foreach (var objParam in objParams)
filteredQuery.Parameters.Add(objParam);
return filteredQuery;
}
}
