Easily Apply Sorting for DataTables.js in C#

Introduction

I recently started using https://datatables.net/ for my front-end development and quickly discovered that writing switch statements and for-each loops to handle the sorting requirements just wasn't good enough.

So I wrote this little helper method to shorten the sorting configuration.

Background

I've created some classes to represent DataTables' structures. They are listed below:

public class DataTableParameters
{
	public int Draw { get; set; }
	public int Start { get; set; }
	public int Length { get; set; }
	public SearchParameter Search { get; set; }
	public OrderParameter[] Order { get; set; }
	public ColumnParameter[] Columns { get; set; } 
}
 
public class SearchParameter
{
	public string Value { get; set; }
	public bool Regex { get; set; }
}
public class OrderParameter
{
	public int Column { get; set; }
	public string Dir { get; set; }
}
public class ColumnParameter
{
	public string Data { get; set; }
	public string Name { get; set; }
	public bool Searchable { get; set; }
	public bool Orderable { get; set; }
	public SearchParameter Search { get; set; } 
}
 
public class DataTableResult
{
	public int draw { get; set; }
	public long recordsTotal { get; set; }
	public int recordsFiltered { get; set; }
	public object[] data { get; set; }
	public string error { get; set; }
}

Using the Code

The helper method is a generic method that accepts an IQueryable that sorting will be applied to, the DataTables OrderParameters and a parameterized list of properties.

public static IOrderedQueryable<T> Order<T>(this IQueryable<T> query, 
  DataTableParameters parameters, params Expression<Func<T, object>>[] mapping)
{
	IOrderedQueryable<T> orderedQuery = null;
	foreach (var order in parameters.Order)
	{
		orderedQuery =
			order.Dir == "asc"
				? OrderBy(orderedQuery ?? query, mapping[order.Column], orderedQuery != null)
				: OrderBy(orderedQuery ?? query, mapping[order.Column], orderedQuery != null, true);
	}
 
	return orderedQuery;
}

Also, a conversion between object and DateTime is needed since EF only can convert primitive types. Other types need to be converted as needed.

public static Expression<Func<T, U>> ConvertExpression<T, U>(Expression<Func<T, object>> expression)
{
	if (expression.Body is UnaryExpression unaryExpression)
	{
		var propertyExpression = (MemberExpression)unaryExpression.Operand;
 
		if (propertyExpression.Type == typeof(U))
			return Expression.Lambda<Func<T, U>>(propertyExpression, expression.Parameters);
	}
 
	return null;
}

Some additional helper methods to simplify implementing more conversions.

private static IOrderedQueryable<T> OrderBy<T>(IQueryable<T> qry, 
   Expression<Func<T, object>> expr, bool ordered = false, bool descending = false)
{
	// Implement conversions as needed
	var t = (expr.Body as UnaryExpression)?.Operand.Type;
 
	if (t == typeof(DateTime))
		return OrderBy<T, DateTime>(qry, expr, ordered, descending);
 
	if (t == typeof(DateTime?))
		return OrderBy<T, DateTime?>(qry, expr, ordered, descending);
 
	return ordered
		? descending
			? (qry as IOrderedQueryable<T>).ThenByDescending(expr)
			: (qry as IOrderedQueryable<T>).ThenBy(expr)
		: descending
			? qry.OrderByDescending(expr)
			: qry.OrderBy(expr);
}

And a wrapper for handling the sort direction.

private static IOrderedQueryable<T> OrderBy<T, TU>(IQueryable<T> qry, 
   Expression<Func<T, object>> expr, bool ordered = false, bool descending = false)
{
	return ordered
		? descending
			? (qry as IOrderedQueryable<T>).ThenByDescending(ConvertExpression<T, TU>(expr))
			: (qry as IOrderedQueryable<T>).ThenBy(ConvertExpression<T, TU>(expr))
		: descending
			? qry.OrderByDescending(ConvertExpression<T, TU>(expr))
			: qry.OrderBy(ConvertExpression<T, TU>(expr));
}

A complete example for a WebAPI HttpGet method:

public IHttpActionResult GetCustomers([FromUri]DataTableParameters parameters)
{
   IQueryable<Customer> customers = customerRepository.GetAll();
   var count = customers.Count();

   // Filter out on search
   if(!string.IsNullOrWhiteSpace(parameters.Search.Value))
      customers = customers.Where(x => x.Firstname.Contains(parameters.Search.Value) || 
                                  x.Lastname.Contains(parameters.Search.Value));

   var filteredCount = customers.Count();

   var ordered = 
     Order(customers, parameters.Order, x => x.Firstname, x => x.Lastname, x => x.Phone, x => x.Email);
 
   var paged = ordered.Skip(parameters.Start).Take(parameters.Length).ToList();

   return Ok(new DataTableResult()
   {
      draw = parameters.Draw,
      recordsTotal = count,
      recordsFiltered = filteredCount,
      data = paged.Select(x => new { x.Firstname, x.Lastname, x.Phone, x.Email }).ToArray()
   });
}

Extension Methods

The following extension methods can be applied for ease-of-use:

public static class DataTableExtensions
{
	public static IOrderedQueryable<T> Order<T>(this IQueryable<T> query, 
           DataTableParameters parameters, params Expression<Func<T, object>>[] mapping)
	{
		return Order(query, parameters, mapping);
	}
 
	public static IQueryable<T> Page<T>(this IQueryable<T> query, 
        DataTableParameters parameters) => query.Skip(parameters.Start).Take(parameters.Length);
}

The use would then be similar to this:

var ordered = customers.Order
              (parameters, x => x.Firstname, x => x.Lastname, x => x.Phone, x => x.Email);
var paged = ordered.Page(parameters).ToList();

If there are columns that do not support sorting/ordering, these are included for indexing by passing null in the correction order. E.g:

var ordered = customers.Order (parameters, null, x => x.Firstname, 
                               x => x.Lastname, x => x.Phone, x => x.Email);

Notes

There is no index checking in the method. You should keep the front- and backend synchronized or add some checking for this.

You could also assign an array of object to the data property in the DataTableResult instead of using anonymous types like so:

data = paged.ToArray()

However, this is more vulnerable to changes in the class structure, e.g., re-arranging properties or adding new ones.

History

• 21^st September, 2018 - Initial publication
• 12^th October, 2018 - Refactored to make conversions easier