Returning back to that list that I talked about in a previous post, we had

ANYELEMENT,CREATEREF, DEREF, IS OF, KEY, MULTISET, NAVIGATE, OFTYPE, OVERLAPS, Projection (including here as I've not yet figured out what it is :-)), REF, ROW , SELECT (including here because it has some weirdness like VALUE), SET, TREAT, USING

For this post, let's take a look at;

NAVIGATE

We've seen this already really.It looks to take the form of;

NAVIGATE( instance-expression, relationship-type, to-end, from-end )

but you can shorten it down by missing off the last 2 parameters if they are unambiguous.

What you get back as the result of a NAVIGATE is a collection(REF) so you've got a number of REFs rather than actual entities. So, we can query with something like this;

"select navigate(c, Northwind.FK_Orders_Customers, Orders, Customers) from NorthwindContext.Customers as c"

Now, I have to say that I found that syntax counter-intuitive in that the to end of the relationship is listed before the from end of the relationship. I expect to list from->to, not to->from but there you go. The shorter syntax makes it easier;

"select navigate(c, Northwind.FK_Orders_Customers) from NorthwindContext.Customers as c"

Now, I got quite puzzled as to what comes back from these queries. In so far as I can tell you get rows that look like;

row ( multiset ( ref ( Northwind.Order ) ) )

and if you were to do something like;

"select value navigate(c, Northwind.FK_Orders_Customers) from NorthwindContext.Customers as c"

then I think you get rows that look like;

multiset ( ref ( Northwind.Order ) )

Not 100% sure but I think that's what you get. Now, if you take another (way more obvious) route and use a navigation property then you'd write something like;

"select value c.orders from NorthwindContext.Customers as c"

and I think what you get then is;

multiset ( Northwind.Order )

I spent quite a bit of time (i.e. hours) trying to figure out how to get the navigate syntax to give me the equivalent result-set and I think, in the end I figured it out as something like;

select
    value deref(o)
from
    NorthwindContext.Customers as c,
    navigate(c, Northwind.FK_Orders_Customers) as o

That is, I think that "magic" comes from using navigate as part of a cross-product here in the from clause - I'll post more on that at a later point as/when I figure it out better.