Linq-to-SQL:组合(OR'ing)多个“包含"过滤器? [英] Linq-to-SQL: Combining (OR'ing) multiple "Contains" filters?

问题描述

我在找出执行此操作的最佳方法时遇到了一些麻烦，如果您能提供帮助，我将不胜感激.

基本上，我正在设置一个过滤器，允许用户查看与用户名的任意过滤器"相关联的审计项目的历史记录.

数据源是 SQL Server 数据库，因此我采用 IQueryable源"(来自 db 上下文对象的直接表引用，或者可能是由附加查询产生的 IQueryable)，应用 WHERE 过滤器，然后返回生成的 IQueryable 对象......但我对如何使用这种方法执行 OR 有点难.

我已经考虑走表达式的路线，因为我知道如何对这些进行 OR，但我一直无法弄清楚如何通过包含"类型评估来做到这一点，所以我目前正在使用UNION，但我担心这可能会对性能产生负面影响，我想知道如果以任意顺序添加其他过滤器(除了此处显示的用户名过滤)，它是否可能无法完全满足我的需求.

这是我的示例代码:

public override IQueryableApplyFilter(IQueryable 源){//取允许的值...列表<字符串>searchStrings = new List();//<片段>(这只是填充我的搜索字符串列表)IQueryableoReturn = null;//单步执行每次迭代，并执行LIKE %value%"查询string[] searchArray = searchStrings.ToArray();for (int i = 0; i < searchArray.Length; i++){字符串值 = searchArray[i];如果(我== 0)//第一步，直接执行WHEREoReturn = source.Where(x => x.Username.Contains(value));别的//对于额外的步骤，在 WHERE 上执行 UNIONoReturn = oReturn.Union(source.Where(x => x.Username.Contains(value)));}返回 oReturn ??来源;}

这感觉像是错误的做事方式，但它似乎确实有效，所以我的问题是首先，有没有更好的方法来做到这一点?另外，有没有办法对表达式进行包含"或喜欢"?

(编辑以更正我的代码:在回滚到工作状态以发布它时，我显然没有回滚到足够远:))

==============================================

预计到达时间:根据给出的解决方案，这是我的新代码(以防有人对此感兴趣):

public override IQueryableApplyFilter(IQueryable 源){列表<字符串>searchStrings = new List(AllowedValues);//<片段>构建搜索值集合string[] searchArray = searchStrings.ToArray();表达式<Func<X，bool>表达式 = PredicateBuilder.False();for (int i = 0; i < searchArray.Length; i++){字符串值 = searchArray[i];expression = expression.Or(x => x.Username.Contains(value));}返回 source.Where(表达式);}

(我注意到一个警告:按照 PredicateBuilder 的示例，搜索字符串的空集合将返回 false (false || value1 || ... )，而在我的原始版本中，我假设一个空列表应该只是合并到未过滤的来源.随着我的考虑，新版本似乎更符合我的需求，所以我采用了)

==============================================

解决方案

您可以使用 PredicateBuilder 来自 LINQkit 以动态构造您的查询.

I'm having some trouble figuring out the best way to do this, and I would appreciate any help.

Basically, I'm setting up a filter that allows the user to look at a history of audit items associated with an arbitrary "filter" of usernames.

The datasource is a SQL Server data base, so I'm taking the IQueryable "source" (either a direct table reference from the db context object, or perhaps an IQueryable that's resulted from additional queries), applying the WHERE filter, and then returning the resultant IQueryable object....but I'm a little stumped as to how to perform OR using this approach.

I've considered going the route of Expressions because I know how to OR those, but I haven't been able to figure out quite how to do that with a "Contains" type evaluation, so I'm currently using a UNION, but I'm afraid this might have negative impact on performance, and I'm wondering if it may not give me exactly what I need if other filters (in addition to user name filtering shown here) are added in an arbirary order.

Here is my sample code:

public override IQueryable<X> ApplyFilter<X>(IQueryable<X> source)
{
    // Take allowed values...
    List<string> searchStrings = new List<string>();

    // <SNIP> (This just populates my list of search strings)

    IQueryable<X> oReturn = null;

    // Step through each iteration, and perform a 'LIKE %value%' query
    string[] searchArray = searchStrings.ToArray();
    for (int i = 0; i < searchArray.Length; i++)
    {
        string value = searchArray[i];
        if (i == 0)
            // For first step, perform direct WHERE
            oReturn = source.Where(x => x.Username.Contains(value));
        else
            // For additional steps, perform UNION on WHERE
            oReturn = oReturn.Union(source.Where(x => x.Username.Contains(value)));
    }
    return oReturn ?? source;
}

This feels like the wrong way to do things, but it does seem to work, so my question is first, is there a better way to do this? Also, is there a way to do a 'Contains' or 'Like' with Expressions?

(Editted to correct my code: In rolling back to working state in order to post it, I apparently didn't roll back quite far enough :) )

=============================================

ETA: Per the solution given, here is my new code (in case anyone reading this is interested):

public override IQueryable<X> ApplyFilter<X>(IQueryable<X> source)
{
    List<string> searchStrings = new List<string>(AllowedValues);

    // <SNIP> build collection of search values 

    string[] searchArray = searchStrings.ToArray();

    Expression<Func<X, bool>> expression = PredicateBuilder.False<X>();

    for (int i = 0; i < searchArray.Length; i++)
    {
        string value = searchArray[i];
        expression = expression.Or(x => x.Username.Contains(value));
    }
    return source.Where(expression);
}

(One caveat I noticed: Following the PredicateBuilder's example, an empty collection of search strings will return false (false || value1 || ... ), whereas in my original version, I was assuming an empty list should just coallesce to the unfiltered source. As I thought about it more, the new version seems to make more sense for my needs, so I adopted that)

=============================================

解决方案

You can use the PredicateBuilder from the LINQkit to dynamically construct your query.

这篇关于Linq-to-SQL:组合(OR'ing)多个“包含"过滤器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！