Unable to sort with property name in LINQ OrderBy

Question

Error is

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.

My code is

public static GridResult GetAllUsers(int count, int tblsize,string sortcreteria)
{
      using (UserEntities entity = new UserEntities())
      {

          var data = entity.User_Details.Take(count)
.OrderBy(i =>.GetType().GetProperty(sortcreteria).GetValue(i,null))
.Skip(tblsize).ToList();
          result.DataSource = data;
          result.Count = entity.User_Details.Count();
      }
      return result;
}

How to sort with property name as string?

A very similar question http://stackoverflow.com/q/15973414/413032 — Davut Gürbüz, Apr 15 '13 at 11:22
"LINQ to Entities does not recognize the method" mean I cannot translate the reflection expression to SQL. — Davut Gürbüz, Apr 15 '13 at 11:22

Ziad · Accepted Answer · 2015-05-20T14:52:58.030

Just add the following extension to your code and you're good to go:

using System.Linq;
using System.Linq.Expressions;
using System;

namespace SomeNameSpace
{
    public static class SomeExtensionClass
    {
        public static IQueryable<T> OrderByField<T>(this IQueryable<T> q, string SortField, bool Ascending)
        {
            var param = Expression.Parameter(typeof(T), "p");
            var prop = Expression.Property(param, SortField);
            var exp = Expression.Lambda(prop, param);
            string method = Ascending ? "OrderBy" : "OrderByDescending";
            Type[] types = new Type[] { q.ElementType, exp.Body.Type };
            var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
            return q.Provider.CreateQuery<T>(mce);
        }
    }

}

Usage:

.OrderByField(sortcriteria, true)

Edit:

For support for the ThenBy method however the following methods returning an IOrderedQueryable should take care of it all:

public static class SomeExtensionClass
{
    private static IOrderedQueryable<T> OrderingHelper<T>(IQueryable<T> source, string propertyName, bool descending, bool anotherLevel)
    {
        var param = Expression.Parameter(typeof(T), "p");
        var property = Expression.PropertyOrField(param, propertyName);
        var sort = Expression.Lambda(property, param);

        var call = Expression.Call(
            typeof(Queryable),
            (!anotherLevel ? "OrderBy" : "ThenBy") + (descending ? "Descending" : string.Empty),
            new[] { typeof(T), property.Type },
            source.Expression,
            Expression.Quote(sort));

        return (IOrderedQueryable<T>)source.Provider.CreateQuery<T>(call);
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string propertyName)
    {
        return OrderingHelper(source, propertyName, false, false);
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string propertyName, bool descending)
    {
        return OrderingHelper(source, propertyName, descending, false);
    }

    public static IOrderedQueryable<T> ThenBy<T>(this IOrderedQueryable<T> source, string propertyName)
    {
        return OrderingHelper(source, propertyName, false, true);
    }

    public static IOrderedQueryable<T> ThenBy<T>(this IOrderedQueryable<T> source, string propertyName, bool descending)
    {
        return OrderingHelper(source, propertyName, descending, true);
    }
}

Thanks for pointing it out. I edited the code so it supports the `ThenBy` method that `IOrderedQueryable` provides. — Ziad, May 20 '15 at 14:56
Great solution. It really helped me when doing a web api with search method. — Lithium, Oct 16 '15 at 09:06

Enrico Campidoglio · Answer 2 · 2013-04-15T11:42:17.450

4

You could try to do this using the (somewhat old) Dynamic LINQ library:

var data = entity.User_Details
    .Take(count)
    .OrderBy(sortcriteria)
    .Skip(tblsize)
    .ToList();

Alternatively, you can still sort the sequence using your original query by moving the objects into memory first, since the LINQ to Entities provider can't translate calls to the Reflection API into SQL:

var data = entity.User_Details
    .Take(count)
    .Skip(tblsize)
    .AsEnumerable()
    .OrderBy(i => i.GetType().GetProperty(sortcriteria).GetValue(i, null))

edited Apr 15 '13 at 11:42

answered Apr 15 '13 at 11:22

Enrico Campidoglio

56,676
12
126
154

Do you think GetValue(i, null) gives a property name? The error says "LINQ to Entities does not recognize " so do you think yours will be turn to SQL ? – Davut Gürbüz Apr 15 '13 at 11:31
@DavutGürbüz Calling `AsEnumerable()` (or any of the methods `ToList()`, `ToArray()` and so on) forces the LINQ query to be evaluated against the database, which will cause the resulting sequence to be moved into memory. From there you can easily order the objects by the value of the property with the specified name. – Enrico Campidoglio Apr 15 '13 at 11:39
Ohh. I didn't see it. Sure but enumeration iterate all of the data so it won't be a real scenario for many cases. – Davut Gürbüz Apr 15 '13 at 11:42
The filtering will still happen in the database so only the matching objects are moved into memory before being sorted. That's a fair trade-off, I'd say. – Enrico Campidoglio Apr 15 '13 at 11:47
For dynamic query It is OK. I prefer LinqKit for where conditions. If the criteria does not depend the iteration item. Why won't we firstly generate the criteria fist and use it in real expression. "i.GetType().GetProperty(sortcriteria)" the part always return same thing. I do not understand GetValue what can it be ; 3 ,5 I'm asking will it return property name ! – Davut Gürbüz Apr 15 '13 at 11:48
The objects will be sorted by the value of the property named `sortcriteria`. – Enrico Campidoglio Apr 15 '13 at 11:55
The generated query won't be the same.. this is gonna sort them in memory after pulling the data as soon as it is iterated.. – curiousBoy Aug 25 '19 at 07:20

score 1 · Answer 3 · edited May 23 '17 at 12:34

1

You will probably need to use Expression Trees to construct the Linq statement OrderBy(x => x.SomeProperty).

Create an OrderBy Expression for LINQ/Lambda

Create LINQ to entities OrderBy expression on the fly

edited May 23 '17 at 12:34

Community

1
1

answered Apr 15 '13 at 11:36

Dustin Kingen

20,677
7
52
92

Unable to sort with property name in LINQ OrderBy

3 Answers3

Linked