How to do server side paging from a DataTable?

[Matt Barker]

Hi,

Our data access layer returns a DataTable generated by a stored procedure using ADO .Net.
I've seen and tried your Kendo.DynamicLinq NuGet package. It works well with "normal" IQueryable objects, but I have the following issues:

Dynamic Linq queries don't work with Linq to DataSets
Converting the DataRows into DynamicObjects doesn't work with Dynamic Linq

Is there an example available to achieve what I want? (Namely server side paging, sorting & filtering)

Thank you.

[Matt Barker]

I think that I've cracked it. This requires testing of course, but here's the general approach in case anybody else ever needs it:

using System;
using System.Runtime.Serialization;

/// <summary>
/// Represents a sort.
/// </summary>
[DataContract, Serializable]
public class Sort
{
    /// <summary>
    /// Gets or sets the direction.
    /// </summary>
    /// <value>
    /// The direction.
    /// </value>
    [DataMember(Name = "dir")]
    public string Direction { get; set; }

    /// <summary>
    /// Gets or sets the field.
    /// </summary>
    /// <value>
    /// The field.
    /// </value>
    [DataMember(Name = "field")]
    public string Field { get; set; }

    /// <summary>
    /// Gets the expression.
    /// </summary>
    /// <returns>
    /// The expression.
    /// </returns>
    public string GetExpression()
    {
        return this.Field + " " + this.Direction;
    }
}

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

/// <summary>
/// Represents a filter.
/// </summary>
[DataContract, Serializable]
public class Filter
{
    /// <summary>
    /// The templates
    /// </summary>
    private static readonly IDictionary<string, string> Templates = new Dictionary<string, string>
    {
        { "eq", "{0} = '{1}'" },
        { "neq", "{0} <> '{1}'" },
        { "lt", "{0} < '{1}'" },
        { "lte", "{0} <= '{1}'" },
        { "gt", "{0} > '{1}'" },
        { "gte", "{0} >= '{1}'" },
        { "startswith", "{0} like '{1}*'" },
        { "endswith", "{0} like '*{1}'" },
        { "contains", "{0} like '*{1}*'" },
        { "doesnotcontain", "{0} not like '*{1}*'" }
    };

    /// <summary>
    /// Gets or sets the field.
    /// </summary>
    /// <value>
    /// The field.
    /// </value>
    [DataMember(Name = "field")]
    public string Field { get; set; }

    /// <summary>
    /// Gets or sets the filters.
    /// </summary>
    /// <value>
    /// The filters.
    /// </value>
    [DataMember(Name = "filters")]
    public IEnumerable<Filter> Filters { get; set; }

    /// <summary>
    /// Gets or sets the logic.
    /// </summary>
    /// <value>
    /// The logic.
    /// </value>
    [DataMember(Name = "logic")]
    public string Logic { get; set; }

    /// <summary>
    /// Gets or sets the operator.
    /// </summary>
    /// <value>
    /// The operator.
    /// </value>
    [DataMember(Name = "operator")]
    public string Operator { get; set; }

    /// <summary>
    /// Gets or sets the value.
    /// </summary>
    /// <value>
    /// The value.
    /// </value>
    [DataMember(Name = "value")]
    public object Value { get; set; }

    /// <summary>
    /// Gets the expression.
    /// </summary>
    /// <returns>
    /// The expression.
    /// </returns>
    public string GetExpression()
    {
        return this.GetExpression(this.Filters, this.Logic);
    }

    /// <summary>
    /// Called when deserialized.
    /// </summary>
    /// <param name="context">The context.</param>
    [OnDeserialized]
    public void OnDeserialized(StreamingContext context)
    {
        if (this.Value != null)
        {
            var value = this.Value.ToString();

            // DateTime values are sent in the format /Date(0000000000000)/
            if (value.Substring(0, 6) == "/Date(" && value.Length > 20)
            {
                // The digits represent the milliseconds since the start of the Unix epoch
                var milliseconds = long.Parse(value.Substring(6, 13));
                var unixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

                // This date format works with the data table select statement
                this.Value = unixEpoch.AddMilliseconds(milliseconds).ToString("yyyy-MM-dd");
            }
        }
    }

    /// <summary>
    /// Gets the expression.
    /// </summary>
    /// <param name="filters">The filters.</param>
    /// <param name="logic">The logic.</param>
    /// <returns>
    /// The expression.
    /// </returns>
    private string GetExpression(IEnumerable<Filter> filters, string logic)
    {
        string result = string.Empty;

        if (filters != null && filters.Any<Filter>() && !string.IsNullOrWhiteSpace(logic))
        {
            var list = new List<string>();

            foreach (Filter filter in filters)
            {
                if (!string.IsNullOrWhiteSpace(filter.Field))
                {
                    string template = Templates[filter.Operator];
                    string value = filter.Value.ToString();

                    list.Add(string.Format(template, filter.Field, value));
                }

                if (filter.Filters != null)
                {
                    list.Add(this.GetExpression(filter.Filters, filter.Logic));
                }
            }

            result = "(" + string.Join(" " + logic + " ", list) + ")";
        }

        return result;
    }
}

And then in the DAL:

/// <summary>
/// Gets the grid.
/// </summary>
/// <param name="procedureName">Name of the procedure.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="take">The take.</param>
/// <param name="skip">The skip.</param>
/// <param name="sort">The sort.</param>
/// <param name="filter">The filter.</param>
/// <returns>
/// The grid.
/// </returns>
public Grid GetGrid(
    string procedureName,
    IDictionary<string, string> parameters,
    int take,
    int skip,
    IEnumerable<Sort> sort,
    Filter filter)
{
    DataTable sourceData = this.GetDataTable(procedureName, parameters);
    var sortExpression = sort == null ? string.Empty : string.Join(",", sort.Select(item => item.GetExpression()));
    var filterExpression = filter == null ? string.Empty : filter.GetExpression();

    IEnumerable<DataRow> filtered = sourceData.Select(filterExpression, sortExpression);
    IEnumerable<DataRow> page = filtered.Skip(skip).Take(take);
    DataTable data = sourceData.Clone();

    page.ToList().ForEach(row => data.ImportRow(row));

    var grid = new Grid()
    {
        Data = JsonConvert.SerializeObject(data),
        Total = filtered.Count()
    };

    return grid;
}