Using OData in the ASP. NET Web API

Http://www.alixixi.com/program/a/2015063094986.shtml

I. What is OData
OData is an Open Data protocol (open Protocol)
In the ASP. NET Web API,
Increased flexibility for CRUD (create, read, update, and delete) applications than traditional WEBAPI
As long as the appropriate use of the relevant protocol, can be in the same situation
Can save a lot of development time for a crud application, thus improving development efficiency

Two. How to Build

Make a simple example of order query
We create two entity objects using Code First mode product (product), Supplier (supplier)
1. Create a new ASP. NET empty project and choose to use the Web API, as

2. Using NuGet to refer to OData and EntityFramework assemblies

3. Add product (product), Supplier (vendor) Two entities in the Models folder

public class Product {public int Id {get; set;}        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;}        }public class Supplier {public int Id {get; set;}        public string Name {get; set;}    Public icollection<product> Products {get; set;} }

4. Add the Productcontext database context object and configure it in Web. config connectionstring

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



<connectionStrings>
<add name= "Demo" connectionstring= "Data source= (localdb) \v11.0;
Initial Catalog=demo; Integrated security=true; Multipleactiveresultsets=true;
Attachdbfilename=datadirectorydemo.mdf "
Providername= "System.Data.SqlClient"/>
</connectionStrings>


5. Build the Database
We start the package management console and run the following 3 commands,
Build the entity that we generated code first into the database
Pm> enable-migrations
Pm> add-migration Firstinit
Pm> Update-database
We can then see the database tables we generated in Server Explorer, such as

6. Next we register our OData route in the Webapiconfig

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

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


We select good model classes and data contexts in the
and repeat the above two steps for supplier entities also generate corresponding Odatacontroller
Note: Due to the OData V3 version of the template in the VS2013 OData template,
The referenced namespace is to be V3 from the
Using System.Web.Http;
Using System.Web.Http.ModelBinding;
Using System.Web.Http.OData;
Using System.Web.Http.OData.Routing;
Modify to V4
Using System.Web.Http;
Using System.Web.OData;

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

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

It means it's been built.

Three. How to use
Since there is no test data, we begin to add some test data to the products and suppliers in the database table first


Now let's look at some simple examples of use
In auto-generated productscontroller and Supplierscontroller
The following actions have 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 queries, and we have to say that OData has done all the querying for us.
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", "Id": 1, "Name": "Products1", "Price": 100.00, " Category ":" Test "," SupplierId ": 1
}

Example 3, querying products, listing only Name,price examples
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: Only products with ID 1 are listed, only the columns are displayed 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, show only data classified 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"
Filter Category=test
2.http://localhost/products? $filter =price LT 10
Filter price less than 10
3. Http://localhost/Products? $filter =price ge 5 and Price le 15
Filter 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 method that we want to query
Greatly improve development efficiency for queries of curd programs

Using OData in the ASP. NET Web API