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

Using an OData Client with an ASP.NET Core API

History

2020-11-22 Updated .NET 5

2020-07-06 Updated .NET Core 3.1

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.5.2" />

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.AddControllers(options => 
	   {
	     options.EnableEndpointRouting = false;
	   }
	);
}

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)
{
	app.UseExceptionHandler("/Home/Error");
	app.UseCors("AllowAllOrigins");

	app.UseSerilogRequestLogging();

	app.UseStaticFiles();

	app.UseRouting();

	app.UseAuthentication();
	app.UseAuthorization();

	app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
                endpoints.MapODataRoute("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;
        }

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

        [HttpGet("odata/Person({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=EmailAddresses
https://localhost:44345/odata/Person(7)?$expand=EmailAddresses
https://localhost:44345/odata/Person?$expand=PersonPhones
https://localhost:44345/odata/Person(7)?$expand=PersonPhones($expand=PhoneNumberTypes)

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

Here’s an example of a query.

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