本篇使用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 = , Name = "Lowest", Gender = Gender.Female, BirthTime = new DateTime(, , ),
Orders = new List<Order>
{
new Order { Id = , Quantity = , Origin = new Origin() { City = "East", PostCode = }},
new Order { Id = , Quantity = , Origin = new Origin() { City = "West", PostCode = }}
}
},
new Customer {
Id = , Name = "Highest", Gender = Gender.Male, BirthTime = new DateTime(, , ),
Orders = new List<Order>
{
new Order { Id = , Quantity = , Origin = new Origin() {City = "North", PostCode = }},
new Order { Id = , Quantity = , Origin = new Origin() {City = "South", PostCode = }}
}
},
new Customer { Id = , Name = "Middle", Gender = Gender.Female, BirthTime = new DateTime(, , ) },
new Customer { Id = , Name = "NewLow", Gender = Gender.Male, BirthTime = new DateTime(, , ) },
}; [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 = , Name = "Order1", Quantity = },
new Order { Id = , Name = "Order3", Quantity = },
new Order { Id = , Name = "Order4", Quantity = },
new Order { Id = , Name = "Order2", Quantity = },
new Order { Id = , Name = "Order0", Quantity = },
}; /// <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 = }; //设置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 = , Name = "Lowest", BirthTime = new DateTime(, , ),
Orders = new List<Order>
{
new Order { Id = , Quantity = },
new Order { Id = , Quantity = }
}
},
new Customer {
Id = , Name = "Highest", BirthTime = new DateTime(, , ),
Orders = new List<Order>
{
new Order { Id = , Quantity = },
new Order { Id = , Quantity = }
}
},
new Customer { Id = , Name = "Middle", BirthTime = new DateTime(, , ) },
new Customer { Id = , Name = "NewLow", BirthTime = new DateTime(, , ) },
}; /// <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)