C# 在 LINQ to SQL 中使用 contains()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2369022/
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
Using contains() in LINQ to SQL
提问by a_m0d
I'm trying to implement a very basic keyword search in an application using linq-to-sql. My search terms are in an array of strings, each array item being one word, and I would like to find the rows that contain the search terms. I don't mind if they contain more than just the search terms (most likely, they will), but all the search terms do have to be present.
我正在尝试使用 linq-to-sql 在应用程序中实现一个非常基本的关键字搜索。我的搜索词在一个字符串数组中，每个数组项都是一个词，我想找到包含搜索词的行。我不介意它们包含的不仅仅是搜索词（很可能会），但所有搜索词都必须存在。
Ideally, I would like something similar to the snippet below, but I know that this won't work. Also, I have looked at this question here, but the author of that question seems content to do things the other way round ( query.Contains(part.partName)), which doesn't work for me.
理想情况下，我想要类似于下面的代码片段，但我知道这行不通。此外，我在这里查看了这个问题，但该问题的作者似乎满足于以相反的方式做事 ( query.Contains(part.partName))，这对我不起作用。
public IQueryable<Part> SearchForParts(string[] query)
{
return from part in db.Parts
where part.partName.Contains(query)
select part;
}
How can I rewrite this query so that it will do what I need?
我怎样才能重写这个查询，让它做我需要的？
采纳答案by leppie
Looking at the other attempts saddens me :(
看着其他的尝试让我很难过:(
public IQueryable<Part> SearchForParts(string[] query)
{
var q = db.Parts.AsQueryable();
foreach (var qs in query)
{
var likestr = string.Format("%{0}%", qs);
q = q.Where(x => SqlMethods.Like(x.partName, likestr));
}
return q;
}
Assumptions:
假设：
partName looks like: "ABC 123 XYZ"
query is { "ABC", "123", "XY" }
零件名称看起来像：“ABC 123 XYZ”
查询是 { "ABC", "123", "XY" }
回答by Rory
You could try:
你可以试试：
public IQueryable<Part> SearchForParts(string[] query)
{
return from part in db.Parts
where query.All(term => part.partName.Contains(term))
select part;
}
However, I'm not sure if LINQ to SQL will be able to transform it into T-SQL. Another option would be:
但是，我不确定 LINQ to SQL 是否能够将其转换为 T-SQL。另一种选择是：
public IQueryable<Part> SearchForParts(string[] query)
{
var result = from part in db.Parts
select part;
foreach(var term in query)
{
result = from part in result
where part.partName.Contains(term)
select part;
}
return result;
}
It's not as pretty, but it should work. You'll get a query with a lot of ANDs in the where clause.
它不是那么漂亮，但它应该可以工作。你会AND在 where 子句中得到一个包含很多s的查询。
回答by Fadrian Sudaman
You can write it as this
你可以这样写
var result = db.Parts.Where(p => query.All(q => p.partName.Contains(q)));
回答by masoud ramezani
please try this:
请试试这个：
public IQueryable<Part> SearchForParts(string[] query)
{
return from part in db.Parts
where Search(part.Name,query)
select part;
}
public bool Search(string partName,string[] query)
{
for (int i = 0; i < query.Length; i++)
{
if(partName.Contains(query[i]))
return true;
}
return false;
}
回答by Trisped
A simpler and more correct solution (then leppie's):
一个更简单、更正确的解决方案（然后是 leppie 的）：
public IQueryable<Part> SearchForParts(string[] query)
{
var q = db.Parts.AsQueryable();
foreach (string qs in query)
{
q = q.Where(x => x.partName.Contains(qs));
}
return q;
}
This will work as long as partNameis a string (or an SQL equivalent of a string).
只要partName是字符串（或字符串的 SQL 等价物），这将起作用。
The important thing to note is partName.Contains(qs)is different than query.Contains(partName).
With partName.Contains(qs), partNameis searched for any occurrence of qs. The resulting SQL would be equivalent (where <qs> is the value of qs):
需要注意的重要一点partName.Contains(qs)是与query.Contains(partName).
使用partName.Contains(qs),partName搜索任何出现的qs。生成的 SQL 将是等效的（其中 <qs> 是 的值qs）：
select * from Parts where partName like '%<qs>%';
Also of note are StartsWithand EndsWithwhich are similar to Containsbut look for the string in the specific location.
同样值得注意的是StartsWith和EndsWith类似于Contains但在特定位置查找字符串。
query.Contains(partName)is the same as a SQL incommand. The resulting SQL would be equivalent to (where <query0> is the value of query[0], <query1> is the value of query[1], and <queryN> is the last value in the query array):
query.Contains(partName)与 SQLin命令相同。生成的 SQL 将等价于（其中 <query0> 是 的值query[0]，<query1> 是 的值query[1]，<queryN> 是查询数组中的最后一个值）：
select * from Parts where partName in ( <query0>, <query1>, ..., <queryN> );
Update:
It is also important to note that leppie's answer does not escape the wildcard characters before adding them to the likestatement. This is not an issue with the Containssolution since Linq will escape the query before sending it. An escaped version of the SqlMethods.Likesolution would be:
更新：
同样重要的是要注意 leppie 的答案在将通配符添加到like语句之前不会转义通配符。这不是Contains解决方案的问题，因为 Linq 将在发送之前对查询进行转义。SqlMethods.Like解决方案的转义版本将是：
public IQueryable<Part> SearchForParts(string[] query)
{
var q = db.Parts.AsQueryable();
foreach (var qs in query)
{
string escaped_bs = qs.Replace("/", "//"),
escaped_us = escaped_bs.Replace("_", "/_"),
escaped_p = escaped_us.Replace("%", "/%"),
escaped_br = escaped_p.Replace("[", "/["),
likestr = string.Format("%{0}%", escaped_br);
q = q.Where(x => SqlMethods.Like(x.partName, likestr, '/'));
}
return q;
}
You don't have to worry about ' since Linq will escape that for you.
您不必担心 ' 因为 Linq 会为您解决这个问题。
回答by murali
I feel this is somewhat simple and working for me:
我觉得这有点简单并且对我有用：
string[] product = products.Split(',');
using (var context = new ProjectTrackerEntities())
{ var result = from part in context.DBAudits where product.Contains(part.TableName) select part; }
回答by NinjaNye
Using the NinjaNye.SearchExtensionnuget package allows you to perform this search with ease:
使用NinjaNye.SearchExtensionnuget 包可让您轻松执行此搜索：
string[] terms = new[]{"search", "term", "collection"};
var result = db.Parts.Search(terms, p => p.PartName);
You could also search multiple string properties
您还可以搜索多个字符串属性
var result = db.Parts.Search(terms, p => p.PartName, p.PartDescription);
Or perform a RankedSearchwhich returns IQueryable<IRanked<T>>which simply includes a property which shows how many times the search terms appeared:
或者执行一个RankedSearch返回IQueryable<IRanked<T>>，它只包含一个显示搜索词出现次数的属性：
//Perform search and rank results by the most hits
var result = db.Parts.RankedSearch(terms, p => p.PartName, p.PartDescription)
.OrderByDescending(r = r.Hits);
There is a more extensive guide on the projects GitHub page: https://github.com/ninjanye/SearchExtensions
项目 GitHub 页面上有更广泛的指南：https: //github.com/ninjanye/SearchExtensions
Hope this helps future visitors
希望这对未来的游客有所帮助
