28

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?

Factor Mystic
  • 26,279
  • 16
  • 79
  • 95

1 Answers1

17

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
EvilTeach
  • 28,120
  • 21
  • 85
  • 141
Vitek Karas MSFT
  • 13,130
  • 1
  • 34
  • 30
  • 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? – Factor Mystic Oct 13 '10 at 12:11
  • 2
    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. – Vitek Karas MSFT Oct 13 '10 at 18:35
  • 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? – Factor Mystic Oct 15 '10 at 02:40
  • 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. – Vitek Karas MSFT Oct 15 '10 at 06:49