c# - LINQ to Entities does not recognize the method 'Method name' method

Question

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?

Answer 1

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