title: jqGrid filtering records using Oleg's examples
body: <p>I am having a problem solving a couple of errors with two of my columns in my grid.  I created a sample DB with a couple of tables and created a jqGrid test application to display the data.  I wanted to do some searching/filtering of the data on the grid.  I used Oleg's examples of searching and filtering with MVC.  My first two columns (ID, Event Title) work perfect but any columns after that won't work.  I am sure I am missing something but can not see what I am missing.  Any help would be great.  Below is the code.</p>
  
  <p>Client Side:</p>
  
  <pre><code>var myGrid = $('#list'),
      decodeErrorMessage = function (jqXHR, textStatus, errorThrown) {
                  var html, errorInfo, i, errorText = textStatus + '\n' + errorThrown;
                  if (jqXHR.responseText.charAt(0) === '[') {
                      try {
                          errorInfo = $.parseJSON(jqXHR.responseText);
                          errorText = "";
                          for (i = 0; i &lt; errorInfo.length; i++) {
                              if (errorText.length !== 0) {
                                  errorText += "&lt;hr /&gt;";
                              }
                              errorText += errorInfo[i].Source + ": " + errorInfo[i].Message;
                          }
                      }
                      catch (e) { }
                  } else {
                      html = /&lt;body.*?&gt;([\s\S]*)&lt;\/body&gt;/.exec(jqXHR.responseText);
                      if (html !== null &amp;&amp; html.length &gt; 1) {
                          errorText = html[1];
                      }
                  }
                  return errorText;
              };
  
          myGrid.jqGrid({
              url: '../Admin/GetEventsGridData/',
              datatype: 'json',
              mtype: 'POST',
              colNames: ['ID', 'Event Title', 'Series', 'Occ', 'Department' ],  
              colModel: [
                          { name: 'EVENT_ID', index: 'EVENT_ID', editable: true, editoptions: { readonly: 'readonly' }, width: 25, searchoptions: { sopt: ['eq', 'ne', 'lt', 'le', 'gt', 'ge']} },
                          { name: 'EVENT_TITLE', index: 'EVENT_TITLE', editable: true, width: 205, searchoptions: { sopt: ['cn', 'nc', 'bw', 'bn', 'eq', 'ne', 'ew', 'en', 'lt', 'le', 'gt', 'ge']} },
                          { name: 'SERIES_DESCRIPTION', index: 'SERIES_DESCRIPTION', editable: true, width: 30, searchoptions: { sopt: ['cn', 'nc', 'bw', 'bn', 'eq', 'ne', 'ew', 'en', 'lt', 'le', 'gt', 'ge']} },
                          { name: 'OCCURRENCES', index: 'OCCURRENCES', editable: true, width: 20, searchoptions: { sopt: ['eq', 'ne', 'lt', 'le', 'gt', 'ge']} }
                          { name: 'DEPARTMENT_NAME', index: 'DEPARTMENT_NAME', editable: true, width: 100, searchoptions: { sopt: ['cn', 'nc', 'bw', 'bn', 'eq', 'ne', 'ew', 'en', 'lt', 'le', 'gt', 'ge']} }
                          ],
              pager: '#pager',
              autowidth: true,
              height: 'auto',
              rowNum: 10,
              rowList: [5, 10, 20, 50],
              sortname: 'EVENT_ID',
              sortorder: "desc",
              viewrecords: true,
              caption: 'Events',
              jsonReader: { cell: "" },
              loadError: function (jqXHR, textStatus, errorThrown) {
                  // remove error div if exist
                  $('#' + this.id + '_err').remove();
                  // insert div with the error description before the grid
                  myGrid.closest('div.ui-jqgrid').before(
                      '&lt;div id="' + this.id + '_err" style="max-width:' + this.style.width +
                      ';"&gt;&lt;div class="ui-state-error ui-corner-all" style="padding:0.7em;float:left;"&gt;&lt;span class="ui-icon ui-icon-alert" style="float:left; margin-right: .3em;"&gt;&lt;/span&gt;&lt;span style="clear:left"&gt;' +
                      decodeErrorMessage(jqXHR, textStatus, errorThrown) + '&lt;/span&gt;&lt;/div&gt;&lt;div style="clear:left" /&gt;&lt;/div&gt;')
              },
              loadComplete: function () {
                  // remove error div if exist
                  $('#' + this.id + '_err').remove();
              }
          });
          myGrid.jqGrid('navGrid', '#pager', { add: true, edit: true, del: true, refresh: true },
                        updateDialog, // add a record
                        updateDialog, // edit a record
                        updateDialog, // delete a record
                        {multipleSearch: true, overlay: false, width: 460 });
          myGrid.jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true, defaultSearch: 'cn' });
          myGrid.jqGrid('navButtonAdd', '#pager',
                      { caption: "Filter", title: "Toggle Searching Toolbar",
                          buttonicon: 'ui-icon-pin-s',
                          onClickButton: function () { myGrid[0].toggleToolbar(); }
                      });
  </code></pre>
  
  <p>This is my server side:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Data.Objects;
  using System.Linq;
  using System.Reflection;
  using System.Text;
  using System.Web.Mvc;
  using System.Web.Script.Serialization;
  using Newtonsoft.Json;
  //using System.Data.Entity.Infrastructure;
  using CATS.Models;
  
  namespace CATS.Controllers
  {
  // to send exceptions as json we define [HandleJsonException] attribute
  public class ExceptionInformation
  {
      public string Message { get; set; }
  
      public string Source { get; set; }
  
      public string StackTrace { get; set; }
  }
  
  public class HandleJsonExceptionAttribute : ActionFilterAttribute
  {
      public override void OnActionExecuted(ActionExecutedContext filterContext)
      {
          if (filterContext.HttpContext.Request.IsAjaxRequest() &amp;&amp; filterContext.Exception != null)
          {
              filterContext.HttpContext.Response.StatusCode =
                  (int)System.Net.HttpStatusCode.InternalServerError;
  
              var exInfo = new List&lt;ExceptionInformation&gt;();
              for (Exception ex = filterContext.Exception; ex != null; ex = ex.InnerException)
              {
                  PropertyInfo propertyInfo = ex.GetType().GetProperty("ErrorCode");
                  exInfo.Add(new ExceptionInformation()
                  {
                      Message = ex.Message,
                      Source = ex.Source,
                      StackTrace = ex.StackTrace
                  });
              }
              filterContext.Result = new JsonResult() { Data = exInfo };
              filterContext.ExceptionHandled = true;
          }
      }
  }
  
  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&lt;Rule&gt; rules { get; set; }
      private static readonly string[] FormatMapping = {
          "(it.{0} = @p{1})",                 // "eq" - equal
          "(it.{0} &lt;&gt; @p{1})",                // "ne" - not equal
          "(it.{0} &lt; @p{1})",                 // "lt" - less than
          "(it.{0} &lt;= @p{1})",                // "le" - less than or equal to
          "(it.{0} &gt; @p{1})",                 // "gt" - greater than
          "(it.{0} &gt;= @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&lt;T&gt; FilterObjectSet&lt;T&gt;(ObjectQuery&lt;T&gt; inputQuery) where T : class
      {
          if (rules.Count &lt;= 0)
              return inputQuery;
  
          var sb = new StringBuilder();
          var objParams = new List&lt;ObjectParameter&gt;(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;
              switch (propertyInfo.PropertyType.FullName)
              {
                  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);
                      break;
                  default:
                      // TODO: 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&lt;T&gt; filteredQuery = inputQuery.Where(sb.ToString());
          foreach (var objParam in objParams)
              filteredQuery.Parameters.Add(objParam);
  
          return filteredQuery;
      }
  }
  
  [HandleJsonException]
  public class AdminController : Controller
  {
      //
      // GET: /Admin/
  
      public ActionResult Admin()
      {
          ViewBag.PageTitle = "Admin";
  
          return View();
      }
  
      public ActionResult GetEventsGridData(string sidx, string sord, int page, int rows, bool _search, string filters)
      {
          var context = new CATEntities();
  
          //var context = new CATContext();
          //var serializer = new JavaScriptSerializer();
  
          //var objectContext = ((IObjectContextAdapter)context).ObjectContext;
          //var eventset = objectContext.CreateObjectSet&lt;EVENT&gt;();
  
          Filters f = (!_search || string.IsNullOrEmpty(filters)) ? null : JsonConvert.DeserializeObject&lt;Filters&gt;(filters);
          ObjectQuery&lt;EVENT&gt; filteredQuery = (f == null ? context.EVENT : f.FilterObjectSet(context.EVENT)); // (ObjectQuery&lt;EVENT&gt;)eventset)
          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 does NOT exist in the LINQ to Entity
          var queryDetails = (from item in pagedQuery
                              select new { item.EVENT_ID, item.EVENT_TITLE, item.EVENT_SERIES.SERIES_DESCRIPTION, item.OCCURRENCES, item.DEPARTMENT.DEPARTMENT_NAME  }).ToList();
  
  
          return Json(new
          {
              total = (totalRecords + rows - 1) / rows,
              page,
              records = totalRecords,
              rows = (from item in queryDetails
                      select new[] {
                              item.EVENT_ID.ToString(),
                              item.EVENT_TITLE,
                              item.SERIES_DESCRIPTION,
                              item.OCCURRENCES.ToString()
                              item.DEPARTMENT_NAME
                      }).ToList()
          });
      }
  
      public ActionResult UpdateGridData(FormCollection formCollection)
      {
          return Json(JsonRequestBehavior.AllowGet);
      }
  }
  </code></pre>
  
  <p>}</p>
  
  <p>My problem is I get this error on the Occurrences column:</p>
  
  <pre><code>System.Data.Entity: The argument types 'Edm.Int32' and 'Edm.String' are incompatible for this operation. Near equals expression, line 6, column 17.
  </code></pre>
  
  <p>All the data in the Occurrences column is Integer data.  This is telling me that it's looking for Integer data but that I am sending it String data. This column is just like the EVENT_ID column.  Not sure where I'm missing the disconnect.</p>
  
  <p>Also having a problem with the Series column.  On the Event table the Series column is a foreign key id to my series table.  From there I am getting the series description.  The Department column is similar.  When I try and do a search/filter on those two columns I get this error:</p>
  
  <pre><code>System.Data.Entity: You must specify a valid predicate for filtering the results. Parameter name: predicate
  </code></pre>
  
  <p>I'm missing something but for the life of me I can't find it.  </p>