Linq to SQL Like Operator(×ªÔØ)
As a response for customer's question, I decided to write about using Like Operator in Linq to SQL queries.
Starting from a simple query from Northwind Database;
var query = from c in ctx.Customers
where c.City == "London"
select c;
The query that will be sent to the database will be:
SELECT CustomerID, CompanyName, ...
from dbo.Customers
WHERE City = [London]
There are some ways to write a Linq query that reaults in using Like Operator in the SQL statement:
1. Using String.StartsWith or String.Endswith
Writing the following query:
var query = from c in ctx.Customers
where c.City.StartsWith("Lo")
select c;
will generate this SQL statement:
SELECT CustomerID, CompanyName, ...
from dbo.Customers
WHERE City LIKE [Lo%]
which is exactly what we wanted. Same goes with String.EndsWith.
But, what is we want to query the customer with city name like "L_n%"? (starts with a Capital 'L', than some character, than 'n' and than the rest of the name). Using the query
var query = from c in ctx.Customers
where c.City.StartsWith("L") && c.City.Contains("n")
select c;
generates the statement:
SELECT CustomerID, CompanyName, ...
from dbo.Customers
WHERE City LIKE [L%]
AND City LIKE [%n%]
which is not exactly what we wanted, and a little more complicated as well.
2. Using SqlMethods.Like method
Digging into System.Data.Linq.SqlClient namespace, I found a little helper class called SqlMethods, which can be very usefull in such scenarios. SqlMethods
Ïà¹ØÎĵµ£º
ÉÁ»Ø
1 ÌØÕ÷
ÉÁ»ØÊý¾Ý¿âÖ»ÊÇÂß¼Êý¾ÝÔâµ½ÆÆ»µÊ±µÄÊֶΡ£
ÉÁ»ØÊý¾Ý¿âÈÕÖ¾±ØÐëÔÚÊý¾Ý¿â»Ö¸´ÇøÖд´½¨¡£
ÉÁ»ØÈÕÖ¾²»ÐèÒª¹éµµ£¬²»ÄÜÓÃÓÚÎïÀí»Ö¸´¡£
Ö´ÐÐÉÁ»ØÊý¾Ý¿â²Ù×÷ʱ£¬±³¾°½ø³ÌRVWR¾ÍÆô¶¯ÁË¡£
2 ʹÓÃÉÁ»ØÊý¾Ý¿â
3ÖÖ¹¤¾ß£¬SQL*Plus£¬RMAN£¬OEM¡£
£¨1£©ÒªÇó
ΪÁËʹÓÃÉÁ»ØÊý¾Ý¿â£¬Êý¾Ý¿â±ØÐë´¦Óڹ鵵ÈÕ־ģʽ¡ ......
SELECT UPPER(F.TABLESPACE_NAME) "±í¿Õ¼äÃû",
D.TOT_GROOTTE_MB "±í¿Õ¼ä´óС(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "ÒÑʹÓÿռä(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_G ......
ÓÃJavaÁ¬½ÓSQL Server2000Êý¾Ý¿âÓжàÖÖ·½·¨£¬ÏÂÃæ½éÉÜÆäÖÐ×î³£ÓõÄÁ½ÖÖ£¨Í¨¹ýJDBCÇý¶¯Á¬½ÓÊý¾Ý¿â£©¡£
1. ͨ¹ýMicrosoftµÄJDBCÇý¶¯Á¬½Ó¡£´ËJDBCÇý¶¯¹²ÓÐÈý¸öÎļþ£¬·Ö±ðÊÇmssqlserver.jar¡¢msutil.jarºÍmsbase.jar£¬¿ÉÒÔµ½Î¢ÈíµÄÍøÕ¾È¥ÏÂÔØ£¨http://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-B ......
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
Ò²¿ÉÒÔʹÓÃÁªºÏÖ÷¼ü id+id2+id3+……
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º
SELECT TOP 10 *
from TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
&nb ......
SQL ServerÓû§×Ô¶¨Ò庯ÊýºÍ´æ´¢¹ý³ÌÓÐÀàËÆµÄ¹¦ÄÜ£¬¶¼¿ÉÒÔ´´½¨À¦°óSQLÓï¾ä£¬´æ´¢ÔÚserverÖй©ÒÔºóʹÓá£ÕâÑùÄܹ»¼«´óµØÌá¸ß¹¤×÷ЧÂÊ£¬Í¨¹ýÒÔϵĸ÷ÖÖ×ö·¨¿ÉÒÔ¼õÉÙ±à³ÌËùÐèµÄʱ¼ä£º
¡¡¡¡ 1 ÖØ¸´Ê¹Óñà³Ì´úÂ룬¼õÉÙ±à³Ì¿ª·¢Ê±¼ä¡£
¡¡¡¡ 2 Òþ²ØSQLϸ½Ú£¬°ÑSQL·±ËöµÄ¹¤×÷Áô¸øÊý¾Ý¿â¿ª·¢ÈËÔ±£¬¶ø³ÌÐò¿ª·¢Ô±Ôò¼¯Öд¦Àí¸ß¼¶±à³Ì ......