I'm trying to figure out how to accomplish the equivalent of:
from Users u
inner join Comments c on c.UserId = u.Id
where Id = 1569
https://odata.sqlazurelabs.com/OData.svc/v0.1/rp1uiewita/StackOverflow/Users?$Expand=Comments&$filter=UserId eq 1569
Users.Where(u=>u.Id==1569).Join(Comments, u=>u.Id, c=>c.UserId, (a,b)=>a.Id==b.UserId)
The right way to do this would be something like:
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
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.