jqGrid:使用 Gregs 示例进行搜索和排序不适用于 Join

本文关键字:搜索 排序 不适用 Join 适用于 使用 Gregs jqGrid | 更新日期: 2023-09-27 17:55:14

我使用jqGrid,Greg的例子对我帮助很大。但是我有一个问题无法解决:

我的 Linq 语句使用联接从三个表中收集数据:

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();

每当我尝试按表调查以外的其他字段进行搜索或排序时,我都会得到一个 EntitySqlException,例如 projectnumber 不是当前加载的架构的元素。

当我从主表中搜索或排序字段(脚本ID,项目名称,平台,状态,程序员)时,一切正常,
项目编号,项目标题AP和子项目名称会导致上述错误。

知道吗?

以下是我来源的重要部分:
视图:

$(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"]
    });

控制器:

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);

}

助手:

    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;
    }
}

jqGrid:使用 Gregs 示例进行搜索和排序不适用于 Join

您确实应该按 proj.projectnumber、proj.projecttitlesap 和 subproj.subprojectname 排序。 如果没有前缀(proj 和 subproj),您将收到该错误,因为您从调查、调查开始加入。字段名称] 和 [字段名称] 相同,但项目。[字段名称] 和子项目。[字段名称] 不相等。尝试在 jqGrid 中找到一种方法来指定排序表达式,例如 GridView 中的 SortExpression asp.net。