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.

You need to construct an expression that points to your property. but it would be better if you just described what you are trying to accomplish with this code because there is probably an easier way to do this — johnny 5, Jul 19 '17 at 18:49
A member Id is different from the customer ID, and customer ID is not on the order. However Member ID and Customer ID are part of the Members table. (legacy code problems) I am trying to get all Orders by Customer ID and order them based on sortProperty being sent as a request from action in the view. (e.g. sort by date, subtotal etc). sortProperty is always a string in this case. — Michael Sefranek, Jul 19 '17 at 18:56
So the `sortProperty` is a `string` containing the property name? — Ivan Stoev, Jul 19 '17 at 18:57
@MichaelSefranek So why don't you just set up foreign keys and then just grab the data normally — johnny 5, Jul 19 '17 at 18:58
Are you looking for something like this [How to use a string to create a EF order by expression?](https://stackoverflow.com/questions/39908403/how-to-use-a-string-to-create-a-ef-order-by-expression) — Ivan Stoev, Jul 19 '17 at 19:02
I think you are missing the point of the ORM, you can grab and filter things out using the context. What are you trying to do `context.Members.Where(x => x.CustomerId == custId).Select(x => x.Orders)` there is almost never any reason to do a sloppy join — johnny 5, Jul 19 '17 at 19:02
yes - setProperty is a string containing property name. -johnny, i was going to try and do it without altering tables for now. That is how I would do it if it were a much smaller app/database. — Michael Sefranek, Jul 19 '17 at 19:03
Take the class from the link, then use `.OrderByMember("Order." + sortProperty)`. Similar for descending. — Ivan Stoev, Jul 19 '17 at 19:11
Nevermind, I solved it with my senior dev. Member ID was in a seperate database from Cust ID and I had access to it elsewhere on the actual page. I still wanted to know the code in theory, but thanks for the attempts. — Michael Sefranek, Jul 19 '17 at 19:50

score 1 · Answer 1 · answered Jul 20 '17 at 07:58

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)
{
    ...
}

score 0 · Answer 2 · answered Nov 15 '17 at 21:28

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.

Linq expression with Join, and conditional OrderBy / OrderByDescending

2 Answers2