有這么一個例子，尋找一個表中的某個字段介于某個給定的集合該怎么辦？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 定義了四種通配符，在這里都可以使用。它們是：
%表示零長度或任意長度的字符串。_表示一個字符。[]表示在某范圍區間的一個字符。[^]表示不在某范圍區間的一個字符
比如：
????????
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
?
'
\
'
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();
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]
????????
var
?q?
=
?(
from
?o?
in
?db.Orders
?????????????????
where
?(new?string
[]
?{?"AROUT",?"BOLID",?"FISSA"?}).
Contains
(o.CustomerID)
?????????????????
select
?o).ToList();
????????
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%
'
????????
var
?q?
=
?(
from
?c?
in
?db.Customers
?????????????????
where
?c.CustomerID.
Contains
("ROUT")
?????????????????
select
?c).ToList();
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%]
????????
var
?q?
=
?(
from
?c?
in
?db.Customers
?????????????????
where
?c.CustomerID.EndsWith("ISSA")
?????????????????
select
?c).ToList();
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]
????????
var
?q?
=
?(
from
?c?
in
?db.Customers
?????????????????
where
?c.CustomerID.StartsWith("ARO")
?????????????????
select
?c).ToList();
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();
????????
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();
????????
var
?q?
=
?(
from
?c?
in
?db.Customers
?????????????????
where
?SqlMethods.
Like
(c.CustomerID,?"A
%
O
%
T")
?????????????????
select
?c).ToList();
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();
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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
