NAVIGATE (Entity SQL)
Navigates over the relationship established between entities.
An instance of an entity.
The type name of the relationship, from the conceptual schema definition language (CSDL) file. The relationship-type is qualified as <namespace>.<relationship type name>.
The end of the relationship.
The beginning of the relationship.
Relationships are first-class constructs in the Entity Data Model (EDM). Relationships can be established between two or more entity types, and users can navigate over the relationship from one end (entity) to another. from and to are conditionally optional when there is no ambiguity in name resolution within the relationship.
NAVIGATE is valid in O and C space.
The general form of a navigation construct is the following:
navigate(instance-expresssion, relationship-type, [ to-end [, from-end ] ] )
Select o.Id, navigate(o, OrderCustomer, Customer, Order) From LOB.Orders as o
Where OrderCustomer is the relationship, and Customer and Order are the to-end (customer) and from-end (order) of the relationship. If OrderCustomer was a n:1 relationship, then the result type of the navigate expression is Ref<Customer>.
The simpler form of this expression is the following:
Select o.Id, navigate(o, OrderCustomer) From LOB.Orders as o
Similarly, in a query of the following form, The navigate expression would produce a Collection<Ref<Order>>.
Select c.Id, navigate(c, OrderCustomer, Order, Customer) From LOB.Customers as c
The instance-expression must be an entity/ref type.
The following Entity SQL query uses the NAVIGATE operator to navigate over the relationship established between Address and SalesOrderHeader entity types. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:
Follow the procedure in How to: Execute a Query that Returns StructuralType Results (EntityClient).
Pass the following query as an argument to the
SELECT address.AddressID, (SELECT VALUE DEREF(soh) FROM NAVIGATE(address, AdventureWorksModel.FK_SalesOrderHeader_Address_BillToAddressID) AS soh) FROM AdventureWorksEntities.Address AS address
This example produces the following output:
AddressID: 1 AddressID: 2 AddressID: 3 AddressID: 4 AddressID: 5