Exists/in/any/all/contains of LINQ to SQL statements (7)

Exists/in/any/all/contains operator

Applicable scenario: Used to determine the elements in the set, further narrowing the scope.

Any

Description: Used to determine whether an element in the collection satisfies a condition; (If the condition is NULL, the collection returns true if it is not empty, otherwise False). There are 2 forms, namely the simple form and the conditional form.

1. Simple form:

Only customers who do not have an order are returned:

var q =

From C in DB. Customers

where!c.orders.any ()

Select C;

The generated SQL statement is:

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. With conditional form:

Return only the categories that have at least one product that is out of stock:

var q =

From C in DB. Categories

where C.products.any (p = p.discontinued)

Select C;

The generated SQL statement is:

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

Description: Used to determine whether all elements in the collection satisfy a condition; 1. With conditional form

var q =

From C in DB. Customers

where C.orders.all (o = o.shipcity = = c.city)

Select C;

Statement Description: This example returns all orders shipped to customers in their city or customers who have not placed an order.

Contains

Description: Used to determine whether an element is contained in a collection; It is a connection operation to two sequences.

string[] Customerid_set =

New string[] {"Arout", "Bolid", "Fissa"};

var q = (

From O in Db. Orders

where Customerid_set.contains (O.customerid)

Select O). ToList ();

Statement Description: Find orders for the three customers "Arout", "Bolid" and "Fissa". An array is defined, using contains in LINQ to SQL, and all the CustomerID are included in the array, that is, all CustomerID are within the set. That is in. You can also place the definition of an array in a LINQ to SQL statement. Like what:

var q = (

From O in Db. Orders

Where (

New string[] {"Arout", "Bolid", "Fissa"})

. Contains (O.customerid)

Select O). ToList ();

The not contains is reversed:

var q = (

From O in Db. Orders

Where! (

New string[] {"Arout", "Bolid", "Fissa"})

. Contains (O.customerid)

Select O). ToList ();

1. Contains an object:

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

}

}

Statement Description: This example uses contain to find which customer contains an order with a OrderID of 10248.

2. Contains multiple values:

string[] Cities =

New string[] {"Seattle", "London", "Vancouver", "Paris"};

var q = db. Customers.where (p=>cities. Contains (p.city)). ToList ();

Statement Description: This example uses contains to find customers in the City of Seattle, London, Paris or Vancouver.