The operation of Like is a bit like in, but the direction has changed. What does it mean? You give a string and look for a field in the data that contains this string. That is, a given string is a subset of a field. Sql scrip{filter}t is written like this.
Selec * from table where id like '%AD%'
Selec * from table where id like '%AD'
Selec * from table where id like 'AD%'
The % above is a wildcard, indicating that the field contains a value, and the unknown position is replaced by %. The first is to indicate that the middle section is AD, and the two ends are unclear. The second is AD at the end, and the previous one is unclear. The third, on the contrary, begins with AD and ends unclearly. The corresponding Linq statement is:
var q = (from c in db. Customers
where c.CustomerID.Contains("ROUT")
select c). ToList();
The SQL it generates is
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%]
Ending with ISSA, head wildcard:
var q = (from c in db. Customers
where c.CustomerID.EndsWith("ISSA")
select c). ToList();
The SQL it generates is
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]
Starting with ARO, tail wildcard:
var q = (from c in db. Customers
where c.CustomerID.StartsWith("ARO")
select c). ToList();
The SQL it generates is
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 also provides a method called SqlMethods.Like, which requires the System.Data.Linq.SqlClient namespace to be added first. The above three can be written
var q = (from c in db. Customers
where SqlMethods.Like(c.CustomerID, "%ROUT%")
select c). ToList();
Here, you need to fill in the wildcard yourself to tell Linq how you matched. Like what
var q = (from c in db. Customers
where SqlMethods.Like(c.CustomerID, "%ISSA")
select c). ToList();
Another example:
var q = (from c in db. Customers
where SqlMethods.Like(c.CustomerID, "ARO%")
select c). ToList();
The most amazing thing about SqlMethods.Like is that you can implement wildcards anywhere with your own defined wildcard expressions. Like what
var q = (from c in db. Customers
where SqlMethods.Like(c.CustomerID, "A%O%T")
select c). ToList();
The SQL it generates is
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]
The most standard thing is to know that it starts with A and ends with T, knows a value O in the middle, and doesn't know anything else. Just use this.
SQL Server defines four wildcards, all of which are available here. They are:
Wildcard character Descrip{filter}tion 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.
% represents a string of zero length or any length. _ indicates a character. [] indicates a character in a range range. [^] indicates a character that is not in a certain range
Like what:
var q = (from c in db. Customers
where SqlMethods.Like(c.CustomerID, "A_O_T")
select c). ToList();
Just use _ to represent a character. The generated SQL is:
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]
For Not Like, it's also very simple, just add a nonsense.
var q = (from c in db. Customers
where ! SqlMethods.Like(c.CustomerID, "A_O_T")
select c). ToList();
SqlMethods.Like also has a parameter called escape character, which will be translated into a statement like the following.
SELECT columns FROM table WHERE
column LIKE '%\%%' ESCAPE '\'
escape is because a field contains special characters, such as %,_ [ ], which are used as wildcards. That's where Escape comes in. This is a SQL Server thing.
|
Posted on 9/7/2015 11:16:28 PM
|
|
|
