Multisorting IQueryable using a string column name within a generic extension method?

Question

I need to sort collection by a few columns. I have list of objects where every object contain columnName and sortDir(Asc or Desc). So, how i can do it? I have tried to do it by reflection, but i have the error:

Message: `LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression.`
StackTrace: `   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()`

I read some articles this and this, but their solutions doesn't help me. Maybe somebody have some ideas for me?

Code:

public static IQueryable<T> SortByParams<T>(IQueryable<T> collection, List<SortBy> sorting)
        {
            bool firstOrder = true;
            IOrderedQueryable<T> sortedRes = null;
            foreach (SortBy sort in sorting)
            {
                try
                {
                    if (firstOrder)
                    {
                        if (sort.sortDir.ToLower() == "desc")
                        {
                            sortedRes = collection.OrderByDescending(obj => obj.GetType().GetProperty(sort.columnName).GetValue(obj, null));
                        }
                        else
                        {                            
                            sortedRes = collection.OrderBy(obj => obj.GetType().GetProperty(sort.columnName).GetValue(obj, null));
                        }
                        firstOrder = false;
                    }
                    else
                    {
                        if (sort.sortDir.ToLower() == "desc")
                        {
                            sortedRes = sortedRes.ThenByDescending(obj => obj.GetType().GetProperty(sort.columnName).GetValue(obj, null));
                        }
                        else
                        {
                            sortedRes = sortedRes.ThenBy(obj => obj.GetType().GetProperty(sort.columnName).GetValue(obj, null));
                        }
                    }
                }
                catch (Exception) { }
                collection = sortedRes;
            }
            return collection;
        }

That error is not related to the sorting. Where do you use int.Parse? — Gert Arnold, Jun 10 '16 at 08:42
@GertArnold, i didn't use it. And i don't know why i have this error. And this is not the RuntimeException, i detect this error in debug mode. Look at the picture please. https://pp.vk.me/c630624/v630624523/31e41/9cHa9xlCmsc.jpg — mrabaev48, Jun 10 '16 at 08:51
Well, as far as I can see this is all your own code. The parse is probably in the `collection` query. Anyway, without this error you'll soon notice that you can't use `obj.GetType() ...` in a LINQ-to-entities query. You don't seem to understand that in the end the whole query expression must be translatable to SQL. — Gert Arnold, Jun 10 '16 at 09:09
Show how you tried Marc Gravell's solution. It should work. Or Ivan's, for that matter. Show what you do in stead of posting images of fragments of exceptions without code. — Gert Arnold, Jun 10 '16 at 12:09
Your own custom comparer to implement the logic, something like this: http://stackoverflow.com/questions/985657/use-own-icomparert-with-linq-orderby — Dexion, Jun 14 '16 at 14:46

score 2 · Accepted Answer · answered Jun 10 '16 at 10:28

Expressions like this

obj => obj.GetType().GetProperty(sort.columnName).GetValue(obj, null)

might be applicable in LINQ To Objects context, but definitely not in LINQ to Entities.

In general you cannot use reflection (and many other methods) inside the LINQ to Entities query expression because they cannot be translated to SQL (or query provider does not recognize and handle them, hence NotSupportedException).

Instead of reflection, you should build the expression using System.Linq.Expressions.

For instance, like this:

public static IQueryable<T> SortByParams<T>(this IQueryable<T> source, List<SortBy> sorting)
{
    var queryExpr = source.Expression;
    string methodAsc = "OrderBy";
    string methodDesc = "OrderByDescending";
    foreach (var item in sorting)
    {
        var selectorParam = Expression.Parameter(typeof(T), "e");
        var selector = Expression.Lambda(Expression.PropertyOrField(selectorParam, item.columnName), selectorParam);
        var method = string.Equals(item.sortDir, "desc", StringComparison.OrdinalIgnoreCase) ? methodDesc : methodAsc;
        queryExpr = Expression.Call(typeof(Queryable), method,
            new Type[] { selectorParam.Type, selector.Body.Type },
            queryExpr, Expression.Quote(selector));
        methodAsc = "ThenBy";
        methodDesc = "ThenByDescending";
    }
    return source.Provider.CreateQuery<T>(queryExpr);
}

thank u for reply. I have tried your solution, but it have some error. Please, look at the picture https://pp.vk.me/c630624/v630624523/31e8b/3HSIZ-25Gos.jpg — mrabaev48, Jun 10 '16 at 12:05
I don't see an error in the picture. Also the posted code is tested and is working. — Ivan Stoev, Jun 10 '16 at 12:21

Multisorting IQueryable using a string column name within a generic extension method?

1 Answers1