OData with ASP.NET Core

This article explores how to setup an OData API with ASP.NET Core. The application uses Entity Framework Core with a database first approach using the adventureworks 2016 Microsoft SQL Database.

Code: https://github.com/damienbod/AspNetCoreOData

Part 2: Using an OData Client with an ASP.NET Core API

https://damienbod.com/2018/10/18/using-an-odata-client-with-an-asp-net-core-api/

Setting up the Database

The adventureworks 2016 database from the Microsoft/sql-server-samples was used to setup the database. This backup was restored to the SQL database and Entity Framework Core was then used to create the models and the context as described here:

https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db

Powershell was used to setup like this:

Scaffold-DbContext “Data Source=”your_instance_name”\sqlexpress;Initial Catalog=AdventureWorks2016;Integrated Security=True” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Database

This created the Entity Framework Core AdventureWorks2016Context Context class, which is used to access the database.

The AdventureWorks2016Context class is then added to the Startup ConfigureServices using the default connection string whch is configured in the app.settings file.

public void ConfigureServices(IServiceCollection services)
{
 services.AddDbContext<AdventureWorks2016Context>(options =>
  options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

The created entities need to be changed a bit to work. For example, the Person entity class which was scaffolded in, requires that the key attribute be added to the BusinessEntityId. This attribute needs to be added to the required properties in the entities.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace AspNetCoreOData.Service.Database
{
    public partial class Person
    {
        public Person()
        {
            BusinessEntityContact = new HashSet<BusinessEntityContact>();
            Customer = new HashSet<Customer>();
            EmailAddress = new HashSet<EmailAddress>();
            PersonCreditCard = new HashSet<PersonCreditCard>();
            PersonPhone = new HashSet<PersonPhone>();
        }

        [Key]
        public int BusinessEntityId { get; set; }
        public string PersonType { get; set; }
        public bool NameStyle { get; set; }
        public string Title { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string Suffix { get; set; }
        public int EmailPromotion { get; set; }
        public string AdditionalContactInfo { get; set; }
        public string Demographics { get; set; }
        public Guid Rowguid { get; set; }
        public DateTime ModifiedDate { get; set; }

        public virtual BusinessEntity BusinessEntity { get; set; }
        public virtual Employee Employee { get; set; }
        public virtual Password Password { get; set; }
        public virtual ICollection<BusinessEntityContact> BusinessEntityContact { get; set; }
        public virtual ICollection<Customer> Customer { get; set; }
        public virtual ICollection<EmailAddress> EmailAddress { get; set; }
        public virtual ICollection<PersonCreditCard> PersonCreditCard { get; set; }
        public virtual ICollection<PersonPhone> PersonPhone { get; set; }
    }
}

Adding OData to the ASP.NET Core application

Add the Microsoft.AspNetCore.OData NuGet package to the application using NuGet or directly add it to the project file.

<PackageReference Include="Microsoft.AspNetCore.OData" Version="7.0.1" />

In the startup class ConfigureServices method, add the OData services. The AddMvc extension method requires, that the EnableEndpointRouting property is set to false in an ASP.NET Core app targeting netcoreapp2.2.

public void ConfigureServices(IServiceCollection services)
{
	...

	services.AddOData();
	services.AddODataQueryFilter();

	services.AddMvc(options => 
	   {
	     options.EnableEndpointRouting = false;
	   }
	).SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}

Create the OData IEdmModel as required:

private static IEdmModel GetEdmModel(IServiceProvider serviceProvider)
{
	ODataModelBuilder builder = new ODataConventionModelBuilder(serviceProvider);

	builder.EntitySet<Person>("Person")
		.EntityType
		.Filter()
		.Count()
		.Expand()
		.OrderBy()
		.Page()
		.Select();

	 ...
	 ...
	 
	EntitySetConfiguration<ContactType> contactType = builder.EntitySet<ContactType>("ContactType");
	var actionY = contactType.EntityType.Action("ChangePersonStatus");
	actionY.Parameter<string>("Level");
	actionY.Returns<bool>();

	var changePersonStatusAction = contactType.EntityType.Collection.Action("ChangePersonStatus");
	changePersonStatusAction.Parameter<string>("Level");
	changePersonStatusAction.Returns<bool>();

	EntitySetConfiguration<Person> persons = builder.EntitySet<Person>("Person");
	FunctionConfiguration myFirstFunction = persons.EntityType.Collection.Function("MyFirstFunction");
	myFirstFunction.ReturnsCollectionFromEntitySet<Person>("Person");

	EntitySetConfiguration<EntityWithEnum> entitesWithEnum = builder.EntitySet<EntityWithEnum>("EntityWithEnum");
	FunctionConfiguration functionEntitesWithEnum = entitesWithEnum.EntityType.Collection.Function("PersonSearchPerPhoneType");
	functionEntitesWithEnum.Parameter<PhoneNumberTypeEnum>("PhoneNumberTypeEnum");
	functionEntitesWithEnum.ReturnsCollectionFromEntitySet<EntityWithEnum>("EntityWithEnum");

	return builder.GetEdmModel();
}

And use the created OData EdmModel in the Configure method in the startup class.

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
	...

	app.UseMvc(b =>
		   b.MapODataServiceRoute("odata", "odata", GetEdmModel(app.ApplicationServices)
	));
}

An ODataController controller can now be created for the OData API.

using System.Linq;
using Microsoft.AspNetCore.Mvc;
using  AspNetCoreOData.Service.Database;
using  AspNetCoreOData.Service.Models;
using Microsoft.AspNet.OData.Routing;
using Microsoft.AspNet.OData;
using Microsoft.AspNet.OData.Query;

namespace  AspNetCoreOData.Service.Controllers
{
    [ODataRoutePrefix("Person")]
    public class PersonController : ODataController
    {
        private AdventureWorks2016Context _db;

        public PersonController(AdventureWorks2016Context AdventureWorks2016Context)
        {
            _db = AdventureWorks2016Context;
        }

        [ODataRoute]
        [EnableQuery(PageSize = 20, AllowedQueryOptions= AllowedQueryOptions.All  )]
        public IActionResult Get()
        {  
            return Ok(_db.Person.AsQueryable());
        }

        [ODataRoute("({key})")]
        [EnableQuery(PageSize = 20, AllowedQueryOptions = AllowedQueryOptions.All)]
        public IActionResult Get([FromODataUri] int key)
        {
            return Ok(_db.Person.Find(key));
        }

        [EnableQuery(PageSize = 20, AllowedQueryOptions = AllowedQueryOptions.All)]
        [ODataRoute("Default.MyFirstFunction")]
        [HttpGet]
        public IActionResult MyFirstFunction()
        {
            return Ok(_db.Person.Where(t => t.FirstName.StartsWith("K")));
        }
    }
}

Start the application and the API can be queried in the browser. Here are some examples:

https://localhost:44345/odata/Person?$select=FirstName,MiddleName,LastName

https://localhost:44345/odata?$metadata

https://localhost:44345/odata/Person?$expand=EmailAddress
https://localhost:44345/odata/Person(7)?$expand=EmailAddress
https://localhost:44345/odata/Person?$expand=PersonPhone
https://localhost:44345/odata/Person(7)?$expand=PersonPhone($expand=PhoneNumberType)

https://localhost:44345/odata/Person?$filter=FirstName eq ‘Ken’
https://localhost:44345/odata/Person?$filter=EmailAddress/any(q: q/EmailAddress1 eq ‘kevin0@adventure-works.com’)

https://localhost:44345/odata/Person?$expand=EmailAddress&$filter=EmailAddress/any(q: q/EmailAddress1 eq ‘kevin0@adventure-works.com’)

https://localhost:44345/odata/PersonPhone?$filter=startswith(PhoneNumber, cast(’42’, Edm.String))

https://localhost:44345/odata/Person?$count=true

This works without much effort and is easy to setup. In the next post, we will setup an OData client to consume the data.

Links:

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db

https://blogs.msdn.microsoft.com/odatateam/2018/07/03/asp-net-core-odata-now-available/

http://odata.github.io/

https://blogs.msdn.microsoft.com/odatateam/

http://azurecoder.net/2018/02/19/creating-odata-api-asp-net-core-2-0/

https://dotnetthoughts.net/getting-started-with-odata-in-aspnet-core/

https://github.com/damienbod/WebAPIODataV4

Advertisements

One comment

  1. […] OData with ASP.NET Core (Damien Bowden) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: