How to Apply Orderby on an Iqueryable Using a String Column Name Within a Generic Extension Method

How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?

We did something similar (not 100% the same, but similar) in a LINQ to SQL project. Here's the code:

public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, params object[] values) {
    var type = typeof(T);
    var property = type.GetProperty(ordering);
    var parameter = Expression.Parameter(type, "p");
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);
    var orderByExp = Expression.Lambda(propertyAccess, parameter);
    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), "OrderBy", new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExp));
    return source.Provider.CreateQuery<T>(resultExp);
}

We didn't actually use a generic, we had a known class, but it should work on a generic (I've put the generic placeholder where it should be).

Edit: For descending order, pass in OrderByDescending instead of "OrderBy":

MethodCallExpression resultExp = Expression.Call(typeof(Queryable), "OrderByDescending", new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExp));

Multisorting IQueryable using a string column name within a generic extension method?

Expressions like this

obj => obj.GetType().GetProperty(sort.columnName).GetValue(obj, null)

might be applicable in LINQ To Objects context, but definitely not in LINQ to Entities.

In general you cannot use reflection (and many other methods) inside the LINQ to Entities query expression because they cannot be translated to SQL (or query provider does not recognize and handle them, hence NotSupportedException).

Instead of reflection, you should build the expression using System.Linq.Expressions.

For instance, like this:

public static IQueryable<T> SortByParams<T>(this IQueryable<T> source, List<SortBy> sorting)
{
    var queryExpr = source.Expression;
    string methodAsc = "OrderBy";
    string methodDesc = "OrderByDescending";
    foreach (var item in sorting)
    {
        var selectorParam = Expression.Parameter(typeof(T), "e");
        var selector = Expression.Lambda(Expression.PropertyOrField(selectorParam, item.columnName), selectorParam);
        var method = string.Equals(item.sortDir, "desc", StringComparison.OrdinalIgnoreCase) ? methodDesc : methodAsc;
        queryExpr = Expression.Call(typeof(Queryable), method,
            new Type[] { selectorParam.Type, selector.Body.Type },
            queryExpr, Expression.Quote(selector));
        methodAsc = "ThenBy";
        methodDesc = "ThenByDescending";
    }
    return source.Provider.CreateQuery<T>(queryExpr);
}

LINQ method, which Dynamically ordering data by column name I send

Try to create a QueryableExtensions with the the following code:

//required using System.Linq;
//required using System.Linq.Expressions;
public static class QueryableExtensions
{
    public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string columnName, bool isAscending = true)
    {
        if (String.IsNullOrEmpty(columnName))
        {
            return source;
        }

        ParameterExpression parameter = Expression.Parameter(source.ElementType, "");

        MemberExpression property = Expression.Property(parameter, columnName);
        LambdaExpression lambda = Expression.Lambda(property, parameter);

        string methodName = isAscending ? "OrderBy" : "OrderByDescending";

        Expression methodCallExpression = Expression.Call(typeof(Queryable), methodName,
                              new Type[] { source.ElementType, property.Type },
                              source.Expression, Expression.Quote(lambda));

        return source.Provider.CreateQuery<T>(methodCallExpression);
    }

Then, in the LINQ statement, we could use the above method to sort the records:

    public IActionResult CategoryIndex()
    {
        //CategoryName Descending
        var result1 = _context.Categories.OrderBy("CategoryName", false).Select(c=>c.CategoryName).ToArray();
        //CategoryID Descending
        var result2 = _context.Categories.OrderBy("CategoryID", false).Select(c => c.CategoryID).ToArray();
        return View();
    }

The screenshot as below:

OrderBy Expression Tree in Net Core Linq for Extension Method

Check the sample code for the solution:

void Main()
{
    var queryableRecords = Product.FetchQueryableProducts();

    Expression expression = queryableRecords.OrderBy("Name");

    var func = Expression.Lambda<Func<IQueryable<Product>>>(expression)
                         .Compile();

    func().Dump();
}

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; }

    public static IQueryable<Product> FetchQueryableProducts()
    {
        List<Product> productList = new List<Product>()
        {
          new Product {Id=1, Name = "A"},
          new Product {Id=1, Name = "B"},
          new Product {Id=1, Name = "A"},
          new Product {Id=2, Name = "C"},
          new Product {Id=2, Name = "B"},
          new Product {Id=2, Name = "C"},
        };

        return productList.AsQueryable();
    }
}

public static class ExpressionTreesExtesion
{

    public static Expression OrderBy(this IQueryable queryable, string propertyName)
    {
        var propInfo = queryable.ElementType.GetProperty(propertyName);

        var collectionType = queryable.ElementType;

        var parameterExpression = Expression.Parameter(collectionType, "g");
        var propertyAccess = Expression.MakeMemberAccess(parameterExpression, propInfo);
        var orderLambda = Expression.Lambda(propertyAccess, parameterExpression);
        return Expression.Call(typeof(Queryable),
                               "OrderBy",
                               new Type[] { collectionType, propInfo.PropertyType },
                               queryable.Expression,
                               Expression.Quote(orderLambda));

    }


}

Result

How it Works:

Created an expression using extension method on the Queryable type, which internally calls OrderBy method of the Queryable type, expecting IQueryable to be the Input, along with the field name and thus runs the ordering function and Ordered collection is the final Output

Option 2:

This may fit your use case better, here instead of calling OrderBy method, we are creating the Expression<Func<T,string>> as an extension method to the IEnumerable<T>, which can then be compiled and supplied to the OrderBy Call, as shown in the example and is thus much more intuitive and simple solution:

Creating Expression:

public static class ExpressionTreesExtesion
{
    public static Expression<Func<T,string>> OrderByExpression<T>(this IEnumerable<T> enumerable, string propertyName)
    {
        var propInfo = typeof(T).GetProperty(propertyName);

        var collectionType = typeof(T);

        var parameterExpression = Expression.Parameter(collectionType, "x");
        var propertyAccess = Expression.MakeMemberAccess(parameterExpression, propInfo);
        var orderExpression = Expression.Lambda<Func<T,string>>(propertyAccess, parameterExpression);
        return orderExpression;
    }
}

How to Call:

var ProductExpression = records.OrderByExpression("Name");

var result  = records.OrderBy(ProductExpression.Compile());

ProductExpression.Compile() above will compile into x => x.Name, where column name is supplied at the run-time

Please note in case the ordering field can be other types beside string data type, then make that also generic and supply it while calling extension method, only condition being property being called shall have the same type as supplied value, else it will be a run-time exception, while creating Expression

Edit 1, how to make the OrderType field also generic

public static Expression<Func<T, TField>> OrderByFunc<T,TField>(this IEnumerable<T> enumerable, string propertyName)
    {
        var propInfo = typeof(T).GetProperty(propertyName);

        var collectionType = typeof(T);

        var parameterExpression = Expression.Parameter(collectionType, "x");
        var propertyAccess = Expression.MakeMemberAccess(parameterExpression, propInfo);
        var orderExpression = Expression.Lambda<Func<T, TField>>(propertyAccess, parameterExpression);
        return orderExpression;
    }

How to call:

Now both the types need to be explicitly supplied, earlier were using generic type inference from IEnumerable<T>:

// For Integer Id field

var ProductExpression = records.OrderByFunc<Product,int>("Id");

// For string name field

var ProductExpression = records.OrderByFunc<Product,string>("Name");

C# Expression to sort generic query by key field

This isn't too difficult, but you need to invoke the OrderBy with reflection as you don't know the type of the key field ahead of time. So given the code you already show, you would do something like this:

// Build up the property expression to pass into the OrderBy method
var parameterExp = Expression.Parameter(typeof(T), "x");
var propertyExp = Expression.Property(parameterExp, keyField);
var orderByExp = Expression.Lambda(propertyExp, parameterExp);

// Note here you can output "orderByExp.ToString()" which will give you this:
//  x => x.NameOfProperty

// Now call the OrderBy via reflection, you can decide here if you want 
// "OrderBy" or "OrderByDescending"
var orderByMethodGeneric = typeof(Queryable)
    .GetMethods()
    .Single(mi => mi.Name == "OrderBy" && mi.GetParameters().Length == 2);

var orderByMethod = orderByMethodGeneric.MakeGenericMethod(typeof(T), propertyExp.Type);

// Get the result
var sortedQueryable = (IQueryable<T>)orderByMethod
    .Invoke(null, new object[] { queryable, orderByExp });

How to use a string to create a EF order by expression?

Creating an expression is not hard, but the tricky part is how to bind it to the corresponding OrderBy(Descending) / ThenBy(Descendig) methods when you don't know the type of the property (hence the type of the selector expression result).

Here is all that encapsulated in a custom extension method:

public static partial class QueryableExtensions
{
    public static IOrderedQueryable<T> OrderByMember<T>(this IQueryable<T> source, string memberPath)
    {
        return source.OrderByMemberUsing(memberPath, "OrderBy");
    }
    public static IOrderedQueryable<T> OrderByMemberDescending<T>(this IQueryable<T> source, string memberPath)
    {
        return source.OrderByMemberUsing(memberPath, "OrderByDescending");
    }
    public static IOrderedQueryable<T> ThenByMember<T>(this IOrderedQueryable<T> source, string memberPath)
    {
        return source.OrderByMemberUsing(memberPath, "ThenBy");
    }
    public static IOrderedQueryable<T> ThenByMemberDescending<T>(this IOrderedQueryable<T> source, string memberPath)
    {
        return source.OrderByMemberUsing(memberPath, "ThenByDescending");
    }
    private static IOrderedQueryable<T> OrderByMemberUsing<T>(this IQueryable<T> source, string memberPath, string method)
    {
        var parameter = Expression.Parameter(typeof(T), "item");
        var member = memberPath.Split('.')
            .Aggregate((Expression)parameter, Expression.PropertyOrField);
        var keySelector = Expression.Lambda(member, parameter);
        var methodCall = Expression.Call(
            typeof(Queryable), method, new[] { parameter.Type, member.Type },
            source.Expression, Expression.Quote(keySelector));
        return (IOrderedQueryable<T>)source.Provider.CreateQuery(methodCall);
    }

How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?

We did something similar (not 100% the same, but similar) in a LINQ to SQL project. Here's the code:

public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, params object[] values) {
    var type = typeof(T);
    var property = type.GetProperty(ordering);
    var parameter = Expression.Parameter(type, "p");
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);
    var orderByExp = Expression.Lambda(propertyAccess, parameter);
    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), "OrderBy", new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExp));
    return source.Provider.CreateQuery<T>(resultExp);
}

We didn't actually use a generic, we had a known class, but it should work on a generic (I've put the generic placeholder where it should be).

Edit: For descending order, pass in OrderByDescending instead of "OrderBy":

MethodCallExpression resultExp = Expression.Call(typeof(Queryable), "OrderByDescending", new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExp));

---

Related Topics