在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屋!