如何在LINQ中实现动态“where”子句？

I want to have a dynamic where condition.

我希望有一个动态的条件。

In the following example:

在以下示例中:

var opportunites =  from opp in oppDC.Opportunities
                    join org in oppDC.Organizations 
                        on opp.OrganizationID equals org.OrgnizationID
                    where opp.Title.StartsWith(title)
                    select new
                    {
                        opp.OpportunityID,
                        opp.Title,
                        opp.PostedBy,
                        opp.Address1,
                        opp.CreatedDate,
                        org.OrganizationName
                    };

Some times I have Title and sometimes I don't. And also I want to add date in where clause dynamically.

有时候我有头衔,有时候我没有头衔。而且我想动态地在where子句中添加日期。

For example, like this SQL:

例如,像这样的SQL:

string whereClause;
string SQL = whereClause == string.Empty ? 
     "Select * from someTable" : "Select * from someTable" + whereclause

8 个解决方案

#1

You can rewrite it like this:

你可以像这样重写它:

 var opportunites =  from opp in oppDC.Opportunities
                            join org in oppDC.Organizations on opp.OrganizationID equals org.OrgnizationID
                            select new
                            {
                                opp.OpportunityID,
                                opp.Title,
                                opp.PostedBy,
                                opp.Address1,
                                opp.CreatedDate,
                                org.OrganizationName
                            };

if(condition)
{
   opportunites  = opportunites.Where(opp => opp.Title.StartsWith(title));
}

EDIT: To answer your question in the comments, yes, you can keep appending to the original Queryable. Remember, this is all lazily executed, so at this point all it's doing it building up the IQueryable so you can keep chaining them together as needed:

编辑:要在评论中回答您的问题,是的,您可以继续附加到原始的可查询。记住,这都是懒惰的执行,所以在这一点上,所有它都在构建IQueryable,这样你就可以根据需要将它们链接在一起:

if(!String.IsNullOrEmpty(title))
{
   opportunites  = opportunites.Where(.....);
}

if(!String.IsNullOrEmpty(name))
{
   opportunites  = opportunites.Where(.....);
}

#2

You can dynamically add a where clause to your IQueryable expression like this:

您可以动态地将where子句添加到IQueryable表达式,如下所示:

var finalQuery = opportunities.Where( x => x.Title == title );

and for the date similarly.

和类似的日期。

However, you will have to wait to create your anonymous type until after you've finished dynamically added your where clauses if your anonymous type doesn't contain the fields you want to query for in your where clause.

但是,如果匿名类型不包含要在where子句中查询的字段,则必须等到创建匿名类型,直到完成动态添加where子句为止。

So you might have something that looks like this:

所以你可能会看到这样的东西:

var opportunities =  from opp in oppDC.Opportunities
                    join org in oppDC.Organizations on 
                    opp.OrganizationID equals org.OrgnizationID
                    select opp                            

if(!String.IsNullOrEmpty(title))
{
   opportunities = opportunities.Where(opp => opp.Title == title);
}

//do the same thing for the date

opportunities = from opp in opportunities
                select new
                        {
                            opp.OpportunityID,
                            opp.Title,
                            opp.PostedBy,
                            opp.Address1,
                            opp.CreatedDate,
                            org.OrganizationName
                        };

#3

The WHERE clause could be done something like

WHERE子句可以像这样完成

//...
where string.IsNullOrEmpty(title) ? true : opp.Title.StartsWith(title)
//...

Dynamically returning records I don't think is possible in LINQ since it needs to be able to create a consistent AnonymousType (in the background)

动态返回记录我认为在LINQ中是不可能的,因为它需要能够创建一致的AnonymousType(在后台)

#4

Because queries are composable, you can just build the query in steps.

由于查询是可组合的,因此您可以按步骤构建查询。

var query = table.Selec(row => row.Foo);

if (someCondition)
{
    query = query.Where(item => anotherCondition(item));
}

#5

The following questions and answers address this quite well:

以下问题和答案很好地解决了这个问题:

Dynamic where clause in LINQ - with column names available at runtime
Is there a pattern using Linq to dynamically create a filter?

LINQ中的动态where子句 - 在运行时可用列名称是否存在使用Linq动态创建过滤器的模式?

#6

If you know in advance all possible where queries like in the SQL example you have given you can write the query like this

如果您事先知道所有可能的情况,那么您给出的SQL示例中的查询可以像这样编写查询

from item in Items
where param == null ? true : ni.Prop == param
select item;

if you don't know all possible where clauses in advance you can add where dymically for example like this:

如果你事先不知道所有可能的where子句,你可以添加dymically,例如:

query = query.Where(item => item.ID != param);

#7

I was searching for creating a dynamic where clause in LINQ and came across a very beautifull solution on the web which uses ExpressionBuilder in C#.

我正在寻找在LINQ中创建一个动态的where子句,并在Web上遇到了一个非常漂亮的解决方案,该解决方案在C#中使用ExpressionBuilder。

I am posting it here since none of the above solution uses this approach. It helped me. Hope it helps you too http://www.codeproject.com/Tips/582450/Build-Where-Clause-Dynamically-in-Linq

我在这里发布它,因为上述解决方案都没有使用这种方法。它帮助了我。希望它也能帮助你http://www.codeproject.com/Tips/582450/Build-Where-Clause-Dynamically-in-Linq

#8

Use this:

bool DontUseTitles = true; // (Or set to false...    
var opportunites =  from opp in oppDC.Opportunities
                    join org in oppDC.Organizations 
                        on opp.OrganizationID equals org.OrgnizationID
                    where (DontUseTitles | opp.Title.StartsWith(title))
                    select new
                    {
                        opp.OpportunityID,
                        opp.Title,
                        opp.PostedBy,
                        opp.Address1,
                        opp.CreatedDate,
                        org.OrganizationName
                    };

Why it works? If DontUseTitles is true, it selects all because "(DontUseTitles | opp.Title.StartsWith(title))" evaluates to true. Otherwise, it uses the second condition and just returns a subset.

它为什么有效?如果DontUseTitles为true,则选择all,因为“(DontUseTitles | opp.Title.StartsWith(title))”的计算结果为true。否则,它使用第二个条件并返回一个子集。

Why does everyone always make things more complex than they need to be? :-)

为什么每个人总是把事情变得比他们需要的更复杂? :-)

#1