asp.net-mvc LINQ to Entity，使用 SQL LIKE 运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2493703/
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 Entity, using a SQL LIKE operator
提问by SlackerCoder
I have a LINQ to ENTITY query that pulls from a table, but I need to be able to create a "fuzzy" type search. So I need to add a where clause that searches by lastname IF they add the criteria in the search box (Textbox, CAN be blank --- in which case it pulls EVERYTHING).
我有一个从表中提取的 LINQ to ENTITY 查询，但我需要能够创建一个“模糊”类型的搜索。因此，我需要添加一个 where 子句，如果他们在搜索框中添加条件（文本框，可以为空 --- 在这种情况下，它会拉取一切），则该子句按姓氏进行搜索。
Here is what I have so far:
这是我到目前为止所拥有的：
var query = from mem in context.Member
orderby mem.LastName, mem.FirstName
select new
{
FirstName = mem.FirstName,
LastName = mem.LastName,
};
That will pull everything out of the Member table that is in the Entity object.
这将从实体对象中的成员表中提取所有内容。
Then I have an addition to the logic:
然后我对逻辑进行了补充：
sLastName = formCollection["FuzzyLastName"].ToString();
if (!String.IsNullOrEmpty(sLastName))
query = query.Where(ln => ln.LastName.Contains(sLastName));
The problem is when the search button is pressed, nothing is returned (0 results). I have run the query against the SQL Server that I expect to happen here and it returns 6 results.
问题是按下搜索按钮时，没有返回任何内容（0 个结果）。我已经针对我希望在这里发生的 SQL Server 运行查询，它返回 6 个结果。
This is the query I expect:
这是我期望的查询：
SELECT mem.LastName, mem.FirstName FROM Members mem WHERE mem.LastName LIKE '%xxx%'
(when xxx is entered into the textbox)
（当 xxx 输入文本框时）
Anyone see anything wrong with this?
有人看到这有什么问题吗？
EDIT: Fixed the SELECT query. I meant for it to read LIKE '%xxx%' (NOT = 'xxx")
编辑：修复了 SELECT 查询。我的意思是让它读 LIKE '%xxx%' (NOT = 'xxx")
回答by John Bubriski
I think you want to use the Contains()function of the string parameter like this:
我想你想像这样使用Contains()字符串参数的功能：
var query = from mem in context.Member
where mem.LastName.Contains("xxx")
orderby mem.LastName, mem.FirstName
select new
{
FirstName = mem.FirstName,
LastName = mem.LastName,
};
I think you can also use StartsWith()and EndsWith()which would be equivalent to the SQL 'xxx%' and '%xxx" respectively.
我认为你也可以使用StartsWith()andEndsWith()分别相当于 SQL 'xxx%' 和 '%xxx" 。
回答by kervin
Add your "select new" to the query only after you append your "Where" clause.
只有在附加“Where”子句后，才将“select new”添加到查询中。
Hence append your select clause using object call syntax as you did with the where clause.
因此，像使用 where 子句一样，使用对象调用语法附加您的 select 子句。
Untested, please excuse small errors, but the general concept would be....
未经测试，请原谅小错误，但一般概念是......
using( someContent sc = new someContent())
{
var query = sc.Member.OrderBy( i => i.LastName)
.ThenBy( i => i.FirstName);
sLastName = formCollection["FuzzyLastName"].ToString();
if (!String.IsNullOrEmpty(sLastName))
query = query.Where(ln => ln.LastName.Contains(sLastName));
query = query.Select( i => new
{
FirstName = i.FirstName,
LastName = i.LastName,
});
}
回答by Oskar Kjellin
SELECT mem.LastName, mem.FirstName FROM Members mem WHERE mem.LastName = 'xxx'
That means that you want the last name to be equal to 'xxx'. What you write in your above post is that the lastname should contain 'xxx'.
这意味着您希望姓氏等于“xxx”。您在上面的帖子中写的是姓氏应包含“xxx”。
To get it to equal you should write:
为了让它相等，你应该写：
if (!String.IsNullOrEmpty(sLastName))
query = query.Where(ln => ln.LastName == sLastName);
Perhaps you should look at ignore case:
也许你应该看看 ignore case：
if (!String.IsNullOrEmpty(sLastName))
query = query.Where(ln => ln.LastName.Equals(sLastName, StringComparison.InvariantCultureIgnoreCase));
