Using OData in the ASP.net Web API

A. What is OData
OData is an Open Data protocol (open Protocol)
In the ASP.net Web API,
For CRUD (Create, read, update, and delete) applications add a lot more flexibility than traditional WEBAPI
As long as the relevant protocols are used correctly, the same
It can save a lot of development time for a CRUD application, and improve development efficiency

Two. How to Build

Make a simple sample order query
We use Code First mode to create two Entity objects product (product), Supplier (supplier)
1. Create a new ASP.net empty project, choose to use the Web API, as shown below

2. Referencing OData and EntityFramework assemblies using NuGet

3. Add product (product) in Models folder, Supplier (supplier) two entities

 public class Product     {        public int Id {get; set;} &NB sp;       public string Name {get; set;}        

Public decimal price {get; set;}         public string Category {get; set;}         [ForeignKey ("Supplier")]         public int? SupplierId {get; set;}         public virtual Supplier Supplier {get; set;}  & nbsp; } public class Supplier     {        public int Id {get; s Et }         public string Name {get; set;}          Public icollection Products {get; set;}    } 

4. Increase the Productcontext database context object and configure it in Web.config ConnectionString

public class Productcontext:dbcontext
{
Public Productcontext ()
: Base ("Demo")
{ }
Public dbset Products {get; set;}
Public dbset Suppliers {get; set;}
}




Providername= "System.Data.SqlClient"/>



5. Build the Database
We started the package management console and ran the following 3 commands,
Build the entity we generated in code a to the database
Pm> enable-migrations
Pm> add-migration Firstinit
Pm> Update-database
We can then see the database table we have generated in Server Explorer, as shown in

6. Next we register our OData route in Webapiconfig

Using Demo2.models;
using System.Web.OData.Builder;
using System.Web.OData.Extensions;
Namespace Demo2
{
    public static class Webapiconfig
    {
 &nb sp;      public static void Register (httpconfiguration config)
         {
            odatamodelbuilder Builder = new Odataconventionmodelbuilder ();
            Builder. Entityset ("Products");
            Builder. Entityset ("Suppliers");
            CONFIG. Mapodataserviceroute ("Odataroute", NULL, builder. Getedmmodel ());
       }
   }
}

7. Next we create a new Odatacontroller for product and supplier,


We have chosen the model class and the data context in the diagram above,
and repeat the above two steps for the supplier entity also generates corresponding Odatacontroller
Note: Due to the OData V3 version of the template in the VS2013 OData template,
The referenced namespace is to be from the V3
Using System.Web.Http;
Using System.Web.Http.ModelBinding;
Using System.Web.Http.OData;
Using System.Web.Http.OData.Routing;
Modified to V4
Using System.Web.Http;
Using System.Web.OData;

By this point, our OData sample program has been built, and when we open the project in the browser
will appear as follows

{
' @odata. Context ': ' http://localhost:8914/$metadata ', ' value ': [
{
' name ': ' Products ', ' kind ': ' Entitys Et "," url ":" Products "
},{
" name ":" Suppliers "," kind ":" EntitySet "," url ":" Suppliers "
}
]
}

Says it's been built.

Three. How to use
with no test data, we started adding some test data to the products and suppliers in the database table first.


Let's take a look at some simple usage examples
In automatically generated ProductsController and Supplierscontroller
The following action has been generated for us

So for some additions, modifications, deletions, updates, I don't do too many examples,
These are not so much different from Webapi,
My main example is the use of the query, I have to say OData has done for us to complete the query function
Example 1: List all product
URL:http://localhost:8914/Products

{
"@odata. Context": "http://localhost:8914/$metadata #products", "Value": [
{
"Id": 1, "Name": "Products1", "Price": 100.00, "Category": "Test", "SupplierId": 1
},{
"Id": 2, "Name": "Products2", "Price": 200.00, "Category": "Test", "SupplierId": 1
},{
"Id": 3, "Name": "Products3", "Price": 300.00, "Category": "Test", "SupplierId": 1
},{
"Id": 4, "Name": "Products4", "Price": 400.00, "Category": "P1", "SupplierId": 2
},{
"Id": 5, "Name": "Products5", "Price": 500.00, "Category": "P1", "SupplierId": 2
},{
"Id": 6, "Name": "Products6", "Price": 600.00, "Category": "P1", "SupplierId": 2
},{
"Id": 7, "Name": "Products7", "Price": 700.00, "Category": "P1", "SupplierId": 2
},{
"Id": 8, "Name": "Products8", "Price": 800.00, "Category": "Test", "SupplierId": 3
},{
"Id": 9, "Name": "Products9", "Price": 900.00, "Category": "P1", "SupplierId": 3
}
]
}

Example 2, querying a single products
Url:http://localhost:8914/products (1) where (1) is the ID

{
"@odata. Context": "http://localhost:8914/$metadata #products/$entity", "IDs": 1, "Name": "Products1", "Price": 100.00, " Category ":" Test "," SupplierId ": 1
}

Example 3, query products, list only name,price cases
Url:http://localhost:8914/products $select =name,price.

{
"@odata. Context": "http://localhost:8914/$metadata #products (name,price)", "Value": [
{
' Name ': ' Products1 ', ' Price ': 100.00
},{
' Name ': ' Products2 ', ' Price ': 200.00
},{
' Name ': ' Products3 ', ' Price ': 300.00
},{
' Name ': ' Products4 ', ' Price ': 400.00
},{
' Name ': ' Products5 ', ' Price ': 500.00
},{
' Name ': ' Products6 ', ' Price ': 600.00
},{
' Name ': ' Products7 ', ' Price ': 700.00
},{
' Name ': ' Products8 ', ' Price ': 800.00
},{
' Name ': ' Products9 ', ' Price ': 900.00
}
]
}

Example 5: List only products with ID 1, show only column Name,price

Url:http://localhost:8914/products (1)? $select =name,price

{
"@odata. Context": "http://localhost:8914/$metadata #products (name,price)/$entity", "Name": "Products1", "Price" : 100.00
}

Example 6: List products (only column Name,price), including supplier
Url:http://localhost:8914/products $select =name,price& $expand =supplier.

{
"@odata. Context": "http://localhost:8914/$metadata #products (name,price,supplier)", "Value": [
{
' Name ': ' Products1 ', ' Price ': 100.00, ' Supplier ': {
"Id": 1, "Name": "Supplier1"
}
},{
' Name ': ' Products2 ', ' price ': 200.00, ' Supplier ': {
"Id": 1, "Name": "Supplier1"
}
},{
' Name ': ' Products3 ', ' price ': 300.00, ' Supplier ': {
"Id": 1, "Name": "Supplier1"
}
},{
' Name ': ' Products4 ', ' price ': 400.00, ' Supplier ': {
"Id": 2, "Name": "Supplier2"
}
},{
' Name ': ' Products5 ', ' Price ': 500.00, ' Supplier ': {
"Id": 2, "Name": "Supplier2"
}
},{
' Name ': ' Products6 ', ' price ': 600.00, ' Supplier ': {
"Id": 2, "Name": "Supplier2"
}
},{
' Name ': ' Products7 ', ' price ': 700.00, ' Supplier ': {
"Id": 2, "Name": "Supplier2"
}
},{
' Name ': ' Products8 ', ' price ': 800.00, ' Supplier ': {
"Id": 3, "Name": "Supplier3"
}
},{
' Name ': ' Products9 ', ' price ': 900.00, ' Supplier ': {
"Id": 3, "Name": "Supplier3"
}
}
]
}

Example 7: Filter products to show only data classified as test
Url:http://localhost:8914/products $filter =category eq ' Test '

{
"@odata. Context": "http://localhost:8914/$metadata #products", "Value": [
{
"Id": 1, "Name": "Products1", "Price": 100.00, "Category": "Test", "SupplierId": 1
},{
"Id": 2, "Name": "Products2", "Price": 200.00, "Category": "Test", "SupplierId": 1
},{
"Id": 3, "Name": "Products3", "Price": 300.00, "Category": "Test", "SupplierId": 1
},{
"Id": 8, "Name": "Products8", "Price": 800.00, "Category": "Test", "SupplierId": 3
}
]
}

Example 8: Filter products, display only the data categorized as test, and sort
Url:http://localhost:8914/products $filter =category eq ' Test ' & $orderby =price desc

{
"@odata. Context": "http://localhost:8914/$metadata #products", "Value": [
{
"Id": 8, "Name": "Products8", "Price": 800.00, "Category": "Test", "SupplierId": 3
},{
"Id": 3, "Name": "Products3", "Price": 300.00, "Category": "Test", "SupplierId": 1
},{
"Id": 2, "Name": "Products2", "Price": 200.00, "Category": "Test", "SupplierId": 1
},{
"Id": 1, "Name": "Products1", "Price": 100.00, "Category": "Test", "SupplierId": 1
}
]
}

Here are the other ways to use $filter
1. http://localhost/Products $filter =category eq "Test"
Filtration Category=test
2.http://localhost/products $filter =price LT 10
Filter price is less than 10
3. Http://localhost/Products? $filter =price ge 5 and Price le 15
Filtration 5<=price>=15
5, you can also use database functions such as:
$filter =substringof ("zz", Name)
$filter =year (releasedate) GT 2005

About sorting
$orderby =price
$orderby =price desc
$orderby =category,price desc

There are also some filters such as
$skip, $top, $inlinecount, etc.
So OData basically implements the relevant methods we want to query
Greatly improve the development efficiency for the query of the curd program