CODEWITHSUNDEEP
c#.netentity-frameworkentity-framework-coreef-core-2.0
MrBliz
MrBliz

Reputation: 5918

Ordering By Child Entity EF Core

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?

Upvotes: 1

Views: 2250

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205819

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)

Upvotes: 3

Related Questions