Example documentation - best practices to extract column for server side sorting?

[dylinmaust]

Hello, fantastic library, just had a question on best practices in sorting via LINQ based on the IDataTablesRequest request

For example, I know I can extract the given column for which the sort has been applied on the client side by:

var sortColumn = request.Columns.Where(c => c.Sort != null).FirstOrDefault();

From there, I have access to the field name as a string and can check the string to conditionally create a where clause, but is there a better way?

[ALMMa]

Hello, @TimeBomb006.

First of all, thanks for your feedback.
I've been really busy with some upgrades to AspNet5 so I couldn't answer earlier.

I'm sorry to inform that there isn't a better way to do that. This is a limitation imposed by how DataTables request are sent and to avoid extremely complex code for this library.

You can also refer to the DataTables.AspNet.Extensions.AnsiSql project (still without Nuget, available from dev branch), where I'm implementing a helper/extension to build an Ansi-SQL where clause directly from the column collection.

[sintetico82]

Hi!
inspired by this stackoverflow answer: http://stackoverflow.com/a/36303246/726868

I create this extension method for IQueryable OrderBy

 public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, IEnumerable<DataTables.AspNet.Core.IColumn> sortModels)
        {
            var expression = source.Expression;
            int count = 0;
            foreach (var item in sortModels)
            {
                var parameter = Expression.Parameter(typeof(T), "x");
                var selector = Expression.PropertyOrField(parameter, item.Field);
                var method = item.Sort.Direction == DataTables.AspNet.Core.SortDirection.Descending ?
                    (count == 0 ? "OrderByDescending" : "ThenByDescending") :
                    (count == 0 ? "OrderBy" : "ThenBy");
                expression = Expression.Call(typeof(Queryable), method,
                    new Type[] { source.ElementType, selector.Type },
                    expression, Expression.Quote(Expression.Lambda(selector, parameter)));
                count++;
            }
            return count > 0 ? source.Provider.CreateQuery<T>(expression) : source;
        }

So you can active your goals like this:

 var orderColums = request.Columns.Where(x => x.Sort != null);
 var dataPage = data.OrderBy(orderColums).Skip(request.Start).Take(request.Length);

bye

[A100K]

sintetico82 - Big UP for the reference code. Works like a charm.

[ronnieoverby]

Some fixes and extra extensions:

using DataTables.AspNet.Core;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace MyApp.Extensions
{
    public  static class DataTablesExtensions
    {
        public static IQueryable<T> SortAndPage<T>(this IQueryable<T> source, IDataTablesRequest request)
        {
            return source.OrderBy(request.Columns).Page(request);
        }

        public static IQueryable<T> Page<T>(this IQueryable<T> source, IDataTablesRequest request)
        {
            return source.Skip(request.Start).Take(request.Length);
        }

        public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, IEnumerable<IColumn> sortModels)
        {
            var expression = source.Expression;
            var count = 0;
            foreach (var item in sortModels.Where(x=>x.Sort != null).OrderBy(x => x.Sort.Order))
            {
                var parameter = Expression.Parameter(typeof(T), "x");
                var selector = Expression.PropertyOrField(parameter, item.Field);
                var method = item.Sort.Direction == SortDirection.Descending ?
                    (count == 0 ? nameof(Queryable.OrderByDescending) : nameof(Queryable.ThenByDescending)) :
                    (count == 0 ? nameof(Queryable.OrderBy) : nameof(Queryable.ThenBy));
                expression = Expression.Call(typeof(Queryable), method,
                    new Type[] { source.ElementType, selector.Type },
                    expression, Expression.Quote(Expression.Lambda(selector, parameter)));
                count++;
            }
            return count > 0 ? source.Provider.CreateQuery<T>(expression) : source;
        }
    }
}

[sintetico82]

It's so funny. I was searching for the same solution now... and i found the answer of myself ahaha
We live in the loop!

[professordave]

I realize this thread is old but I will ask anyway.

If i have an enumerable can I use this?

If not, when would I have a queryable?

If I can use this with enumerable is there equivalent code for filtering?

I tried converting the enumerable to queryable using asqueryable() but the code copied from above throws exception with nulls or something on columns.

So is it a bug with the code above or converting enumerable to queryable.

Thanks in advance

[ronnieoverby]

@professordave I think you should share your exceptions. I would think using the AsQueryable extension method would be the thing to do.

[professordave]

Thanks I will do on Monday. Then the follow up, is there an equivalent code for the search?

In what scenario would you need/ use a sort but not a search?

[professordave]

Sort and Page works!! Thanks! Now what about filter/search?

We are able to use sort and page against an in memory object, enumerable, works.

What about filtering? Thanks in advance

[ronnieoverby]

datatables.aspnet/samples/DataTables.AspNet.Samples.AspNetCore.BasicIntegration/Controllers/HomeController.cs

Lines 66 to 68 in 115ee9a

	var filteredData = String.IsNullOrWhiteSpace(request.Search.Value)
	? data
	: data.Where(_item => _item.Name.Contains(request.Search.Value));