OData可以通过形如http://localhost/Products?$orderby=Name这样的QueryString传递查询条件、排序等。你可以在任何Web API Controller中启用OData查询条件,并且不需要让Controller设置为OData的终结点(EndPoint),以便支持过滤、排序。
启用查询选项前,你应该看下这篇文档——OData安全指导.
启用OData查询选项
Web API支持下列的查询选项:
选项 | 说明 |
$expand | 展开关联的内嵌实体 |
$filter | 通过等式过滤查询条件(请注意:查询时区分大小写的) |
$inlinecount | 告诉服务器需要返回的记录数(服务器分页)。 |
$orderby | 排序 |
$select | 要选择的属性字段 |
$skip | 跳过N条记录 |
$top | 只返回最开始的N条记录 |
要使用OData查询条件,需要显示的启用。可以对整个Web应用程序进行配置,或者只指定特定的Controller或者Action。
全局配置,可以在global.ascx中修改,这样任意一个返回类型为IQueryable的方法都会被匹配。
1: public static class WebApiConfig
2: {
3: public static void Register(HttpConfiguration config)
4: {
5: config.Routes.MapHttpRoute(
6: name: "DefaultApi",
7: routeTemplate: "api/{controller}/{id}",
8: defaults: new { id = RouteParameter.Optional }
9: );
10:
11: config.Formatters.JsonFormatter.AddQueryStringMapping("$format", "json", "application/json");
12:
13: config.Formatters.XmlFormatter.AddQueryStringMapping("$format", "xml", "application/xml");
14:
15: config.EnableQuerySupport();
16: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
如果不需要这么处理,可以在方法或者Controller上标识。
1: public class ProductsController : ApiController
2: {
3: [Queryable]
4: IQueryable<Product> Get() {}
5: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
查询范例
客户端驱动的分页
对于一个大数据集,客户端每次希望返回10条记录,在点击“Next”时获取下一页。请求的URI如下:
http://localhost/Products?$top=10&$skip=20
过滤
查询选项就是通过等式进行匹配,可以包含逻辑和算术操作符,String和时间函数。
返回Category为Toys的产品 | http://localhost/Products?$filter=Category eq 'Toys' |
返回所有价格小于10的产品列表 | http://localhost/Products?$filter=Price lt 10 |
返回价格在5到15之间的所有产品列表 | http://localhost/Products?$filter=Price ge 5 and Price le 15 |
返回名称中包含zz的产品列表 | http://localhost/Products?$filter=substringof('zz',Name) |
返回2005年以后发布的产品列表 | http://localhost/Products?$filter=year(ReleaseDate) gt 2005 |
排序
对数据进行排序。
按价格排序 | http://localhost/Products?$orderby=Price |
按价格降序排列 | http://localhost/Products?$orderby=Price desc |
先按照category排序,然后根据价格降序排列 | http://localhost/odata/Products?$orderby=Category,Price desc |
服务器端驱动分页
有时候为了对API接口进行访问,可以主动在服务器端进行分页限制。要启用服务器端分页,在Queryable属性中添加参数PageSize。
1: [Queryable(PageSize=10)]
2: public IQueryable<Product> Get()
3: {
4: return products.AsQueryable();
5: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
经过上述设置以后,返回的消息体中应该类似于下面。其中包含了nextpage的链接,客户端可以通过该链接获取下一页。
1: {
2: "odata.metadata":"http://localhost/$metadata#Products",
3: "value":[
4: { "ID":1,"Name":"Hat","Price":"15","Category":"Apparel" },
5: { "ID":2,"Name":"Socks","Price":"5","Category":"Apparel" },
6: // Others not shown
7: ],
8: "odata.nextLink":"http://localhost/Products?$skip=10"
9: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
如果希望得到数据库的总记录数,那么应该包含$inlinecount条件,类似于这样http://localhost/Products?$inlinecount=allpages。
1: {
2: "odata.metadata":"http://localhost/$metadata#Products",
3: "odata.count":"50",
4: "value":[
5: { "ID":1,"Name":"Hat","Price":"15","Category":"Apparel" },
6: { "ID":2,"Name":"Socks","Price":"5","Category":"Apparel" },
7: // Others not shown
8: ]
9: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
下一页链接和总记录数都需要使用OData格式。对于不是此种格式,则需要将查询结果包装在一个PageResult<T>对象中。因此需要多增加一些代码。
1: public PageResult<Product> Get(ODataQueryOptions<Product> options)
2: {
3: ODataQuerySettings settings = new ODataQuerySettings()
4: {
5: PageSize = 5
6: };
7:
8: IQueryable results = options.ApplyTo(_products.AsQueryable(), settings);
9:
10: return new PageResult<Product>(
11: results as IEnumerable<Product>,
12: Request.GetNextPageLink(),
13: Request.GetInlineCount());
14: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
返回结果应该如下:
1: {
2: "Items": [
3: { "ID":1,"Name":"Hat","Price":"15","Category":"Apparel" },
4: { "ID":2,"Name":"Socks","Price":"5","Category":"Apparel" },
5:
6: // Others not shown
7:
8: ],
9: "NextPageLink": "http://localhost/api/values?$inlinecount=allpages&$skip=10",
10: "Count": 50
11: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
限制查询条件
查询选项给予客户端对于服务器端查询的掌控入口,在一些条件下,处于安全和性能的理由,我们希望对可用的选项进行限制,此时需要利用Queryable属性进行处理。如果只允许使用skip和top,选项设置如下:
1: [Queryable(AllowedQueryOptions= AllowedQueryOptions.Skip | AllowedQueryOptions.Top)]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
如果要对排序字段进行限制(如非排序字段),应该如下:
1: [Queryable(AllowedOrderByProperties="Id")] //英文逗号分隔
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
在过滤条件中,只允许使用eq操作符,格式是这样:
1: [Queryable(AllowedLogicalOperators=AllowedLogicalOperators.Equal)]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
不允许启用算术操作符,则应该如下:
1: [Queryable(AllowedArithmeticOperators=AllowedArithmeticOperators.None)]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
同样,这种限制还可以对全局网站应用程序启用。
1: var queryAttribute = new QueryableAttribute()
2: {
3: AllowedQueryOptions = AllowedQueryOptions.Top | AllowedQueryOptions.Skip,
4: MaxTop = 100
5: };
6:
7: config.EnableQuerySupport(queryAttribute);
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
直接调用查询选项
如果不使用Queryable标签,你还可以直接在Controller中调用查询选项。
1: public IQueryable<Product> Get(ODataQueryOptions opts)
2: {
3: var settings = new ODataValidationSettings()
4: {
5: // Initialize settings as needed.
6: AllowedFunctions = AllowedFunctions.AllMathFunctions
7: };
8:
9: opts.Validate(settings);
10:
11: IQueryable results = opts.ApplyTo(products.AsQueryable());
12: return results as IQueryable<Product>;
13: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Web API通过URI查询条件来操作ODataQueryOptions,将一个IQueryable对象传递给ApplyTo方法,该方法返回另一个IQueryable。
对于复杂的应用场景,如果没有IQueryable查询,可以检查ODataQueryOptions并且将查询选项转换为另一种形式(例如RaghuRam Nadiminti的文章《将OData查询转换为HQL》)。
查询验证
Queryable属性在执行查询前会进行验证,实际上通过QueryableAttribute.ValidateQuery进行,你也可以对此进行自定义(可以参见OData安全指导)。
1: public class MyOrderByValidator : OrderByQueryValidator
2: {
3: // Disallow the 'desc' parameter for $orderby option.
4: public override void Validate(OrderByQueryOption orderByOption,
5: ODataValidationSettings validationSettings)
6: {
7: if (orderByOption.OrderByNodes.Any(
8: node => node.Direction == OrderByDirection.Descending))
9: {
10: throw new ODataException("The 'desc' option is not supported.");
11: }
12: base.Validate(orderByOption, validationSettings);
13: }
14: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
还有一种方法,也可以对Queryable属性进行扩展。
1: public class MyQueryableAttribute : QueryableAttribute
2: {
3: public override void ValidateQuery(HttpRequestMessage request,
4: ODataQueryOptions queryOptions)
5: {
6: if (queryOptions.OrderBy != null)
7: {
8: queryOptions.OrderBy.Validator = new MyOrderByValidator();
9: }
10: base.ValidateQuery(request, queryOptions);
11: }
12: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
然后再global.ascx中或者controller、action上使用。
1: // Globally:
2: config.EnableQuerySupport(new MyQueryableAttribute());
3:
4: // Per controller:
5: public class ValuesController : ApiController
6: {
7: [MyQueryable]
8: public IQueryable<Product> Get()
9: {
10: return products.AsQueryable();
11: }
12: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
如果直接使用了ODataQueryOptions,可以这样处理。
1: public IQueryable<Product> Get(ODataQueryOptions opts)
2: {
3: if (opts.OrderBy != null)
4: {
5: opts.OrderBy.Validator = new MyOrderByValidator();
6: }
7:
8: var settings = new ODataValidationSettings()
9: {
10: // Initialize settings as needed.
11: AllowedFunctions = AllowedFunctions.AllMathFunctions
12: };
13:
14: // Validate
15: opts.Validate(settings);
16:
17: IQueryable results = opts.ApplyTo(products.AsQueryable());
18: return results as IQueryable<Product>;
19: }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
来源:http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options
作者:Mike Wasson(微软科技作家)