title: How to implement a server-side filter that is similar to the client-side filter in DataTables
body: <p>I'm attempting to implement the DataTables jQuery plugin in my ASP.NET MVC site. I used a slight variation of the code at this <a href="https://www.echosteg.com/jquery-datatables-asp.net-mvc5-server-side" rel="nofollow">LINK</a> to implement the server-side processing and it's working like it's supposed to. However, I didn't realize at the time that the server-side filtering would only return a search if the columns contained a single word or an exact sequence of words until trying it out. This differs from the client-side version in that the global search will filter on any column, for any word, in any order...truly filtering down the results.</p>
  
  <p>For example, I have two columns in the table: ID and Description. If one of the values for ID is "A1B2C3" and one of the values for Description is "Capturing the sequence of events prior to initialization," if I search on the client-side by typing in "sequence initial," it will return the record. However, using server-side, if I type in "sequence initial," nothing will be returned. If I type in "sequence of," then the record will be returned, which tells me that the search is being performed by the entire search term and looking for a record that contains the entire phrase. This isn't the "filter-down" search I was hoping for.</p>
  
  <p>I'm not well-versed in LINQ, so I don't know if what I'm trying to do is possible, but here's the FilterResult method that returns the search:</p>
  
  <pre><code>public IQueryable&lt;IDS&gt; FilterResult(string search, List&lt;IDS&gt; dtResult, List&lt;string&gt; columnFilters)
  {
      IQueryable&lt;IDS&gt; results = dtResult.AsQueryable();
  
      results = results.Where(p =&gt; (search == null || p.ID != null &amp;&amp; p.ID.ToLower().Contains(search.ToLower())
              || p.Description != null &amp;&amp; p.Description.ToLower().Contains(search.ToLower())));
  
      return results;
  }
  </code></pre>
  
  <p>And here's my DataHandler method in the controller:</p>
  
  <pre><code>[HttpPost]
  public JsonResult DataHandler(DTParameters param, FilterViewModel filterModel)
  {
      try
      {
          var dtSource = new List&lt;IDS&gt;();
          dtSource = db.DBIDS.ToList();
  
          List&lt;string&gt; columnSearch = new List&lt;string&gt;();
  
          foreach (var column in param.Columns)
          {
              columnSearch.Add(column.Search.Value);
          }
  
          List&lt;IDS&gt; currentData = new ResultSet().GetCurrentDataSet(filterModel.currentSearch, db.DBIDS.ToList());
  
          List&lt;IDS&gt; data = new ResultSet().GetResult(param.Search.Value, param.SortOrder, param.Start, param.Length, currentData, columnSearch);
          int count = new ResultSet().Count(param.Search.Value, currentData, columnSearch);
  
          DTResult&lt;IDS&gt; result = new DTResult&lt;IDS&gt;
          {
              draw = param.Draw,
              data = data,
              recordsFiltered = count,
              recordsTotal = count
          };
  
          return Json(result, JsonRequestBehavior.AllowGet);
      }
      catch (Exception ex)
      {
          return Json(new { error = ex.Message }, JsonRequestBehavior.AllowGet);
      }
  }
  </code></pre>
  
  <p>I realize that my DataHandler is using the same data set for each call, which I believe is part of the problem. I attempted to capture the search term in a variable called <code>newSearch</code>, then assign it to a variable called <code>oldSearch</code>, and use the <code>oldSearch</code> as custom data (or HTTP variable) that's being sent to the server. My thought was that I would perform the search on the database using the <code>oldSearch</code> term to return a record set. I would then use that record set with the new search term to return the results I'm after. However, I'm not sure how to change the LINQ query so that it will search similarly to the client-side search.</p>
  
  <p>Here's the DataTables code where I'm attempting to capture the <code>oldSearch</code> and send it to  the server:</p>
  
  <pre><code>$("#myTable").dataTable({
      ajax: {
          type: "POST",
          url: "datahandler",
          contentType: "application/json; charset=utf-8",
          data: function (data) { data.currentSearch = oldSearch;  return data = JSON.stringify(data); }
          },
      columns: columnArray,
      lengthChange: false,
      order: [1, "asc"],
      paging: true,
      pageLength: 10,
      processing: true,
      searching: true,
      serverSide: true
  });
  
  $("#myTable").dataTable().on("preXhr.dt", function (e, settings, data) {
      newSearch = $("#dTable").DataTable().search();
      data.currentSearch = oldSearch;
  });
  
  $("#dTable").dataTable().on("xhr.dt", function () {
      oldSearch = newSearch;
  });
  </code></pre>
  
  <p>Is this possible with LINQ? For reference, when I attempted to send the search term to the server, I also attempted to change the LINQ query by using the existing query and adding on to it. The add-on splits each word in the description into a list and splits the search term into a list, then uses <code>.Any</code> for the comparison:</p>
  
  <pre><code>results = results.Where(p =&gt; (search == null || p.ID != null &amp;&amp; p.ID.ToLower().Contains(search.ToLower())
              || p.Description != null &amp;&amp; (p.Description.ToLower().Contains(search.ToLower()) || p.Description.ToLower().Split(' ').ToList().Any(x =&gt; search.ToLower().Split(' ').ToList().Contains(x)))));
  </code></pre>
  
  <p>This doesn't work because the search is looking for each item in the list specifically. There's no filter, so any record that contains any of the words in the search list will be returned.</p>
  
  <p>I've seen some posts online that indicate that people typically do a column-based search, but I'd prefer to stick to the one global search box due to limited real estate on the page where the DataTable resides.</p>