Sorting,Paging in mvc5 with column name as parameter

Question

I have to implement sorting. While i want to do this by not hard coding the model like x.SlNo below.

public ActionResult Index(string sortOrder, string sortDirection, int? itemsPerPage, int? page, string SearchString)
{                                              
    IPagedList<TransferBenefits> entityList = null;                                 
    sortOrder = String.IsNullOrEmpty(sortOrder) ? "SlNo" : sortOrder;       

    var propertyInfo = typeof(TransferBenefits).GetProperty(sortOrder);
    var selectList = db.transferBenefits.OrderBy(x => propertyInfo.GetValue(x, null));
    //selectList = db.transferBenefits.OrderBy(x => x.SlNo); --uncommenting this works.
    entityList = selectList.ToPagedList(pageNumber: page ?? 1, pageSize: itemsPerPage ?? 10);

    return View(entityList) 
}

I am getting this error while uncommenting code runs fine.

An exception of type 'System.NotSupportedException' occurred in mscorlib.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.Object GetValue(System.Object, System.Object[])' method, and this method cannot be translated into a store expression.

You should switch to Dynamic Linq, the very old but still working approach. Check for example this http://stackoverflow.com/questions/2728340/how-can-i-do-an-orderby-with-a-dynamic-string-parameter — Wiktor Zychla, May 16 '16 at 10:33
I would be great if possible with lamda expression. I have to take permission before using any library. — rajeev, May 16 '16 at 10:39
There it is https://dynamiclinq.codeplex.com/SourceControl/latest#DynamicLinq/System.Linq.Dynamic/Dynamic.cs — Wiktor Zychla, May 16 '16 at 10:43
Dynamic Linq worked. Thanks Wiktor. It might be great if it could be done with lamda. — rajeev, May 16 '16 at 11:10
Thanks Arun, jquery datatable is a great way with lots of features. — rajeev, Jun 15 '21 at 05:39

score 0 · Answer 1 · edited May 16 '16 at 12:36

You can use Expression to build a parameter.

public static class IQueryableExtensions
{
    public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortName, string sortOrder)
    {
        if (string.IsNullOrEmpty(sortName)) return query;
        var retQuery = query;
        var propGroup = sortName.Split(',');

        for (int k = 0; k < propGroup.Length; k++)
        {
            var typeOfProp = typeof(T);
            var sortField = propGroup[k];
            var currentOrder = GetOrderMethod(sortOrder, k);
            var param = Expression.Parameter(typeof(T), "o");
            var props = sortField.Split('.');
            Expression propertyAccess = param;
            var i = 0;
            do
            {
                var property = ReflectionTypePropertyCache<T>.GetProperty(props[i]);
                if (property == null) throw new Exception("property not found :" + sortName);
                typeOfProp = property.PropertyType;
                propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
                i++;
            } while (i < props.Length);

            var orderByExp = Expression.Lambda(propertyAccess, param);
            var resultExp = Expression.Call(typeof(Queryable),
                    currentOrder,
                    new[] { typeof(T), typeOfProp },
                    retQuery.Expression,
                    Expression.Quote(orderByExp));
            retQuery = retQuery.Provider.CreateQuery<T>(resultExp);
        }
        return retQuery;
    }

    private static string GetOrderMethod(string sortOrder, int index)
    {
        const string ASC = "OrderBy";
        const string DESC = "OrderByDescending";
        const string THENASC = "ThenBy";
        const string THENDESC = "ThenByDescending";
        string AscVar, DescVar;
        if (index == 0)
        {
            AscVar = ASC;
            DescVar = DESC;
        }
        else
        {
            AscVar = THENASC;
            DescVar = THENDESC;
        }

        if (string.IsNullOrEmpty(sortOrder)) return AscVar;
        var orderArr = sortOrder.Split(',');
        if (index >= orderArr.Length) return AscVar;
        if (orderArr[index].ToLower() == "desc") return DescVar;
        return AscVar;
    }
}

And you can use it as :

var selectList = db.transferBenefits.OrderBy(sortOrder,"");

Sorting,Paging in mvc5 with column name as parameter

1 Answers1