跨關聯性查詢Querying Across Relationships

對類別定義中其他物件或其他物件集合的參考,會直接對應到資料庫中的外部索引鍵關聯性。References to other objects or collections of other objects in your class definitions directly correspond to foreign-key relationships in the database. 您可以在使用點標記法進行查詢時使用這些關聯性,進而存取關聯性屬性以及從某個物件巡覽到另一個物件。You can use these relationships when you query by using dot notation to access the relationship properties and navigate from one object to another. 這些存取作業會轉譯成對等 SQL 中更複雜的聯結或相關聯的子查詢 (Subquery)。These access operations translate to more complex joins or correlated subqueries in the equivalent SQL.

例如,下列查詢會從訂單巡覽至客戶,而將查詢結果限制在位於倫敦 (London) 之客戶的訂單。For example, the following query navigates from orders to customers as a way to restrict the results to only those orders for customers located in London.

        Northwnd db = new Northwnd(@"northwnd.mdf");

        IQueryable<Order> londonOrderQuery =
from ord in db.Orders
where ord.Customer.City == "London"
select ord;
    Dim db As New Northwnd("c:\northwnd.mdf")
    Dim londonOrderQuery = _
From ord In db.Orders _
Where ord.Customer.City = "London" _
Select ord

如果關聯性屬性不存在您必須將它們寫入手動聯結,就像您一樣在 SQL 查詢中,如下列程式碼所示:If relationship properties did not exist you would have to write them manually as joins, just as you would do in a SQL query, as in the following code:

        Northwnd db = new Northwnd(@"northwnd.mdf");
        IQueryable<Order> londonOrderQuery =
from cust in db.Customers
join ord in db.Orders on cust.CustomerID equals ord.CustomerID
where cust.City == "London"
select ord;
    Dim db As New Northwnd("c:\northwnd.mdf")
    Dim londOrderQuery = _
From cust In db.Customers _
Join ord In db.Orders On cust.CustomerID Equals ord.CustomerID _
Select ord

您可以使用關聯性屬性來定義這個特定的關聯性一次。You can use the relationship property to define this particular relationship one time. 然後,您就可以使用更為方便的點語法。You can then use the more convenient dot syntax. 但是關聯性屬性的存在更加重要,因為網域指定的物件模型 (Object Model) 通常會定義為階層架構或圖形。But relationship properties exist more importantly because domain-specific object models are typically defined as hierarchies or graphs. 您進行程式設計的物件會具有對其他物件的參考。The objects that you program against have references to other objects. 物件對物件的關聯性會對應到資料庫中的外部索引鍵樣式關聯性,只是純屬巧合。It is only a happy coincidence that object-to-object relationships correspond to foreign-key-styled relationships in databases. 屬性存取則提供了撰寫聯結的簡便方法。Property access then provides a convenient way to write joins.

關於這點,相較於成為查詢本身的一部分,關聯性屬性對於查詢的結果而言更加重要。With regard to this, relationship properties are more important on the results side of a query than as part of the query itself. 在查詢已擷取特定客戶的相關資料後,類別定義會表示客戶具有訂單。After the query has retrieved data about a particular customer, the class definition indicates that customers have orders. 換句話說,您會預期特定客戶的 Orders 屬性成為已填入 (Populate) 該客戶所有訂單的集合。In other words, you expect the Orders property of a particular customer to be a collection that is populated with all the orders from that customer. 事實上,這就是您透過這種方式定義類別而宣告的合約。That is in fact the contract you declared by defining the classes in this manner. 即使查詢並未要求訂單,您仍預期會在此看見訂單。You expect to see the orders there even if the query did not request orders. 您期望物件模型能維持某種假象,那就是資料庫的記憶體中擴充具有立即可用的相關物件。You expect your object model to maintain an illusion that it is an in-memory extension of the database with related objects immediately available.

您目前具有關聯性,因此可藉由參考類別中定義的關聯性屬性,進而撰寫查詢。Now that you have relationships, you can write queries by referring to the relationship properties defined in your classes. 這些關聯性參考會對應至資料庫中的外部索引鍵關聯性。These relationship references correspond to foreign-key relationships in the database. 使用這些關聯性的作業會轉譯成對等 SQL 中更複雜的聯結。Operations that use these relationships translate to more complex joins in the equivalent SQL. 只要您已定義關聯性 (使用 AssociationAttribute 屬性 (Attribute)),就不必在 LINQ to SQLLINQ to SQL 中編寫明確聯結。As long as you have defined a relationship (using the AssociationAttribute attribute), you do not have to code an explicit join in LINQ to SQLLINQ to SQL.

若要協助維持此假象,LINQ to SQLLINQ to SQL會實作名為技術延後載入To help maintain this illusion, LINQ to SQLLINQ to SQL implements a technique called deferred loading. 如需詳細資訊,請參閱 < 延後執行與立即載入For more information, see Deferred versus Immediate Loading.

請考慮下列的 SQL 查詢,以規劃一份CustomerID - OrderID組:Consider the following SQL query to project a list of CustomerID-OrderID pairs:

SELECT t0.CustomerID, t1.OrderID  
FROM   Customers AS t0 INNER JOIN  
          Orders AS t1 ON t0.CustomerID = t1.CustomerID  
WHERE  (t0.City = @p0)  

若要使用 LINQ to SQLLINQ to SQL 取得相同的結果,您可使用已存在於 Orders 類別中的 Customer 屬性參考。To obtain the same results by using LINQ to SQLLINQ to SQL, you use the Orders property reference already existing in the Customer class. Orders參考文件提供執行的查詢與專案所需的資訊CustomerID - OrderID配對,如下列程式碼所示:The Orders reference provides the necessary information to execute the query and project the CustomerID-OrderID pairs, as in the following code:

        Northwnd db = new Northwnd(@"northwnd.mdf");
        var idQuery =
from cust in db.Customers
from ord in cust.Orders
where cust.City == "London"
select new { cust.CustomerID, ord.OrderID };
    Dim db As New Northwnd("c:\northwnd.mdf")
    Dim idQuery = _
From cust In db.Customers, ord In cust.Orders _
Where cust.City = "London" _
Select cust.CustomerID, ord.OrderID

您也可以進行反轉。You can also do the reverse. 也就是說,您可以查詢 Orders,並使用其 Customer 關聯性參考來存取關聯之 Customer 物件的相關資訊。That is, you can query Orders and use its Customer relationship reference to access information about the associated Customer object. 下列程式碼專案相同CustomerID - OrderID配對和以前一樣,但這次,藉由查詢Orders而不是CustomersThe following code projects the same CustomerID-OrderID pairs as before, but this time by querying Orders instead of Customers.

        Northwnd db = new Northwnd(@"northwnd.mdf");
        var idQuery =
from ord in db.Orders
where ord.Customer.City == "London"
select new { ord.Customer.CustomerID, ord.OrderID };
    Dim db As New Northwnd("c:\northwnd.mdf")
    Dim idQuery = _
From ord In db.Orders _
Where ord.Customer.City = "London" _
Select ord.CustomerID, ord.OrderID

另請參閱See also