C# LINQ 中的动态 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/848415/
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
Dynamic WHERE clause in LINQ
提问by Keith Barrows
What is the best way to assemble a dynamic WHERE clause to a LINQ statement?
将动态 WHERE 子句组装到 LINQ 语句的最佳方法是什么？
I have several dozen checkboxes on a form and am passing them back as: Dictionary<string, List<string>> (Dictionary<fieldName,List<values>>) to my LINQ query.
我在表单上有几十个复选框，并将它们作为：Dictionary<string, List<string>> (Dictionary<fieldName,List<values>>) 传递回我的 LINQ 查询。
public IOrderedQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeName, Dictionary<string,List<string>> filterDictionary)
{
var q = from c in db.ProductDetail
where c.ProductGroupName == productGroupName && c.ProductTypeName == productTypeName
// insert dynamic filter here
orderby c.ProductTypeName
select c;
return q;
}
采纳答案by Thomas Stock
(source: scottgu.com)
（来源：scottgu.com）
You need something like this? Use the Linq Dynamic Query Library(download includes examples).
你需要这样的东西吗？使用Linq 动态查询库（下载包括示例）。
Check out ScottGu's blogfor more examples.
查看ScottGu 的博客以获取更多示例。
回答by Linus
You can also use the PredicateBuilder from LinqKit to chain multiple typesafe lambda expressions using Or or And.
您还可以使用 LinqKit 中的 PredicateBuilder 使用 Or 或 And 链接多个类型安全的 lambda 表达式。
回答by Nitin Bourai
A simple Approach can be if your Columns are of Simple Type like String
如果您的列是像字符串这样的简单类型，则可以采用一种简单的方法
public static IEnumerable<MyObject> WhereQuery(IEnumerable<MyObject> source, string columnName, string propertyValue)
{
return source.Where(m => { return m.GetType().GetProperty(columnName).GetValue(m, null).ToString().StartsWith(propertyValue); });
}
回答by Todd DeLand
You could use the Any() extension method. The following seems to work for me.
您可以使用 Any() 扩展方法。以下似乎对我有用。
XStreamingElement root = new XStreamingElement("Results",
from el in StreamProductItem(file)
where fieldsToSearch.Any(s => el.Element(s) != null && el.Element(s).Value.Contains(searchTerm))
select fieldsToReturn.Select(r => (r == "product") ? el : el.Element(r))
);
Console.WriteLine(root.ToString());
Where 'fieldsToSearch' and 'fieldsToReturn' are both List objects.
其中 'fieldsToSearch' 和 'fieldsToReturn' 都是 List 对象。
回答by Zignd
This project on CodePlex have what you want.
CodePlex 上的这个项目有你想要的。
System.Linq.Dynamic- http://dynamiclinq.codeplex.com/
System.Linq.Dynamic- http://dynamiclinq.codeplex.com/
Project Description
Extends System.Linq.Dynamic to support Execution of Lambda expressions defined in a string against Entity Framework or any provider that supports IQueryable.
项目描述
扩展 System.Linq.Dynamic 以支持针对实体框架或任何支持 IQueryable 的提供程序执行字符串中定义的 Lambda 表达式。
As it is an extension of the source code you can find on Scott Guthrie's Blogit will allow you to do things like this:
由于它是源代码的扩展，您可以在Scott Guthrie 的博客上找到它，它允许您执行以下操作：
And things like this:
和这样的事情：
回答by mike
I came up with a solution that even I can understand... by using the 'Contains' method you can chain as many WHERE's as you like. If the WHERE is an empty string, it's ignored (or evaluated as a select all). Here is my example of joining 2 tables in LINQ, applying multiple where clauses and populating a model class to be returned to the view. (this is a select all).
我想出了一个即使我也能理解的解决方案……通过使用“包含”方法，您可以根据需要链接任意数量的 WHERE。如果 WHERE 是一个空字符串，它会被忽略（或评估为全选）。这是我在 LINQ 中连接 2 个表的示例，应用多个 where 子句并填充要返回到视图的模型类。（这是一个全选）。
public ActionResult Index()
{
string AssetGroupCode = "";
string StatusCode = "";
string SearchString = "";
var mdl = from a in _db.Assets
join t in _db.Tags on a.ASSETID equals t.ASSETID
where a.ASSETGROUPCODE.Contains(AssetGroupCode)
&& a.STATUSCODE.Contains(StatusCode)
&& (
a.PO.Contains(SearchString)
|| a.MODEL.Contains(SearchString)
|| a.USERNAME.Contains(SearchString)
|| a.LOCATION.Contains(SearchString)
|| t.TAGNUMBER.Contains(SearchString)
|| t.SERIALNUMBER.Contains(SearchString)
)
select new AssetListView
{
AssetId = a.ASSETID,
TagId = t.TAGID,
PO = a.PO,
Model = a.MODEL,
UserName = a.USERNAME,
Location = a.LOCATION,
Tag = t.TAGNUMBER,
SerialNum = t.SERIALNUMBER
};
return View(mdl);
}
回答by Xavier John
I have similar scenario where I need to add filters based on the user input and I chain the where clause.
我有类似的场景，我需要根据用户输入添加过滤器并链接 where 子句。
Here is the sample code.
这是示例代码。
var votes = db.Votes.Where(r => r.SurveyID == surveyId);
if (fromDate != null)
{
votes = votes.Where(r => r.VoteDate.Value >= fromDate);
}
if (toDate != null)
{
votes = votes.Where(r => r.VoteDate.Value <= toDate);
}
votes = votes.Take(LimitRows).OrderByDescending(r => r.VoteDate);
回答by KJM
This is the solution I came up with if anyone is interested.
如果有人感兴趣，这是我想出的解决方案。
https://kellyschronicles.wordpress.com/2017/12/16/dynamic-predicate-for-a-linq-query/
https://kellyschronicles.wordpress.com/2017/12/16/dynamic-predicate-for-a-linq-query/
First we identify the single element type we need to use ( Of TRow As DataRow) and then identify the “source” we are using and tie the identifier to that source ((source As TypedTableBase(Of TRow)). Then we must specify the predicate, or the WHERE clause that is going to be passed (predicate As Func(Of TRow, Boolean)) which will either be returned as true or false. Then we identify how we want the returned information ordered (OrderByField As String). Our function will then return a EnumerableRowCollection(Of TRow), our collection of datarows that have met the conditions of our predicate(EnumerableRowCollection(Of TRow)). This is a basic example. Of course you must make sure your order field doesn't contain nulls, or have handled that situation properly and make sure your column names (if you are using a strongly typed datasource never mind this, it will rename the columns for you) are standard.
首先我们确定我们需要使用的单个元素类型（Of TRow As DataRow），然后确定我们正在使用的“源”并将标识符绑定到该源（（source As TypedTableBase（Of TRow））。然后我们必须指定谓词，或将要传递的 WHERE 子句（谓词 As Func(Of TRow, Boolean)），它将返回为 true 或 false。然后我们确定我们希望返回的信息如何排序（OrderByField As String）。我们的函数然后将返回一个 EnumerableRowCollection(Of TRow)，我们的数据行集合满足我们的谓词(EnumerableRowCollection(Of TRow))。这是一个基本的例子。当然你必须确保你的订单字段不包含空值，或者已经正确处理了这种情况并确保您的列名（如果您使用的是强类型数据源，没关系，它会为您重命名列）是标准的。
回答by Josué Camacho
It seems much simpler and simpler to use the ternary operator to decide dynamically if a condition is included
使用三元运算符动态决定是否包含条件似乎越来越简单
List productList = new List();
List productList = new List();
productList =
db.ProductDetail.Where(p => p.ProductDetailID > 0 //Example prop
&& (String.IsNullOrEmpty(iproductGroupName) ? (true):(p.iproductGroupName.Equals(iproductGroupName)) ) //use ternary operator to make the condition dynamic
&& (ID == 0 ? (true) : (p.ID == IDParam))
).ToList();
回答by Misael da Costa Homem
Just to share my idea for this case.
只是分享我对这个案例的想法。
Another approach by solution is:
另一种解决方法是：
public IOrderedQueryable GetProductList(string productGroupName, string productTypeName, Dictionary> filterDictionary)
{
return db.ProductDetail
.where
(
p =>
(
(String.IsNullOrEmpty(productGroupName) || c.ProductGroupName.Contains(productGroupName))
&& (String.IsNullOrEmpty(productTypeName) || c.ProductTypeName.Contains(productTypeName))
// Apply similar logic to filterDictionary parameter here !!!
)
);
}
This approach is very flexible and allow with any parameter to be nullable.
这种方法非常灵活，允许任何参数为空。
