有多重未知条件的Where子句?

I am currently developing a Staff management system for my company. The fields may vary and change time to time, so I have an interface for each field like this:

我目前正在为我的公司开发一个员工管理系统。字段可能会随时间的变化而变化，所以我为每个字段设置了一个接口:

public interface IStaffInfoField
{

    // ...

    IQueryable<Staff> Filter(IQueryable<Staff> pList, string pAdditionalData);

    // ...

}

For each field, I implement the Filter method, for example with Name:

对于每个字段，我都实现了Filter方法，例如名称:

class NameStaffInfoField : BaseStaffInfoField
{

    // ...

    public override IQueryable<Staff> Filter(IQueryable<Staff> pList, string pAdditionalData)
    {
        return pList.Where(q => q.Name.Contains(pAdditionalData));
    }

    // ...

}

Now the users want to search with multiple conditions, it's easy, I just iterate through the list and call Filter. However, they also want a OR condition (say, staff which have name A, OR name B, AND Department Name C, OR Age 30). Note: Users are end-users and they input the search queries through comboboxes and textboxes.

现在用户想要搜索多个条件，这很简单，我只是遍历列表并调用过滤器。然而，他们也想要一个或条件(比如，有名字a、名字B、部门名C或30岁的员工)。注意:用户是终端用户，他们通过组合框和文本框输入搜索查询。

Can I modify my pattern or the lambda expression somehow to achieve that? Because throughout the progress, I don't save the original list to Union it for OR condition. I think it will be slow if I save the expression and Union it for OR condition.

我可以修改我的模式或者lambda表达式来实现它吗?因为在整个过程中，我并没有保存原始列表，以使其符合或条件。我认为如果我保存表达式并将其合并到OR条件下，将会很慢。

The only solution I can think of now is to add a method to interface that require raw SQL WHERE statement. But my entire program hasn't used pure SQL query yet, is it bad to use it now?

我现在能想到的唯一解决方案是向需要原始SQL WHERE语句的接口添加一个方法。但是我的整个程序还没有使用纯SQL查询，现在使用它不好吗?

3 个解决方案

#1

You can download Albahari's LINQKit. It contains a PredicateBuilder that allows you, among other useful things, to concatenate LINQ expressions with OR in a dynamic way.

你可以下载Albahari的LINQKit。它包含一个谓词构建器，允许您以动态的方式连接LINQ表达式。

var predicate = PredicateBuilder.False<Staff>();
predicate = predicate.Or(s => s.Name.Contains(data));
predicate = predicate.Or(s => s.Age > 30);
return dataContext.Staff.Where(predicate);

You can also download the source code and see how it is implemented.

您还可以下载源代码并查看它是如何实现的。

#2

Since your method returns IQueryable, clients already can use it for arbitrarily complicated queries.

由于您的方法返回IQueryable，客户端已经可以使用它进行任意复杂的查询。

IQueryable<Staff> result = xxx.Filter( .... );

result = result.Where( ... );
if ( ... )
    result = result.Where( s => ( s.Age > 30 || s.Salary < 1 ) && s.Whatever == "something" );

The IQueryable is very flexible. The query tree is evaluated and translated to sql when you start to enumerate results.

IQueryable非常灵活。当您开始枚举结果时，查询树将被计算并转换为sql。

I only wonder why would you need the interface at all?! Since your Filter method expects the IQueryable, this means that client already has the IQueryable! Why would she call your Filter method then if she can already apply arbitrarily complicated query operators on her own?

我只是想知道为什么你需要这个接口?!由于您的筛选方法需要IQueryable，这意味着客户端已经拥有IQueryable!如果她已经可以自己应用任意复杂的查询操作符，她为什么要调用您的Filter方法呢?

Edit:

编辑:

After your additional explanation, if I were you I would create a simple interface to let users create their own query trees containing OR and AND clauses and create a simple function that would translate the user query tree to linq expression tree.

在您的附加说明之后，如果我是您，我将创建一个简单的界面，让用户创建包含或和和子句的查询树，并创建一个简单的函数，将用户查询树转换为linq表达式树。

In other words, do not let end users work at linq query tree level, this is too abstract and also too dangerous to let users touch such low level layer of your code. But abstract trees manually translated to linq trees sound safe and easy.

换句话说，不要让最终用户在linq查询树级别上工作，这太抽象，也太危险了，不能让用户接触到您代码的如此底层。但是，人工翻译到linq树的抽象树听起来安全而简单。

#3

If your users are end users, and they enter criteria through a UI, you may want to look at a UI control that supports IQueryable. Telerik has a large number of pre-baked controls. In most cases end users interact with a grid and they apply filters to the columns. There are several other vendors that do the same thing.

如果您的用户是终端用户，并且他们通过UI输入条件，那么您可能需要查看支持IQueryable的UI控件。Telerik有大量预先准备好的控件。在大多数情况下，终端用户与网格交互，并对列应用过滤器。还有其他几个供应商也在做同样的事情。

A second option, if you want to make your life difficult, you could take the input text that the user supplies, parse it into a expression tree and then map that expression tree to a IQueryable. If you are not familiar with parsers this task will be fairly difficult to implement.

第二个选项，如果您想使您的生活变得困难，您可以获取用户提供的输入文本，将其解析为表达式树，然后将表达式树映射到IQueryable。如果您不熟悉解析器，这个任务将很难实现。

#1