有這么一個(gè)例子，尋找一個(gè)表中的某個(gè)字段介于某個(gè)給定的集合該怎么辦？Sql寫起來(lái)很簡(jiǎn)單，比如：Select * from table where id in (2,3, 4, 5)。 就是尋找id字段為這個(gè)給定的集合(2,3, 4, 5)內(nèi)的值。那Linq to Sql該怎么做呢？一個(gè)字，簡(jiǎn)單。
In Operator ?
比如，我們想要查找，"AROUT", "BOLID" 和?"FISSA" 這三個(gè)客戶的訂單。該如何做呢？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語(yǔ)句為
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]
先定義了一個(gè)數(shù)組，在linq query中，使用Contains，也很好理解，就是這個(gè)數(shù)組，包含了所有的CustomerID, 即返回結(jié)果中，所有的CustomerID都在這個(gè)集合內(nèi)。也就是in。 你也可以把數(shù)組的定義放在linq語(yǔ)句里。比如：
????????
var
?q?
=
?(
from
?o?
in
?db.Orders
?????????????????
where
?(new?string
[]
?{?"AROUT",?"BOLID",?"FISSA"?}).
Contains
(o.CustomerID)
?????????????????
select
?o).ToList();
Not in 呢？加個(gè)取反就是
????????
var
?q2?
=
?(
from
?o?
in
?db.Orders
?????????????????
where
?!(new?string
[]
?{?"AROUT",?"BOLID",?"FISSA"?}).
Contains
(o.CustomerID)
?????????????????
select
?o).ToList();
就這么簡(jiǎn)單。
Like Operator
Like的操作，有點(diǎn)像in，但是，方向變了。什么意思呢。就是你給定一個(gè)字符串，去尋找數(shù)據(jù)中某個(gè)字段包含這個(gè)字符串。就是給定的字符串是某字段的子集。Sql Script是這么寫的。
Selec?
*
?
from
?
table
?
where
?id?
like
?
'
%AD%
'
Selec?
*
?
from
?
table
?
where
?id?
like
?
'
%AD
'
Selec?
*
?
from
?
table
?
where
?id?
like
?
'
AD%
'
上面的%是通配符，表示，該字段含有某個(gè)值，不知道的位置使用%代替。第一個(gè)是表示中間一段是AD，兩頭不清楚。第二個(gè)是結(jié)尾是AD,前面的不清楚。第三個(gè)相反，開頭是AD，結(jié)尾不清楚。其對(duì)應(yīng)的Linq 語(yǔ)句為
????????
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結(jié)尾,頭部通配：
????????
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名稱空間。上面的三個(gè)可以寫成
???????
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最奇妙的地方，莫過(guò)于，自己定義的通配表達(dá)式，你可以在任何地方實(shí)現(xiàn)通配。比如
????????
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]
就是最標(biāo)準(zhǔn)的知道以A開頭，以T結(jié)尾，中間知道一個(gè)值O，其他就什么不知道了。就用這個(gè)。
SQL Server 定義了四種通配符，在這里都可以使用。它們是：
%表示零長(zhǎng)度或任意長(zhǎng)度的字符串。_表示一個(gè)字符。[]表示在某范圍區(qū)間的一個(gè)字符。[^]表示不在某范圍區(qū)間的一個(gè)字符
比如：
????????
var
?q?
=
?(
from
?c?
in
?db.Customers
?????????????????
where
?SqlMethods.
Like
(c.CustomerID,?"A_O_T")
?????????????????
select
?c).ToList();
就用_代表一個(gè)字符。其生成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]
對(duì)于Not Like，也很簡(jiǎn)單，加個(gè)取非就是。
????????
var
?q?
=
?(
from
?c?
in
?db.Customers
?????????????????
where
?!SqlMethods.
Like
(c.CustomerID,?"A_O_T")
?????????????????
select
?c).ToList();
SqlMethods.Like還有一個(gè)參數(shù)，叫escape Character，其將會(huì)被翻譯成類似下面的語(yǔ)句。
SELECT
?columns?
FROM
?
table
?
WHERE
?
????
column
?
LIKE
?
'
%\%%
'
?
ESCAPE
?
'
\
'
In Operator ?
比如，我們想要查找，"AROUT", "BOLID" 和?"FISSA" 這三個(gè)客戶的訂單。該如何做呢？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的操作，有點(diǎn)像in，但是，方向變了。什么意思呢。就是你給定一個(gè)字符串，去尋找數(shù)據(jù)中某個(gè)字段包含這個(gè)字符串。就是給定的字符串是某字段的子集。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名稱空間。上面的三個(gè)可以寫成
???????
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]
就是最標(biāo)準(zhǔn)的知道以A開頭，以T結(jié)尾，中間知道一個(gè)值O，其他就什么不知道了。就用這個(gè)。
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. |
%表示零長(zhǎng)度或任意長(zhǎng)度的字符串。_表示一個(gè)字符。[]表示在某范圍區(qū)間的一個(gè)字符。[^]表示不在某范圍區(qū)間的一個(gè)字符
比如：
????????
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]
對(duì)于Not Like，也很簡(jiǎn)單，加個(gè)取非就是。
????????
var
?q?
=
?(
from
?c?
in
?db.Customers
?????????????????
where
?!SqlMethods.
Like
(c.CustomerID,?"A_O_T")
?????????????????
select
?c).ToList();
SqlMethods.Like還有一個(gè)參數(shù)，叫escape Character，其將會(huì)被翻譯成類似下面的語(yǔ)句。
SELECT
?columns?
FROM
?
table
?
WHERE
?
????
column
?
LIKE
?
'
%\%%
'
?
ESCAPE
?
'
\
'
escape 是因?yàn)槟匙侄沃泻刑厥庾址热?,_ [ ]這些被用作通配符的。這時(shí)就要用到Escape了。這是sql server的事情了。詳細(xì)情況請(qǐng)參考：
http://msdn2.microsoft.com/en-us/library/Aa933232(SQL.80).aspx
?
TrackBack: http://www.cnblogs.com/126/archive/2007/08/01/839448.html
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
