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
Ïà¹ØÎĵµ£º
RepeaterÔÚǰ̨ʹÓñȽÏÁé»î×ÔÓÉ£¬µ«ÓÐÒ»¸öÎÊÌâ¾ÍÊÇRepeater²»Ö§³ÖÖ±½Ó·ÖÒ³£¬Õâ¸öºÜ¶àÈË¿´ÆðÀ´¾ÍÓе㲻ÏëÓÃÁË£¬µ«ÎÒÏë´ó¼Ò¶¼ÖªµÀGridView¿Ø¼þ»òDataGrid¿Ø¼þÔÚÆôÓÃ×Ô´ø·ÖÒ³µÄʱºòÆäʵЧÂÊÊǷdz£µÍµÄ£¬´óµÄ²»Ëµ£¬Ò»µ«µ½Á˰ÙÍò¼¶Êý¾ÝÒԺ󣬾ͻá¸Ð¾õÊǶàôµÄÍ´¿àºÍÎÞÄÍÁË£¬ËùÒÔ¼´Ê¹ÊÇÓÃDataGrid(GridView)¿Ø¼þ£¬¸ßÊÖÃÇ»¹ÊÇÖ»»á ......
Ò»¡¢±¸·ÝÊý¾Ý¿â
1¡¢´ò¿ªSQLÆóÒµ¹ÜÀíÆ÷£¬ÔÚ¿ØÖÆÌ¨¸ùĿ¼ÖÐÒÀ´Îµã¿ªMicrosoft SQL Server
2¡¢SQL Server×é-->Ë«»÷´ò¿ªÄãµÄ·þÎñÆ÷-->Ë«»÷´ò¿ªÊý¾Ý¿âĿ¼
3¡¢Ñ¡ÔñÄãµÄÊý¾Ý¿âÃû³Æ£¨ÈçÂÛ̳Êý¾Ý¿âForum£©-->È»ºóµãÉÏÃæ²Ëµ¥ÖеŤ¾ß-->Ñ¡Ôñ±¸·ÝÊý¾Ý¿â
4¡¢±¸·ÝÑ¡ÏîÑ¡ÔñÍêÈ«±¸·Ý£¬Ä¿µÄÖеı¸·Ýµ½Èç¹ûÔÀ´Óз¾¶ºÍÃû³ ......
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
¡¡¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º ......
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[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 ......
ms sqlÖеÄintÐÍĬÈÏÊÇ4룬mysqlµÄintÐÍĬÈÏÊÇ11λ£¬Ç°Õßµ½ºóÕßµÄÒÆÖ²²»»áÓÐʲôÒþ»¼£¬µ«ÊǺóÕßµ½Ç°ÕßµÄÒÆÖ²¾Í´æÔÚλÊý²»¹»£¬²»ÄÜ´æ´¢´óÊýµÄÒþ»¼¡£
»¹ÓÐbigint£¬ms sqlÊÇ8룬mysqlĬÈÏÊÇ20λ¡£ ......