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)