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

系统 1880 0
有这么一个例子,寻找一个表中的某个字段介于某个给定的集合该怎么办?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元、自定义金额等您想捐的金额吧,站长会非常 感谢您的哦!!!

发表我的评论
最新评论 总共0条评论