Like operation, a bit as in, but the direction changed. What does that mean. Is that you give a string to find a field in the data that contains the string. Is that the given string is a subset of a field. That's what SQL script says.
Selec * FROM table where id like '%ad% '
Selec * FROM table where id like '%ad '
Selec * FROM table where id like ' ad% '
The% above is the wildcard character, which indicates that the field contains a value, and the location of the unknown is substituted with%. The first one is that the middle section is AD, the two are not clear. The second is the end is ad, the front is not clear. The third opposite, the beginning is ad, the end is not clear. Its corresponding LINQ statement is
var q = (from C in DB. Customers
Where C.customerid.contains ("rout")
Select C). ToList ();
The SQL that 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%]
End with Issa, head matching:
var q = (from C in DB. Customers
where C.customerid.endswith ("ISSA")
Select C). ToList ();
The SQL that 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 matching:
var q = (from C in DB. Customers
where C.customerid.startswith ("ARO")
Select C). ToList ();
The SQL that 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 way, called sqlmethods.like, to add System.Data.Linq.SqlClient namespaces first. The top 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 out the wildcard character and tell LINQ how you matched it. 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 ();
One of the most wonderful places to Sqlmethods.like is that you can achieve a wildcard in any place by your own defined wildcard expression. Like what
var q = (from C in DB. Customers
where Sqlmethods.like (C.customerid, "a%o%t")
Select C). ToList ();
The SQL that 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]
Is the most standard know with a start, with T end, the middle know a value o, the other is what do not know. Just use this.
SQL Server defines four wildcard characters that can be used here. They are:
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 the ' I-Names ' with EAN (Dean, Sean, and).
[] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname like ' [C-p]arsen ' finds author the last names ending with Arsen and beginning with a single character between C and P, for example Carsen, Larsen, Karsen, and.
[^] 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 was not L.
% represents a 0-length or arbitrary-length string. _ Represents a character. [] represents a character that is in a range interval. [^] denotes a character that is not in a range
Like what:
var q = (from C in DB. Customers
where Sqlmethods.like (C.customerid, "a_o_t")
Select C). ToList ();
You use _ to represent a character. Its 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]
As for not, it's also very simple, plus a.
var q = (from C in DB. Customers
Where! Sqlmethods.like (C.customerid, "a_o_t")
Select C). ToList ();
Sqlmethods.like also has an argument called Escape Character, which will be translated into statements similar to 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. It's time to use the escape. This is a matter for SQL Server. For more information, please refer to:
http://msdn2.microsoft.com/en-us/library/Aa933232 (sql.80). aspx