WebApi

Using JQuery DataTables , Entity Framework (EF) and Dynamic LINQ in conjunction


The Problem

Few months ago I started using DataTables (A plugin for JQuery in order to create neat tables in HTML). However, I noticed that twice I wrote code kind of similar for addressing the same issue. Thus with this project I’d like to set a base for extensible code which deals with the common request and response work related to DataTable API. The goal is to quickly provide an endpoint which provides data compatible with DataTable data source.
All the filtering, sorting and projection should happen at server side.

For this solution I’ll use the following components:
DataTables see https://www.datatables.net/
Dynamic LINQ see http://dynamiclinq.azurewebsites.net/

My project is inspired by https://github.com/kendo-labs/dlinq-helpers. Kendo Grid is a very robust table for HTML.

Remarks

– Filtering logic has not been taking into consideration, mainly because I didn’t find a common mechanism for receive filter information at server side. If this is a requirement, I will consider to mimic Kendo UI protocol for sending filtering information.
– Order and Search have been implemented
– Implementations for Array of Array and Array of Objects have been provided, however the developer is responsible of calling the proper methods.

The Solution

First of all The idea is to create an extensible module. At this time the filtering logic is pending to really have a useful. Thus I will start by showing the code of the DataTables module. The following code holds the models used by the model these classes are basically POCOS to be used to receive or send information.

using System.Runtime.Serialization;

namespace DataTables.Models
{
    /// <summary>
    /// Holds the information required for a given column
    /// </summary>
    [DataContract]
    public class ColumnRequest
    {
        /// <summary>
        /// Specific search information for the column
        /// </summary>
        [DataMember(Name = "search")]
        public SearchRequest Search { get; set; }

        /// <summary>
        /// Column's data source, as defined by columns.data.
        /// </summary>
        [DataMember(Name = "data")]
        public string Data { get; set; }

        /// <summary>
        /// Column's name, as defined by columns.name.
        /// </summary>
        [DataMember(Name = "name")]
        public string Name { get; set; }

        /// <summary>
        /// Flag to indicate if this column is search-able (true) or not (false). This is controlled by columns.searchable.
        /// </summary>
        [DataMember(Name = "searchable")]
        public bool? Searchable { get; set; }

        /// <summary>
        /// Flag to indicate if this column is orderable (true) or not (false). This is controlled by columns.orderable.
        /// </summary>
        [DataMember(Name = "orderable")]
        public bool? Orderable { get; set; }

    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;

namespace DataTables.Models
{
    /// <summary>
    /// 
    /// </summary>
    [DataContract]
    public class SearchRequest
    {
        /// <summary>
        /// Search value
        /// </summary>
        [DataMember(Name = "value")]
        public string Value { get; set; }

        /// <summary>
        /// true if the search value should be treated as a regular expression for advanced searching, false otherwise. 
        /// </summary>
        [DataMember(Name = "regex")]
        public bool Regex { get; set; }

        public virtual string Operator { get; set; }

        public SearchRequest()
        {
            Operator = @"contains";
        }

        private static readonly IDictionary<string, string> operators = new Dictionary<string, string>
        {
            {"eq", "="},
            {"neq", "!="},
            {"lt", "<"},
            {"lte", "<="},
            {"gt", ">"},
            {"gte", ">="},
            {"startswith", "StartsWith"},
            {"endswith", "EndsWith"},
            {"contains", "Contains"},
            {"doesnotcontain", "Contains"}
        };

        public virtual string ToExpression(DataTableRequest request)
        {
            if (Regex)
                throw new NotImplementedException("Regular Expression is not implemented");

            string comparison = operators[Operator];
            List<string> expressions = new List<string>();
            foreach (var searchableColumn in request.Columns.Where(c => c.Searchable.HasValue && c.Searchable.Value))
            {
                if (Operator == "doesnotcontain")
                {
                    expressions.Add(string.Format("!{0}.ToString().{1}(\"{2}\")", searchableColumn.Data, comparison, Value));
                }
                else if (comparison == "StartsWith" || comparison == "EndsWith" || comparison == "Contains")
                {
                    expressions.Add(string.Format("{0}.ToString().{1}(\"{2}\")", searchableColumn.Data, comparison, Value));
                }
                else
                {
                    expressions.Add(string.Format("{0} {1} \"{2}\"", searchableColumn.Data, comparison, Value));
                }
            }
            return string.Join(" or ", expressions);
        }
    }
}

using System.Runtime.Serialization;

namespace DataTables.Models
{
    /// <summary>
    /// Contains the information about the sort request
    /// </summary>
    [DataContract]
    public class OrderRequest
    {
        /// <summary>
        /// Indicates the orientation of the sort "asc" for ascending or "desc" for desc
        /// </summary>
        [DataMember(Name = "dir")]
        public string Dir { get; set; }

        /// <summary>
        /// Column which contains the number of column which requires this sort.
        /// </summary>
        [DataMember(Name = "column")]
        public int? Column { get; set; }

        public string ToExpression(DataTableRequest request)
        {
            return string.Concat(request.Columns[Column.Value].Data, " ", Dir);
        }
    }
}


using System.Collections;
using System.Runtime.Serialization;

namespace DataTables.Models
{
    /// <summary>
    /// Encapsulates a Data Table response format
    /// </summary>
    /// <typeparam name="TData"></typeparam>
    [DataContract]
    public class DataTableResponse 
    {
        /// <summary>
        /// The draw counter that this object is a response to - from the draw parameter sent as part of the data request. Note that it is strongly recommended for security reasons that you cast this parameter to an integer, rather than simply echoing back to the client what it sent in the draw parameter, in order to prevent Cross Site Scripting (XSS) attacks.
        /// </summary>
        [DataMember(Name = "draw")]
        public int? Draw { get; set; }

        /// <summary>
        /// The data to be displayed in the table. 
        /// <remarks>
        /// This is an array of data source objects, one for each row, which will be used by DataTables. 
        /// Note that this parameter's name can be changed using the ajax option's dataSrc property.
        /// </remarks>
        /// </summary>
        [DataMember(Name = "data")]
        public IEnumerable Data { get; set; }

        /// <summary>
        /// Total records, before filtering (i.e. the total number of records in the database)
        /// </summary>
        [DataMember(Name = "recordsTotal")]
        public int? RecordsTotal { get; set; }

        /// <summary>
        /// Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned for this page of data).
        /// </summary>
        [DataMember(Name = "recordsFiltered")]
        public int? RecordsFiltered { get; set; }

        /// <summary>
        /// Optional: If an error occurs during the running of the server-side processing script, you can inform the user of this error by passing back the error message to be displayed using this parameter. Do not include if there is no error.
        /// </summary>
        [DataMember(Name = "error")]
        public string Error { get; set; }
    }
}

using System.Collections.Generic;
using System.Runtime.Serialization;

namespace DataTables.Models
{
    /// <summary>
    /// This class holds the minimum amount of information provided by DataTable to perform a request on server side.
    /// </summary>
    [DataContract]
    public class DataTableRequest
    {
        /// <summary>
        /// Column data request description
        /// </summary>
        [DataMember(Name = "columns")]
        public List<ColumnRequest> Columns { get; set; }

        /// <summary>
        /// Column requested order description
        /// </summary>
        [DataMember(Name = "order")]
        public List<OrderRequest> Order { get; set; }

        /// <summary>
        /// Global search value. To be applied to all columns which have search-able as true.
        /// </summary>
        [DataMember(Name = "search")]
        public SearchRequest Search { get; set; }

        /// <summary>
        /// Paging first record indicator. This is the start point in the current data set (0 index based - i.e. 0 is the first record).
        /// </summary>
        [DataMember(Name = "start")]
        public int? Start { get; set; }

        /// <summary>
        /// Number of records that the table can display in the current draw. It is expected that the number of records returned will be equal to this number, unless the server has fewer records to return. 
        /// </summary>
        [DataMember(Name = "length")]
        public int? Length { get; set; }

        /// <summary>
        /// Draw counter. This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables (Ajax requests are asynchronous and thus can return out of sequence). 
        /// This is used as part of the draw return parameter. 
        /// </summary>
        [DataMember(Name = "draw")]
        public int? Draw { get; set; }
    }
}

They way I have decided to implement logic is through extension methods.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
using DataTables.Models;

namespace DataTables.Extensions
{
    public static class IQueryableExtensions
    {
        /// <summary>
        /// Computes a DataTables response based on the request. This response is compatible with an array of arrays
        /// </summary>
        /// <remarks>
        /// This response is compatible with an array of arrays
        /// </remarks>
        /// <typeparam name="TEntity">Entity to be returned in the data</typeparam>
        /// <param name="query">Initial data source. It will be part of the response after computations</param>
        /// <param name="request">Holds information about the request</param>
        /// <param name="getEntityFieldNamesFunc">Function which provides a sequence of fields of the entity to be used</param>
        /// <param name="getEntityAsEnumerableFunc">Function which converts an entity to an array</param>
        /// <returns></returns>
        public static DataTableResponse GetDataTableResponse<TEntity>(this IEnumerable<TEntity> query, DataTableRequest request,
            Func<TEntity, IEnumerable<string>> getEntityFieldNamesFunc, Func<TEntity, System.Collections.IEnumerable> getEntityAsEnumerableFunc)
        {
            // When using Arrays set the columns data before performing operations.
            SetColumnData(request, getEntityFieldNamesFunc);

            var response = GetDataTableResponse(ref query, request);

            // Converts the result compatible with an array (required by default for DataTables)
            response.Data = query.Select(getEntityAsEnumerableFunc);
            return response;
        }

        /// <summary>
        /// Computes a DataTables response based on the request. This response is compatible with an array of objects.
        /// </summary>
        /// <remarks>
        /// This response is compatible with an array of objects.
        /// </remarks>
        /// <typeparam name="TEntity">Entity to be returned in the data</typeparam>
        /// <param name="query">Initial data source. It will be part of the response after computations</param>
        /// <param name="request">Holds information about the request</param>
        /// <returns></returns>
        public static DataTableResponse GetDataTableResponse<TEntity>(this IEnumerable<TEntity> query, DataTableRequest request)
        {
            // When using Arrays set the columns data before performing operations.
            var response = GetDataTableResponse(ref query, request);

            // Converts the result compatible with an array (required by default for DataTables)
            response.Data = query;
            return response;
        }

        private static DataTableResponse GetDataTableResponse<TEntity>(ref IEnumerable<TEntity> query, DataTableRequest request)
        {
            var response = new DataTableResponse();
            // Setting up response
            response.Draw = request.Draw;
            response.RecordsTotal = query.Count();

            // sorting
            query = query.OrderBy(string.Join(",", request.Order.Select(o => o.ToExpression(request))));

            // search
            if (request.Search != null && !string.IsNullOrEmpty(request.Search.Value))
            {
                query = query.Where(request.Search.ToExpression(request));
            }

            // filtering


            // Counting results
            response.RecordsFiltered = query.Count();

            // Returning page (subset of filtered records)
            if (request.Start.HasValue && request.Start.Value > 0)
                query = query.Skip(request.Start.Value);
            if (request.Length.HasValue && request.Length.Value >= 0)
                query = query.Take(request.Length.Value);
            return response;
        }

        private static void SetColumnData<TEntity>(DataTableRequest request, Func<TEntity, IEnumerable<string>> func)
        {
            SetColumnData(request, func(default(TEntity)).ToArray());
        }

        private static void SetColumnData(DataTableRequest request, params string[] fields)
        {
            for (int index = 0; index < fields.Length && index < request.Columns.Count; index++)
            {
                var fieldIndex = 0;
                if (int.TryParse(request.Columns[index].Data, out fieldIndex))
                    request.Columns[index].Data = fields[fieldIndex];
            }
        }
    }
}

The previous code takes advantage Dynamic LINQ to compute queries, and using as input the request information. With the previous code in place, we can use in our Controller the following code:

using DataTableSample.Services;
using DataTables.Models;
using DataTableSample.Models;
using System.Linq.Dynamic;
using DataTables.Extensions;

namespace DataTableSample.Controllers
{
    public class EmployeesController : ApiController
    {
        private CompanyModel db = new CompanyModel();

        // GET: api/Employees
        public async Task<IHttpActionResult> GetEmployees([FromUri]DataTableRequest request)
        {
            var isUsingArrayOfArrays = request.Columns.Where(c => { var n = 0; return int.TryParse(c.Data, out n); }).Any();
            var response = isUsingArrayOfArrays
                ? db.Employees.AsNoTracking().AsQueryable().GetDataTableResponse(request, GetEntityFieldNames, GetEntityAsEnumerable)
                : db.Employees.AsNoTracking().AsQueryable().GetDataTableResponse(request)
                ;
            return Json(response);
        }

        /// <summary>
        /// Converts the Entity to a sequence of values (an Array)
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        private System.Collections.IEnumerable GetEntityAsEnumerable(Employee e)
        {
            yield return e.FirstName;
            yield return e.LastName;
            yield return e.Position;
            yield return e.Office;
            yield return e.StartDate;
            yield return e.Salary;
        }

        /// <summary>
        /// Provides the name of the Columns used in the Array
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        private IEnumerable<string> GetEntityFieldNames(Employee e)
        {
            yield return nameof(e.FirstName);
            yield return nameof(e.LastName);
            yield return nameof(e.Position);
            yield return nameof(e.Office);
            yield return nameof(e.StartDate);
            yield return nameof(e.Salary);
        }
    }
}

The last portion of code is

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" />
    <meta charset="utf-8" />
</head>
<body>
    <h1>Using Objects</h1>
    <table id="example1" class="display" cellspacing="0" style="width:100%;">
        <thead>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>
    <h1>Using Arrays</h1>
    <table id="example2" class="display" cellspacing="0" style="width:100%;">
        <thead>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>
</body>
</html>
<script src="Scripts/jquery-1.7.js"></script>
<script src="Scripts/DataTables/jquery.dataTables.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#example1').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax": "api/Employees",
            "columns": [
                { "data": "FirstName" },
                { "data": "LastName" },
                { "data": "Position" },
                { "data": "Office" },
                { "data": "StartDate" },
                { "data": "Salary" }
            ]
        });

        $('#example2').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax": "api/Employees"
        });
    });
</script>

By running the application the two tables will be populated, one of them will be using Array of Arrays and the second will be using an Array of objects.

The full source code can be downloaded from:
https://github.com/hmadrigal/playground-dotnet/tree/master/MsWebApi.DataTables

Cheers,
Herb