你的位置:首页 > ASP.net教程

[ASP.net教程]OData查询ASP.NET Web API全攻略


本篇使用ASP.NET Web API来体验OData各种query。


首先是本篇即将用到的Model。使用的OData版本是4.0。

 

public class Customer{  public int Id { get; set; }  public string Name { get; set; }  public Gender Gender { get; set; }  public DateTimeOffset BirthTime { get; set; }  public List<Order> Orders { get; set; }}public enum Gender{  Male,  Female}public class Order{  public int Id { get; set; }  public string Name { get; set; }  public int Quantity { get; set; }  public Origin Origin { get; set; }}public class Origin{  public string City { get; set; }  public int PostCode { get; set; }} 

 

在WebApiConfig类中配置OData的路由和EDM。

 

public static class WebApiConfig{  public static void Register(HttpConfiguration config)  {    // Web API 配置和服务    // Web API 路由    config.MapHttpAttributeRoutes();    config.MapODataServiceRoute(routeName: "OData", routePrefix: "odata", model: GetEdmModel());    config.Routes.MapHttpRoute(      name: "DefaultApi",      routeTemplate: "api/{controller}/{id}",      defaults: new { id = RouteParameter.Optional }    );  }  private static IEdmModel GetEdmModel()  {    var modelBuilder = new ODataConventionModelBuilder();    modelBuilder.EntitySet<Customer>("Customers");    modelBuilder.EntitySet<Order>("Orders");        return modelBuilder.GetEdmModel();  }}

 

一个类有一个集合导航属性

 

Customer: 1,有一个集合导航属性List<Order> Orders
Order:多,但没有有关Customer的外键和导航属性

 

public class CustomersController : ODataController{  private static List<Customer> CustomerList = new List<Customer>  {    new Customer {      Id = 11, Name = "Lowest", Gender = Gender.Female, BirthTime = new DateTime(2001, 1, 1),      Orders = new List<Order>      {        new Order { Id = 0 , Quantity = 10, Origin = new Origin() { City = "East", PostCode = 1024 }},        new Order { Id = 1 , Quantity = 50, Origin = new Origin() { City = "West", PostCode = 4096 }}      }    },    new Customer {      Id = 33, Name = "Highest", Gender = Gender.Male, BirthTime = new DateTime(2002, 2, 2),      Orders = new List<Order>      {        new Order { Id = 2 , Quantity = 10, Origin = new Origin() {City = "North", PostCode = 2048 }},        new Order { Id = 3 , Quantity = 5, Origin = new Origin() {City = "South", PostCode = 8192 }}      }    },    new Customer { Id = 22, Name = "Middle", Gender = Gender.Female, BirthTime = new DateTime(2003, 3, 3) },    new Customer { Id = 3, Name = "NewLow", Gender = Gender.Male, BirthTime = new DateTime(2004, 4, 4) },  };  [EnableQuery(AllowedArithmeticOperators = System.Web.OData.Query.AllowedArithmeticOperators.Add)]  public IEnumerable<Customer> Get()  {    return CustomerList;  }  /// <summary>  /// Customer有一个类型为List<Order>的集合导航属性,这里根据Customer的主键、Oder的主键、Cstomer的导航属性名称,从而删除Customer的某个Order  /// DELETE http://localhost:63372/odata/Customers(11)/Orders/$ref?$id=../../Orders(0)  /// </summary>  /// <param name="key">Customer的主键</param>  /// <param name="relatedKey">Order的主键</param>  /// <param name="navigationProperty">Customer的导航属性</param>  /// <returns></returns>  [HttpDelete]  public IHttpActionResult DeleteRef(int key, int relatedKey, string navigationProperty)  {    //先找到Customer    var customer = CustomerList.Single(c => c.Id == key);    //再找到该Customer的Oder    var order = customer.Orders.Single(o => o.Id == relatedKey);    if(navigationProperty != "Orders")    {      return BadRequest();    }    customer.Orders.Remove(order);    return StatusCode(System.Net.HttpStatusCode.NoContent);  }}

 

//获取所有
GET http://localhost:63372/odata/Customers


//排序
GET http://localhost:63372/odata/Customers/?$orderby=Id
GET http://localhost:63372/odata/Customers/?$orderby=Name


//排序,跳过,顶部
GET http://localhost:63372/odata/Customers/?$orderby=Id&$skip=1&$top=2
GET http://localhost:63372/odata/Customers/?$orderby=Name&$skip=2&$top=1


//过滤 集合导航属性,满足所有条件
//过滤Customer的集合导航属性Orders,该集合中只要有一个Order的Quantity大于等于10,就返回该Customer
GET http://localhost:63372/odata/Customers/?$filter=Orders/any(order: order/Quantity ge 10)


//过滤集合导航属性,满足任一条件
//过滤Customer的集合导航属性Orders,该集合中所有Order的Quantity大于等于10,就返回该Customer
GET http://localhost:63372/odata/Customers/?$filter=Orders/all(order: order/Quantity ge 10)


//odata不认识的关键词
//$unkown不是odata内置的关键词
//报错:The query parameter '$unknown' is not supported.
GET http://localhost:63372/odata/Customers/?$orderby=Name&$unknown=12

 

//不带$前缀
//unknown不带$前缀,
//依然返回数据,但unknown直接被忽略,就当不存在
GET http://localhost:63372/odata/Customers/?$orderby=Name&unknown=12


//未知属性名
//UnknownPropertyName是未知属性名
//报错:400 Bad Reqest
GET http://localhost:63372/odata/Customers/?$orderby=UnknownPropertyName


//过滤,按属性值
GET http://localhost:63372/odata/Customers/?$filter=Name eq 'Lowest'


//过滤,按表达式
GET http://localhost:63372/odata/Customers/?$filter=Id add 2 eq 4


//过滤,使用string的方法
GET http://localhost:63372/odata/Customers/?$filter=length(Name) eq 6


//过滤,使用有关year的方法
GET http://localhost:63372/odata/Customers/?$filter=year(BirthTime) eq 2001


//过滤,使用别名
GET http://localhost:63372/odata/Customers/?$filter=@p1&@p1=year(BirthTime) eq 2001


//过滤,使用乘法
//mul 是不允许的,因为在CustomersController的EnableQuery配置中只允许加法
//报错:400 Bad Reqest
GET http://localhost:63372/odata/Customers/?$filter=Id mul 2 eq 6


//select
GET http://localhost:63372/odata/Customers/?$select=Name,BirthTime


//expand,把类以及它的导航属性全部显示出来
GET http://localhost:63372/odata/Customers/?$expand=Orders


//混合select和expand
GET http://localhost:63372/odata/Customers/?$select=Name&$expand=Orders($select=Name,Quantity)


//混合filter, exapand, 别名
//先根据Customer的Gender属性过滤,Gender是枚举,过滤的值或条件交给@p1这个变量,@p1是MyOdataQuerySample.API.Models命名空间下,Gender枚举中的Femail枚举值
//再expand到Customer的导航属性Orders,再排序,根据@p2这个变量,@p2是Order类中Origin属性下的City属性
GET http://localhost:63372/odata/Customers/?$filter=Gender eq @p1&$expand=Orders($orderby=@p2)&@p1=MyOdataQuerySample.API.Models.Gender'Female'&@p2=Origin/City


//删除,删除某个Customer的Orders集合中的某个Order,使用相对路径删除
//删除编号为11的Customer与编号为0的Order之间的关系
//Customers(11)中的11被API的key参数接受,Orders被API的navigationProperty接受,Orders(0)中的0被API的relatedKey接受
//../../表示相对路径,第一个..表示http://localhost:63372/odata,第二个..表示Customers
DELETE http://localhost:63372/odata/Customers(11)/Orders/$ref?$id=../../Orders(0)

//接着查询确认编号为11的Customer是否和编号为0的Order是否有关系
//结果:编号为11的Customer的导航属性Orders中已经没有编号为0的Order了
GET http://localhost:63372/odata/Customers/?$expand=Orders


//删除,删除某个Customer的Orders集合中的某个Order,使用绝对路径删除
DELETE http://localhost:63372/odata/Customers(11)/Orders/$ref?$id=http://localhost:63372/odata/Orders(1)

 

//接着查询确认编号为11的Customer是否和编号为1的Order是否有关系
//结果:编号为11的Customer的导航属性Orders中已经没有编号为1的Order了
GET http://localhost:63372/odata/Customers/?$expand=Orders

 

创建自定义的过滤,排序等规则

 

public class OrdersController : ODataController{  private static List<Order> OrderList = new List<Order>  {    new Order { Id = 11, Name = "Order1", Quantity = 1 },    new Order { Id = 33, Name = "Order3", Quantity = 3 },    new Order { Id = 4, Name = "Order4", Quantity = 100 },    new Order { Id = 22, Name = "Order2", Quantity = 2 },    new Order { Id = 3, Name = "Order0", Quantity = 0 },  };  /// <summary>  /// 我们通常使用[EnableQuery]来使某个action可以接受OData的Query  /// 这里提供了另外一种支持OData的Query的方式,把ODataQueryOptions作为参赛  /// </summary>  /// <param name="queryOptions"></param>  /// <returns></returns>  public IQueryable<Order> Get(ODataQueryOptions queryOptions)  {    //如果odata query中有过滤    if(queryOptions.Filter != null)    {      queryOptions.Filter.Validator = new RestrictiveFilterByQueryValidator();    }    //过滤可以自定义,如果其它自定义呢?使用ODataValidationSettings    //设置max top    ODataValidationSettings settings = new ODataValidationSettings() {MaxTop = 9 };    //设置orderby的属性    settings.AllowedOrderByProperties.Add("Id");    queryOptions.Validate(settings);    return queryOptions.ApplyTo(OrderList.AsQueryable()) as IQueryable<Order>;  }  /// <summary>  /// 自定义过滤查询的Validator  /// </summary>  private class RestrictiveFilterByQueryValidator : FilterQueryValidator  {    public override void ValidateSingleValuePropertyAccessNode(SingleValuePropertyAccessNode propertyAccessNode, ODataValidationSettings settings)    {      if(propertyAccessNode.Property.Name == "Quantity")      {        throw new ODataException("不允许针对Quantity属性过滤");      }      base.ValidateSingleValuePropertyAccessNode(propertyAccessNode, settings);    }  }}

 

以上,

● Get方法中的ODataQueryOptions类型也可支持odata query
● 通过ODataQueryOptions的Filter.Validator属性,我们可以设置自定义继承FilterQueryValidator的子类,自定义过滤条件
● ODataValidationSettings用来自定义其它规则,比如排序、max top,等等
● 把ODataValidationSettings的实例作为ODataQueryOptions的实例方法Validate的实参
● 最终通过ODataQueryOptions的实例方法ApplyTo,把规则作用到IQueryable<T>类型集合上去


GET http://localhost:63372/odata/Orders

//排序,使用controller中允许的字段
GET http://localhost:63372/odata/Orders/?$orderby=Id


//orderby, skip, top,在设定的规则之内
GET http://localhost:63372/odata/Orders/?$orderby=Id&$skip=1&$top=2


//orderby在规则之内,top在规则之外
//报错:500 Internal Server Error, 因为top的上限是9
GET http://localhost:63372/odata/Orders/?$orderby=Id&$top=2000


//orderby在规则之外,top在规则之内
//报错:500 Internal Server Error,因为只允许把Id作为排序字段
GET http://localhost:63372/odata/Orders/?$orderby=Name&$top=2

//filter,在规则之内
//规则不允许对Quantity进行过滤
GET http://localhost:63372/odata/Orders/?$filter=Id ge 10


//filter,在规则之外
//规则不允许对Quantity进行过滤
//报错:500 Internal Server Error
GET http://localhost:63372/odata/Orders/?$filter=Quantity ge 100

 

API返回HttpResponseMessage,对返回信息有更多的控制

 

public class ResponseController : ODataController{  private static List<Customer> CustomerList = new List<Customer>  {    new Customer {      Id = 11, Name = "Lowest", BirthTime = new DateTime(2001, 1, 1),      Orders = new List<Order>      {        new Order { Id = 0 , Quantity = 10 },        new Order { Id = 1 , Quantity = 50 }      }    },    new Customer {      Id = 33, Name = "Highest", BirthTime = new DateTime(2002, 2, 2),      Orders = new List<Order>      {        new Order { Id = 2 , Quantity = 10 },        new Order { Id = 3 , Quantity = 5 }      }    },    new Customer { Id = 22, Name = "Middle", BirthTime = new DateTime(2003, 3, 3) },    new Customer { Id = 3, Name = "NewLow", BirthTime = new DateTime(2004, 4, 4) },  };  /// <summary>  /// 之前的返回类型有IEnumerable, IQueryable, IHttpActionResult  /// 这里是HttpResponseMessage,允许忘header里面加字段,方便操作status  /// </summary>  /// <returns></returns>  [EnableQuery(AllowedArithmeticOperators =System.Web.OData.Query.AllowedArithmeticOperators.Add)]  public HttpResponseMessage Get()  {    HttpResponseMessage response = Request.CreateResponse<IEnumerable<Customer>>(HttpStatusCode.OK, CustomerList);    response.Headers.Add("Sample-Header", "Sample-Value");    return response;  }  /// <summary>  /// 删除某个Customer下Orders导航属性中的某个Order  /// </summary>  /// <param name="key">Customer的主键</param>  /// <param name="relatedKey">Order的主键</param>  /// <returns></returns>  [HttpDelete]  [ODataRoute("Response({key})/Orders({relatedKey})/$ref")]//自定义OData路由规则  public HttpResponseMessage DeleteOrdersFromCustomer(int key, int relatedKey)  {    var customer = CustomerList.Single(c => c.Id == key);    var order = customer.Orders.Single(o => o.Id == relatedKey);    customer.Orders.Remove(order);    HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.NoContent);    response.Headers.Add("Delete-Ref", "true");    return response;  }}

 

以上,

● 返回类型是HttpResponseMessage,借此可以自定义返回状态,以及返回Header,等
● 通过[ODataRoute("Response({key})/Orders({relatedKey})/$ref")]设置自定义路由规则

//查看所有
//返回的Headers中有在API中自定义的Sample-Header → Sample-Value
GET http://localhost:63372/odata/Response


//orderby
//返回的Headers中有在API中自定义的Sample-Header → Sample-Value
GET http://localhost:63372/odata/Response/?$orderby=Id


//orderby,skip, top
//返回的Headers中有在API中自定义的Sample-Header → Sample-Value
GET http://localhost:63372/odata/Response/?$orderby=Id&$skip=1&$top=2


//filter+any
//Orders是Customer的导航属性,order:order有点像lambda表达式,order/Quantity用/表示Order中的Quantity属性
//返回的Headers中有在API中自定义的Sample-Header → Sample-Value
GET http://localhost:63372/odata/Response/?$filter=Orders/any(order: order/Quantity ge 10)


//filter+all
//返回的Headers中有在API中自定义的Sample-Header → Sample-Value
GET http://localhost:63372/odata/Response/?$filter=Orders/all(order: order/Quantity ge 10)

 

//删除某个Customer下Order集合中的某个Order
//Response(11)/Orders/$ref表示关系
//$id=../../Orders(0),用的是相对路径,相当于http://localhost:63372/odata/Orders(0)
//返回的Headers中有在API中自定义的Delete-Ref → true
DELETE http://localhost:63372/odata/Response(11)/Orders/$ref?$id=../../Orders(0)

 




普吉岛旅游团报价优惠普吉岛蜜月旅游行程参考普吉岛特价旅游多少钱到普吉岛游费用报价大全普吉岛自助游攻略2015七娘山野炊山庄学生票多少钱?深圳七娘山野炊山庄学生门票价格? 七娘山野炊山庄一日游?南澳七娘山野炊山庄怎么走? 中山五桂山逍遥谷电话?逍遥谷门票预订? 逍遥谷烧烤吗?五桂山逍遥谷怎么收费? 惠州蓝田瑶族风情园怎么样?蓝田瑶族风情园有什么好玩的? 蓝田瑶族风情园门票包含什么?龙门蓝田瑶族风情园有表演吗? 蓝田瑶族风情园团购价格?龙门蓝田瑶族风情园门票优惠政策? 广宁竹海自驾游团购价格?广宁竹海大观门票网上预订? 万佛湖门票_安徽六安万佛湖门票价格_多少钱 深圳大梅沙门票_深圳大梅沙海滨公园门票_大梅沙门票价格多少钱 伏牛山在哪_伏牛山在哪里_伏牛山在哪个省 梅花山门票_南京梅花山门票_南京梅花山怎么走 韩国商业街——COEX 韩国温泉 韩国购物之旅——釜山国际市场 韩国免税店购物 ICS843004AGI-02LFT Datasheet ICS843004AGI-02LFT Datasheet IDT7202LA12P Datasheet IDT7202LA12P Datasheet IDT23S08E-3DCG Datasheet IDT23S08E-3DCG Datasheet 跟团去葡萄牙 跟团去葡萄牙 跟团去葡萄牙 葡萄牙旅游 旅行社 葡萄牙旅游 旅行社 葡萄牙旅游 旅行社 葡萄牙旅游行程安排 葡萄牙旅游行程安排 葡萄牙旅游行程安排