Linq expression with Join, and conditional OrderBy / OrderByDescending

Question

I am building a Linq expression, I don't have the ability to use Dynamic Linq in this project. Here is my initial statement.

                   var orderListQuery = context.Orders.Where(oExpr)
                        .Join(context.Members,
                            o => o.MemberId,
                            m => m.Id,
                            (o, m) => new {Order = o, Member = m})
                        .Where(oM => oM.Member.CustomerId == custId);

The next part is where I would like to conditionally add a descending expression:

                orderListQuery = descending
                    ? orderListQuery.OrderByDescending(sortProperty)
                    : orderListQuery.OrderBy(sortProperty);

the error which I already saw a post on is that I need to explicitly list the <TSource, TKey>, but due to the complexity of the syntax from the join, I have no idea how to explicitly list these. I tried <Order, string>, which does not work.

Anything helps, thanks.

Answer 1

Thanks for that incredible detailed answer. It has been a few months now, and more about MVC and the existing application code is becoming clear. What had happened in my case was that we had an extension method defined that allowed OrderBy and OrderByDescending to accept strings passed in by our sorting module from the front end. e.g:

    public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string propertyName)
    {
        return GetExpression(source, propertyName);
    }

This would then funnel it into a GetExpression function that returned an IOrderedQueryable<T>

I had not included the reference to this, and due to my inexperience with both the framework and the application I was cranking my head for essentially no reason.

Answer 2

Because you forget to write what goal you are trying to reach (no specification), it is a bit unclear what your problem is, and how this can be solved. I assume that your problem is not the join (although it can be simplified), but the problem is how users of your method can provide a sort property.

How to specify and use the Sort Property

The problem is, that the caller of your function decides about the sort property. Of course you only want to make useful and very reusable functions, so you don't want to limit the user of your function, he can specify any sort property he wants, as long as it is something the result of your join can be sorted on.

The result of your join contains data from a sequence of Orders and a Sequence of members. It is obvious that your user can only request to sort the result of your join on (a combination of) values from Orders and or Members.

The key selector in Queryable.OrderBy has the format Expression<Func<TSource, TKey>> keySelector. In this format the TSource is the result of your join.

As the user can only sort on values from Orders and/or Members, his key selector should be a function that selects a key from Orders and/or Members

public MyResult MyQuery<TKey>(Expression<Func<Order, Member, TKey> keySelector,
    SortOrder sortOrder)
{
    ... // TODO: your query?
}

Examples of usage would be:

// order by Order.Id:
var result = MyQuery( (order, member) => order.Id, SortOrder.Ascending);    
// order by Member.Name:
var result = MyQuery( (order, member) => member.Name, SortOrder.Descending); 
// order by something complicated
var result = MyQuery( (order, member) => new{Id = order.Id, Name = member.Name},
             SortOrder.Ascending);

Now that the interface is specified, let's define some helper classes and fill your function

class MyResult
{
    public Order Order {get; set;}
    public Member Member {get; set;}
}

class SortableMyResult<TKey>
{
    public TKey SortKey {get; set;}
    public MyResult MyResult {get; set;}
}

MyResult MyQuery<TKey>(
    Expression<Func<Order, Member, TKey> keySelector, SortOrder sortOrder)
{
    var query = context.Orders.Where(oExpr)
        .Join(context.Members,
            order => order.MemberId,
            member => member.Id,
            (order, member) => new SortableMyResult<TKey>()
            {
                SortKey = KeySelector(order, member),
                MyResult = new MyResult()
                {
                    Order = order,
                    Member = member,
                 }
            ))
            .Where(....);

What I've done, is that your original result is put in a MyResult object. I have also calculated the value of SortKey using the Expression that the caller provided, and put the SortKey and the MyResult in a SortableMyResult.

Did you notice that the return value of your Expression is a TKey, which is also the type of property SortKey?

Because I've defined the helper classes, my compiler can check that I did not make any errors.

Do the sorting:

IQueryable<SortableMyResult<TKey>> sortedMyResult;
switch (sortOrder)
{
    case sortOrder.Ascending:
       sortedMyResult = query.OrderBy(item => item.SortKey);
       break;
    case sortOrder.Descending:
       sortedMyResult = query.OrderByDescending(item => item.SortKey);
       break;
    default: // do not order
       sortedMyResult = query;
       break;
}

Finally, extract and return MyResult:

return sortedMyResult.Select(sortedMyResult => sortedMyResult.MyResult);

If you want to make your function even more generic, you can let the caller give the opportunity to provide an IComparer (and if he doesn't, use the default comparer for TKey):

MyResult MyQuery<TKey>(
    Expression<Func<Order, Member, TKey> keySelector,
    SortOrder sortOrder,
    IComparer<TKey> comparer = EqualityComparer<TKey>.Default)
{
    ...
}