OData查询ASP.NET Web API全攻略

时间：2021-11-17 18:59:38

本篇使用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)

 

标签：

相关文章