当前位置: 首页 > 编程语言 > .NET综合 > 正文

LINQ to SQL语句(7)之Exists/In/Any/All/Contains

时间:2010-12-11 博客园 李永京

Exists/In/Any/All/Contains操作符

适用场景:用于判断集合中元素 ,进一步缩小范围。

Any

说明:用于判断集合中是否有元素满足 某一条件;不延迟。(若条件为空,则集合只要不为空就返回True,否则为 False)。有2种形式,分别为简单形式和带条件形式。

1.简单形式:

仅返回没有订单的客户:

var q =
  from c in db.Customers
  where !c.Orders.Any()
  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 NOT (EXISTS(
  SELECT NULL AS [EMPTY] FROM [dbo].[Orders] AS [t1]
  WHERE [t1].[CustomerID] = [t0]. [CustomerID]
  ))

2.带条件形式:

仅返回至少有 一种产品断货的类别:

var q =
  from c in db.Categories
  where c.Products.Any(p => p.Discontinued)
  select c;

生成SQL语句为:

SELECT [t0]. [CategoryID], [t0].[CategoryName], [t0].[Description],
[t0]. [Picture] FROM [dbo].[Categories] AS [t0]
WHERE EXISTS(
   SELECT NULL AS [EMPTY] FROM [dbo].[Products] AS [t1]
  WHERE ([t1].[Discontinued] = 1) AND
  ([t1].[CategoryID] = [t0]. [CategoryID])
  )
All
说明:用于判断集合中所有元素是 否都满足某一条件;不延迟1.带条件形式

var q =
  from c in db.Customers
  where c.Orders.All(o => o.ShipCity == c.City)
  select c;

语句描述:这个例子返回所有订单 都运往其所在城市的客户或未下订单的客户。