How do I use OData Expand like a SQL join?

Question

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?

Cloakanddagger

This appears to have been Stack Overflow's 1 millionth question, congratulations! – Manger

Well, if you count questions that weren't deleted up until the time this question was asked. – Aschim

@Manger I submitted a millisecond after the actual millionth question, so I guess this is 1e6+1 – Cloakanddagger

I took the "total questions" number and then counted backwards, but apparently that's not accurate... :( – Manger

Answer 1

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

Roundish

How does this url syntax express the relationship between Users.Id and Comments.UserId? How do those fields know about each other? Is this relationship identified server side? – Cloakanddagger

In OData there are no "joins" like in SQL server. Relationships are represented as so called "navigation" properties. Basically properties with a value of one or multiple entities (or links to those entities). So yes, if the data is backed by a SQL table, the exact join needs to be defined on the server. I updated the reply above to note that the sample model didn't have the navigation properties in it, but the one on stackexchange actually does have those. – Roundish

I'm concerned by your additional note, does the success or failure of this query rely on the implementation of OData? Is this query not guaranteed by the spec? – Cloakanddagger

The spec doesn't define the model used by the service. It's the choice of the service author to implement navigation properties or not. The OData in general assumes that if the author of the service wants the clients to be able to issue interesting queries, the navigation properties will be defined appropriately. – Roundish