[翻译]在ASP.NET Web API中通过OData支持查询和分页

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(微软科技作家)

上一篇:创建型模式(前引)简单工厂模式Simple Factory


下一篇:通过微软的cors类库,让ASP.NET Web API 支持 CORS