7

I'm having a similar problem that was asked here: LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression

I'm trying to paginate my source, but in my case, I can't put the result of GetPropertyValue in a variable, because I need x to do that:

public IEnumerable<TModel> Paginate(IQueryable<TModel> source, ref int totalPages, int pageIndex, int pageSize, string sortfield, SortDirection? sortdir)
{
    totalPages = (int)Math.Ceiling(source.Count() / (double)pageSize);

    if (sortdir == SortDirection.Descending)
    {
         return source.OrderByDescending(x => GetPropertyValue(x, sortfield)).Skip(pageIndex * pageSize).Take(pageSize).ToList();
    }
    else
    {
         return source.OrderBy(x => GetPropertyValue(x, sortfield)).Skip(pageIndex * pageSize).Take(pageSize).ToList();
    }
}

private static object GetPropertyValue(object obj, string name)
{
    return obj == null ? null : obj.GetType().GetProperty(name).GetValue(obj, null);
}

What could I do, in this case?

Community
  • 1
  • 1
hasser
  • 1,066
  • 1
  • 13
  • 24
  • What/where exactly is the issue you are getting? It looks as though your getting a not all paths return a value? – Sayse Jul 31 '13 at 17:59
  • I'm getting a "LINQ to Entities does not recognize the method 'Method name' method", more specifically the method "GetPropertyValue". – hasser Jul 31 '13 at 18:09

2 Answers2

12

Lambda Expressions (Those are used within Where, OrderBy etc) cannot contain any C# specific code, they can only contain expression tree, which is translated to SQL. You cannot call any arbitrary methods there, except the ones that are mentioned by EF documentation such as SqlFunctions etc.

In order to do sorting with a field name at runtime, you have to create a lambda expression at runtime and pass it on.

public IEnumerable<TModel> Paginate(IQueryable<TModel> source, ref int totalPages, int pageIndex, int pageSize, string sortfield, SortDirection? sortdir)
{
    totalPages = (int)Math.Ceiling(source.Count() / (double)pageSize);

    if (sortdir == SortDirection.Descending)
    {
         return source.OrderByDescending(sortfield).Skip(pageIndex * pageSize).Take(pageSize).ToList();
    }
    else
    {
         return source.OrderBy(sortfield).Skip(pageIndex * pageSize).Take(pageSize).ToList();
    }
}


public static class QueryableHelper
{
    public static IQueryable<TModel> OrderBy<TModel>(this IQueryable<TModel> q, string name)
    {
        Type entityType = typeof(TModel);
        PropertyInfo p = entityType.GetProperty(name);
        MethodInfo m = typeof(QueryableHelper).GetMethod("OrderByProperty").MakeGenericMethod(entityType, p.PropertyType);
        return(IQueryable<TModel>) m.Invoke(null, new object[] { q, p });
    }

    public static IQueryable<TModel> OrderByDescending<TModel>(this IQueryable<TModel> q, string name)
    {
        Type entityType = typeof(TModel);
        PropertyInfo p = entityType.GetProperty(name);
        MethodInfo m = typeof(QueryableHelper).GetMethod("OrderByPropertyDescending").MakeGenericMethod(entityType, p.PropertyType);
        return (IQueryable<TModel>)m.Invoke(null, new object[] { q, p });
    }

    public static IQueryable<TModel> OrderByPropertyDescending<TModel, TRet>(IQueryable<TModel> q, PropertyInfo p)
    {
        ParameterExpression pe = Expression.Parameter(typeof(TModel));
        Expression se = Expression.Convert(Expression.Property(pe, p), typeof(object));
        return q.OrderByDescending(Expression.Lambda<Func<TModel, TRet>>(se, pe));
    }

    public static IQueryable<TModel> OrderByProperty<TModel, TRet>(IQueryable<TModel> q, PropertyInfo p)
    {
        ParameterExpression pe = Expression.Parameter(typeof(TModel));
        Expression se = Expression.Convert(Expression.Property(pe, p), typeof(object));
        return q.OrderBy(Expression.Lambda<Func<TModel, TRet>>(se, pe));
    }
}

This solution only works on single level of property, but if you want nested levels than it needs more work, perhaps you can look at following SDK which does all of that.

However if you take a look at Entity REST SDK itself, it has many things and all the things that you might need. Disclaimer: I am the Author.

https://entityrestsdk.codeplex.com

Akash Kava
  • 39,066
  • 20
  • 121
  • 167
  • Almost worked. Now I'm getting a "Expression of type 'System.Int64' cannot be used for return type 'System.Object'". I changed `Func>` to `Func>` and it worked, but I don't know the type of the object that will be passed to the function. Could be a `String`, for example. Anyway to workaround this? – hasser Jul 31 '13 at 18:29
  • 2
    Can you try now? I have modified it little. I am leaving home for the day, if it works great, otherwise I will try to post solution, that is little larger, but that will be tomorrow. – Akash Kava Jul 31 '13 at 18:33
  • Great, you've got way too little points for this fabulous answer! – Kamil T Aug 02 '16 at 09:20
  • Seems like it can't handle different sort property types. I also get an error "Expression of type 'System.Int' cannot be used for return type 'System.Object'". – Ian Aug 02 '17 at 10:54
  • 1
    Replacing with this works for me: `Expression se = Expression.Convert(Expression.Property(pe, p), typeof(TRet));` – Ian Aug 02 '17 at 11:02
  • It sure would be nice to know how to make it case insensitive :) – Ian Aug 02 '17 at 11:06
  • Replacing with this works for me: Expression se = Expression.Convert(Expression.Property(pe, p), typeof(TRet)); Also worked for me with a DateTime attribute! – Simon Sondrup Kristensen Sep 25 '17 at 08:59
  • I am using EF 6.2. Replacing typeof(object) with p.PropertyType did the job for me. Great Work man. – Harish Ninge Gowda Sep 26 '19 at 09:17
1

Instead of using reflection, you should dynamically create an Expression<Func<TSource, TOrder>> and pass it to OrderBy.

Take a look here to understand how create a dynamic query.

as-cii
  • 12,819
  • 4
  • 41
  • 43