using same delegate in LINQ Where(x => f(x) > y) and OrderBy(f) with SQL compilation

Question

I'm attempting to write a relatively simple but general pagination function for EFCore, and am having trouble finding the right function type to make sure that my sort-key selector is translated to SQL for both the Where and OrderBy clauses.

    public List<TItem> GetPage<TItem>(IQueryable<TItem> items, TFunc keyExtractor, int? itemsAfter = default)
    {
        if (itemsAfter != default)
        {
            items = items.Where(item => keyExtractor(item) > itemsAfter);
        }

        var materialized = items.OrderBy(keyExtractor).Take(pageSize).ToList();

        // ... stuff to trim page size ...
    }

• when TFunc is Expression<Func<TItem, int>>, it's properly handled in OrderBy but the Where clause must be changed to keyExtractor.Compile().Invoke(item) > itemsAfter and is not translated, giving me "Microsoft.EntityFrameworkCore.Query: Warning: The LINQ expression 'where (Convert(__Compile_1.Invoke([t]), Nullable`1) > __itemsAfter_2)' could not be translated and will be evaluated locally."
• when TFunc is Func<TItem, int>, it is properly handled by the Where clause but the OrderBy uses IEnumerable.OrderBy rather than IQueryable.OrderBy. This means that the order & take are done locally - which is fine for the top-level object itself, but child properties do not take the Take into account and pull an enormous amount of data.

Is there a better way to convert one of these to the other, such that the query will be translated? Or is there a third type I can accept that could be easily made into these two types? My keyExtractor parameters are all relatively simple, e.g. item => item.id. Due to reasons outside of my control, I'm currently stuck with EFCore 2.1

Answer 1

For Expression<Func<TItem, int>> you need to build greater than expression yourself. Something along this lines:

public List<TItem> GetPage<TItem>(IQueryable<TItem> items, Expression<Func<TItem, int>> keyExtractor, int? itemsAfter = default)
{
    if (itemsAfter != default)
    {
        var greaterThan = Expression.GreaterThan(keyExtractor.Body, Expression.Constant(itemsAfter));
        var filter = Expression.Lambda<Func<TItem, bool>>(greaterThan, keyExtractor.Parameters);
        items = items.Where(filter);
    }
     var materialized = items.OrderBy(keyExtractor).Take(pageSize).ToList();
     // ... stuff to trim page size ...

}