[Solved] Order column by Data not in column

[Erica]

Hello,

I have a column that contains the first and last names of a person. I also have 2 hidden columns and one of those hidden columns contains the last name. I want to sort the "Full Name" column by the "Last Name". When the user clicks on the column header, it should order it by the last name as opposed to the text within the column. 

I know I EnableCustomBinding, but I have no idea how to create the functionality with the sortable in Razor in my Ascx page or in my controller. Can someone help me? Online, it only describes how to write some custom code for paging, which is not what I want. Please help. I've been working on this for awhile. Thank you!

[Dadv]

Hi,
you don't need to do any special thing if you have the 2 field in the model.

You Bound to the field you need and you add the template with the two field to display. by default the sort will occur on the bound field.

Here an example of custom binding in ajax, This is a fully working project, using generic sorter an generic filter :

The view Index

@(Html.Telerik().Grid<Order>()
        .Name("Grid")
       .DataKeys(d=>d.Add(a=>a.OrderID).RouteKey("OrderID"))
        .Columns(columns =>
        {
            columns.Bound(o => o.OrderID).Width(100);
            columns.Bound(o => o.ShipName).Width(100).ClientTemplate("<#= ShipName #> <#=ShipPostalCode#>");
            columns.Bound(o => o.ShipPostalCode).Width(100);
        })
            .DataBinding(dataBinding => dataBinding.Ajax().Select("_CustomBinding","Home"))
        .Pageable()
        .EnableCustomBinding(true)
        .Sortable()
        .Filterable()
)

The Controller and the Extenders

public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "Welcome to ASP.NET MVC!";

            return View();
        }

        public ActionResult About()
        {
            return View();
        }



        [GridAction(EnableCustomBinding = true)]
        public ActionResult _CustomBinding(GridCommand command)
        {
            var dataContext = new NORTHWINDEntities();

            var data = dataContext.Orders.AsQueryable();

            if (command.FilterDescriptors != null)
            {
                data = data.Filter(command.FilterDescriptors.Cast<FilterDescriptor>().ToList());
            }

            int count = data.Count();

            if (command.SortDescriptors != null)
            {
                if (command.SortDescriptors.Count == 0)
                    command.SortDescriptors.Add(new SortDescriptor { Member = "OrderID", SortDirection = System.ComponentModel.ListSortDirection.Descending });

                data = data.Sort(command.SortDescriptors);
            }

            if (command.Page == 0)
                command.Page = 1;
            if (command.PageSize == 0)
                command.PageSize = 20;

            data = data.Skip((command.Page - 1) * command.PageSize).Take(command.PageSize);


            return View(new GridModel { Data = data, Total = count });
        }
    }

    internal static class GenericSorter
    {
        public static IQueryable<T> Sort<T>(this IQueryable<T> query, IEnumerable<SortDescriptor> sorters)
        {
            int i = 0;
            foreach (var sort in sorters)
            {
                if (i++ == 0)
                    query = query.Sort(sort.Member, (int)sort.SortDirection);
                else
                    query = (query as IOrderedQueryable<T>).ThenSortBy(sort.Member, (int)sort.SortDirection);
            }

            return query;
        }

        public static IQueryable<T> Sort<T>(this IQueryable<T> query, string sortMember, int sortDirection)
        {
            Type tp = typeof(T).GetProperty(sortMember).PropertyType;


            Type sorterType = typeof(GenericSorterDirection<,>).MakeGenericType(typeof(T), tp);

            var sorterObject = Activator.CreateInstance(sorterType);

            string direction = "Ascending";

            if (sortDirection != 0)
            {
                direction = "Descending";
            }

            return (IQueryable<T>)sorterType.GetMethod(direction, new Type[] { typeof(List<T>), typeof(string) })
            .Invoke(sorterObject, new object[] { query, sortMember });
        }

        public static IQueryable<T> Sort<T>(this IEnumerable<T> query, string sortMember, int sortDirection)
        {
            Type tp = typeof(T).GetProperty(sortMember).PropertyType;


            Type sorterType = typeof(GenericSorterDirection<,>).MakeGenericType(typeof(T), tp);

            var sorterObject = Activator.CreateInstance(sorterType);

            string direction = "Ascending";

            if (sortDirection != 0)
            {
                direction = "Descending";
            }

            return (IQueryable<T>)sorterType.GetMethod(direction, new Type[] { typeof(List<T>), typeof(string) })
            .Invoke(sorterObject, new object[] { query, sortMember });
        }


        public static IOrderedQueryable<T> ThenSortBy<T>(this IOrderedQueryable<T> query, string sortMember, int sortDirection)
        {
            Type tp = typeof(T).GetProperty(sortMember).PropertyType;


            Type sorterType = typeof(GenericSorterDirection<,>).MakeGenericType(typeof(T), tp);

            var sorterObject = Activator.CreateInstance(sorterType);

            string direction = "Ascending";

            if (sortDirection != 0)
            {
                direction = "Descending";
            }

            var temp = sorterType.GetMethod("ThenBy" + direction, new Type[] { typeof(IOrderedQueryable<T>), typeof(string) });
            var temp2 = temp.Invoke(sorterObject, new object[] { query, sortMember });

            return (IOrderedQueryable<T>)temp2;
        }
    }

    internal sealed class GenericSorterDirection<T, PT>
    {
        public IEnumerable<T> Ascending(IEnumerable source, string sortMember)
        {
            var param = Expression.Parameter(typeof(T), "item");

            var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortMember), typeof(PT)), param);

            return source.OfType<T>().AsQueryable<T>().OrderBy<T, PT>(sortLambda);
        }

        public IEnumerable<T> Ascending(IEnumerable<T> source, string sortMember)
        {
            var param = Expression.Parameter(typeof(T), "item");

            var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortMember), typeof(PT)), param);

            return source.AsQueryable<T>().OrderBy<T, PT>(sortLambda);
        }

        public IEnumerable<T> Descending(IEnumerable source, string sortMember)
        {
            var param = Expression.Parameter(typeof(T), "item");

            var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortMember), typeof(PT)), param);

            return source.OfType<T>().AsQueryable<T>().OrderByDescending<T, PT>(sortLambda);
        }

        public IEnumerable<T> Descending(IEnumerable<T> source, string sortMember)
        {
            var param = Expression.Parameter(typeof(T), "item");

            var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortMember), typeof(PT)), param);

            return source.AsQueryable<T>().OrderByDescending<T, PT>(sortLambda);
        }

        public IEnumerable<T> ThenByDescending(IOrderedQueryable<T> source, string sortMember)
        {
            var param = Expression.Parameter(typeof(T), "item");

            var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortMember), typeof(PT)), param);

            return source.ThenByDescending<T, PT>(sortLambda);
        }

        public IEnumerable<T> ThenByAscending(IOrderedQueryable<T> source, string sortMember)
        {
            var param = Expression.Parameter(typeof(T), "item");

            var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortMember), typeof(PT)), param);

            return source.ThenBy<T, PT>(sortLambda);
        }
    }

    internal static class GenericFilter
    {
        public static IQueryable<T> Filter<T>(this IQueryable<T> query, string member, FilterOperator oper, object initialValue)
        {
            var isNull = Expression.Constant(null);
            var param = Expression.Parameter(typeof(T), "i");

            var prop = Expression.Property(param, member);

            var temp1 = Expression.Convert(prop, typeof(object));
            var temp2 = Expression.NotEqual(temp1, isNull);
            var temp3 = Expression.Call(prop, "ToString", null);

            var stringProp = Expression.Condition(temp2, temp3, Expression.Convert(isNull, typeof(string)));
            string stringValue = initialValue != null ? initialValue.ToString() : null;

            object value = initialValue != null ? Convert.ChangeType(initialValue, prop.Type) : null;

            Expression<Func<T, bool>> tempQuery = null;
            switch (oper)
            {
                case FilterOperator.IsLessThan://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.LessThan(prop, Expression.Constant(value)), param);
                    break;
                case FilterOperator.IsLessThanOrEqualTo://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.LessThanOrEqual(prop, Expression.Constant(value)), param);
                    break;
                case FilterOperator.IsEqualTo://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.Equal(stringProp, Expression.Constant(stringValue)), param);
                    break;
                case FilterOperator.IsNotEqualTo://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.NotEqual(stringProp, Expression.Constant(stringValue)), param);
                    break;
                case FilterOperator.IsGreaterThanOrEqualTo://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.GreaterThanOrEqual(prop, Expression.Constant(value)), param);
                    break;
                case FilterOperator.IsGreaterThan://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.GreaterThan(prop, Expression.Constant(value)), param);
                    break;
                case FilterOperator.StartsWith://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.Condition(Expression.NotEqual(prop, isNull), Expression.Call(Expression.Call(stringProp, "ToLower", null), "StartsWith", null, Expression.Constant(stringValue)), Expression.Constant(false)), param);
                    break;
                case FilterOperator.EndsWith://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.Condition(Expression.NotEqual(prop, isNull), Expression.Call(Expression.Call(stringProp, "ToLower", null), "EndsWith", null, Expression.Constant(stringValue)), Expression.Constant(false)), param);
                    break;
                case FilterOperator.Contains://
                    tempQuery = Expression.Lambda<Func<T, bool>>(Expression.Condition(Expression.NotEqual(prop, isNull), Expression.Call(Expression.Call(stringProp, "ToLower", null), "Contains", null, Expression.Constant(stringValue)), Expression.Constant(false)), param);
                    break;
                case FilterOperator.IsContainedIn://

                    //tempQuery = Expression.Lambda<Func<T, bool>>(Expression.Call(Expression.Constant(list), "Contains", null, prop), param);

                    break;
                default:
                    break;
            }

            return query.Where(tempQuery);
        }


        public static IQueryable<T> Filter<T>(this IQueryable<T> query, IList<FilterDescriptor> filters)
        {
            if (filters == null || filters.Count == 0)
                return query;

            var isNull = Expression.Constant(null);
            var param = Expression.Parameter(typeof(T), "i");

            Expression<Func<T, bool>> finalQuery = null;

            Expression lastQuery = null;
            foreach (var filter in filters)
            {
                Expression prop = Expression.Property(param, filter.Member);

                var temp1 = Expression.Convert(prop, typeof(object));
                var temp2 = Expression.NotEqual(temp1, isNull);
                var temp3 = Expression.Call(prop, "ToString", null);



                object value = null;

                if (filter.Value != null)
                {
                    Type type = filter.Value.GetType();
                    if (prop.Type != type)
                    {
                        if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(System.Nullable<>))
                        {
                            System.ComponentModel.NullableConverter nc = new System.ComponentModel.NullableConverter(type);
                            value = Convert.ChangeType(filter.Value, nc.UnderlyingType);
                        }
                        else if (prop.Type.IsGenericType && prop.Type.GetGenericTypeDefinition() == typeof(System.Nullable<>))
                        {
                            prop = Expression.Property(prop, "Value");
                            value = filter.Value;
                        }
                        else
                        {
                            value = Convert.ChangeType(filter.Value, prop.Type);
                        }

                    }
                    else
                    {
                        value = filter.Value;
                    }
                }

                Expression tempQuery = null;

                switch (filter.Operator)
                {
                    case FilterOperator.IsLessThan://IsLessThan
                        tempQuery = Expression.LessThan(prop, Expression.Constant(value));
                        break;
                    case FilterOperator.IsLessThanOrEqualTo://IsLessThanOrEqualTo
                        tempQuery = Expression.LessThanOrEqual(prop, Expression.Constant(value));
                        break;
                    case FilterOperator.IsEqualTo://IsEqualTo
                        tempQuery = Expression.Equal(prop, Expression.Constant(value));
                        break;
                    case FilterOperator.IsNotEqualTo://IsNotEqualTo
                        tempQuery = Expression.NotEqual(prop, Expression.Constant(value));
                        break;
                    case FilterOperator.IsGreaterThanOrEqualTo://IsGreaterThanOrEqualTo
                        tempQuery = Expression.GreaterThanOrEqual(prop, Expression.Constant(value));
                        break;
                    case FilterOperator.IsGreaterThan://IsGreaterThan
                        tempQuery = Expression.GreaterThan(prop, Expression.Constant(value));
                        break;
                    case FilterOperator.StartsWith://StartsWith
                        if (prop.Type == typeof(string) && value.GetType() == typeof(string))
                            tempQuery = Expression.Condition(Expression.NotEqual(prop, isNull), Expression.Call(Expression.Call(prop, "ToLower", null), "StartsWith"
                            , null, Expression.Constant(value)), Expression.Constant(false));
                        break;
                    case FilterOperator.EndsWith://EndsWith
                        if (prop.Type == typeof(string) && value.GetType() == typeof(string))
                            tempQuery = Expression.Condition(Expression.NotEqual(prop, isNull), Expression.Call(Expression.Call(prop, "ToLower", null), "EndsWith", null, Expression.Constant(value)), Expression.Constant(false));
                        break;
                    case FilterOperator.Contains://Contains
                        if (prop.Type == typeof(string) && value.GetType() == typeof(string))
                            tempQuery = Expression.Condition(Expression.NotEqual(prop, isNull), Expression.Call(Expression.Call(prop, "ToLower", null), "Contains", null, Expression.Constant(value)), Expression.Constant(false));
                        break;
                    case FilterOperator.IsContainedIn://IsContainedIn

                        //tempQuery = Expression.Call(Expression.Constant(list), "Contains", null, prop);

                        break;
                    default:
                        break;
                }

                if (tempQuery != null)
                {
                    if (lastQuery == null)
                    {
                        lastQuery = tempQuery;
                    }
                    else
                    {
                        lastQuery = Expression.AndAlso(lastQuery, tempQuery);
                    }
                }
            }
            if (lastQuery == null)
                return query;

            finalQuery = Expression.Lambda<Func<T, bool>>(lastQuery, param);
            return query.Where(finalQuery);

        }

        public static T GetFilterValueByMember<T>(this IList<FilterDescriptor> filters, string member)
        {
            if (string.IsNullOrWhiteSpace(member))
                throw new System.ArgumentNullException("member");

            var filter = filters.FirstOrDefault(f => f.Member == member);
            if (filter != null)
            {
                return (T)filter.Value;
            }

            return default(T);
        }
    }

Regards,

[Erica]

Thanks! Setting the column as the bound and then using the client template worked VERY well. My only problem now is that the Title of the column does not display as desired. It now shows "Last Name" instead of "Name" and it has to show "Name" - the problem is that .Title("Name") isn't working, and I set the DisplayName in the model. I need to set the DisplayName to be "Last Name" when it is in edit form, but for the gridview it MUST show Name. how can I do this as well?

Thanks so much for saving me a lot of headache with the sorting though. The technique is really simple!

[Dadv]

You're welcome, don't forget to mark as answer.

for the title you can use .Title("Name")

columns.Bound(o => o.ShipName).Width(100).ClientTemplate("<#= ShipName #> <#=ShipPostalCode#>").Title("Name"); 

[Erica]

Hey, thanks!