How do I use OData Expand like a SQL join?
I'm trying to figure out how to accomplish the equivalent of:
select *
from Users u
inner join Comments c on c.UserId = u.Id
where Id = 1569
(table aliases for better sql readability)
...on the StackOverflow OData endpoint. How would this url be constructed? I'm looking at the documentation for Expand at OData.org and I would have thought it'd look something like:
https://odata.sqlazurelabs.com/OData.svc/v0.1/rp1uiewita/StackOverflow/Users?$Expand=Comments&$filter=UserId eq 1569 but isn't right.
In Linq, it would be this (I think), but Join isn't supported:
Users.Where(u=>u.Id==1569).Join(Comments, u=>u.Id, c=>c.UserId, (a,b)=>a.Id==b.UserId)
I don't need to figure this out in Linq strictly, I'm just trying to figure out how to construct the query url. Basically, how can I translate the SQL join predicate to an OData url and do this in one call?
Answer
The right way to do this would be something like:
http://odata.stackexchange.com/stackoverflow/atom/Users(1569)?$expand=Comments
The problem is that there seem to be no users in the data source (don't know why), so the above query will return a 404. But it is the right syntax.
The idea is that if you want information about just one user you "navigate" to it by using the /Users(1569) (the stuff in parethesis is the primary key of the entity set). Then if you also want to include all the comments, you simply add $expand=Comments. If you want just the comments and not the information about the user you can do /Users(1569)/Comments.
Note that the service you used doesn't define navigation properties, so the above won't work as "joins" are not really supported. But the stackexchange odata endpoint does have the navigation properties defined.
Basically the joins are defined on the server/service so that the client doesn't have to know which column is a foreign key to which primary key.
It also helps with data sources which don't use relational databases as their storage, as it doesn't force them to create fake foreign keys.
You can expand down further "layers" of the graph. If the entity returned in the expand also defines further navigation properties, then you can specify a comma-separated list of the navigation properties.
Here's an example for a made-up service, note that this is expanding each customer in the collection, which is similar to a multiple join.
.../Customers?$expand=Orders,OrderDetails