Establish a link between two lists in linq to entities where clause

I’m pretty new to Linq and EF

You are out of luck, because “this simple thing” is relatively easy in LINQ to Objects, but quite hard (almost impossible) in LINQ to Entities.

In order to somehow solve it, you need to build manually LINQ to Entities compatible Expression.

First, you’ll need some helpers for building expression predicates. PredicateBuilder is a popular choice, but it does not produce EF compatible expressions and requires LinqKit and AsExpandable inside the repository. So I use the below helpers which are similar, but produce final compatible expressions:

public static class PredicateUtils
{
    sealed class Predicate<T>
    {
        public static readonly Expression<Func<T, bool>> True = item => true;
        public static readonly Expression<Func<T, bool>> False = item => false;
    }
    public static Expression<Func<T, bool>> Null<T>() { return null; }
    public static Expression<Func<T, bool>> True<T>() { return Predicate<T>.True; }
    public static Expression<Func<T, bool>> False<T>() { return Predicate<T>.False; }
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
    {
        if (Equals(left, right)) return left;
        if (left == null || Equals(left, True<T>())) return right;
        if (right == null || Equals(right, True<T>())) return left;
        if (Equals(left, False<T>()) || Equals(right, False<T>())) return False<T>();
        var body = Expression.AndAlso(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));
        return Expression.Lambda<Func<T, bool>>(body, left.Parameters);
    }
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
    {
        if (Equals(left, right)) return left;
        if (left == null || Equals(left, False<T>())) return right;
        if (right == null || Equals(right, False<T>())) return left;
        if (Equals(left, True<T>()) || Equals(right, True<T>())) return True<T>();
        var body = Expression.OrElse(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));
        return Expression.Lambda<Func<T, bool>>(body, left.Parameters);
    }

    static Expression Replace(this Expression expression, Expression source, Expression target)
    {
        return new ExpressionReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ExpressionReplacer : ExpressionVisitor
    {
        public Expression Source;
        public Expression Target;
        public override Expression Visit(Expression node)
        {
            return node == Source ? Target : base.Visit(node);
        }
    }
}

Second, define a helper method in your controller for a single criteria like this

static Expression<Func<Person, bool>> AbilityFilter(int index, int value)
{
    return p => p.AllAbilities.OrderBy(a => a.Id).Skip(index).Take(1).Any(a => a.Value > value);
}

Finally, build the filter and pass it to GetAll method:

var filter = PredicateUtils.Null<Person>();
for (int i = 0; i < AbilitiesInput.Count; i++)
    filter = filter.And(AbilityFilter(i, AbilitiesInput[i]));
GetAll(filter);

The techniques used are definitely not for a novice, but I see no simple way to solve that particular problem.