請MVC5 WebApi2 支援OData協定查詢

一、配置項

1、WebApiConfig.cs添加如下代碼：

2、Global.asax.cs添加如下代碼：

二、使用方法

ODataController.cs

三、查詢示例：

1、 查詢前10條資料，并傳回Id值小于10的資料集合：

 2、查詢ParnetId等于6551的資料人集合：

3、上面的集合按照Code倒序排列：

4、隻傳回Name,Id屬性的集合：

運算符

描述

示例

eq

等于

<code>/AccountSet?$filter=Address1_City eq 'Redmond'</code>

ne

不等于

<code>/AccountSet?$filter=Address1_City ne null</code>

gt

大于

<code>/AccountSet?$filter=CreditLimit/Value gt 1000</code>

ge

大于或等于

<code>/AccountSet?&amp;$filter=CreditLimit/Value ge 1000</code>

Lt

小于

<code>/AccountSet?$filter=CreditLimit/Value lt 1000</code>

le

小于或等于

<code>/AccountSet?$filter=CreditLimit/Value le 1000</code>

and

邏輯與

<code>/AccountSet?$filter=CreditLimit/Value ge 1000 and Address1_StateOrProvince eq 'TX'</code>

or

邏輯或

<code>/AccountSet?$filter=AccountCategoryCode/Value eq 2 or AccountRatingCode/Value eq 1</code>

not

邏輯非

<code>/AccountSet?$filter=(AccountCategoryCode/Value ne null) and not (AccountCategoryCode/Value eq 1)</code>

選項

說明

<a href="https://msdn.microsoft.com/zh-cn/library/gg309461.aspx#BKMK_expand">$expand</a>

訓示應在所檢索的記錄或集合中檢索相關記錄。

<a href="https://msdn.microsoft.com/zh-cn/library/gg309461.aspx#BKMK_filter">$filter</a>

指定為在集合中傳回記錄計算結果必須為 true 的表達式或函數。

<a href="https://msdn.microsoft.com/zh-cn/library/gg309461.aspx#BKMK_orderby">$orderby</a>

确定使用哪些值對記錄集合進行排序。

<a href="https://msdn.microsoft.com/zh-cn/library/gg309461.aspx#BKMK_select">$select</a>

指定要傳回的屬性子集。

<a href="https://msdn.microsoft.com/zh-cn/library/gg309461.aspx#BKMK_skip">$skip</a>

設定在集合中檢索記錄之前要跳過的記錄數。

<a href="https://msdn.microsoft.com/zh-cn/library/gg309461.aspx#BKMK_top">$top</a>

确定要傳回的最大記錄數。

相關教程：http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api

It all starts with a Data Service hosted somewhere:

http://server/service.svc

You access the Data Service entities through resource sets, like this:

http://server/service.svc/People

You request a specific entity using its key like this:

http://server/service.svc/People(16)

Or by using a reference relationship to something else you know:

http://server/service.svc/People(16)/Mother

This asks for person 16’s mother.

Once you have identified an entity you can refer to it’s properties directly:

http://server/service.svc/People(16)/Mother/Firstname

But the last query wraps the property value in XML, if you want just the raw property value you append $value to the url like this:

http://server/service.svc/People(16)/Mother/Firstname/$value

$filter:

You can filter resource sets using $filter:

http://server/service.svc/People?$filter=Firstname  eq ‘Fred’

Notice that strings in the filter are single quoted.

Numbers need no quotes though:

http://server/service.svc/Posts?$filter=AuthorId eq 1

To filter by date you have identity the date in the filter, like this:

http://server/service.svc/Posts?$filter=CreatedDate eq DateTime’2009-10-31′

You can filter via reference relationships:

http://server/service.svc/People?$filter=Mother/Firstname eq ‘Wendy’

The basic operators you can use in a filter are:

Operator

Description

C# equivalent

equals

==

not equal

!=

greater than

&gt;

greater than or equal

&gt;=

lt

less than

&lt;

less than or equal

&lt;=

&amp;&amp;

||

()

grouping

If you want to include related items in the results you use $expand like this:

http://server/service.svc/Blogs?$expand=Posts

This returns the matching Blogs and each Blog’s posts.

Some Data Services allow you to limit the results to just the properties you require – aka projection – for example if you just want the Id and Title of matching Posts you would need something like this:

http://server/service.svc/Posts?$select=Id,Title

You can even project properties of related objects too, like this:

http://server/service.svc/Posts?$expand=Blog&amp;$select=Id,Title,Blog/Name

This projects just the Id, Title and the Name of the Blog for each Post.

If you just want to know how many records would be returned, without retrieving them you need $count:

http://server/service.svc/Blogs/$count

Notice that $count becomes one of the segments of the URL – it is not part of the query string – so if you want to combine it with another operation like $filter you have to specify $count first, like this:

http://server/service.svc/Posts/$count?$filter=AuthorId eq 6

This query returns the number of posts authored by person 6.

If you need your results ordered you can use $orderby:

http://server/service.svc/Blogs?$orderby=Name

Which returns the results in ascending order, to do descending order you need:

http://server/service.svc/Blogs?$orderby=Name%20desc

To filter by first by one property and then by another you need:

http://server/service.svc/People?$orderby=Surname,Firstname

Which you can combine with desc if necessary.

If you want just the first 10 items you use $top like this:

http://server/service.svc/People?$top=10

If you are only interested in certain page of date, you need $top and $skip together:

http://server/service.svc/People?$top=10&amp;$skip=20

This tells the Data Service to skip the first 20 matches and return the next 10. Useful if you need to display the 3rd page of results when there are 10 items per page.

Note: It is often a good idea to combine $top &amp; $skip with $orderby too, to guarantee the order results are retrieved from the underlying data source is consistent.

Using $top and $skip allows the client to control paging.

With Server Driven Paging turned on the client might ask for every record, but they will only be given one page of results.

This as you can imagine can make life a little tricky for client application developers.

If the client needs to know how many results there really are, they can append the $inlinecount option to the query, like this:

http://server/service.svc/People?$inlinecount=allpages

The results will include a total count ‘inline’, and a url generated by the server to get the next page of results.

This generated url includes a $skiptoken, that is the equivalent of a cursor or bookmark, that instructs the server where to resume:

http://server/service.svc/People?$skiptoken=4

Sometime you just need to get the urls for entities related to a particular entity, which is where $links comes in:

http://server/service.svc/Blogs(1)/$links/Posts

This tells the Data Service to return links – aka urls – for all the Posts related to Blog 1.

If you need to know what model an OData compliant Data Service exposes, you can do this by going to the root of the service and appending $metadata like this:

http://server/service.svc/$metadata

學習交流群：364976091