在 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
和
Ability，包含 Id、PersonId 和 Value

因此，Person 类有一个 ICollection，称为 AllAbility.

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 更改为 List 或 List 但收到错误消息，指出 No mapping existscode>，尝试使用Select 来创建新对象，也尝试使用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...

如果有人能解释我如何实现这个简单的事情，我会很高兴.
非常感谢.

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

推荐答案

我对 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 兼容的表达式并且需要 存储库中的 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屋！