http://introducinglinq.com/blogs/marcorusso/archive/2008/01/14/the-not-in-clause-in-linq-to-sql.aspx
考虑以下代码,它返回在订单表中没有订单的所有客户。这是一个 SQL 查询,可以返回该值。
SELECT *
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] NOT IN (
SELECT [t1].[CustomerID]
FROM [dbo].[Orders] AS [t1]
)
这不是获得所需结果的最快方式(使用NOT EXISTS 是更受欢迎的方式——稍后会详细介绍)。LINQ 提供了一个 Contains 扩展方法,允许编写以下代码。
NorthwindDataContext dc = new NorthwindDataContext();
dc.Log = Console.Out;
var query =
from c in dc.Customers
where !(from o in dc.Orders
select o.CustomerID)
.Contains(c.CustomerID)
select c;
foreach (var c in query) Console.WriteLine( c );
在LINQ to SQL中,查询会被转化为以下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 NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Orders] AS [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]
))
这种方法不仅在语义上等同,而且执行速度更快。以下是打开SET STATISTICS IO后的结果。第一个结果是使用NOT IN子句的手写查询。第二个结果是由LINQ to SQL生成的查询。