架构师_程序员_码农网

 找回密码
 注册[Register]

QQ登录

只需一步,快速开始

查看: 11094|回复: 0

[资料] Linq to sql中的Like模糊查询

[复制链接]
发表于 2015-9-7 23:16:28 | 显示全部楼层 |阅读模式
Like的操作,有点像in,但是,方向变了。什么意思呢。就是你给定一个字符串,去寻找数据中某个字段包含这个字符串。就是给定的字符串是某字段的子集。Sql scrip{过滤}t是这么写的。


Selec * from table where id like '%AD%'
Selec * from table where id like '%AD'
Selec * from table where id like 'AD%'
上面的%是通配符,表示,该字段含有某个值,不知道的位置使用%代替。第一个是表示中间一段是AD,两头不清楚。第二个是结尾是AD,前面的不清楚。第三个相反,开头是AD,结尾不清楚。其对应的Linq 语句为


var q = (from c in db.Customers
where c.CustomerID.Contains("ROUT")
select c).ToList();
其生成的sql为


SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] LIKE @p0
-- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [%ROUT%]
以ISSA结尾,头部通配:


var q = (from c in db.Customers
where c.CustomerID.EndsWith("ISSA")
select c).ToList();
其生成的sql为


SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] LIKE @p0
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [%ISSA]
以ARO开始,尾部通配:


var q = (from c in db.Customers
where c.CustomerID.StartsWith("ARO")
select c).ToList();
其生成的sql为


SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] LIKE @p0
-- @p0: Input String (Size = 4; Prec = 0; Scale = 0) [ARO%]

Linq 还提供了一种方法,叫做SqlMethods.Like,需要先添加System.Data.Linq.SqlClient名称空间。上面的三个可以写成


var q = (from c in db.Customers
where SqlMethods.Like(c.CustomerID, "%ROUT%")
select c).ToList();
这里,你需要自己填写通配符,告诉Linq你是如何匹配。比如


var q = (from c in db.Customers
where SqlMethods.Like(c.CustomerID, "%ISSA")
select c).ToList();
再比如:


var q = (from c in db.Customers
where SqlMethods.Like(c.CustomerID, "ARO%")
select c).ToList();
SqlMethods.Like最奇妙的地方,莫过于,自己定义的通配表达式,你可以在任何地方实现通配。比如


var q = (from c in db.Customers
where SqlMethods.Like(c.CustomerID, "A%O%T")
select c).ToList();
其生成的sql为


SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] LIKE @p0
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [A%O%T]

就是最标准的知道以A开头,以T结尾,中间知道一个值O,其他就什么不知道了。就用这个。
SQL Server 定义了四种通配符,在这里都可以使用。它们是:
Wildcard character Descrip{过滤}tion Example
% Any string of zero or more characters. WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore) Any single character. WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l.

%表示零长度或任意长度的字符串。_表示一个字符。[]表示在某范围区间的一个字符。[^]表示不在某范围区间的一个字符
比如:


var q = (from c in db.Customers
where SqlMethods.Like(c.CustomerID, "A_O_T")
select c).ToList();
就用_代表一个字符。其生成sql为


SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] LIKE @p0
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [A_O_T]

对于Not Like,也很简单,加个取非就是。


var q = (from c in db.Customers
where !SqlMethods.Like(c.CustomerID, "A_O_T")
select c).ToList();

SqlMethods.Like还有一个参数,叫escape Character,其将会被翻译成类似下面的语句。


SELECT columns FROM table WHERE
column LIKE '%\%%' ESCAPE '\'
escape 是因为某字段中含有特殊字符,比如%,_ [ ]这些被用作通配符的。这时就要用到Escape了。这是sql server的事情了。





上一篇:网站性能优化:cache-control设置详解
下一篇:锐捷一直断是咋回事呢
码农网,只发表在实践过程中,遇到的技术难题,不误导他人。
您需要登录后才可以回帖 登录 | 注册[Register]

本版积分规则

免责声明:
码农网所发布的一切软件、编程资料或者文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。如有侵权请邮件与我们联系处理。

Mail To:help@itsvse.com

QQ|手机版|小黑屋|架构师 ( 鲁ICP备14021824号-2 )|网站地图

GMT+8, 2024-3-29 00:10

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

快速回复 返回顶部 返回列表