C#3.0入门系列（十一）-之In, Like操作

有这么一个例子，寻找一个表中的某个字段介于某个给定的集合该怎么办？Sql写起来很简单，比如：Select * from table where id in (2,3, 4, 5)。就是寻找id字段为这个给定的集合(2,3, 4, 5)内的值。那Linq to Sql该怎么做呢？一个字，简单。

In Operator
比如，我们想要查找，"AROUT", "BOLID" 和 "FISSA" 这三个客户的订单。该如何做呢？Linq to Sql是这么做的。

string [] customerID_Set = new string [] { "AROUT", "BOLID", "FISSA" };

var q = ( from o in db.Orders

where customerID_Set. Contains (o.CustomerID)

select o).ToList();

其生成的sql语句为

SELECT [ t0 ] . [ OrderID ] , [ t0 ] . [ CustomerID ] , [ t0 ] . [ EmployeeID ] , [ t0 ] . [ OrderDate ] , [

t0 ] . [ RequiredDate ] , [ t0 ] . [ ShippedDate ] , [ t0 ] . [ ShipVia ] , [ t0 ] . [ Freight ] , [ t0 ] . [ Sh

ipName ] , [ t0 ] . [ ShipAddress ] , [ t0 ] . [ ShipCity ] , [ t0 ] . [ ShipRegion ] , [ t0 ] . [ ShipPosta

lCode ] , [ t0 ] . [ ShipCountry ]

FROM [ dbo ] . [ Orders ] AS [ t0 ]

WHERE [ t0 ] . [ CustomerID ] IN ( @p0 , @p1 , @p2 )

-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [AROUT]

-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [BOLID]

-- @p2: Input String (Size = 5; Prec = 0; Scale = 0) [FISSA]

先定义了一个数组，在linq query中，使用Contains，也很好理解，就是这个数组，包含了所有的CustomerID, 即返回结果中，所有的CustomerID都在这个集合内。也就是in。你也可以把数组的定义放在linq语句里。比如：

var q = ( from o in db.Orders

where (new string [] { "AROUT", "BOLID", "FISSA" }). Contains (o.CustomerID)

select o).ToList();

Not in 呢？加个取反就是

var q2 = ( from o in db.Orders

where !(new string [] { "AROUT", "BOLID", "FISSA" }). Contains (o.CustomerID)

select o).ToList();

就这么简单。

Like Operator
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名称空间。上面的三个可以写成

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://msdn2.microsoft.com/en-us/library/Aa933232(SQL.80).aspx

TrackBack: http://www.cnblogs.com/126/archive/2007/08/01/839448.html

C#3.0入门系列（十一）-之In, Like操作

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义