WHERE [t1].[ContactName] LIKE @p1 ) AS [t2] ) AS [t3]
ORDER BY [t3].[ContactName]
-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [%A%] -- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [A%] 取相交项
描述：查询城市是A打头的顾客和城市包含A的顾客的交集，并按照顾客名字排序 查询句法：
var 取相交项 = (from c in ctx.Customers where c.City.Contains(\
(from c in ctx.Customers where c.ContactName.StartsWith(\
对应SQL：
CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[C
], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)
ec = 0; Scale = 0) [A%] ec = 0; Scale = 0) [%A%]
排除相交项
描述：查询城市包含A的顾客并从中删除城市以A开头的顾客，并按照顾客名字排序 查询句法：
var 排除相交项 = (from c in ctx.Customers where c.City.Contains(\
(from c in ctx.Customers where c.ContactName.StartsWith(\
对应SQL：
SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax] FROM (
SELECT DISTINCT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax] FROM [dbo].[Customers] AS [t0] ) AS [t1]
WHERE (NOT (EXISTS( SELECT NULL AS [EMPTY] FROM [dbo].[Customers] AS [t2]
WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)
))) AND ([t1].[City] LIKE @p1) ORDER BY [t1].[ContactName]
-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]
-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%] 子查询
描述：查询订单数超过5的顾客信息 查询句法：
var 子查询 = from c in ctx.Customers where
(from o in ctx.Orders group o by o.CustomerID into o where o.Count() > 5 select o.Key).Contains(c.CustomerID) select c;
对应SQL：
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0] WHERE EXISTS(
SELECT NULL AS [EMPTY] FROM (
SELECT COUNT(*) AS [value], [t1].[CustomerID] FROM [dbo].[Orders] AS [t1] GROUP BY [t1].[CustomerID] ) AS [t2]
WHERE ([t2].[CustomerID] = [t0].[CustomerID]) AND ([t2].[value] > @p0) )
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5] in操作
描述：查询指定城市中的客户 查询句法：
var in操作 = from c in ctx.Customers
where new string[] { \ select c; 对应SQL：
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax] FROM [dbo].[Customers] AS [t0]
WHERE [t0].[City] IN (@p0, @p1, @p2)
-- @p0: Input String (Size = 11; Prec = 0; Scale = 0) [Brandenburg] -- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [Cowes] -- @p2: Input String (Size = 7; Prec = 0; Scale = 0) [Stavern] join
描述：内连接，没有分类的产品查询不到 查询句法：
var innerjoin = from p in ctx.Products join c in ctx.Categories
on p.CategoryID equals c.CategoryID select p.ProductName; 对应SQL：
SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0]
INNER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID]) 描述：外连接，没有分类的产品也能查询到 查询句法：
var leftjoin = from p in ctx.Products join c in ctx.Categories
on p.CategoryID equals c.CategoryID into pro
from x in pro.DefaultIfEmpty() select p.ProductName; 对应SQL：
SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID]) 你可能会很奇怪，原先很复杂的SQL使用查询句法会很简单(比如按照条件分组)。但是原先觉得很好理解的SQL使用查询句法会觉得很复杂(比如连接查询)。其实，我们还可以通过其它方式进行连接操作，在以后说DataLoadOptions类型的时候会再说。虽然Linq to sql已经非常智能了，但是对于非常复杂的查询还是建议通过存储过程实现，下次讲解如何调用存储过程。
一步一步学Linq to sql（五）：存储过程
2007-10-20 00:00 作者： LoveCherry 出处： 天极网 责任编辑：>dizzarz 普通存储过程
首先在查询分析器运行下面的代码来创建一个存储过程：
create proc sp_singleresultset as
set nocount on
select * from customers