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

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

Do you have automatic client side evaluation disabled? Cause the Func<TItem, int> case seems very suspitious to actually handle Where on client side. — Guru Stron
– Guru Stron, Commented Oct 21, 2021 at 17:11
Show in question samples of the usage. Does keyExtractor accept just one field? — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Oct 21, 2021 at 17:25
@GuruStron unfortunately it's a large inherited codebase without testing - we haven't been able to get it in shape to disable client-side evaluation yet. @SvyatoslavDanyliv I did include a sample what gets passed to keyExtractor - item => item.id. The extractor takes one TItem and returns an int. — matt
– matt, Commented Oct 22, 2021 at 22:26

Guru Stron · Accepted Answer · 2021-10-21 17:12:04Z

0

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 ...

}

edited Oct 21, 2021 at 17:12

answered Oct 21, 2021 at 17:06

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related