To create an OData service for a SQL Server database, we will create a new Visual Studio ASP.NET web application:
Select Empty template and check Web API:
This will create:
Now, add the OData NuGet packages:
Add Entity Framework:
Next, add a new item:
Select EF Designer from database:
Select the database connection, click new connection if required, and click Next. We will connect to an Azure SQL database with Adventure Works sample data:
Select tables and click Finish:
A new connection string is added to the Web.Config:
With the Entity Framework model:
Now, open the file App_Start/WebApiConfig.cs:
We will add OData code to the Register method:
public static void Register(HttpConfiguration config) |
{ |
ODataModelBuilder builder = new ODataConventionModelBuilder(); |
builder.EntitySet<Product>("Products"); |
config.MapODataServiceRoute( |
routeName: "ODataRoute", |
routePrefix: null, |
model: builder.GetEdmModel()); |
// Web API configuration and services |
/* Remove the original code |
// Web API routes |
config.MapHttpAttributeRoutes(); |
config.Routes.MapHttpRoute( |
name: "DefaultApi", |
routeTemplate: "api/{controller}/{id}", |
defaults: new { id = RouteParameter.Optional } |
); |
*/ |
} |
Now we will add a controller. We will add one for Products.
This produces the code:
If we run this now, we see:
Now, add a directive to using System.Web.OData;
Change teh class to an ODataController.
Change the IEnumerable Get to return Products:
public class ProductsController : ODataController |
{ |
adventureworksContext db = new adventureworksContext(); |
protected override void Dispose(bool disposing) |
{ |
db.Dispose(); |
base.Dispose(disposing); |
} |
// GET api/<controller> |
public IEnumerable<Product> Get() |
{ |
return db.Products; |
} |
Now when we run this, products are returned as OData:
From here, you can add create, update, delete.
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
I am using visual studio 2017 and attempting to build a simple oData service for a single table and my visual studio cannot find this and related objects (ODataModelBuilder). Do you know what i need to reference and include in my “Using” statements in order for that to work?
trying the same thing, single table OData endpoint.
To get this to work I had to put these at start of WebApiConfig.cs
using Microsoft.AspNet.Odata.Builder;
using Microsoft.AspNet.Odata.Extensions;