Click here to Skip to main content
13,794,250 members
Click here to Skip to main content
Add your own
alternative version

Stats

8.1K views
8 bookmarked
Posted 21 Sep 2018
Licenced CPOL

Easily Apply Sorting for DataTables.js in C#

, 13 Oct 2018
Rate this:
Please Sign up or sign in to vote.
Helper method to apply sorting from DataTables.js parameters 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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Stefan Vincent Haug
Software Developer (Senior) Fundator AS
Norway Norway
MCSD/MCSA/MCP working with what I like best - architecture, development and consulting.

You may also be interested in...

Pro

Comments and Discussions

 
QuestionFilter Method Pin
melnac13-Oct-18 3:50
membermelnac13-Oct-18 3:50 
AnswerRe: Filter Method Pin
Stefan Vincent Haug13-Oct-18 8:52
professionalStefan Vincent Haug13-Oct-18 8:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.181207.3 | Last Updated 13 Oct 2018
Article Copyright 2018 by Stefan Vincent Haug
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid