在linq中的两个列表之间建立链接到实体where子句 [英] Establish a link between two lists in linq to entities where clause

问题描述

我现在很新到 Linq 和 EF ，我被困在我如何连接两个列表在Linq到实体。

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 ，其中列 Id

和

能力，列 Id ， PersonId 和 code>

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

因此， Person class有一个 ICollection< Ability> / code>，称为 AllAbilities 。

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

在某些视图的ViewModel中，我收到了一个列表的int，表示用户为 Ability.Value 输入的文本框值，称为 AbilitiesInput 。
我的需要很简单，在控制器我必须调用一个查询，它将执行以下操作：

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] }
)

其中 GetAll 方法看起来像在我的通用回购中：

Where GetAll method looks like that in my generic repo :

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

要恢复，我只需要一个布尔值，可以检查每个 AllAbilities [i] 是否高于 AbilitiesInput [i] ，但我没有尝试过的工作。

我试图将 AbilitiesInput 更改为列表< KeyValuePair> ; 或列表<元组< Tuple> ，但收到错误，表示没有映射存在要使用选择来创建一个新对象，也尝试使用 IndexOf 或 FindIndex 获取索引而不用foreach ...

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

如果有人可以解释我如何实现这个简单的事情，我会这样，很高兴。 br>
非常感谢。

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

推荐答案




c $ c> Linq 和 EF

你没有运气，因为这个简单的事情是相对的在LINQ to Objects中很容易，但很难（几乎不可能）在LINQ to Entities中。

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.

为了解决这个问题，您需要手动构建LINQ to Entities兼容的 Expression 。

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

首先，您需要一些帮助来构建表达式谓词。 PredicateBuilder 是受欢迎的选择，但它不会产生EF兼容表达式，并且需要<$ c存储库内的$ c> LinqKit 和 AsExpandable 所以我使用以下类似的帮助器，但产生最终兼容的表达式：

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);
}

最后，构建过滤器并将其传递给 GetAll 方法：

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.

这篇关于在linq中的两个列表之间建立链接到实体where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！