Ordering By Child Entity EF Core

Question

I have an extension method to sort my entities, and in some cases i need to sort on a property of a child collection

 public static IQueryable<Payment> SetSort(this IQueryable<Payment> payments, string sortProperty, string direction)


            if (string.Equals(sortProperty, PaymentSortProperties.TimeStamp, StringComparison.CurrentCultureIgnoreCase))
            {
                return sortDirection == SortDirection.Asc ? payments.OrderBy(x => x.History.OrderBy(h=> h.Timestamp)) : payments.OrderByDescending(x => x.History.OrderByDescending(h => h.Timestamp));
            }

}

Called from

 public async Task<IPagedList<Payment>> Get(int pageNumber, int pageSize, string sortProperty, string direction, string searchString)
    {
            var result = _data.Payments
                .Include(x => x.History)
                .ThenInclude(x=>x.Status)
                .Filter(searchString)
            .SetSort(sortProperty, direction);

            return await result.ToPagedListAsync(pageNumber, pageSize);         
    }

i get the error System.ArgumentException: At least one object must implement IComparable.

I've seen examples that suggest i do it like this

    if (string.Equals(sortProperty, PaymentSortProperties.TimeStamp, StringComparison.CurrentCultureIgnoreCase))
                {
                    return sortDirection == SortDirection.Asc ?
 payments.OrderBy(x => x.History.Min(h=> h.Timestamp)) 
: payments.OrderByDescending(x => x.History.Max(h => h.Timestamp));
                }

but that triggers a SELECT n + 1 query (ie causing all entities in dB to be loaded into memory, and then sorted).

What is the right way of going about it?

There are better ways to add a sort expression dynamically, Just one of many examples here: https://stackoverflow.com/a/36303246/861716. But that doesn't solve your direct problem, which is (probably) that you're trying to enter a dotted name. You don't show which property you try to sort by, so we have to guess. — Gert Arnold, Oct 24 '17 at 14:15
The property i'm trying to sort by is Timestamp on the History collection, which is in the first code block. — MrBliz, Oct 24 '17 at 14:16
Ah yes. How would image that ordering by the result of `x => x.History.OrderBy(h=> h.Timestamp)` should look like? — Gert Arnold, Oct 24 '17 at 14:19
That the Payments are ordered by the earliest timestamp in the History collection (when using OrderBy) — MrBliz, Oct 24 '17 at 14:22
You're trying to order collections is what I mean. Look closely at what your code does. — Gert Arnold, Oct 24 '17 at 14:25
The example you've been shown is closer to the solution, what is the problem when using this code ? What problem does a `SELECT n+1` query causes ? — Antoine Pelletier, Oct 24 '17 at 14:30
Select N+ 1 causes all entities in dB to be loaded into memory, and then sorted. — MrBliz, Oct 24 '17 at 14:32
@GertArnold thank you, so what is the correct way to order Payments by the earliest timestamp in the History collection? — MrBliz, Oct 24 '17 at 14:37

score 3 · Answer 1 · answered Oct 24 '17 at 19:24

Well, the Min / Max is the correct way in general. Unfortunately as you have noticed, EF Core (as of v2.0) still doesn't translate well (GroupBy) aggregate methods and falls back to client evaluation for processing them.

As a workaround, I could suggest the alternative pattern OrderBy[Descending] + Select + FirstOrDefault which luckily translates to SQL:

return sortDirection == SortDirection.Asc ?
    payments.OrderBy(p => p.History.OrderBy(h => h.Timestamp).Select(h => h.Timestamp).FirstOrDefault()) :
    payments.OrderByDescending(x => x.History.OrderByDescending(h => h.Timestamp).Select(h => h.Timestamp).FirstOrDefault());

Here is the same encapsulated in a custom extension method:

public static class QueryableExtensions
{
    public static IOrderedQueryable<TOuter> OrderBy<TOuter, TInner, TKey>(
        this IQueryable<TOuter> source,
        Expression<Func<TOuter, IEnumerable<TInner>>> innerCollectionSelector,
        Expression<Func<TInner, TKey>> keySelector,
        bool ascending)
    {
        return source.OrderBy(innerCollectionSelector, keySelector, ascending, false);
    }

    public static IOrderedQueryable<TOuter> ThenBy<TOuter, TInner, TKey>(
        this IOrderedQueryable<TOuter> source,
        Expression<Func<TOuter, IEnumerable<TInner>>> innerCollectionSelector,
        Expression<Func<TInner, TKey>> keySelector,
        bool ascending)
    {
        return source.OrderBy(innerCollectionSelector, keySelector, ascending, true);
    }

    static IOrderedQueryable<TOuter> OrderBy<TOuter, TInner, TKey>(
        this IQueryable<TOuter> source,
        Expression<Func<TOuter, IEnumerable<TInner>>> innerCollectionSelector,
        Expression<Func<TInner, TKey>> innerKeySelector,
        bool ascending, bool concat)
    {
        var parameter = innerCollectionSelector.Parameters[0];
        var innerOrderByMethod = ascending ? "OrderBy" : "OrderByDescending";
        var innerOrderByCall = Expression.Call(
            typeof(Enumerable), innerOrderByMethod, new[] { typeof(TInner), typeof(TKey) },
            innerCollectionSelector.Body, innerKeySelector);
        var innerSelectCall = Expression.Call(
            typeof(Enumerable), "Select", new[] { typeof(TInner), typeof(TKey) },
            innerOrderByCall, innerKeySelector);
        var innerFirstOrDefaultCall = Expression.Call(
            typeof(Enumerable), "FirstOrDefault", new[] { typeof(TKey) },
            innerSelectCall);
        var outerKeySelector = Expression.Lambda(innerFirstOrDefaultCall, parameter);
        var outerOrderByMethod = concat ? ascending ? "ThenBy" : "ThenByDescending" : innerOrderByMethod;
        var outerOrderByCall = Expression.Call(
            typeof(Queryable), outerOrderByMethod, new[] { typeof(TOuter), typeof(TKey) },
            source.Expression, Expression.Quote(outerKeySelector));
        return (IOrderedQueryable<TOuter>)source.Provider.CreateQuery(outerOrderByCall);
    }
}

so you can use simply:

return payments.OrderBy(p => p.History, h => h.Timestamp, sortDirection == SortDirection.Asc)

Thanks, I’ll give that a try tomorrow – MrBliz Oct 24 '17 at 23:03 — MrBliz, Oct 24 '17 at 23:03

Ordering By Child Entity EF Core

1 Answers1