How to create dynamic Orderby statement on child list object in ef core

Question

I am trying to create the orderby dynamically based on the sort direction

public class Item
{   
    public Guid ItemId {get; set;}
    public string ItemName { get; set; }
    public List<ItemProductType> ItemProductTypes {get; set;}
}
public class ItemProductType
{
    public Guid ItemId {get; set;}
    public Guid ProductTypeId {get; set; }
    public Item Item { get; set; }
    public ProductType ProductType { get; set; }
}
public class ProductType
{   
    public Guid ProductTypeId {get; set;}
    public string ProductTypeName { get; set; }
    public List<ItemProductType> ItemProductTypes {get; set;}
}

I need to create a orderby statement on the item table like

_context.Items.Include(a => a.ItemProductTypes)
    .ThenInclude(a => a.ProductType)        
    .OrderBy(a => 
        a.ItemProductTypes.OrderBy(b => b.ProductType.ProductTypeName)
            .Select(c => c.ProductType.ProductTypeName)
            .FirstOrDefault()
    ).ToListAsync();

Above code is working but I need to change orderby ascending or descending based on the Sortdirection from ColumnHeader click.

i tried using OrderBySortDynamic extension method on Queryable to get the orderby or orderbydescending dynamically

public static IQueryable<t> OrderBySortDynamic<t, TKey>(this IQueryable<t> query, Expression<Func<t, TKey>> keySelector, bool sortDescending) 
{ 
    string command = "OrderBy"; 
    if (sortDescending) 
    { 
        command = "OrderByDescending"; 
    } 

    Expression resultExpression = Expression.Call( 
        typeof(Queryable), 
        command, 
        new[] { typeof(t), keySelector.ReturnType },
        query.Expression, 
        Expression.Quote(keySelector));
    return query.Provider.CreateQuery<t>(resultExpression); 
} 

how can i create an extension method for performing orderby dynamically on the child navigationproperty which a.ItemProductTypes.OrderBy(b=>b.ProductType.ProductTypeName). I tried updating the above extension method with Ienumerable but it does not work.

Answer 1

First thing to consider is that custom extension methods cannot be used inside expression tree. e.g. here

.OrderBy(a => 
    a.ItemProductTypes.OrderBy(b => b.ProductType.ProductTypeName)
        .Select(c => c.ProductType.ProductTypeName)
        .FirstOrDefault()
)

you can use custom method in place of the outer OrderBy, but not for the inner - everything after a => is part of the expression tree and must be well known method / property, because the methods/properties inside the expression tree in general are never called, but processed by the query expression translator.

With that being said, let see what we can do with your issue. The outer OrderBy can be replaced with the custom extension method. But the one you provided is overcomplicated and not really dynamic - all it does is to call either OrderBy or OrderByDescending passing the ready provided arguments. So it could be simplified as (basically the same signature as OrderBy with additional bool parameter):

public static IOrderedQueryable<T> OrderBy<T, TKey>(
    this IQueryable<T> source,
    Expression<Func<T, TKey>> keySelector,
    bool descending
) => descending ? source.OrderByDescending(keySelector) : source.OrderBy(keySelector);

This would work if the selector uses single value (either direct or indirect via reference navigation property). In order to handle the inner OrderBy, you'd need another custom method receiving the necessary parts as arguments.

In this expression

a => a.ItemProductTypes
    .OrderBy(b => b.ProductType.ProductTypeName)
    .Select(c => c.ProductType.ProductTypeName)
    .FirstOrDefault()
)

the customizable parts are a.ItemProductTypes for selecting inner enumerable from the outer "object", and b => b.ProductType.ProductTypeName to select the inner key for ordering.

So the second custom method would be like this:

public static IOrderedQueryable<TOuter> OrderBy<TOuter, TInner, TKey>(
    this IQueryable<TOuter> source,
    Expression<Func<TOuter, IEnumerable<TInner>>> innerSelector,
    Expression<Func<TInner, TKey>> innerKeySelector,
    bool descending
)
{
    var innerOrderBy = Expression.Call(
        typeof(Enumerable),
        descending ? nameof(Enumerable.OrderByDescending) : nameof(Enumerable.OrderBy),
        new[] { typeof(TInner), typeof(TKey) },
        innerSelector.Body,
        innerKeySelector
    );

    var innerSelect = Expression.Call(
        typeof(Enumerable),
        nameof(Enumerable.Select),
        new[] { typeof(TInner), typeof(TKey) },
        innerOrderBy,
        innerKeySelector
    );

    var innerFirstOrDefault = Expression.Call(
        typeof(Enumerable),
        nameof(Enumerable.FirstOrDefault),
        new[] { typeof(TKey) },
        innerSelect
    );

    var outerKeySelector = Expression.Lambda<Func<TOuter, TKey>>(
        innerFirstOrDefault,
        innerSelector.Parameters
    );

    return source.OrderBy(outerKeySelector, descending);
}

Here the implementation has to use Expression class methods to compose a key selector to be passed to the previous custom extension method. All you need to know is that these methods are static, the containing class, the method name, generic type arguments and parameters. And that they are called in opposite order of what is provided by the C# extension method syntax sugar, i.e.

OrderBy(...).Select(...).FirstOrDefault()

is actually

FirstOrDefault(Select(OrderBy(...), ...))

Now you can use the second extension method in your query:

var query = _context.Items
    .Include(a => a.ItemProductTypes)
        .ThenInclude(a => a.ProductType)
    .OrderBy(
        a => a.ItemProductTypes,
        b => b.ProductType.ProductTypeName,
        descending: true // pass your criteria here
    );

---

Another possible solution is to write your query for ascending order (like in the example), and then use custom ExpressionVisitor to replace all OrderBy calls with OrderByDescending - something like string.Replace, but with expressions.

Following is a sample implementation of the above:

public static IQueryable<T> SwitchOrderBy<T>(
    this IQueryable<T> source,
    bool descending
)
{
    if (!descending) return source;
    var expression = new OrderBySwitcher().Visit(source.Expression);
    if (source.Expression == expression) return source;
    return source.Provider.CreateQuery<T>(expression);
}

class OrderBySwitcher : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if ((node.Method.DeclaringType == typeof(Enumerable)
            || node.Method.DeclaringType == typeof(Queryable))
            && node.Method.Name == nameof(Enumerable.OrderBy))
        {
            var args = new Expression[node.Arguments.Count];
            for (int i = 0; i < args.Length; i++)
                args[i] = Visit(node.Arguments[i]);
            return Expression.Call(
                node.Method.DeclaringType,
                nameof(Enumerable.OrderByDescending),
                node.Method.GetGenericArguments(),
                args
            );
        }
        return base.VisitMethodCall(node);
    }
}

and sample usage

var query = _context.Items
    .Include(a => a.ItemProductTypes)
        .ThenInclude(a => a.ProductType)
    .OrderBy(a => a.ItemProductTypes
        .OrderBy(b => b.ProductType.ProductTypeName)
        .Select(c => c.ProductType.ProductTypeName)
        .FirstOrDefault()
    )
    // everything down to here is same as in the original query
    .SwitchOrderBy(descending: true); // pass your criteria here