问题描述
我有一个oData模型，它具有一对多关系，例如person->addresses和个人驾驶许可证.我希望能够根据地址实体和驾驶执照实体中的属性对结果集进行排序.由于可能有多个地址，因此我最初会基于称为IsPrimary的属性从地址集中选择一个项目.由于可能会有一个以上的驾驶执照，我将选择英国"驾驶执照.这可能吗?
I have an oData model with a couple of one-to-many relationship, say person->addresses and person->driving-licences. I would like to be able to sort the result set based on properties in the address entity and driving licence entity. As there could be more than one address, I would initially select a single item from the addresses set, based on a property called IsPrimary. As there could be more that one driving licence, I would select the 'UK' driving licence. Is this possible?
我希望我可以做类似的事情:
I was hoping I could do something like:
/people?$expand=addresses($filter=isPrimary eq true),drivinglicences($filter=country eq 'UK')&$orderby=addresses/postcode,drivinglicences/active
不幸的是，我收到以下错误消息:
Unfortunately I get the following error:
在URI中指定的查询无效.属性'isPrimary'的属性访问的父值不是单个值.属性访问只能应用于单个值."
"The query specified in the URI is not valid. The parent value for a property access of a property 'isPrimary' is not a single value. Property access can only be applied to a single value."
有人知道该规范是否支持我要执行的操作吗?还是我的查询有问题?还是.NET库是否有问题.
Does anyone know if what I'm trying to do is supported by the spec? Or whether it is an issue with my query? Or whether it is an issue with the .NET library.
我正在使用:Microsoft.AspNet.OData-7.2.3
I'm using:Microsoft.AspNet.OData - 7.2.3
非常感谢.
推荐答案
您在这里看到的是设计使然，或者不受规范支持，错误消息甚至突出显示了受支持的唯一表达式类型:
What you see here is by design, or rather not supported by the specification, the error message even highlights the only type of expressions supported:
因此，最简单的解决方案是修改API，使其包含绑定到直接应用$filter或$order的people集合的 Function 或 Function 以新的形状返回数据，该形状可能仅具有单例PrimaryAddress属性.如何在此结果中包括驾驶执照取决于您，它甚至可以是功能的参数，也许您的人员控制器具有带有此签名的可查询功能:
So the simplest solution is to modify the API either to include a Function bound to the people collection that applies the $filter or $order directly, or a Function that returns the data in a new shape, one that only has perhaps a singleton PrimaryAddress property. How you include driving license in this result is up to you, it could even be a parameter to the function, perhaps your people controller has a queryable function with this signature:
[EnableQuery]
public IHttpActionResult WithLicences(string countryCode)
但是，这超出了OP关于特定语法支持的问题
However that is out of the scope of OPs question about specific syntax support
尽管它似乎是一项重要功能，但我们必须记住，$select( Projection )和$filter是在不同的时间点进行评估的，随后是 OData 查询与 SQL 相似的执行顺序，但是分别对过滤条件和$ orderby进行评估，结果集的投影是最后要应用的评估.
Although it seems like an important feature, we must remember that $select (Projection) and $filter are evaluated at different points in time, OData queries follow a similar execution sequence to SQL however the filter criteria and $orderby are evaluated separately, and the projection of the resultset is the last evaluation to be applied.
- 由于$ filter和$ orderby是独立应用的，所以这两个概念都不知道另一个，因此，任何一个都不能引用或假定先于另一个应用.
您可以通过指定$orderby和/或$filter中不包含在$select中的字段来证明这一点，甚至可以引用$expand和查询将正确评估.
You can prove this by specifying a field in the $orderby and/or $filter that is not included in the $select, you can even reference singleton navigation fields that are not included in an $expand and the query will evaluate correctly.
通过允许特殊的自定义功能的应用，作者鼓励API设计人员向其资源端点提供自然扩展，以方便执行可能非常复杂或不确定的预定查询.纯OData查询语法难以表达.
By allowing special provision of custom functions to be applied the authors are encouraging API designers to provide natural extensions to their resource endpoints that can facilitate the execution of pre-determined queries that may be otherwise complex or problematic to express in pure OData query syntax.
要在纯 SQL 中实现OPs类型的查询，仍然需要嵌套查找，CTE或自连接...高级语法.在OData v4中，该规范未提供针对路径表达式($orderby派生自)的集合中的特定项的语法.
To achieve OPs type of query in pure SQL would still require either a nested lookup, CTE or self join... advanced syntax. In OData v4, the specification does not provide a syntax for targeting specific items within a collection for path expressions (of which $orderby derives from)
可以通过与资源路径中相同的语法来使用复杂属性的属性，即通过指定复杂属性的名称，后跟正斜杠(/)和复杂属性的属性名称，等等.上，
Properties of complex properties can be used via the same syntax as in resource paths, i.e. by specifying the name of a complex property, followed by a forward slash (/) and the name of a property of the complex property, and so on,
与目标基数0..1或1相关的实体的属性和导航属性可通过指定导航属性，后跟正斜杠(/)和相关实体的属性名称来使用上.
Properties and navigation properties of entities related with a target cardinality 0..1 or 1 can be used by specifying the navigation property, followed by a forward slash (/) and the name of a property of the related entity, and so on.
如果复杂属性为null，或者没有实体关联(在目标基数为0..1的情况下)，则其值及其组成部分的值将被视为null.
If a complex property is null, or no entity is related (in case of target cardinality 0..1), its value, and the values of its components, are treated as null.
RE :我在规范中找不到任何明确的内容. :)
RE: I couldn't find anything explicit in the spec. :)
关于OData规范，这就是事实，该规范未列出不支持的内容，仅列出了应支持的内容.因此，可以忽略的是，如果找不到有关如何执行 something 的参考，则不需要支持该 something .
That is the very thing about the OData specification,the specification does not list what is not supported, only what should be supported. So by omission, if you cannot find a reference to how to do something, then that something is not required to be supported.
这是在5月线程中进行的持续讨论，最近是 https://stackoverflow.com/a/55324393/1690217许多人抱怨这无疑是数据访问平台的基本功能，但是重要的是要尊重OData平台的初衷，并通过提供适合我们业务领域的定制端点来使我们的API保持简单.
This has been on ongoing discussion held in may threads, recently https://stackoverflow.com/a/55324393/1690217 Many people complain that this is surely a fundamental feature of a data access platform, however it is important to respect the original intent of the OData platform and keep our APIs simple by providing customised endpoints to suit our business domain.
这篇关于oData v4-在相关的一对多实体中按属性对外部实体进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！