Missing order field in IDataTablesRequest

[ymolinet]

According this document : https://datatables.net/manual/server-side the order field is missing in IDataTablesRequest

[Zapnologica]

I am also missing this? How do I know if a coloum has been ordered or not?

[ALMMa]

jQuery DataTables will send, on this array, only the columns which have been ordered. DataTables.AspNet handles that accordingly and the corresponding array will be populated only with columns ordered by the end-user.

[ymolinet]

in the IDataTablesRequest parameters, the only way to know which column is order is to check each column[x] var. If you check the Datatables..et documentation, you can see that an order field is also alvailable.

The order[i] and columns[i] parameters that are sent to the server are arrays of information:

order[i] - is an array defining how many columns are being ordered upon - i.e. if the array length is 1, then a single column sort is being performed, otherwise a multi-column sort is being performed. columns[i] - an array defining all columns in the table. In both cases, i is an integer which will change to indicate the array value. In most modern server-side scripting environments this data will automatically be available to you as an array.

We don't have the order field at this time.

[kayakguy]

I'm running into the same issue with the missing "order" information. My problem is compounded by the fact that the "columns" information is not coming through for me either (see other issue post). So I'm not able to gather any order information at all in the server code.

[luciusli]

You can do something like request.Columns.Where(c => c.Sort != null).OrderBy(c => c.Sort.Order), but I think it's quite cumbersome to include this code every time.

[NimzyMaina]

I did a suggested by @luciusli and I was able to find the IColumn to use in the orderBy(). I also added the DataTables.AspNet.Extensions.AnsiSql package to my project. This helps to get name of the column to be used to order the query. Example of the result is columnName or columnName DESC.

Since this is a string. I found this extension method to do an orderBy(string) from Daed Tech. I modified it a bit though.

using System.Collections.Generic;
using System.Linq;
using System.Reflection;

namespace Your.NameSpace
{
    public static class EnumerableExtensions
    {

        public static IEnumerable<T> OrderBy<T>(this IEnumerable<T> entities, string propertyName)
        {
            if (!entities.Any() || string.IsNullOrEmpty(propertyName))
                return entities;

                var x = propertyName.Split(" ");

                var propertyInfo = entities.First().GetType().GetProperty(x[0], BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

                if(x.Length == 1){
                    return entities.OrderBy(e => propertyInfo.GetValue(e, null));
                }
                return entities.OrderByDescending(e => propertyInfo.GetValue(e, null));     
            
        }
        
    }
}

Then in my controller I did this

public IActionResult PageData([FromQuery] Core.IDataTablesRequest request)
        {
            // Nothing important here. Just creates some mock data.
            var data = Models.SampleEntity.GetSampleData();

           // Get ordering IColumn
           var icol =  request.Columns.Where(c => c.Sort != null).OrderBy(c => c.Sort.Order);

            var order = icol.GetSort();

            // Global filtering.
            // Filter is being manually applied due to in-memmory (IEnumerable) data.
            // If you want something rather easier, check IEnumerableExtensions Sample.
            // DON'T FORGET TO IMPORT EXTENSION METHOD  
            var filteredData = String.IsNullOrWhiteSpace(request.Search.Value)
				? data.orderBy(order)
				: data.Where(_item => _item.Name.ToLower().Contains(request.Search.Value.ToLower())).orderBy(order);

            // Paging filtered data.
            // Paging is rather manual due to in-memmory (IEnumerable) data.
            var dataPage = filteredData.Skip(request.Start).Take(request.Length);

            // Response creation. To create your response you need to reference your request, to avoid
            // request/response tampering and to ensure response will be correctly created.
            var response = DataTablesResponse.Create(request, data.Count(), filteredData.Count(), dataPage);

            // Easier way is to return a new 'DataTablesJsonResult', which will automatically convert your
            // response to a json-compatible content, so DataTables can read it when received.
            return new DataTablesJsonResult(response, true);
        }

Please make sure to import these nuget packages DataTables.AspNet.Core , DataTables.AspNet.Extensions.AnsiSql and DataTables.AspNet.AspNetCore if you are using Dotnet Core.

Don't forget to register the package in you Startup.cs file

 public void ConfigureServices(IServiceCollection services)
 {
        services.AddMvc();
        // DataTables.AspNet registration with default options.
        services.RegisterDataTables();
}

If you feel you can improve this kindly let me know.

Happy coding!!!

[ymolinet]

I'm sharing my personnal implementation... with 3 extensions of IEnumerabe and not using ANSI_SQL Extension

I'm extend IEnumerable with 3 methods :

• Compute() : Main method to manage common code in my own controllers, manage pagination, searching, ordering
• OrderBy() : Rewrite of the @NimzyMaina code without ANSI_SQL but use IColumn instead.
• PropertyContains() : To manage search in collection attributes, with cast to string

public static class IEnumerableExtensions
    {
        public static IEnumerable<T> Compute<T>(this IEnumerable<T> data, IDataTablesRequest request, out int filteredDataCount)
        {
            filteredDataCount = 0;
            if (!data.Any() || request == null)
                return data;

            // Global filtering.
            // Filter is being manually applied due to in-memmory (IEnumerable) data.
            // If you want something rather easier, check IEnumerableExtensions Sample.
            // var filteredData = data.Where(_item => _item.Hostname.Contains(request.Search.Value));
            IEnumerable<T> filteredData = Enumerable.Empty<T>();

            // Inutile de faire une recherche s'il n'y a rien à chercher.
            if (!String.IsNullOrEmpty(request.Search.Value))
            {
                var filteredColumn = request.Columns.Where(c => c.IsSearchable == true);
                foreach (IColumn sColumn in filteredColumn)
                {
                    var propertyInfo = data.First().GetType().GetProperty(sColumn.Name, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                    // IEnumerable<T> columnResult = data.Where(d => d.GetType().GetProperty(sColumn.Name, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance).GetValue(d, null).ToString().Contains(request.Search.Value));
                    IEnumerable<T> columnResult = data.PropertyContains(propertyInfo, request.Search.Value);
                    filteredData = filteredData.Concat(columnResult);
                }
                // Pour éviter les doublons
                filteredData = filteredData.Distinct();
            }
            else filteredData = data;

            // Ordering filtred data
            var orderedColumn = request.Columns.Where(c => c.IsSortable == true && c.Sort != null);
            foreach (IColumn sColumn in orderedColumn)
            {
                filteredData = filteredData.OrderBy(sColumn);
            }

            // Paging filtered data.
            // Paging is rather manual due to in-memmory (IEnumerable) data.
            // var dataPage = filteredData.OrderBy(d => d.ID).Skip(request.Start);
            var dataPage = filteredData.Skip(request.Start);
            if (request.Length != -1) dataPage = dataPage.Take(request.Length);

            filteredDataCount = filteredData.Count();
            return dataPage;
        }

        // https://github.com/ALMMa/datatables.aspnet/issues/58
        public static IEnumerable<T> OrderBy<T>(this IEnumerable<T> entities, IColumn column)
        {
            if (!entities.Any() || column == null)
                return entities;

            var propertyInfo = entities.First().GetType().GetProperty(column.Field, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

            if (column.Sort.Direction == SortDirection.Ascending)
            {
                return entities.OrderBy(e => propertyInfo.GetValue(e, null));
            }
            return entities.OrderByDescending(e => propertyInfo.GetValue(e, null));
        }

        // Inspire : https://stackoverflow.com/questions/22104050/linq-to-entities-does-not-recognize-the-method-system-object-getvalue
        // and : https://stackoverflow.com/questions/4553836/how-to-create-an-expression-tree-to-do-the-same-as-startswith
        public static IEnumerable<T> PropertyContains<T>(this IEnumerable<T> data, PropertyInfo propertyInfo, string value)
        {
            ParameterExpression param = Expression.Parameter(typeof(T));
            MemberExpression m = Expression.MakeMemberAccess(param, propertyInfo);
            ConstantExpression c = Expression.Constant(value, typeof(string));
            MethodInfo mi_contains = typeof(string).GetMethod("Contains", new Type[] { typeof(string) });
            MethodInfo mi_tostring = typeof(object).GetMethod("ToString");
            Expression call = Expression.Call(Expression.Call(m, mi_tostring), mi_contains, c);

            Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(call, param);

            return data.AsQueryable().Where(lambda);
        }
}

And how to use it

public ActionResult PageData(IDataTablesRequest request)
        {
            int filteredDataCount = 0;
            List<object> data = Models.SampleEntity.AsNoTracking().ToList();
            var dataPage = data.Compute(request, out filteredDataCount);
            var response = DataTablesResponse.Create(request, data.Count(), filteredDataCount, dataPage);
            return new DataTablesJsonResult(response, JsonRequestBehavior.AllowGet);
        }