C# LINQ to SQL Where 子句可选条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/632434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ to SQL Where Clause Optional Criteria
提问by RSolberg
I am working with a LINQ to SQL query and have run into an issue where I have 4 optional fields to filter the data result on. By optional, I mean has the choice to enter a value or not. Specifically, a few text boxes that could have a value or have an empty string and a few drop down lists that could have had a value selected or maybe not...
我正在使用 LINQ to SQL 查询，但遇到了一个问题，我有 4 个可选字段来过滤数据结果。通过可选，我的意思是可以选择是否输入一个值。具体来说，一些文本框可能有一个值或一个空字符串，还有一些下拉列表可能有一个值或可能没有选择......
For example:
例如：
using (TagsModelDataContext db = new TagsModelDataContext())
{
var query = from tags in db.TagsHeaders
where tags.CST.Equals(this.SelectedCust.CustCode.ToUpper())
&& Utility.GetDate(DateTime.Parse(this.txtOrderDateFrom.Text)) <= tags.ORDDTE
&& Utility.GetDate(DateTime.Parse(this.txtOrderDateTo.Text)) >= tags.ORDDTE
select tags;
this.Results = query.ToADOTable(rec => new object[] { query });
}
Now I need to add the following fields/filters, but only if they are supplied by the user.
现在我需要添加以下字段/过滤器，但前提是它们由用户提供。
- Product Number - Comes from another table that can be joined to TagsHeaders.
- PO Number - a field within the TagsHeaders table.
- Order Number - Similar to PO #, just different column.
- Product Status - If the user selected this from a drop down, need to apply selected value here.
- 产品编号 - 来自可以连接到 TagsHeaders 的另一个表。
- 采购订单编号 - TagsHeaders 表中的一个字段。
- 订单号 - 类似于 PO #，只是不同的列。
- 产品状态 - 如果用户从下拉列表中选择了此项，则需要在此处应用所选值。
The query I already have is working great, but to complete the function, need to be able to add these 4 other items in the where clause, just don't know how!
我已经有的查询工作得很好，但要完成该功能，需要能够在 where 子句中添加这 4 个其他项目，只是不知道如何！
采纳答案by andleer
You can code your original query:
您可以对原始查询进行编码：
var query = from tags in db.TagsHeaders
where tags.CST.Equals(this.SelectedCust.CustCode.ToUpper())
&& Utility.GetDate(DateTime.Parse(this.txtOrderDateFrom.Text)) <= tags.ORDDTE
&& Utility.GetDate(DateTime.Parse(this.txtOrderDateTo.Text)) >= tags.ORDDTE
select tags;
And then based on a condition, add additional where constraints.
然后根据条件，添加额外的 where 约束。
if(condition)
query = query.Where(i => i.PONumber == "ABC");
I am not sure how to code this with the query syntax but id does work with a lambda. Also works with query syntax for the initial query and a lambda for the secondary filter.
我不确定如何使用查询语法对此进行编码，但 id 确实适用于 lambda。也适用于初始查询的查询语法和二级过滤器的 lambda。
You can also include an extension method (below) that I coded up a while back to include conditional where statements. (Doesn't work well with the query syntax):
您还可以包含我不久前编写的扩展方法（如下）以包含条件 where 语句。（不适用于查询语法）：
var query = db.TagsHeaders
.Where(tags => tags.CST.Equals(this.SelectedCust.CustCode.ToUpper()))
.Where(tags => Utility.GetDate(DateTime.Parse(this.txtOrderDateFrom.Text)) <= tags.ORDDTE)
.Where(tags => Utility.GetDate(DateTime.Parse(this.txtOrderDateTo.Text)) >= tags.ORDDTE)
.WhereIf(condition1, tags => tags.PONumber == "ABC")
.WhereIf(condition2, tags => tags.XYZ > 123);
The extension method:
扩展方法：
public static IQueryable<TSource> WhereIf<TSource>(
this IQueryable<TSource> source, bool condition,
Expression<Func<TSource, bool>> predicate)
{
if (condition)
return source.Where(predicate);
else
return source;
}
Here is the same extension method for IEnumerables:
这是 IEnumerables 的相同扩展方法：
public static IEnumerable<TSource> WhereIf<TSource>(
this IEnumerable<TSource> source, bool condition,
Func<TSource, bool> predicate)
{
if (condition)
return source.Where(predicate);
else
return source;
}
回答by CodeRedick
Just need to use a conditional checking for the parameter's existence. For instance:
只需要使用条件检查参数的存在。例如：
where (string.IsNullOrEmpty(ProductNumber) || ProductNumber == tags.productNumber)
That way if the product number isn't entered that expression will return true in all cases, but if it is entered it will only return true when matching.
这样，如果未输入产品编号，则该表达式在所有情况下都将返回 true，但如果输入，则仅在匹配时返回 true。
回答by Gregory A Beamer
You have the ability to OR with ||.
您可以使用 || 进行 OR。
Check out this thread, as it might give you some nice pointers: C# LINQ equivalent of a somewhat complex SQL query
看看这个线程，因为它可能会给你一些很好的指针： C# LINQ 相当于一个有点复杂的 SQL 查询
