实体框架 LINQ 复杂查询 - 组合多个谓词 [英] Entity Framework LINQ complex query - combine multiple predicates
问题描述
我正在尝试创建一个复杂的 Linq 查询，如下所示:获取拥有与给定过滤器参数匹配的员工的所有组织.
I'm trying to create a complex Linq query that goes like this: Get all organisations which have employees that match the given filter parameters.
示例过滤器:
- 名字:约翰
- 姓名:史密斯
我的第一次尝试:
if (!filter.Name.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.Name.ToLower().Contains(filter.Name.ToLower())));
}
if (!filter.Firstname.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
}
if (!filter.ContactNumber.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)));
}
这种方法的问题在于，如果组织 A 中有人姓约翰(例如约翰约翰逊)，而组织 A 中有人姓史密斯(Jenny Smith).组织 (A) 包含那两个人被遣返了.它不应该.我只想要拥有名字为约翰"的人的组织.和姓氏史密斯"
The problem with this approach is that when there is someone with the firstname John (ex. John Johnson) in organisation A, and someone with the last name Smith (Jenny Smith) in organisation A. The organisation (A) that contains those two persons gets returned. Which it shouldn't. I only want organisations that have people with the firstname "john" AND the lastname "Smith"
我找到了一种有效但肮脏且不可扩展的方法:
I found a working, but dirty and non-scalable approach:
if (!filter.ContactNumber.IsNullOrWhiteSpace())
{
if (!filter.Name.IsNullOrWhiteSpace() && !filter.Firstname.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)
&& p.Name.ToLower().Contains(filter.Name.ToLower())
&& p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
}
else if (!filter.Name.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)
&& p.Name.ToLower().Contains(filter.Name.ToLower())));
} else if (!filter.Firstname.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber)
&& p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
} else
{
query = query.Where(o => o.Persons.Any(p => p.ContactNumber.contains(filter.ContactNumber));
}
} else if(!filter.Name.IsNullOrWhiteSpace())
{
if (!filter.Firstname.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower()) && p.Name.ToLower().Contains(filter.Name.ToLower())));
} else
{
query = query.Where(o => o.Persons.Any(p => p.Name.ToLower().Contains(filter.Name.ToLower())));
}
} else if (!filter.Firstname.IsNullOrWhiteSpace())
{
query = query.Where(o => o.Persons.Any(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower())));
}
如您所见，这不是一个非常干净的解决方案.
As you can see this not a very clean solution.
我也尝试在表达式中使用方法调用，但 Linq 无法翻译.有什么方法可以制作谓词表达式列表并将它们合并为一个?或者还有其他更好的解决方案吗?
I also tried using method calls inside the expression but Linq couldnt translate that. Is there any way I can can make a list of predicate expressions an merge them to one? Or is there a another, better solution?
顺便说一下，因为我需要一个分页列表，所以它必须在一个查询中.
By the way, since I need a paginated list, it all has to be in one query.
供您参考，这就是我的过滤器类的样子.它只是从我的前端发送的一个类，其中包含所有需要过滤的字段.
For your information, this is what my filter class looks like. It is just a class send from my front-end with all the fields that need to be filtered.
public class ContactFilter
{
public string Name{ get; set; }
public string Firstname{ get; set; }
public string ContactNummer { get; set; }
}
推荐答案
有什么方法可以制作一个谓词表达式列表并将它们合并为一个?
Is there any way I can can make a list of predicate expressions an merge them to one?
是的，这就是我在这种情况下更喜欢的方法.
Yes, and that's the approach I'd prefer in this situation.
首先构建列表:
var filterExpressions = new List<Expression<Func<Person, bool>>();
if (!filter.Name.IsNullOrWhiteSpace())
{
filterExpressions.Add(p => p.Name.ToLower().Contains(filter.Name.ToLower()));
}
if (!filter.Firstname.IsNullOrWhiteSpace())
{
filterExpressions.Add(p => p.Firstname.ToLower().Contains(filter.Firstname.ToLower()));
}
if (!filter.ContactNumber.IsNullOrWhiteSpace())
{
filterExpressions.Add(p => p.ContactNumber.contains(filter.ContactNumber));
}
从那里，你可以使用这个实现来和一起任意表达式.如果没有要应用的过滤器，您还需要决定要执行的操作(我将使用默认设置为无过滤器，但您可能还想执行其他操作).
From there, you can use this implementation to And arbitrary Expressions together. You'll also need to decide what to do if there are no filters to apply (I'll use a default of no filter, but you may want to do something else).
var predicate = filterExpressions.DefaultIfEmpty(p => true)
.Aggregate((a, b) => a.And(b));
现在我们进入最困难的部分.我们有一个表达式来表示您想要传递给 Any 调用的 lambda.如果我们可以这样做就好了:
Now we get to the hard part. We have an expression that represents the lambda you want to pass to a call to Any. It would be nice if we could just do:
query = query.Where(o => o.Persons.Any(predicate));
但遗憾的是，这行不通，因为 o.Persons 的类型不是 IQueryable.所以现在我们有一个表达式，我们想要嵌入另一个表达式，其中内部表达式需要是一个 lambda.幸运的是，这不是太复杂:
But sadly, this won't work because the type of o.Persons isn't an IQueryable. So now we have an expression that we want to embed in another expression in which the inner expression needs to be a lambda. Fortunately this isn't too complicated:
public static Expression<Func<TSource, TResult>> EmbedLambda
<TSource, TResult, TFunc1, TFunc2>(
this Expression<Func<TFunc1, TFunc2>> lambda,
Expression<Func<TSource, Func<TFunc1, TFunc2>, TResult>> expression)
{
var body = expression.Body.Replace(
expression.Parameters[1],
lambda);
return Expression.Lambda<Func<TSource, TResult>>(
body, expression.Parameters[0]);
}
(使用上述链接中的辅助类)
(Using a helper class from the above link)
现在我们只需要调用该方法.请注意，由于这一切的运作方式，我们将无法完全依赖类型推断，因此需要明确指定某些类型.
Now we just need to call the method. Note we won't be able to rely entirely on type inference due to the way this all works out, so some types need to be specified explicitly.
query = query.Where(predicate.EmbedLambda((UnknownType o, Func<Person, bool> p) => o.Persons.Any(p)));
这篇关于实体框架 LINQ 复杂查询 - 组合多个谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
