LINQ to SQL语句(7)之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;

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;

语句描述:这一个例子再次回到全部订单都运到其所在城市的客商或未下订单的客商。

Contains

表达:用于剖断集结中是还是不是含有有某大器晚成因素;不延迟。它是对多个系列进行连接操作的。

string[] customerID_Set =
    new string[] { "AROUT", "BOLID", "FISSA" };
var q = (
    from o in db.Orders
    where customerID_Set.Contains(o.CustomerID)
    select o).ToList();

语句描述:查找”AROUT”, “BOLID” 和 “FISSA”
那七个顾客的订单。先定义了七个数组,在LINQ to
SQL中运用Contains,数组中隐含了全部的CustomerID,即重回结果中,全体的CustomerID都在此个会集内。也正是in。
你也能够把数组的定义放在LINQ to SQL语句里。举个例子:

var q = (
    from o in db.Orders
    where (
    new string[] { "AROUT", "BOLID", "FISSA" })
    .Contains(o.CustomerID)
    select o).ToList();

Not Contains则取反:

var q = (
    from o in db.Orders
    where !(
    new string[] { "AROUT", "BOLID", "FISSA" })
    .Contains(o.CustomerID)
    select o).ToList();

1.包含三个对象:

var order = (from o in db.Orders
             where o.OrderID == 10248
             select o).First();
var q = db.Customers.Where(p => p.Orders.Contains(order)).ToList();
foreach (var cust in q)
{
    foreach (var ord in cust.Orders)
    {
        //do something
    }
}

语句描述:那么些例子使用Contain查找哪个客商饱含OrderID为10248的订单。

2.分包八个值:

string[] cities = 
    new string[] { "Seattle", "London", "Vancouver", "Paris" };
var q = db.Customers.Where(p=>cities.Contains(p.City)).ToList();

语句描述:这一个例子使用Contains查找其所在城市为里昂、London、法国首都或布里斯班的客商。