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

Question

I'm pretty new to Linq and EF and I'm stuck regarding how I could just link two lists in Linq to Entities.

I'm using Database First and I have two tables :

Person, with columns Id
and
Ability, with columns Id, PersonId and Value

Thus, Person class has a ICollection<Ability>, called AllAbilities.

In the ViewModel of some View, I get back a list of int, representing textboxes values entered by the user for Ability.Value, called AbilitiesInput. My need is simple, in the Controller I have to call a query which would do the following:

GetAll(person =>
    for(i = 0; i < AbilitiesCount; i++) { person.AllAbilities[i] > AbilitiesInput[i] }
)

Where GetAll method looks like that in my generic repo :

public virtual async Task<List<TEntity>> GetAll(
        Expression<Func<TEntity, bool>> wherePredicate = null
{ ... }

To resume, I just need a boolean which could check if every AllAbilities[i] is upper to AbilitiesInput[i], but nothing I tried has worked.
I tried to change AbilitiesInput to List<KeyValuePair> or List<Tuple> but got an error saying that No mapping exists, tried to use a Select to create a new object, also tried to use IndexOf or FindIndex to get index without foreach...

If anyone could explain me how I can achieve this simple thing I would be so, so glad.
Thanks a lot.

Answer 1

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.

Answer 2

Not sure if I got it right, but the code below may help get correct solution. Using (x,i) will enumerate through the collection and get an index so you can compare the two collections.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        List<TextBox> AbilitiesInput = null;
        public Form1()
        {
            InitializeComponent();

            AbilitiesInput = new List<TextBox>() { textBox1, textBox2, textBox3, textBox4 };

            Person person = new Person();
            List<Ability> results = person.AllAbilities.Where((x, i) => x.Value > int.Parse(AbilitiesInput[i].Text)).ToList();
        }
    }
    public class Person
    {
        public int Id { get; set; }
        public List<Ability> AllAbilities { get; set; }

        public Person()
        {
            AllAbilities = new List<Ability>();
        }

    }
    public class Ability
    {
        public int Id { get; set;}
        public int PersonId { get; set; }
        public int Value { get; set; }
    }
}