Generic Class for handling Datatables.NET serverside processing in C# using LINQ/Entity

DataTablesSSP.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
using System.Web.Mvc;
using Newtonsoft.Json;

namespace SUBSET.Code
{


    public class DataTablesSSP
    {
        public static JsonResult DataTablesServerSideProcessing<T>(IQueryable<T> data, DataTableAjaxPostModel model)
        {
            //Check to see if any column filters have been set by dropdowns on the page (These will have a list of ints as their search values)
            data = ApplyIntListFilters(data, model);

            //Check to see if there are any date-range filters set (from DateTimeRangepicker on page)
            data = ApplyDateRangeFilter(data, model);

            //Has the user typed anything into the search box? Check searchable columns
            data = ApplySearchBox(data, model);

            //Apply the ordering before returning it
            data = ApplyOrdering(data, model);

            // Enumerate a page worth of results from the data. Start is number of records to skip, length is page size.
            var retVal = data.Skip(model.Start).Take(model.Length).ToList();

            // Total Count so that DataTable knows how many pages.
            var total = data.Count();

            return new JsonResult
            {
                Data = new { draw = model.Draw, recordsFiltered = total, recordsTotal = total, data = retVal },
                JsonRequestBehavior = JsonRequestBehavior.AllowGet
            };

        }

        /// <summary>
        /// Apply a column filter to the data
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data">Data to Filter</param>
        /// <param name="model"></param>
        /// <returns></returns>
        private static IQueryable<T> ApplyIntListFilters<T>(IQueryable<T> data, DataTableAjaxPostModel model)
        {
            foreach (var m in model.Columns.Where(x => x.Search.SearchIntList != null))
            {
                // Column's "Data" field is used for the field to filter in the `data` object.
                var dynamicLinqStr = "";

                var searchValues = m.Search.SearchIntList;
                var colToFilter = m.Data;

                // Using Dynamic Linq, build query string. Unfortunatley not as simple as (x=> searchValues.Contains(x.columnname))!
                for (var i = 0; i < searchValues.Count; i++)
                {
                    if (i < (searchValues.Count - 1))
                    {
                        dynamicLinqStr += $"{colToFilter} == {searchValues[i]} || ";
                    }
                    else
                    {
                        dynamicLinqStr += $"{colToFilter} == {searchValues[i]}";
                    }
                }

                data = data.Where($"{dynamicLinqStr}");

            }

            // If no filters were set, data will be untouched
            return data;
        }

        /// <summary>
        /// Check model for minDate and maxDate filter values
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        private static IQueryable<T> ApplyDateRangeFilter<T>(IQueryable<T> data, DataTableAjaxPostModel model)
        {
            //Column Name will be "minDate" or "maxDate", Column data will have the name of the field to apply the value to.
            var minDate = model.Columns.SingleOrDefault(x => x.Name == "minDate");
            var maxDate = model.Columns.SingleOrDefault(x => x.Name == "maxDate");

            if (minDate?.Search.Value != null && maxDate?.Search.Value != null)
            {
                DateTime.TryParse($"{minDate.Search.Value} 00:00:00", out var dtMinDate);
                DateTime.TryParse($"{maxDate.Search.Value} 23:59:59", out var dtMaxDate);

                return data.Where($"{minDate.Data} >= @0 && {maxDate.Data} <= @1", dtMinDate, dtMaxDate);
            }

            //If we're here, minDate and/or maxDate were not set. Return data untouched.
            return data;

        }

        /// <summary>
        /// Apply search filter to dataset
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        private static IQueryable<T> ApplySearchBox<T>(IQueryable<T> data, DataTableAjaxPostModel model)
        {
            // Has the user typed anything into the search field?
            if (model.Search.Value == null) return data;

            var searchVal = model.Search.Value;
            var searchable = model.Columns.Where(x => x.Searchable).ToList();

            // User can either search exact or wildcard (end of string only for now)
            if (searchVal.Contains('*'))
            {
                //Ensure it doesn't start with a *, else it will try and search the datbase for records that are like '' on first keypress

                if (!searchVal.StartsWith("*") && searchVal.IndexOf("*") + 1 == searchVal.Length)
                {
                    // strip out the *, we don't our search results to have that char!
                    // Search desired columns for values that start with searchVal
                    searchVal = searchVal.Replace("*", "");

                    // Build up a list of columns to search
                    var dynamicLinqStr = "";

                    var cnt = searchable.Count();
                    for (var i = 0; i < cnt; i++)
                    {
                        if (i < (cnt - 1))
                        {
                            dynamicLinqStr += $"{searchable[i].Data}.ToString().StartsWith(@0) || ";
                        }
                        else
                        {
                            dynamicLinqStr += $"{searchable[i].Data}.ToString().StartsWith(@0)";
                        }
                    }
                    data = data.Where($"{dynamicLinqStr}", searchVal);
                }
                else
                {
                    //Return Nothing. * should not be at the beginning of the string or in the middle.
                    //TODO: Can we do some magic and allow wildcard in any position?
                    data = data.Take(0);
                }
            }
            else
            {
                var dynamicLinqStr = "";

                var cnt = searchable.Count();
                for (var i = 0; i < cnt; i++)
                {
                    if (i < (cnt - 1))
                    {
                        dynamicLinqStr += $"{searchable[i].Name}.ToString() == @0 || ";
                    }
                    else
                    {
                        dynamicLinqStr += $"{searchable[i].Name}.ToString() == @0";
                    }
                }
                data = data.Where($"{dynamicLinqStr}", searchVal);

            }

            return data;
        }

        /// <summary>
        /// Apply ordering to dataset
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        private static IQueryable<T> ApplyOrdering<T>(IQueryable<T> data, DataTableAjaxPostModel model)
        {
            // What order should the list be in? Consult the model!
            if (model.Order == null) return data;

            var srtCol = model.Order.First().Column;
            var srtColName = model.Columns[srtCol].Name;
            var srtColDir = model.Order.First().Dir;

            data = data.OrderBy(srtColName + " " + srtColDir);

            return data;
        }
    }

    public class DataTableAjaxPostModel
    {
        public int Draw { get; set; }
        public int Start { get; set; }

        public int Length { get; set; }
        public List<Column> Columns { get; set; }
        public Search Search { get; set; }
        public List<Order> Order { get; set; }
    }

    public class Column
    {

        public string Data { get; set; }

        public string Name { get; set; }

        public bool Searchable { get; set; }

        public bool Orderable { get; set; }

        public Search Search { get; set; }

    }

    public class Search
    {
        [JsonProperty(PropertyName = "value")]
        public string Value { get; set; }
        [JsonProperty(PropertyName = "regex")]
        public string Regex { get; set; }

        /// <summary>
        /// If the "Value" property is a comma separated string of numbers, or a single number, then it will be returned as a `List<int>`
        /// </summary>
        public List<int> SearchIntList
        {
            get
            {
                var retval = new List<int>();

                if (Value == null)
                {
                    return null;
                }

                if (Value.Contains(','))
                {
                    foreach (var val in Value.Split(','))
                    {
                        //Try to parse string value to integer, only add to return list if it succeeds
                        var success = int.TryParse(val, out var number);
                        if (success)
                        {
                            retval.Add(number);
                        }
                    }
                }
                else
                {
                    var success = int.TryParse(Value, out var number);
                    if (success)
                    {
                        retval.Add(number);
                    }
                }

                return retval.Count > 0 ? retval : null;


            }

        }


    }
    public class Order
    {

        public int Column { get; set; }

        public string Dir { get; set; }
    }
}