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

Source: Internet
Author: User

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 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 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

Description: Used to determine whether all elements in the set satisfy a certain condition;

1. With conditional form
var q = from 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. For example:

var q = (    from in  db. Orders    where  (    newstring"arout"  "Bolid" "fissa"  })    . Contains (O.customerid)    select o). ToList ();

The not contains is reversed:

var q = (    from in  db. Orders    where ! (    newstring"arout""bolid  ""fissa " })    . Contains (O.customerid)    select o). ToList ();

1. Contains an object:

varOrder = ( fromOinchdb. OrderswhereO.orderid = =10248             Selecto). First ();varQ = db. Customers.where (p =p.orders.contains (Order)). ToList ();foreach(varCustinchq) {    foreach(varOrdinchCust. 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 =     Newstring"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.

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.