在linq中的两个列表之间建立到实体where子句的链接

Question

我还很陌生，Linq而且EF对于如何将Linq中的两个列表链接到实体，我深感困惑。

我正在使用数据库优先，并且我有两个表：

Person与柱Id

和

Ability与列Id，PersonId并Value

因此，Personclass具有一个ICollection<Ability>，称为AllAbilities。

在某些View的ViewModel中，我返回了一个int列表，该列表表示用户为所输入的文本框值Ability.Value（称为）AbilitiesInput。我的需求很简单，在Controller中，我必须调用一个查询，该查询将执行以下操作：

GetAll(person =>

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

)

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

public virtual async Task<List<TEntity>> GetAll(

        Expression<Func<TEntity, bool>> wherePredicate = null

{ ... }

要恢复，我只需要一个布尔值就可以检查每个值是否都AllAbilities[i]高于AbilitiesInput[i]，但是我尝试过的任何方法都没有起作用。

我尝试更改AbilitiesInput为List<KeyValuePair>或，List<Tuple>但出现错误说No mapping exists，尝试使用A Select创建一个新对象，也尝试使用IndexOf或FindIndex获取没有foreach的索引...

如果有人能解释我如何实现这一简单目标，我将非常高兴。

非常感谢。

Answer 1

我是很新，Linq和EF您很不走运，因为“简单的事情”在LINQ to Objects中相对容易，但是在LINQ to Entities中却相当困难（几乎不可能）。为了以某种方式解决它，您需要手动构建LINQ to Entitiescompatible Expression。首先，您需要一些帮助程序来构建表达谓词。PredicateBuilder是一个流行的选择，但它不会产生EF兼容表情和要求LinqKit，并AsExpandable在仓库里。因此，我使用以下类似的辅助方法，但会产生最终的兼容表达式：public static class PredicateUtils{&nbsp; &nbsp; sealed class Predicate<T>&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public static readonly Expression<Func<T, bool>> True = item => true;&nbsp; &nbsp; &nbsp; &nbsp; public static readonly Expression<Func<T, bool>> False = item => false;&nbsp; &nbsp; }&nbsp; &nbsp; public static Expression<Func<T, bool>> Null<T>() { return null; }&nbsp; &nbsp; public static Expression<Func<T, bool>> True<T>() { return Predicate<T>.True; }&nbsp; &nbsp; public static Expression<Func<T, bool>> False<T>() { return Predicate<T>.False; }&nbsp; &nbsp; public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; if (Equals(left, right)) return left;&nbsp; &nbsp; &nbsp; &nbsp; if (left == null || Equals(left, True<T>())) return right;&nbsp; &nbsp; &nbsp; &nbsp; if (right == null || Equals(right, True<T>())) return left;&nbsp; &nbsp; &nbsp; &nbsp; if (Equals(left, False<T>()) || Equals(right, False<T>())) return False<T>();&nbsp; &nbsp; &nbsp; &nbsp; var body = Expression.AndAlso(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));&nbsp; &nbsp; &nbsp; &nbsp; return Expression.Lambda<Func<T, bool>>(body, left.Parameters);&nbsp; &nbsp; }&nbsp; &nbsp; public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; if (Equals(left, right)) return left;&nbsp; &nbsp; &nbsp; &nbsp; if (left == null || Equals(left, False<T>())) return right;&nbsp; &nbsp; &nbsp; &nbsp; if (right == null || Equals(right, False<T>())) return left;&nbsp; &nbsp; &nbsp; &nbsp; if (Equals(left, True<T>()) || Equals(right, True<T>())) return True<T>();&nbsp; &nbsp; &nbsp; &nbsp; var body = Expression.OrElse(left.Body, right.Body.Replace(right.Parameters[0], left.Parameters[0]));&nbsp; &nbsp; &nbsp; &nbsp; return Expression.Lambda<Func<T, bool>>(body, left.Parameters);&nbsp; &nbsp; }&nbsp; &nbsp; static Expression Replace(this Expression expression, Expression source, Expression target)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; return new ExpressionReplacer { Source = source, Target = target }.Visit(expression);&nbsp; &nbsp; }&nbsp; &nbsp; class ExpressionReplacer : ExpressionVisitor&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public Expression Source;&nbsp; &nbsp; &nbsp; &nbsp; public Expression Target;&nbsp; &nbsp; &nbsp; &nbsp; public override Expression Visit(Expression node)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return node == Source ? Target : base.Visit(node);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}其次，在控制器中为这样的单个条件定义一个辅助方法static Expression<Func<Person, bool>> AbilityFilter(int index, int value){&nbsp; &nbsp; return p => p.AllAbilities.OrderBy(a => a.Id).Skip(index).Take(1).Any(a => a.Value > value);}最后，构建过滤器并将其传递给GetAll方法：var filter = PredicateUtils.Null<Person>();for (int i = 0; i < AbilitiesInput.Count; i++)&nbsp; &nbsp; filter = filter.And(AbilityFilter(i, AbilitiesInput[i]));GetAll(filter);使用的技术绝对不是新手，但我认为没有简单的方法可以解决该特定问题。

Answer 2

不知道我是否正确，但是下面的代码可能有助于获得正确的解决方案。使用（x，i）将枚举整个集合并获得一个索引，以便您可以比较两个集合。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{&nbsp; &nbsp; public partial class Form1 : Form&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; List<TextBox> AbilitiesInput = null;&nbsp; &nbsp; &nbsp; &nbsp; public Form1()&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; InitializeComponent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AbilitiesInput = new List<TextBox>() { textBox1, textBox2, textBox3, textBox4 };&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Person person = new Person();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<Ability> results = person.AllAbilities.Where((x, i) => x.Value > int.Parse(AbilitiesInput[i].Text)).ToList();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; public class Person&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public int Id { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public List<Ability> AllAbilities { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public Person()&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AllAbilities = new List<Ability>();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; public class Ability&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public int Id { get; set;}&nbsp; &nbsp; &nbsp; &nbsp; public int PersonId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public int Value { get; set; }&nbsp; &nbsp; }}

Answer 3

非常感谢您的答复，但是为了在查询的where子句中使用它，我需要一个布尔值而不是List<Ability> results。我如何将其更改为布尔值？我尝试用替换Where，All但All似乎无法处理(x, i)。