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?

Vargas

may it ascending or descending – Charlatanism

A very similar question https://mcmap.net/q/903090/-how-to-create-lambda-expression-that-returns-object-39-s-property-having-this-property-39-s-name/413032 – Proceeds

"LINQ to Entities does not recognize the method" mean I cannot translate the reflection expression to SQL. – Proceeds

https://mcmap.net/q/740847/-use-reflection-to-get-lambda-expression-from-property-name – Proceeds

Answer 1

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);
    }
}

Rosemarie

Shouldn't it return a IOrderedQueryable? – Peyton

Thanks for pointing it out. I edited the code so it supports the ThenBy method that IOrderedQueryable provides. – Rosemarie

Great solution. It really helped me when doing a web api with search method. – Micmac

Answer 2

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))

Minni

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 ? – Proceeds

@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. – Minni

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. – Proceeds

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. – Minni

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 ! – Proceeds

The objects will be sorted by the value of the property named sortcriteria. – Minni

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.. – Luminescence

Answer 3

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

Kidding