c＃使用Linq2Sql在表列中查找匹配的单词 [英] c# finding matching words in table column using Linq2Sql
问题描述
我试图使用Linq2Sql来返回包含字符串列表中的值的所有行。 linq2sql类对象有一个字符串属性，包含用空格分隔的单词。
I am trying to use Linq2Sql to return all rows that contain values from a list of strings. The linq2sql class object has a string property that contains words separated by spaces.
public class MyObject
{
public string MyProperty { get; set; }
}
示例MyProperty值为：
Example MyProperty values are:
MyObject1.MyProperty = "text1 text2 text3 text4"
MyObject2.MyProperty = "text2"
例如，使用字符串集合，我通过以下列表
For example, using a string collection, I pass the below list
var list = new List<>() { "text2", "text4" }
因为它们都包含text2值。
This would return both items in my example above as they both contain "text2" value.
我试图使用下面的代码，但是，因为我的扩展方法Linq2Sql不能
I attempted the following using the below code however, because of my extension method the Linq2Sql cannot be evaluated.
public static IQueryable<MyObject> WithProperty(this IQueryable<MyProperty> qry,
IList<string> p)
{
return from t in qry
where t.MyProperty.Contains(p, ' ')
select t;
}
我也写了一个扩展方法
public static bool Contains(this string str, IList<string> list, char seperator)
{
if (str == null) return false;
if (list == null) return true;
var splitStr = str.Split(new char[] { seperator },
StringSplitOptions.RemoveEmptyEntries);
bool retval = false;
int matches = 0;
foreach (string s in splitStr)
{
foreach (string l in list)
{
if (String.Compare(s, l, true) == 0)
{
retval = true;
matches++;
}
}
}
return retval && (splitStr.Length > 0) && (list.Count == matches);
}
有什么帮助或想法如何实现？
Any help or ideas on how I could achieve this?
推荐答案
在正确的轨道上。您的扩展方法 WithProperty 的第一个参数必须是 IQueryable< MyObject> 类型，而不是 IQueryable< MyProperty> 。
Youre on the right track. The first parameter of your extension method WithProperty has to be of the type IQueryable<MyObject>, not IQueryable<MyProperty>.
无论如何你不需要一个扩展方法 IQueryable 。只需在lambda中使用 Contains 方法进行过滤。这应该工作：
Anyways you dont need an extension method for the IQueryable. Just use your Contains method in a lambda for filtering. This should work:
List<string> searchStrs = new List<string>() { "text2", "text4" }
IEnumerable<MyObject> myFilteredObjects = dataContext.MyObjects
.Where(myObj => myObj.MyProperty.Contains(searchStrs, ' '));
更新：
上述代码段不会 工作。这是因为包含方法无法转换为SQL语句。我想了一会儿关于这个问题，并通过思考'我将如何在SQL中做'一个解决方案：你可以通过查询每个单个关键字，并联合所有结果在一起。遗憾的是，Linq-to-SQL的延迟执行阻止了在一个查询中执行所有操作。所以我想出了妥协的妥协。它查询每个关键字。这可以是以下之一：
The above code snippet does not work. This is because the Contains method can not be converted into a SQL statement. I thought a while about the problem, and came to a solution by thinking about 'how would I do that in SQL?': You could do it by querying for each single keyword, and unioning all results together. Sadly the deferred execution of Linq-to-SQL prevents from doing that all in one query. So I came up with this compromise of a compromise. It queries for every single keyword. That can be one of the following:
- 等于字符串
- / li>
- 在字符串开头，然后是分隔符
- 或在字符串末尾并以分隔符开头
这跨越一个有效的表达式树，可以通过Linq-to-SQL翻译成SQL。在查询后，我不推迟执行通过immediatelly提取数据，并将其存储在一个列表。
This spans a valid expression tree and is translatable into SQL via Linq-to-SQL. After the query I dont defer the execution by immediatelly fetch the data and store it in a list. All lists are unioned afterwards.
public static IEnumerable<MyObject> ContainsOneOfTheseKeywords(
this IQueryable<MyObject> qry, List<string> keywords, char sep)
{
List<List<MyObject>> parts = new List<List<MyObject>>();
foreach (string keyw in keywords)
parts.Add((
from obj in qry
where obj.MyProperty == keyw ||
obj.MyProperty.IndexOf(sep + keyw + sep) != -1 ||
obj.MyProperty.IndexOf(keyw + sep) >= 0 ||
obj.MyProperty.IndexOf(sep + keyw) ==
obj.MyProperty.Length - keyw.Length - 1
select obj).ToList());
IEnumerable<MyObject> union = null;
bool first = true;
foreach (List<MyObject> part in parts)
{
if (first)
{
union = part;
first = false;
}
else
union = union.Union(part);
}
return union.ToList();
}
并使用它：
List<string> searchStrs = new List<string>() { "text2", "text4" };
IEnumerable<MyObject> myFilteredObjects = dataContext.MyObjects
.ContainsOneOfTheseKeywords(searchStrs, ' ');
这个解决方案真的一切都不是优雅。对于10个关键字，我必须查询db 10次，每次捕获数据并将其存储在内存中。这是浪费内存和性能不好。我只是想表明，这是可能在Linq（也许它可以优化在这里或那里，但我认为它不会得到完美）。
That solution is really everything else than elegant. For 10 keywords, I have to query the db 10 times and every time catch the data and store it in memory. This is wasting memory and has a bad performance. I just wanted to demonstrate that it is possible in Linq (maybe it can be optimized here or there, but I think it wont get perfect).
我强烈建议将该函数的逻辑切换到数据库服务器的存储过程。一个单一查询，由数据库服务器优化，并且不浪费内存。
I would strongly recommend to swap the logic of that function into a stored procedure of your database server. One single query, optimized by the database server, and no waste of memory.
另一种替代方法是重新思考数据库设计。如果你想查询一个字段的内容（你正在处理这个字段就像一个关键字数组，由空格分隔），你可能只是选择了一个不适当的数据库设计。你宁愿要创建一个新的表，外加表你的表。新表只有一个关键字。这些查询将很多更简单，更快速，更容易理解。
Another alternative would be to rethink your database design. If you want to query contents of one field (you are treating this field like an array of keywords, seperated by spaces), you may simply have chosen an inappropriate database design. You would rather want to create a new table with a foreign key to your table. The new table has then exactly one keyword. The queries would be much simpler, faster and more understandable.
这篇关于c＃使用Linq2Sql在表列中查找匹配的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
