Using OData with ASP.NET Core Web API

In this article, we will learn how to use OData with ASP.NET Core Web API services.

OData (Open Data Protocol) is an open protocol that defines a set of best practices for building and consuming RESTful APIs. Initiated by Microsoft in 2007, it has become an OASIS standard and has been approved by ISO/IEC. Currently, OData is in version 4. 

Let’s start.

Introduction to OData

OData follows common practices for building RESTful web services. For instance, it uses URLs to identify resources and HTTP verbs like GET, POST, etc. to define operations on them. Data exposed by the OData service is described by an Entity Data Model (EDM), while JSON or AtomPub are used for message encoding.

OData can be compared with GraphQL as both standards can be used for API development. OData is simpler and easier to understand, but less powerful than GraphQL. Since it is based on REST, it is easier to pick up and create or migrate existing APIs, while GraphQL will need a lot of work to make it work for our API.

Let’s consider the case where we are building a web service that provides information on companies and their products. In order to request a resource using OData we can send a simple GET request to the web service:

GET /odata/companies

This will get us all the available entries. We can also query for a specific entry, by providing its ID:

GET /odata/companies(23)

Moreover, we may send query options to the web service, in the same way, that we send query string parameters in GET requests:

GET /odata/companies?$top=5&$skip=10

Here we request the top 5 results, after skipping the first 10 entries. Note that all options in OData start with the dollar sign ($) and that they can be combined in the same query.

Here is a list of the query options available in OData:

• /odata/companies?$top=5  –   Get the top 5 entries
• /odata/companies?$count=true   –   Get the total entry count (along with all the entries)
• /odata/companies?$filter=city eq ‘New York’   –   Get entries where city=’New York’
• /odata/companies?$filter=contains(city, 'York')   –   Get entries where the city contains ‘York’
• /odata/companies?$orderby=Name   –   Order entries by company name
• /odata/companies?$skip=5   –   Skip the five first entries and return the rest
• /odata/companies?$select=ID,Name   –   Return only the ID and Name of all companies
• /odata/companies?$expand=Products   –   Include also the products produced by the companies

OData can also be used for the creation, update, or deletion of resources using POST, PUT or DELETE requests respectively.

Prepare ASP.NET Core Web API Project

Microsoft provides support for the OData standard (currently at version 4), with the Microsoft.AspNetCore.OData package(currently at version 8.0.11).

For this article, we will implement the above-mentioned scenario and create a web service that uses OData with ASP.NET Core Web API to create an endpoint for data on companies and products.

The Model

First, we define our model:

public class Company
{
    public int ID { get; set; }
    public string? Name { get; set; }
    public int Size { get; set; }
    public List<Product>? Products { get; set; }
}

Each company provides a range of products:

public class Product
{
    public int ID { get; set; }
    public int CompanyID { get; set; }
    public string? Name { get; set; }
    public decimal Price { get; set; }
}

We will use the in-memory database for this article. For that, we are going to implement the Context class:

public class ApiContext: DbContext
{
    public ApiContext(DbContextOptions<ApiContext> options)
        : base(options)
    {
    }

    public DbSet<Company> Companies { get; set; }
    public DbSet<Product> Products { get; set; }
}

We also create a simplified repository class that will handle the interactions with the database:

public class CompanyRepo : ICompanyRepo
{
    private readonly ApiContext _context;
    public CompanyRepo(ApiContext context)
    {
        _context = context;
    }

    public IQueryable<Company> GetAll()
    {
        return _context.Companies
            .Include(a => a.Products)
            .AsQueryable();
    }

    public IQueryable<Company> GetById(int id)
    {
        return _context.Companies
            .Include(a => a.Products)
            .AsQueryable()
            .Where(c => c.ID == id);
    }

    public void Create(Company company)
    {
        _context.Companies
            .Add(company);
        _context.SaveChanges();
    }

    public void Update(Company company)
    {
        _context.Companies
            .Update(company);
        _context.SaveChanges();
    }

    public void Delete(Company company)
    {
        _context.Companies
            .Remove(company);
        _context.SaveChanges();
    }
}

The CompanyRepo class implements the ICompanyRepo interface:

public interface ICompanyRepo
{
    public IQueryable<Company> GetAll();
    public IQueryable<Company> GetById(int id);
    public void Create(Company company);
    public void Update(Company company);
    public void Delete(Company company);
}

Note that both our GetXXX() methods return a reference to IQueryable<Company>. OData will use this interface to perform queries on company data.

Using OData With ASP.NET Core Web API

Now, we proceed with creating our controller. The controller seems like a usual RESTful controller and actually, it can work like one. This means that we can send a GET request to /api/companies and receive a list of all companies in the repository.

[Route("api/[controller]")]
[ApiController]
public class CompaniesController : ControllerBase
{
    private readonly ICompanyRepo _repo;
    public CompaniesController(ICompanyRepo repo)
    {
        _repo = repo;
    }

    ...
}

GET requests

The power of OData lies mainly in the functionality that it offers for performing queries. Therefore, the GET requests are the most interesting part of OData. In the controller, we implement two types of GET requests:

[EnableQuery(PageSize = 3)]
[HttpGet]
public IQueryable<Company> Get()
{
    return _repo.GetAll();
}

[EnableQuery]
[HttpGet("{id}")]
public SingleResult<Company> Get([FromODataUri] int key)
{
    return SingleResult.Create(_repo.GetById(key));
}

In order to enable OData, we do three things:

• Annotate the respective methods with the [EnableQuery] attribute
• Made the first method return an IQueryable reference to the company list
• Made the second method return a SingleResult reference to the selected item in the company list

The [EnableQuery] attribute enables clients to send queries, by using query options such as $filter, $sort, and $page. By using IQueryable, OData will be able to query the list in various ways. Note that we can also define a default page size (3) for each request.

You should pay special care to the second method: if you define the method input parameter with a name other than key then this method will not work!

POST/PUT/DELETE Requests With OData and ASP.NET Core Web API

We can support all CRUD requests with OData, by adding the respective methods in the companies controller. The POST request inserts a new company into the database:

[HttpPost]
public IActionResult Post([FromBody] Company company)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    _repo.Create(company);

    return Created("companies", company);
}

With the PUT request, we can update an entry:

[HttpPut]
public IActionResult Put([FromODataUri] int key, [FromBody] Company company)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }

    if (key != company.ID)
    {
        return BadRequest();
    }

    _repo.Update(company);

    return NoContent();
}

Finally, we can delete a company from the database, by using the DELETE method:

[HttpDelete]
public IActionResult Delete([FromODataUri] int key)
{
    var company = _repo.GetById(key);
    if (company is null)
    {
        return BadRequest();
    }

    _repo.Delete(company.First());

    return NoContent();
}

OData Setup

Finally, we have to register OData in Program.cs:

static IEdmModel GetEdmModel()
{
    ODataConventionModelBuilder builder = new();
    builder.EntitySet<Company>("Companies");
    return builder.GetEdmModel();
}

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers()
    .AddOData(options => options
        .AddRouteComponents("odata", GetEdmModel())
        .Select()
        .Filter()
        .OrderBy()
        .SetMaxTop(20)
        .Count()
        .Expand()
    );

builder.Services.AddDbContext<ApiContext>(opt => opt.UseInMemoryDatabase(databaseName: "CompaniesDB"));
builder.Services.AddScoped<ICompanyRepo, CompanyRepo>();
var app = builder.Build();
DBSeeder.AddCompaniesData(app);

...

We register Oda with the AddOData() method, where we also define a number of options regarding the capabilities of our web service. Here, we create an OData endpoint that will:

• contain “odata” in the URL (instead of  the usual “api”)
• enable selection, filtering, ordering, counting, and expansion functionality
• return up to 20 entries at each request

The AddOData() method uses the GetEdmModel()method that returns the data model, which is the basis of an OData service. The OData service uses an abstract data model called Entity Data Model (EDM) to describe the exposed data in the service. The ODataConventionModelBuilder class creates an EDM by using a set of default naming conventions EDM, an approach that requires the least code. We can use the ODataModelBuilder class to create the EDM if we want more control over the EDM.

We should pay attention to the EntitySet method: we should provide the exact name of the controller (Companies) as its parameter. If we use a different name for the entity set (e.g. Company instead of Companies), then OData will not work.

Finally, in order to seed the in-memory database, we run the AddCompaniesData() static method, which we define in a separate class (DBSeeder.cs):

public class DBSeeder
{
    public static void AddCompaniesData(WebApplication app)
    {
        var scope = app.Services.CreateScope();
        var db = scope.ServiceProvider.GetService<ApiContext>();

        db.Companies.Add(
            new Company()
            {
                ID = 1,
                Name = "Company A",
                Size = 25
            });

        //more companies here...
        
        db.Products.Add(
            new Product()
            {
                ID = 1,
                CompanyID = 1,
                Name = "Product A",
                Price = 10
            });

        //more products here..

        db.SaveChanges();
    }
}

Now, we are ready to run our OData service and test the API!

Testing the API

Using Postman, or a web browser, we can send a GET request to our web service: /odata/companies?$filter=Size gt 20&$count=true.

After we do that, we can inspect the returned JSON payload as a result:

{
  "@odata.context": "https://localhost:7004/odata/$metadata#Companies",
  "@odata.count": 3,
  "value": [
    {
      "ID": 1,
      "Name": "Company A",
      "Size": 25
    },
    {
      "ID": 2,
      "Name": "Company B",
      "Size": 56
    },
    {
      "ID": 4,
      "Name": "Company D",
      "Size": 205
    }
  ]
}

We see that the companies’ data returned by OData do not contain any information about their respective products. In order to get the products too, we additionally need to send the $expand option: /odata/companies?$filter=Size gt 20&$count=true&$expand=Products.

The results now contain also information about products:

{
  "@odata.context": "https://localhost:7004/odata/$metadata#Companies(Products())",
  "@odata.count": 3,
  "value": [
    {
      "ID": 1,
      "Name": "Company A",
      "Size": 25,
      "Products": [
        {
          "ID": 1,
          "Name": "Product A",
          "Price": 10
        },
        {
          "ID": 2,
          "Name": "Product B",
          "Price": 35
        }
      ]
    },
    
    // --> more companies and products here
  ]
}

Next, we create a new company by sending a POST request to URL /odata/companies with the body:

{
    "ID": 10,
    "Name": "Company Z",
    "Size": 100
}

The returned response contains the newly created object:

{
    "@odata.context": "https://localhost:7004/odata/$metadata#Companies/$entity",
    "ID": 10,
    "Name": "Company Z",
    "Size": 100
}

We can also modify an existing entry (the one with ID=1) by sending a PUT request to the URL /odata/companies(1) with the body:

{
    "ID": 1,
    "Name": "Company A",
    "Size": 350
}

Finally, we may delete an entry (again with ID=1) by sending a DELETE request to the URL /odata/companies(1). Both PUT and DELETE methods return a 204 No Content response.

Conclusion

In this article, we have learned how to incorporate the OData standard in our ASP.NET Core projects, in order to create web APIs with extensive querying capabilities.