博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
Linq Like
阅读量:6444 次
发布时间:2019-06-23

本文共 4149 字,大约阅读时间需要 13 分钟。

Like的操作,有点像in,但是,方向变了。什么意思呢。就是你给定一个字符串,去寻找数据中某个字段包含这个字符串。就是给定的字符串是某字段的子集。Sql Script是这么写的。

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名称空间。(System.Data.Linq.dll中)上面的三个可以写成
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 Description 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的事情了。

转载地址:http://nfpwo.baihongyu.com/

你可能感兴趣的文章
建立Ftp站点
查看>>
NavigationController的使用
查看>>
android 内存泄漏
查看>>
HTML5:Canvas-绘制图形
查看>>
Sass--传多个参数
查看>>
多线程编程之Windows环境下创建新线程
查看>>
ASP.Net MVC的开发模式
查看>>
困惑的托管与非托管混合调试时遇到的不能设断点的问题
查看>>
groupbox 下的datagridview的列标题字体修改混乱
查看>>
HDU-3092 Least common multiple---数论+分组背包
查看>>
CentOS 7使用systemctl如何补全服务名称
查看>>
软件架构笔记 一
查看>>
Unity3D NGUI 给button按钮添加单间事件
查看>>
C# 使用各种API
查看>>
echo命令的简单用法和实例
查看>>
CentOS7.4 KVM虚拟化之环境准备(1)
查看>>
《程序员代码面试指南》第八章 数组和矩阵问题 子矩阵的最大累加和问题
查看>>
密码的校验.大小写字母,数字,特殊字符中的至少3种
查看>>
hibernate常见错误
查看>>
FIN_WAIT_2状态解释
查看>>