Creating an OData v4 API with ASP.NET Core 2.0

Lucas Araujo | Azure Coder
13 min readFeb 19, 2018

--

Hallo Mensen :D Let’s talk OData, shall we?

In the last few years my work revolved a lot around REST APIs. I think most of us will agree that REST APIs are a really good way of obtaining data from a server without caring too much about details on how to get access to that data.

You call an URL with the proper parameters (including auth or not), headers, and HTTP verb and you get some data back. Easy enough, right?

The negative side is that these APIs are either very rigid on the way they are implemented, meaning that you can only get the data in the exact way defined by the server, or they are very verbose on their implementation, meaning that you will have a lot of code to maintain on the server side to make the API flexible.

Then what?… Use OData!

What is the Open Data Protocol (OData)?

OData Logo
OData Logo

OData, or Open Data Protocol, is a set of ISO approved standards for building and consuming RESTul APIs, but what does it mean, in practice?

It means that OData is not really an implementation that you simply use but documentation specifying how you must implement it yourself. You can read a lot more about OData here.

Why would I want to use OData?

There are a number of benefits that you will obtain from implementing OData standards, from an easier learning path on the usage of your API by your customers/consumers, to the fact that OData is easily readable by machines, and in this post I want to talk about the flexibility that implementing OData gives to your API through the OData URL Conventions.

Using URL Conventions you can expose a much cleaner and generic API and let the consumer specify their needs through the call.

OData URL Conventions

The OData URL Conventions are a set of commands that you can pass to the API through the HTTP call query string.

An OData URL is typically composed by three parts: service root URL, resource path and query options.

OData Url Format
  • Service Root URL is the root address for the API.
  • Resource Path identifies exactly which resource you are trying to achieve.
  • Query Options is how you define to the API the format in which you need that data to be delivered.

How does that sound? Simple enough, right? but also, with the proper options, extremely powerful. Let me give you a list of possible options within the Query Options block of the call:

  • $select: Allows you to define a subset of properties to return from that Resource.
  • $expand: Allows you to include data from a related resource to your query results.
  • $orderby: Not surprisingly, allows you to define the ordering of the returned dataset.
  • $top: Allows you to select the top X results of the query.
  • $skip: Allows you to skip X results of the query.
  • $count: Allows you to get a count of items that would result from that query.
  • $search: Allows for a free-text search on that particular resource
  • $format: Allows you to define the format for the returned data in some query types
  • $filter: Allows you to define a filter for your dataset.

As you can see, many of the commands are pretty similar to what you have in most of the common query languages.

I will go into a bit more detail in each of those options on the Code sample.

OData and ASP.NET

ASP.NET Core still don’t have a stable library to implement the OData protocol! But worry you not, as Microsoft has been working on it for some time and right now we have a really promising beta version on Nuget. You can find it here.

ASP.NET Framework has a really good library to implement OData, and it is quite stable by now. You can find it here.

Enough with the theory, how can we implement this query protocol in my ASP.NET Core Application?

letscode

Implementing your API

Let’s start creating a simple ASP.NET Core Web API Application on Visual Studio and creating our models.

public class Author
{
public Author()
{
Books = new Collection<Book>();
}
[Key]
public int Id { get; set; }
[Required]
public string Name { get; set; }
public virtual ICollection<Book> Books { get; set; }
}
public class Publisher
{
public Publisher()
{
Books = new Collection<Book>();
}
[Key]
public int Id { get; set; }
[Required]
public string Name { get; set; }
public virtual ICollection<Book> Books { get; set; }
}
public class Book
{
[Key]
public int Id { get; set; }
[Required]
public string Name { get; set; }
public int AuthorId { get; set; }
public Author Author { get; set; }
public int PublisherId { get; set; }
public Publisher Publisher { get; set; }
}

Also, let’s create our DbContext…

public class BooksContext : DbContext
{
public DbSet<Book> Books { get; set; }
public DbSet<Author> Authors { get; set; }
public DbSet<Publisher> Publishers { get; set; }
public BooksContext() : base()
{
}
public BooksContext(DbContextOptions options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Publisher>()
.HasMany(p => p.Books)
.WithOne(b => b.Publisher)
.HasForeignKey(b => b.PublisherId);
modelBuilder.Entity<Author>()
.HasMany(a => a.Books)
.WithOne(b => b.Author)
.HasForeignKey(b => b.AuthorId);
}
}

…and configure our Services.

public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<BooksContext>(options =>
{
options.UseSqlite("Data Source=Books.db");
});
services.AddMvc().AddJsonOptions(opt =>
{
opt.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
opt.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
});
}

Good! Our plumbing is set, now we are going to seed some initial data to our database.

internal static class BooksInitializer
{
private static bool _initialized = false;
private static object _lock = new object();
private static List<Author> authors;
private static List<Book> books;
private static List<Publisher> publishers;
public static void Seed(BooksContext context)
{
AddPublishers(context);
AddAuthors(context);
AddBooks(context);
}
internal static void Initialize(BooksContext context)
{
if (!_initialized)
{
lock (_lock)
{
if (_initialized)
return;
InitializeData(context);
}
}
}
private static void AddAuthors(BooksContext context)
{
authors = new List<Author>
{
// Data
};
if (!context.Authors.Any())
{
context.Authors.AddRange(authors);
context.SaveChanges();
}
}
private static void AddBooks(BooksContext context)
{
books = new List<Book>
{
// Data
};
if (!context.Books.Any())
{
context.Books.AddRange(books);
context.SaveChanges();
}
}
private static void AddPublishers(BooksContext context)
{
publishers = new List<Publisher>
{
// Data
};
if (!context.Publishers.Any())
{
context.Publishers.AddRange(publishers);
context.SaveChanges();
}
}
private static void InitializeData(BooksContext context)
{
context.Database.Migrate();
Seed(context);
}
}

And now we call our seeder on app startup.

public static void Main(string[] args)
{
var host = BuildWebHost(args);
using (var scope = host.Services.CreateScope())
{
var services = scope.ServiceProvider;
var context = services.GetRequiredService<BooksContext>();
BooksInitializer.Initialize(context);
}
host.Run();
}

We must not forget to add our migration.

PS C:\BooksAPI\BooksAPI.REST>dotnet ef migrations add Initial
Done. To undo this action, use 'ef migrations remove'

And last, but not least, let’s implement the simplest API possible on our 3 entities.

[Produces("application/json")]
[Route("api/Authors")]
public class AuthorsController : Controller
{
private readonly BooksContext context;
public AuthorsController(BooksContext context) => this.context = context; // GET: api/Authors
[HttpGet]
public IEnumerable<Author> Get() => context.Authors.Include(r => r.Books).ThenInclude(b => b.Publisher);
}
[Produces("application/json")]
[Route("api/books")]
public class BooksController : Controller
{
private readonly BooksContext context;
public BooksController(BooksContext context) => this.context = context; // GET: api/books
[HttpGet]
public IEnumerable<Book> Get() => context.Books.Include(b => b.Author).Include(b => b.Publisher);
}
[Produces("application/json")]
[Route("api/Publishers")]
public class PublishersController : Controller
{
private readonly BooksContext context;
public PublishersController(BooksContext context) => this.context = context; // GET: api/Publishers
[HttpGet]
public IEnumerable<Publisher> Get() => context.Publishers.Include(r => r.Books).ThenInclude(b => b.Author);
}

Done. Now we can test it using Postman:

Wow! It certainly looks like a nice API, doesn’t it? What is the problem with it? Why would I ever want to add OData to it?

Well, there are two fundamental problems with this approach to our API: the payload size and the querying of the data from the database.

Payload Size

The payload format is completely defined on the API/Server side of the application and the client cannot define which data he really needs to receive.

This can be made more flexible by adding complexity to the code (more parameters? more calls?) but this is not what we want right?

In the most common scenarios the client will simply have to ignore a lot of data that he doesn’t care about.

Look at the result of our query for Books below and tell me what should we do if I only want the name for the first book on the list?

We have no options here other than accept all this data and filter what we need on client side.

[
{
"id": 1,
"name": "Into Thin Air",
"authorId": 4,
"author": {
"id": 4,
"name": "Jon Krakauer",
"books": []
},
"publisherId": 2,
"publisher": {
"id": 2,
"name": "Anchor",
"books": []
}
},
// ... a LOT more Data
{
"id": 13,
"name": "Harry Potter And The Goblet Of Fire",
"authorId": 3,
"author": {
"id": 3,
"name": "J.K. Rowling",
"books": [
{
"id": 10,
"name": "Harry Potter and the Sorcerer's Stone",
"authorId": 3,
"publisherId": 3,
"publisher": {
"id": 3,
"name": "Scholastic",
"books": [
{
"id": 11,
"name": "Harry Potter And The Chamber Of Secrets",
"authorId": 3,
"publisherId": 3
},
{
"id": 12,
"name": "Harry Potter and the Prisoner of Azkaban",
"authorId": 3,
"publisherId": 3
}
]
}
},
{
"id": 11,
"name": "Harry Potter And The Chamber Of Secrets",
"authorId": 3,
"publisherId": 3,
"publisher": {
"id": 3,
"name": "Scholastic",
"books": [
{
"id": 10,
"name": "Harry Potter and the Sorcerer's Stone",
"authorId": 3,
"publisherId": 3
},
{
"id": 12,
"name": "Harry Potter and the Prisoner of Azkaban",
"authorId": 3,
"publisherId": 3
}
]
}
},
{
"id": 12,
"name": "Harry Potter and the Prisoner of Azkaban",
"authorId": 3,
"publisherId": 3,
"publisher": {
"id": 3,
"name": "Scholastic",
"books": [
{
"id": 10,
"name": "Harry Potter and the Sorcerer's Stone",
"authorId": 3,
"publisherId": 3
},
{
"id": 11,
"name": "Harry Potter And The Chamber Of Secrets",
"authorId": 3,
"publisherId": 3
}
]
}
}
]
},
"publisherId": 3,
"publisher": {
"id": 3,
"name": "Scholastic",
"books": [
{
"id": 10,
"name": "Harry Potter and the Sorcerer's Stone",
"authorId": 3,
"author": {
"id": 3,
"name": "J.K. Rowling",
"books": [
{
"id": 11,
"name": "Harry Potter And The Chamber Of Secrets",
"authorId": 3,
"publisherId": 3
},
{
"id": 12,
"name": "Harry Potter and the Prisoner of Azkaban",
"authorId": 3,
"publisherId": 3
}
]
},
"publisherId": 3
},
{
"id": 11,
"name": "Harry Potter And The Chamber Of Secrets",
"authorId": 3,
"author": {
"id": 3,
"name": "J.K. Rowling",
"books": [
{
"id": 10,
"name": "Harry Potter and the Sorcerer's Stone",
"authorId": 3,
"publisherId": 3
},
{
"id": 12,
"name": "Harry Potter and the Prisoner of Azkaban",
"authorId": 3,
"publisherId": 3
}
]
},
"publisherId": 3
},
{
"id": 12,
"name": "Harry Potter and the Prisoner of Azkaban",
"authorId": 3,
"author": {
"id": 3,
"name": "J.K. Rowling",
"books": [
{
"id": 10,
"name": "Harry Potter and the Sorcerer's Stone",
"authorId": 3,
"publisherId": 3
},
{
"id": 11,
"name": "Harry Potter And The Chamber Of Secrets",
"authorId": 3,
"publisherId": 3
}
]
},
"publisherId": 3
}
]
}
}
]

Querying the Data

For much of the same reason, all the queries to the database have to be done in a very rigid way, not allowing for smaller queries whenever possible.

In the same query as above, we just sent a request for a list of books, and let’s have a look at what was sent to the database:

SELECT "b"."Id", "b"."AuthorId", "b"."Name", "b"."PublisherId", "b.Publisher"."Id", "b.Publisher"."Name", "b.Author"."Id", "b.Author"."Name"
FROM "Books" AS "b"
INNER JOIN "Publishers" AS "b.Publisher" ON "b"."PublisherId" = "b.Publisher"."Id"
INNER JOIN "Authors" AS "b.Author" ON "b"."AuthorId" = "b.Author"."Id""

All this huge query just to get the name of one book. That doesn’t sound good, right?

Let’s make it better with OData :)

Changing our API to OData

The good news is that we can use much of the same structure for our OData API, we just need to make a few configurations. Let’s start by installing the package.

PM> Install-Package Microsoft.AspnetCore.Odata -Version 7.0.0-beta1
Successfully installed 'Microsoft.AspNetCore.OData 7.0.0-beta1' to OData.Books

As you can see the OData package for .NET Core is still in beta, as opposed to the .NET Framework version of the package, who is stable for a long time already. I have high hopes that this package will be out of beta in no time!

Let’s configure our entities to understand the OData commands.

public class BooksModelBuilder
{
public IEdmModel GetEdmModel(IServiceProvider serviceProvider)
{
var builder = new ODataConventionModelBuilder(serviceProvider);
builder.EntitySet<Book>(nameof(Book))
.EntityType
.Filter() // Allow for the $filter Command
.Count() // Allow for the $count Command
.Expand() // Allow for the $expand Command
.OrderBy() // Allow for the $orderby Command
.Page() // Allow for the $top and $skip Commands
.Select();// Allow for the $select Command;
builder.EntitySet<Author>(nameof(Author))
.EntityType
.Filter() // Allow for the $filter Command
.Count() // Allow for the $count Command
.Expand() // Allow for the $expand Command
.OrderBy() // Allow for the $orderby Command
.Page() // Allow for the $top and $skip Commands
.Select() // Allow for the $select Command
.ContainsMany(x => x.Books)
.Expand();
builder.EntitySet<Publisher>(nameof(Publisher))
.EntityType
.Filter() // Allow for the $filter Command
.Count() // Allow for the $count Command
.Expand() // Allow for the $expand Command
.OrderBy() // Allow for the $orderby Command
.Page() // Allow for the $top and $skip Commands
.Select() // Allow for the $select Command
.HasMany(x => x.Books)
.Expand();
return builder.GetEdmModel();
}
}

I commented the function of each call on this class, pay close attention to it, as the calls are paramount for the full use of the URL Conventions. And now let’s wire our OData configuration with the rest of the API.

public void ConfigureServices(IServiceCollection services)
{
// ... Other Configurations
services.AddOData();
services.AddTransient<BooksModelBuilder>();
// ... MVC Service Configurations
}
public void Configure(IApplicationBuilder app, IHostingEnvironment env, BooksModelBuilder modelBuilder)
{
// ... Other Configurations
app.UseMvc(routeBuilder =>
{
routeBuilder.MapODataServiceRoute("ODataRoutes", "odata", modelBuilder.GetEdmModel(app.ApplicationServices));
});
}

All good! Finally, we must adjust our three controllers and have then accept OData URLs. Things you should notice were changed on the Controllers:

  • All the controllers were renamed to be in the singular form. That is only necessary due to our configuration in the ModelBuilder, they can be configured to be plural.
  • The return types were all changed to IQueryable<T>
  • The .Include() calls were removed, as they are no longer necessary. The OData package will take care of this for you.
  • We are no longer inheriting from Controller but from ODataController
  • We have a new decorator for the API calls: [EnableQuery]
[Produces("application/json")]
public class AuthorController : ODataController
{
private readonly BooksContext context;
public AuthorController(BooksContext context) => this.context = context; // GET: odata/Author
[EnableQuery]
public IQueryable<Author> Get() => context.Authors.AsQueryable();
}
[Produces("application/json")]
public class BookController : ODataController
{
private readonly BooksContext context;
public BookController(BooksContext context) => this.context = context; // GET: odata/Book
[EnableQuery]
public IQueryable<Book> Get() => context.Books.AsQueryable();
}
[Produces("application/json")]
public class PublisherController : ODataController
{
private readonly BooksContext context;
public PublisherController(BooksContext context) => this.context = context; // GET: odata/Publisher
[EnableQuery]
public IQueryable<Publisher> Get() => context.Publishers.AsQueryable();
}

And that is it! Our API is ready to be used with OData URL Conventions. Let’s try it?

New API and Results

You can play with the new API format on Postman:

The original call to get books would look like this:

http://localhost:5000/api/books

The new call will look like this

http://localhost:5000/odata/Book

First of all, let’s try to get a list of books and look at the results:

{
"@odata.context": "http://localhost:5000/odata/$metadata#Book",
"value": [
{
"Id": 1,
"Name": "Into Thin Air",
"AuthorId": 4,
"PublisherId": 2
},
// ... Other Books
{
"Id": 13,
"Name": "Harry Potter And The Goblet Of Fire",
"AuthorId": 3,
"PublisherId": 3
}
]
}

MUCH Cleaner, right? Let’s even make it smaller, as we just want the name of the first book on the list:

http://localhost:5000/odata/Book?$top=1

{
"@odata.context": "http://localhost:5000/odata/$metadata#Book",
"value": [
{
"Id": 1,
"Name": "Into Thin Air",
"AuthorId": 4,
"PublisherId": 2
}
]
}

And let’s have a look at the database query for this call:

SELECT "$it"."Id", "$it"."AuthorId", "$it"."Name", "$it"."PublisherId"
FROM "Books" AS "$it"
ORDER BY "$it"."Id"
LIMIT @__TypedProperty_0"

Yes! A much more efficient call! But wait… We just need the NAME of the book, why don’t we make it more specific?

http://localhost:5000/odata/Book?$top=1&$select=Name

{
"@odata.context": "http://localhost:5000/odata/$metadata#Book(Name)",
"value": [
{
"Name": "Into Thin Air"
}
]
}

And that is an awesomely small payload! And the query is also more efficient

SELECT "$it"."Id" AS "Value0", "$it"."Name" AS "Value"
FROM "Books" AS "$it"
ORDER BY "Value0"
LIMIT @__TypedProperty_0"

What if you want details about any specific Author and all his related books?

http://localhost:5000/odata/Author?$expand=Books&$filter=Name eq ‘J.K. Rowling’

{
"@odata.context": "http://localhost:5000/odata/$metadata#Author",
"value": [
{
"Id": 3,
"Name": "J.K. Rowling",
"Books@odata.context": "http://localhost:5000/odata/$metadata#Author(3)/Books",
"Books": [
{
"Id": 10,
"Name": "Harry Potter and the Sorcerer's Stone",
"AuthorId": 3,
"PublisherId": 3
},
{
"Id": 11,
"Name": "Harry Potter And The Chamber Of Secrets",
"AuthorId": 3,
"PublisherId": 3
},
{
"Id": 12,
"Name": "Harry Potter and the Prisoner of Azkaban",
"AuthorId": 3,
"PublisherId": 3
},
{
"Id": 13,
"Name": "Harry Potter And The Goblet Of Fire",
"AuthorId": 3,
"PublisherId": 3
}
]
}
]
}

Amazing, isn’t it? That can really increase the quality of our APIs.

As a last piece of information, let’s not forget that OData is designed to be readable by machines! So we have a couple of out-of-the-box urls with documentation on our API:

http://localhost:5000/odata/$metadata

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
<edmx:DataServices>
<Schema Namespace="BooksAPI.OData.Models" xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityType Name="Book">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Edm.Int32" Nullable="false" />
<Property Name="Name" Type="Edm.String" Nullable="false" />
<Property Name="AuthorId" Type="Edm.Int32" />
<Property Name="PublisherId" Type="Edm.Int32" />
<NavigationProperty Name="Author" Type="BooksAPI.OData.Models.Author">
<ReferentialConstraint Property="AuthorId" ReferencedProperty="Id" />
</NavigationProperty>
<NavigationProperty Name="Publisher" Type="BooksAPI.OData.Models.Publisher">
<ReferentialConstraint Property="PublisherId" ReferencedProperty="Id" />
</NavigationProperty>
</EntityType>
<EntityType Name="Author">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Edm.Int32" Nullable="false" />
<Property Name="Name" Type="Edm.String" Nullable="false" />
<NavigationProperty Name="Books" Type="Collection(BooksAPI.OData.Models.Book)" ContainsTarget="true" />
</EntityType>
<EntityType Name="Publisher">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Edm.Int32" Nullable="false" />
<Property Name="Name" Type="Edm.String" Nullable="false" />
<NavigationProperty Name="Books" Type="Collection(BooksAPI.OData.Models.Book)" />
</EntityType>
</Schema>
<Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityContainer Name="Container">
<EntitySet Name="Book" EntityType="BooksAPI.OData.Models.Book">
<NavigationPropertyBinding Path="Author" Target="Author" />
<NavigationPropertyBinding Path="Publisher" Target="Publisher" />
</EntitySet>
<EntitySet Name="Author" EntityType="BooksAPI.OData.Models.Author" />
<EntitySet Name="Publisher" EntityType="BooksAPI.OData.Models.Publisher">
<NavigationPropertyBinding Path="Books" Target="Book" />
</EntitySet>
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>

Cool, isn’t it?

What’s Next?

Well, now that you know how simple it is to implement the OData protocol in .NET, I would recommend that you spend some time getting familiar with the Protocol itself, you can find all the guidance that you need here.

Also, if you have the intention to use the protocol with .NET Core, I suggest that you keep a close eye on the Nuget Package page and also the feature request on GitHub.

Source Code

You can find the whole source code for this solution on my GitHub.

And that is all for today folks :)

I hope you enjoyed it, and don’t hesitate to use the comments section if you were left with any questions.

Sources:

Originally published at The Azure Coder.

--

--