DataTable.net - Implementation of serverside processing in .net Core MVC with ef core dynamic query to paging, sorting and searching

!README.md

DataTable.net - serverside processing

DataTable.net - Implementation of serverside processing in .net Core MVC with ef core dynamic query to paging, sorting and searching

ClientController.cs

public class ClientController : Controller
{
    private CustomContext context;
    public ClientController(CustomContext context)
    {
        this.context = context;
    }
    
    public ActionResult Index(int id)
    {
        ViewBag.RouteId = id;
        return View();
    }
    
    [HttpPost]
    public async Task<IActionResult> List(int id, DataTableQueryViewModel model)
    {
        var res = await context.Clients.Where(x => x.RouteId == id).Include(x => x.Route).DataTableSearch(model);

        return Json(res);
    }
}

DataTableExtension.cs

public static class DataTableExtension
    {
        public async static Task<DataTableResultViewModel<T>> DataTableSearch<T>(this IQueryable<T> dataSet, DataTableQueryViewModel model) where T: class
        {
            try
            {
                var searchBy = (model.Search != null) ? model.Search.Value : null;
                var take = model.Length;
                var skip = model.Start;

                string sortBy = "";
                bool sortDir = true;

                if (model.Order != null && model.Order.Count > 0)
                {
                    sortBy = model.Columns[model.Order[0].Column].Name ?? string.Empty;
                    sortDir = model.Order[0].Dir.ToLower() == "asc";
                }

                var searchableColumns = model.Columns.Where(x => x.Searchable).Select(x => x.Data.ToLower()).ToList();

                var result = await GetDataFromDbase<T>(dataSet, searchBy, searchableColumns, take, skip, sortBy, sortDir);
                result.Draw = model.Draw;

                return result;
            }
            catch (Exception ex)
            {
                return new DataTableResultViewModel<T>();
            }
        }

        private async static Task<DataTableResultViewModel<T>> GetDataFromDbase<T>(IQueryable<T> dataSet, string searchBy, List<string> searchableColumns, int take, int skip, string sortBy, bool sortDir) where T: class
        {
            var whereClause = BuildDynamicWhereClause<T>(searchBy, searchableColumns);

            PropertyInfo prop = typeof(T).GetProperty(sortBy);

            if (prop == null)
            {
                sortBy = "Id";
                sortDir = true;

                prop = typeof(T).GetProperty(sortBy);
            }

            var query = dataSet.Where(whereClause);

            if (sortDir)
                query = query.OrderBy(x => prop.GetValue(x));
            else
                query = query.OrderByDescending(x => prop.GetValue(x));


            var result = await query
                           .Skip(skip)
                           .Take(take)
                           .ToListAsync();

            int filteredResultsCount = result.Count();
            int totalResultsCount = dataSet.Count();

            return new DataTableResultViewModel<T>
            {
                RecordsFiltered = totalResultsCount,
                RecordsTotal = totalResultsCount,
                Result = result
            };
        }

        private static Expression<Func<T, bool>> BuildDynamicWhereClause<T>(string searchValue, List<string> searchableColumns)
        {
            ParameterExpression parameterExpression = Expression.Parameter(typeof(T), "x");
            Expression orExpression = null;
            MemberExpression member = null;
            ConstantExpression value = null;
            if (!string.IsNullOrWhiteSpace(searchValue))
            {
                var searchTerms = searchValue.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries).ToList();

                MethodInfo containsMethod = typeof(string).GetTypeInfo().GetDeclaredMethod("Contains");
                foreach (string item in searchTerms)
                {
                    value = Expression.Constant(item.ToLower(), typeof(string));

                    var searchableProperties = typeof(T).GetProperties().Where(x => searchableColumns.Contains(x.Name.ToLower())).ToList();
                    foreach (var p in searchableProperties)
                    {
                        member = Expression.Property(parameterExpression, p.Name);

                        Expression safeObject = null;
                        Expression convertedBool = null;
                        if (p.PropertyType != typeof(decimal) && p.PropertyType != typeof(int) &&
                            p.PropertyType != typeof(bool) && p.PropertyType != typeof(double) && 
                            !p.PropertyType.IsEnum)
                        {
                            var safeObjectMehthod = typeof(DataTableExtension).GetMethod("SafeObject", BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic);
                            safeObject = Expression.Call(null, safeObjectMehthod, member);
                        }
                        else if (p.PropertyType == typeof(bool))
                        {
                            var convertBoolMehthod = typeof(DataTableExtension).GetMethod("ConvertBool", BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic);
                            convertedBool = Expression.Call(null, convertBoolMehthod, member);
                        }

                        Expression toString = Expression.Call( ( safeObject ?? convertedBool ) ?? member , typeof(Object).GetMethod("ToString", Type.EmptyTypes));
                        Expression toLower = Expression.Call(toString, typeof(string).GetMethod("ToLower", Type.EmptyTypes));
                        Expression contains = Expression.Call(toLower, containsMethod, value);

                        orExpression = orExpression != null ? Expression.OrElse(orExpression, contains) : contains;
                    }
                }

                return Expression.Lambda<Func<T, bool>>(orExpression, parameterExpression);
            }

            return x => true;
        }

        public static object SafeObject(this object obj) => obj == null ? string.Empty : obj;
        public static string ConvertBool(this bool obj) => obj ? "Si" : "No";
    }

DataTableQueryViewModel.cs

public class DataTableQueryViewModel
{
    [JsonProperty(PropertyName = "draw")]
    public int Draw { get; set; }
    [JsonProperty(PropertyName = "start")]
    public int Start { get; set; }
    [JsonProperty(PropertyName = "length")]
    public int Length { get; set; }
    [JsonProperty(PropertyName = "columns")]
    public List<Column> Columns { get; set; }
    [JsonProperty(PropertyName = "search")]
    public Search Search { get; set; }
    [JsonProperty(PropertyName = "order")]
    public List<Order> Order { get; set; }
}

public class Column
{
    [JsonProperty(PropertyName = "data")]
    public string Data { get; set; }
    [JsonProperty(PropertyName = "name")]
    public string Name { get; set; }
    [JsonProperty(PropertyName = "searchable")]
    public bool Searchable { get; set; }
    [JsonProperty(PropertyName = "orderable")]
    public bool Orderable { get; set; }
    [JsonProperty(PropertyName = "search")]
    public Search Search { get; set; }
}

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

public class Order
{
    [JsonProperty(PropertyName = "column")]
    public int Column { get; set; }
    [JsonProperty(PropertyName = "dir")]
    public string Dir { get; set; }
}

DataTableResultViewModel.cs

public class DataTableResultViewModel<T>
{
    [JsonProperty(PropertyName = "draw")]
    public int Draw { get; set; }
    [JsonProperty(PropertyName = "recordsTotal")]
    public int RecordsTotal { get; set; }
    [JsonProperty(PropertyName = "recordsFiltered")]
    public int RecordsFiltered { get; set; }
    [JsonProperty(PropertyName = "data")]
    public List<T> Result { get; set; }

    public DataTableResultViewModel()
    {
        Result = new List<T>();
    }
}

Index.cshtml

<table id="dataTable" class="table table-striped" cellspacing="0">
    <thead>
        <tr>
            <th>Name</th>
            <th>Route</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

<script>
  $(function ()
  {
      $("#dataTable").DataTable(
      {
          "columns": [
          {
              "data": "name",
              "name": "Name"
          },
          {
              "data": "route",
              "name": "Route"
          }],
          "ajax": {
              "url": '@Url.Action("List", "Client")/@ViewBag.RouteId',
              "type": "POST",
          },
      });
  });
</script>