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;

語句描述:這個例子返回所有訂單都運往其所在城市的客戶或未下訂單的客戶。

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 
          !(
              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查找其所在城市為西雅圖、倫敦、巴黎或溫哥華的客戶。

文章標籤
創作者介紹

吉米.NET

Jimmy 發表在 痞客邦 留言(0) 人氣()