-1

I'm trying to make and order by with dynamic parameter, this is the code:

var res = from c in db.CUSTOMERS select c;

if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(direction))
      {
       var property = typeof(CUSTOMERS).GetProperty(sortBy);

       if(direction == "asc")
         {
            res = res.OrderBy(x => property.GetValue(x));
         }
      else
         {
           res = res.OrderBy(x => property.GetValue(x));
         } 
}

return res.ToList();

but I get this error :

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

enter image description here

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
hous
  • 2,577
  • 2
  • 27
  • 66
  • maybe [this answer](https://stackoverflow.com/a/1689239/5309228) can help you? – Franz Gleichmann Jan 22 '21 at 16:47
  • @FranzGleichmann, the same error message :\ – hous Jan 22 '21 at 16:55
  • what version of EF are you using? maybe you've got an older version not supporting that feature yet. plus: have you tried the other answers? for example providing a string with the property name instead of a lambda referencing the property? – Franz Gleichmann Jan 22 '21 at 16:59
  • 2
    What LINQ are you using: LINQ to SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x? What database provider are you using? LINQ to databases uses `Expression` trees to translate C# code into SQL - there is no SQL for `GetValue` so it can't be translated. – NetMage Jan 22 '21 at 17:01
  • The library mentioned [here](https://stackoverflow.com/a/6942654/13524307) might work for you. – Quacke Jan 22 '21 at 17:29
  • @hous what are you trying to do? What is `property.GetValue(x)` supposed to mean? If you want to order by different properties, use different `OrderBy` calls. – Panagiotis Kanavos Jan 22 '21 at 17:31
  • This is the first time and the first week I use c# and asp.net, I don't know what are you asking about. I'll see with manager. thank you – hous Jan 22 '21 at 17:42
  • @hous this isn't about C# or ASP.NET, it's about SQL, databases, Entity Framework and LINQ. ASP.NET is a web framework, it doesn't deal with database queries. Data access is the job of ADO.NET and ORMs like Entity Framework. LINQ is a convenient language that eventually gets translated to SQL for execution on the server – Panagiotis Kanavos Jan 22 '21 at 17:46

1 Answers1

7

If you want to do sorting on the Server Side, you have to correct expression.

 var query = from c in db.CUSTOMERS select c;
    
 if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(direction))
 {
    query = query.ApplyOrderBy(new [] {Tuple.Create(sortBy, direction != "asc")});
 }

 return query.ToList();

And implementation:

public static class QueryableExtensions
{
    public static IQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, IEnumerable<Tuple<string, bool>> order)
    {
        var expr = ApplyOrderBy(typeof(T), query.Expression, order);
        return query.Provider.CreateQuery<T>(expr);
    }

    static Expression MakePropPath(Expression objExpression, string path)
    {
        return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
    }

    static Expression ApplyOrderBy(Type entityType, Expression queryExpr, IEnumerable<Tuple<string, bool>> order)
    {
        var param = Expression.Parameter(entityType, "e");
        var isFirst = true;
        foreach (var tuple in order)
        {
            var lambda = Expression.Lambda(MakePropPath(param, tuple.Item1), param);
            var methodName =
                isFirst ? tuple.Item2 ? nameof(Queryable.OrderByDescending) : nameof(Queryable.OrderBy)
                : tuple.Item2 ? nameof(Queryable.ThenByDescending) : nameof(Queryable.ThenBy);

            queryExpr = Expression.Call(typeof(Queryable), methodName, new[] { entityType, lambda.Body.Type }, queryExpr, lambda);
            isFirst = false;
        }

        return queryExpr;
    }
}
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32