Exploring Odata - Benefits and drawbacks

In the dynamic world of technology, where innovation often means breaking the rules and exploring new approaches, we should not ignore also the importance of standards and principles. Indeed, sometimes the standards can feel restrictive, potentially feel that they reduce creativity or that they are slowing us down. At other times, a structured approach is crucial because it saves time and money. In this article, I will share my personal experience with OData protocol, mostly on the requesting data part, which was a journey with ups and downs.

Author: Oana Ciocan, Technical Lead

Oana Ciocan, Technical Lead

OData introduction

Let’s start with the definition from https://www.odata.org: “OData (Open Data Protocol) is an open protocol to allow the creation and consumption of queryable and interoperable RESTful APIs in a simple and standard way.”

OData was initially created by Microsoft and then was standardized at OASIS. Given the fact that OData is a standard, there are various libraries (.NET, Java, js, C++, Python, PHP etc.) that can be used to produce and consume OData services.

Let’s consider an example of application

We want to show employees from a company in a table, and we want to be able to filter data from each column and in various combinations.

A table with various filters

How would you solve this requirement? How many endpoints would you expose?

The nice thing about programming is that a problem can have multiple solutions.

Would you choose to write an endpoint for each filter/combination for filters?

[HttpGet("/{id}")]
public ActionResult<IEnumerable<Employee>> GetById([FromRoute] int id)
{
    var result = _employees
        .Where(e => e.Id == id)
        .ToList();
    return Ok(result);
}

[HttpGet("ByFirstName")]
public ActionResult<IEnumerable<Employee>> GetByFirstName(
    [FromQuery] string firstName)
{
    var result = _employees
        .Where(e => e.FirstName
            .Contains(firstName, StringComparison.OrdinalIgnoreCase))
        .ToList();
    return Ok(result);
}

[HttpGet("ByLastName")]
public ActionResult<IEnumerable<Employee>> GetByLastName(
    [FromQuery] string lastName)
{
    var result = _employees
        .Where(e => e.LastName
            .Contains(lastName, StringComparison.OrdinalIgnoreCase))
        .ToList();
    return Ok(result);
}

[HttpGet("ByFirstNameAndLastName")]
public ActionResult<IEnumerable<Employee>> GetByFirstNameAndLastName(
    [FromQuery] string firstName, [FromQuery] string lastName)
{
    var result = _employees
        .Where(e => e.FirstName
            .Contains(firstName, StringComparison.OrdinalIgnoreCase) &&
            e.LastName
            .Contains(lastName, StringComparison.OrdinalIgnoreCase))
        .ToList();
    return Ok(result);
}

Or maybe you would choose to define a DTO class which defines all filtering criteria and implement a method that filters employees by the provided criteria

[HttpGet]
public ActionResult<IEnumerable<Employee>> GetEmployees(
    [FromQuery] EmployeeFilterDto filter)
{
    var filteredEmployees = _employees.AsQueryable();

    if (filter.Id.HasValue)
        filteredEmployees = filteredEmployees
            .Where(e => e.Id == filter.Id.Value);

    if (!string.IsNullOrEmpty(filter.FirstName))
        filteredEmployees = filteredEmployees
            .Where(e => e.FirstName
                .Contains(filter.FirstName, StringComparison.OrdinalIgnoreCase));

    if (!string.IsNullOrEmpty(filter.LastName))
        filteredEmployees = filteredEmployees
            .Where(e => e.LastName
                .Contains(filter.LastName, StringComparison.OrdinalIgnoreCase));

    if (filter.DateOfBirth.HasValue)
        filteredEmployees = filteredEmployees
            .Where(e => e.DateOfBirth.Date == filter.DateOfBirth.Value.Date);

    if (!string.IsNullOrEmpty(filter.Gender))
        filteredEmployees = filteredEmployees
            .Where(e => e.Gender
                .Equals(filter.Gender, StringComparison.OrdinalIgnoreCase));

    if (filter.HireDate.HasValue)
        filteredEmployees = filteredEmployees
            .Where(e => e.HireDate.Date == filter.HireDate.Value.Date);

    if (!string.IsNullOrEmpty(filter.JobTitle))
        filteredEmployees = filteredEmployees
            .Where(e => e.JobTitle
                .Contains(filter.JobTitle, StringComparison.OrdinalIgnoreCase));

    return Ok(filteredEmployees.ToList());
}

Or would you choose to create an endpoint with a line of code and add an attribute on it that does all the work for you?

[EnableQuery]
      [HttpGet]
      public IQueryable<Employee> Get()
      {
          return _employees.AsQueryable();
      }

Basically, with less than 10 lines of code you will be able to query all kinds of data with various filters. What you must do is to:

Import OData package (Microsoft.AspNetCore.OData)
Register OData and configure options needed by you

builder.Services.AddControllers()
    .AddOData(options => options.Filter().Count().Select().Expand().OrderBy());

Add EnableQuery attribute on a Get endpoint which returns IQueryable<Employee>

How would requests look like?

This is the most basic implementation, in which we operate an in-memory list of elements. And in this case, by far, I would choose OData. This approach provides a flexible way to filter and query data, making it easy to handle a variety of scenarios with a single and simple endpoint.

Some examples of standards enforced by OData:

$filter – by using this parameter, you can filter data based on specific conditions
$select – allows you to specify which fields to return
$expand - allows you to retrieve related entities based on property name
$orderby – sort the results based on specific fields ascending or descending
$top, $skip – helps with pagination
Etc.

But is it always that easy?

The truth is that it is not always enough just to add a [EnableQuery] attribute on an endpoint, and it solves all your problems. You might need custom validations, or you might need to add specific business rules, or maybe you have security or performance concerns. For this kind of situation, a powerful class on .Net: ODataQueryOptions<T> allows you to manually process OData query parameters that come from the client.

Let’s suppose that you build software for a company with millions of employees, and it is a business requirement that you filter by department ID. In addition to the business rule, this department filter can also prevent performance issues.

The implementation might look like this:

[HttpGet]
public IActionResult Get(ODataQueryOptions<Employee> queryOptions)
{
    var query = _employees.AsQueryable();

    if (!HasDepartmentFilter(queryOptions))
        return BadRequest("Filtering by Department is required.");

    query = queryOptions.Filter
        .ApplyTo(query, new ODataQuerySettings()) as IQueryable<Employee>;

    return Ok(query);
}

private bool HasDepartmentFilter(ODataQueryOptions<Employee> queryOptions)
{
    if (queryOptions.Filter == null) 
        return false;

    var filterQuery = queryOptions.Filter.RawValue;

    // Check if filter contains 'Department' or 'Department.Id'
    return filterQuery.Contains("Department") || 
           filterQuery.Contains("Department/Id");
}

Now, the controller validates $filter clause and just if the filter string contains Department or Department.Id will apply other OData query options: other filters, $orderBy, pagination ($top, $skip) etc.

Now, that we saw how it works with in-memory data, the next question would be:

Does OData for .NET work with storage systems?

Yes. OData offers a versatile approach to data integration, especially with those database providers that are supported by entity framework. Some examples of storage systems would be

Relational databases: eg. SQL Server.
NoSql Databases: eg. Cosmos DB
Although not as straightforward as the ones mentioned above, a custom implementation allows OData to interact with Azure Blob Storage. For example, custom could mean some “translations” from the OData filter in Blob Storage tags.

Using OData comes with many advantages

For simple APIs with basic CRUD operations, it saves a lot of development and test time. Development because you expose an endpoint which covers a lot of scenarios. Testing time because in the end it is a built-in mechanism that filters, selects, paginates data etc. and if you send the right combination of parameters, you know for sure the response will be correct.
You minimize the chances that a customer requires new functionality and it’s not there already. Basically, you’ll have more chances to cover a (filtering) scenario even though you didn’t think of it in advance or to deliver something without needing additional implementation and deploys.
It provides a standardized way to access and manipulate data across different services, platforms, tools. Once you know the model, you can use the standard parameters and operate data exactly how you want.
It allows you to customize and extend it to fit specific requirements.

But... don’t forget to consider also some red flags before deciding to use it in your project:

Complexity of the data model
OData works well with simple data models, but if your data model is highly complex with many relationships, it might be challenging to expose it efficiently through OData. Consequently, you might end up with excessive customization or performance issues.
Performance overhead
OData flexibility in filtering might lead to scenarios with bad performance. Three things to pay attention to:
The first one would be applying OData filters in memory rather than at database level, particularly with large datasets. Fetching large amounts of data in memory and then applying filters can lead to high memory consumption and slow processing times. Always apply filters at the database level. Configure OData to translate query options like ‘$filter’, ‘$orderby’ into SQL queries executed by the database.
The second one: you propagate filters to the database, but OData supports querying across related entities, which can lead to complex SQL queries with multiple joins. If not optimized, these queries can be slow.
Consider using pagination: ‘$skip’ and ‘$top’ options from OData to avoid fetching large amounts of data in a single query.
Security concerns
Exposing your data through OData endpoints might make it vulnerable to injection attacks or excessive data exposure. A proper authentication and authorization mechanism must be in place. Additionally, you can enhance security by using AutoMapper. With correct DTOs, you can ensure that only necessary data is exposed to the clients. Sensitive data can be excluded from DTOs even though it exists at the entity level.
Complexity of the business logic
If your application has complex business logic, not just straightforward CRUD operations, OData might not be the best choice. It might require many custom implementations, which will ultimately add even more complexity, making it difficult to maintain.

In Conclusion

The goal of this article was to provide an alternative to classical solutions, not the best, not the worst. OData brings many advantages, but it also presents challenges. Before adopting OData in your projects, evaluate your business requirements, how often they can change or how many variations they have, the knowledge of the team working on it, the time they need to be delivered, etc.

References

https://www.odata.org/getting-started/understand-odata-in-6-steps/

Surround yourself with opportunities

Find out more

Insights

Growing your career at Centric

Insight

From Figma to Power Apps