如何像SQL连接一样使用OData Expand? [英] 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

(表别名可提高sql的可读性)

...在StackOverflow OData端点上.该网址将如何构造?我正在OData.org和我在 Expand 的文档中找到会以为它看起来像:

...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但不正确.

在Linq中，应该是这样(我认为)，但是不支持Join:

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)

我不需要严格地在Linq中弄清楚这一点，我只是想弄清楚如何构造查询网址.基本上，如何将SQL连接谓词转换为OData URL并在一次调用中完成?

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?

推荐答案

正确的方法是:

http://odata.stackexchange.com/stackoverflow/atom/Users(1569)?$expand=Comments

问题在于数据源中似乎没有用户(不知道为什么)，因此上述查询将返回404.但这是正确的语法.

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.

的想法是，如果您只想获得有关一个​​用户的信息，则可以使用/Users(1569)导航"到该用户(括号中的内容是实体集的主键).然后，如果您还想包含所有注释，只需添加$expand=Comments.如果只需要注释而不是有关用户的信息，则可以执行/Users(1569)/Comments.

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.

请注意，您使用的服务未定义导航属性，因此上述内容不能真正用作"joins".但是stackexchange odata端点确实定义了导航属性.

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

这篇关于如何像SQL连接一样使用OData Expand?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！