title: Dynamic filtering, sorting and paging on generic list with Entity Framework
body: <p>I am using jQuery datatable (<a href="http://www.datatables.net" rel="nofollow">http://www.datatables.net</a>) in my MVC4 app and as you may know this table allows server side processing. I am going to use the table in multiple views tied to multiple controllers so I'd like to implement a generic way to filer, sort and page data without the need to write a method for each controller. If I were to do that, they would all look the same but they would target a different entity from the database and be doing textual filtering and sorting on different columns. Here what I have to do today:</p>
  
  <pre><code>    public virtual ActionResult AjaxHandler(jQueryDataTableParamModel param)
      {
          var myProducts = _productRepository.Products;
          IEnumerable&lt;Product&gt; filteredProducts = myProducts;
  
          // Filtering
          if (!string.IsNullOrEmpty(param.sSearch))
          {
              var searchTermLower = param.sSearch.Trim().ToLower();
              filteredProducts = filteredProducts
                       .Where(c =&gt; c.Title.Contains(param.sSearch)
                                   ||
                        c.Manufacturer.ManufacturerName.ToLower().Contains(searchTermLower)
                                   ||
                        c.Category.CategoryTitle.ToLower().Contains(searchTermLower)
                                   ||
                        c.Size.Title.ToLower().Contains(searchTermLower)
                                   ||
                        c.Price.ToString("C").Contains(searchTermLower));
          }
  
          // Sorting
          var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
          var sortDirection = Request["sSortDir_0"];
          if (sortColumnIndex == 0)
          {
              filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x =&gt; x.CreatedDate) : filteredProducts.OrderByDescending(x =&gt; x.CreatedDate);
          }
          else if (sortColumnIndex == 1)
          {
              filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x =&gt; x.Title) : filteredProducts.OrderByDescending(x =&gt; x.Title);
          }
          else if (sortColumnIndex == 2)
          {
              filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x =&gt; x.Manufacturer.ManufacturerName) : filteredProducts.OrderByDescending(x =&gt; x.Manufacturer.ManufacturerName);
          }
          else if (sortColumnIndex == 3)
          {
              filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x =&gt; x.Size.Title) : filteredProducts.OrderByDescending(x =&gt; x.Size.Title);
          }
          else if (sortColumnIndex == 4)
          {
              filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x =&gt; x.Category.CategoryTitle) : filteredProducts.OrderByDescending(x =&gt; x.Category.CategoryTitle);
          }
          else if (sortColumnIndex == 4)
          {
              filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x =&gt; x.Price) : filteredProducts.OrderByDescending(x =&gt; x.Price);
          }
  
          // Paging
          var displayedProducts = filteredProducts.Skip(param.iDisplayStart).Take(param.iDisplayLength);
          var result = from c in displayedProducts
                       select new[] { c.ProductId.ToString(CultureInfo.InvariantCulture), c.CreatedDate.ToString("G"), c.Title, c.Manufacturer.ManufacturerName, c.Size.Title, c.Category.CategoryTitle, c.Price.ToString("C") };
          return Json(new
          {
              sEcho = param.sEcho,
              iTotalRecords = myProducts.Count(),
              iTotalDisplayRecords = filteredProducts.Count(),
              aaData = result
          }, JsonRequestBehavior.AllowGet);
      }
  </code></pre>
  
  <p>I tried several things to make this generic none of which worked completely - some because of the fact that I filter on all columns and others for other reasons. I am hoping there is a better way to do this so I can just pass columns or functions that select columns instead and have it work.</p>