MethodCallExpression orderby

Question

I am trying to build a lambda query using expression trees to make it generic to use throughout an application. I based my initial attempt on the code found here. I altered the code so that it does a Compare to return 100 entities whose LastName is just before the searchText, like so:

TEntity entity = entitySet.FirstOrDefault();
string searchName = entity.GetType().GetProperty("SearchName").ToString();
searchText = "Baker";
int records = 100;

IQueryable<TEntity> queryableData = entitySet.AsQueryable<TEntity>();
var param = Expression.Parameter(typeof(TEntity), searchName);
var body = Expression.LessThan(Expression.Call(
    typeof(string), 
    "Compare", 
    null, 
    Expression.PropertyOrField(param, searchName), 
    Expression.Constant(searchText)), 
    Expression.Constant(0));

var lambda = Expression.Lambda<Func<TEntity, bool>>(body, param);
           
MethodCallExpression whereCallExpression = Expression.Call(
    typeof(Queryable),
    "Where",
    new Type[] { typeof(TEntity) },
    queryableData.Expression,
    lambda);

var data = entitySet.AsQueryable<TEntity>().Provider.CreateQuery<TEntity>(whereCallExpression).Take(records);

The code above works, it returns 100 entities from the database that reside just before my searchText, but they are the wrong entities because they are not in order in the entitySet. So, I need an OrderBy clause in my Expression tree so that I get "Azure", Axel, Avis", etc.

I tried this:

 MethodCallExpression orderByCallExpression = Expression.Call(
    typeof(Queryable),
    "OrderByDescending",
    new Type[] { typeof(TEntity), typeof(TEntity) },
    whereCallExpression,
    Expression.Lambda<Func<TEntity, string>>(param, new ParameterExpression[] { param }));

var data = entitySet.AsQueryable<TEntity>().Provider.CreateQuery<TEntity>(orderByCallExpression).Take(records);

I get the error

"An exception of type 'System.ArgumentException' occurred in System.Core.dll but was not handled in user code

Additional information: Expression of type MyEntity' cannot be used for return type 'System.String'"

StackTrace: at System.Linq.Expressions.Expression.ValidateLambdaArgs(Type delegateType, Expression& body, ReadOnlyCollection`1 parameters)

at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, String name, Boolean tailCall, IEnumerable`1 parameters)

at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, Boolean tailCall, IEnumerable`1 parameters)

So, I looked here and tried this:

Expression<Func<TEntity, string>> sortExp = l => l.SearchName);

MethodCallExpression orderByCallExpression = Expression.Call(
    typeof(Queryable),
    "OrderByDescending",
    new Type[] { typeof(TEntity), typeof(string) },
    whereCallExpression,
    sortExp);

I get the error

"An exception of type 'System.NotSupportedException' occurred in mscorlib.dll but was not handled in user code

Additional information: The specified type member 'SearchName' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."

I think the problem is that the SearchName property is a string that holds the name of the field on the entity that I want to OrderBy. I tried:

l => (string)l.GetType().GetProperty("SearchName").GetValue(SearchName);

but that gives me the error:

"An exception of type 'System.NotSupportedException' occurred in mscorlib.dll but was not handled in user code

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

TEntity is based on EntityObject

SearchName is a property of type string on TEntity that holds the name of a field in the DB.

entitySet is of type ObjectSet<TEntity>

Any suggestions would be appreciated.

Update and Solution I found code here that got me thinking in a different direction. Here is the code that works for ordering by SearchName:

var type = typeof(TEntity);
var property = type.GetProperty(searchName);
var parameter = Expression.Parameter(type, "p");
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
                                                        
MethodCallExpression orderByCallExpression = Expression.Call(
        typeof(Queryable),
        "OrderByDescending",
        new Type[] { typeof(TEntity), typeof(string) },
        whereCallExpression,
        Expression.Quote(orderByExp));                                   

var data = entitySet.AsQueryable<TEntity>().Provider.CreateQuery<TEntity>(orderByCallExpression).Take(records);

Answer 1

Here is the solution that I used for the SearchName not being a field in the table.

if (entity.SearchName == "LastFirstName")
{
    //// Construct Expressions to hold the propert values
    Expression lastNameExp = Expression.PropertyOrField(param, "LastName");
    Expression firstNameExp = Expression.PropertyOrField(param, "FirstName");

    //// Construct a String.Concat method. 
    MethodInfo methodInfo = typeof(string).GetMethod("Concat", new Type[] { typeof(string), typeof(string) });

    //// Combine the LastName + FirstName for the compare
    MethodCallExpression combinedExp = Expression.Call(methodInfo, lastNameExp, firstNameExp);