OData 無法使用 $count, $orderby, $select, $top, $expand, $filter

同事回報在介接 OData API 時發現無法使用 $filter 來過濾資料，經一步測試發現不僅是 $filter 無法使用，OData 其他運算子都無法正常運作，來看看該如何處置吧

錯誤訊息 1

訊息內容

The query specified in the URI is not valid. The property 'Id' cannot be used in the $filter query option.

錯誤截圖

錯誤訊息 2

訊息內容

The query specified in the URI is not valid. The limit of '0' for Top query has been exceeded. The value from the incoming request is '1'.

錯誤截圖

問題發生原因

Now the default setting for WebAPI OData is : client can't apply $count, $orderby, $select, $top, $expand, $filter in the query, query like localhost\odata\Customers?$orderby=Name will failed as BadRequest, because all properties are not sort-able by default, this is a breaking change in 6.0.0,

詳細內容請參考 13.1 Model Bound Attributes

這是 6.0.0 的異動內容

解決方式

修改全域查詢設定

在 WebApiConfig 的 Register 中加上下列設定

config.Count().Filter().OrderBy().Expand().Select().MaxTop(null);

修改範例

ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
config.Count().Filter().OrderBy().Expand().Select().MaxTop(null);
builder.EntitySet<BU>("BU");
config.MapODataServiceRoute(
        routeName: "ODataRoute",
        routePrefix: "odata",
        model: builder.GetEdmModel()
);

僅修改需要的 Entity 設定

filter id

在 builder.EntitySet 後加上 builder.EntityType<BU>().Filter("Id");

ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<BU>("BU");
builder.EntityType<BU>().Filter("Id");
builder.GetEdmModel());
config.MapODataServiceRoute(
    routeName: "ODataRoute",
    routePrefix: "odata",
    model: builder.GetEdmModel()
);

top

在 builder.EntitySet 後加上 builder.EntityType<BU>().Page(100, 100);

ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<BU>("BU");
builder.EntityType<BU>().Page(100, 100);
builder.GetEdmModel());
config.MapODataServiceRoute(
    routeName: "ODataRoute",
    routePrefix: "odata",
    model: builder.GetEdmModel()
);

實際效果

3filterok

4topok

心得

我看到問題發生原因是 breaking change 時，我好傻眼，原來之前一直覺得 OData 舊版本使用者討不到便宜的原因是出在這XD，但憑心而論這實在是自己的問題，誰叫我自己不看 release notes 就直接開工，自以為用過就有恃無恐，吃到苦頭了吧@@”

文章目錄