Challenges with OData- Backwards Compatibility and Flexible Response Formatting

[fykou]

Hi,

Sytem

• NET 8
• EF core
• ASP.NET Core OData 8.x
• Asp.Versioning.OData 8.x aspnet-api-versioning

Background

About two years ago, my team decided to implement OData in our existing API, despite having no prior experience with it. Due to the nature of our application and our customers' needs, we must ensure backwards compatibility with nearly all changes.

At the time, our API was split into two:

1. SQL-based endpoints – Data fetching was done using raw SQL and DataTables.
2. EF Code-First endpoints – Used Entity Framework for data retrieval.

Initial OData Implementation

• We started by implementing OData as a custom OData parser for the SQL-based endpoints.
• This involved tokenizing OData parameters (e.g., $filter, $skip) from requests and applying operations to SQL commands.
• Due to the extensive list of OData operations and the complexity of manually constructing SQL queries, we only supported a subset of operations on $filter.
• $select and $expand were not supported at all.

For the EF Code-First endpoints, the implementation was smoother, but:

• The main challenge was maintaining backwards compatibility while integrating OData's due to its heavy focus on conventions, especially when it comes to routing.
• Even today, we struggle to include a simple api/v1/endpointCategory/... resource prefix without breaking parts of OData.

The Current Issue

We recently refactored the old SQL-based endpoints to use EF (Database-First) and OData. However, we now face a new set of challenges:

• The tables for the entities are denormalized (but all entities has some common columns).
• The properties that should be included in the response depend on multiple factors like different query parameters, user access and mapping tables.
• We use a custom implementation for reference classes that is based on inheritance. Eg. both FirstEntityClass and SecondEntityClass inherits from SystemClass. System class has _guid, source, ... defined, which tells us that both the forementioned classes has these columns as well. By recursing and concatenating all attributes on the base-class all the way up to the top level class, we know what attributes to expose on the endpoint. The reason it is like this is because we also use this structure for operations in Azure Data Factory.
• In addition to reference classes we have a mapping table used to map the name from the table to the property name we want to expose (query params supports changing mapping set), this works in the same way as how you would make a DTO class, but ours is stored in the database and can be changed during runtime (this is also used by ADF).
• The response must support formatting in multiple formats based on query parameters like. Formats can be standard JSON, flat format and EAV (Entity-Attribute-Value). We also have custom formats for specific customers.

Question

Given that we're too committed now to back out and the above constraints, how can we best approach this implementation while ensuring:

• OData query parameter support (from the customers point of view)
• Flexibility in response formatting
• Backwards compatibility, especially when it comes to routing

I guess the root of my problems is that odata seems to me to be an all-or-nothing type of standard, I would love to just use the ODataQueryOptions for building select, expand, filter, top and skip without all the extra stuff that follows like the $metadata inspection (which I do like, but I would give this up in a hartbeat if I had to for this to work properly) and the $data debug endpoint.

The current state (attempt at a PoC) of the code for this service looks something like this:

// Program.cs odata section
builder.Services
       .AddApiVersioning(options => { options.ReportApiVersions = true; })
       .AddOData(options =>
        {
            options.AddRouteComponents("api/v{version:apiVersion}/Objects");
            // ... other paths for other services
        });


//EDM model config built using Asp.Versioning.ApiVersion
public class GenericDataModelConfiguration : IModelConfiguration
{
    public void Apply(ODataModelBuilder builder, ApiVersion apiVersion, string? routePrefix)
    {
        if (routePrefix == "api/v{version:apiVersion}/Objects")
        {
            builder.EntitySet<SomeDataEntity>("SomeDataEntities").EntityType.HasKey(x => x._guid).HasKey(x => x._guid).Page().Select().Filter().OrderBy().Expand().Count();
            builder.EntitySet<SomeOtherDataEntity>("SomeOtherDataEntities").EntityType.HasKey(x => x._guid).HasKey(x => x._guid).Page().Select().Filter().OrderBy().Expand().Count();
        }
    }
}


// Controller
[ApiController]
[ApiVersion(1.0)]
[Route("api/v{version:apiVersion}/Objects")]
public class GenericDataController(DataService dataService) : ODataController
{
    [HttpGet("{assetName}/SomeDataEntities")]
    public IActionResult GetSomeDataEntity(
        // ... more params
        ODataQueryOptions<SomeDataEntity> options
    )
    {
        var userGuid = ... //from claims
        var requestParams = new RequestParamsType(...)

        var response = dataService.GetGenericObjectData(userGuid, requestParams, options);
        return response.ToObjectResult();
    }

    // ... more controller methods like [HttpGet("{assetName}/SomeOtherDataEntity")]
}


// Service
public Dictionary<string, object>> GetGenericObjectData<TEntity>(Guid userGuid, RequestParamsType requestParams, ODataQueryOptions<TEntity> options)
        where TEntity : GenericObjectBaseAbstractClass // all scaffolded entities inherit this
    {
        var dbSet = dbContext.Set<TEntity>();
        var entityQuery = dbSet.AsQueryable();

        // filters the query based on user permissions
        entityQuery = await authService.FilterOnPermissionScope(entityQuery, userGuid);

        // entityQuery = (IQueryable<TEntity>)options.ApplyTo(entityQuery); // unable to make include and options.expand work with this
        entityQuery = await entityQuery.GetQueryAsync(mapper, options); // AutoMapper.Extensions.OData, only because I cant get the above to work. mapping here is TEntity <-> TEntity

        entityQuery.Include(x => x.source); // required to build parts of the response (added in the formatter)

        var classHeirarchyObjects = dbContext.ObjectClass.irrelevantStuff;

        var valueMappingDict = dbContext.ValueMapping.irrelevantStuff;

		// initResponseTemplateWithoutData here is a custom class that contains sourceMappingValue, destinationMappingValue, some metadata (like hidden field and such), and a value field which is empty.
        var initResponseTemplateWithoutData = responseFormatterService.BuildResponseTemplate(classHeirarchyObjects, options.SelectExpand?.RawExpand); 
        var materializedData = await entityQuery.ToListAsync();
        var responseObj = responseFormatterService.PopulateData(responseObj, materializedData); // responseObj here is same as initResponseTemplateWithoutData, but with data.
        responseObj = responseFormatterService.MapPropertyNames(responseObj, valueMappingDict);

        IEnumerable<Dictionary<string, object?>> result;
        switch (requestParams.Format)
        {
            case "flat":
                result = responseObj.ToFlat();
                break;
            case "eav":
                result = responseObj.ToEav();
                break;
            // ... more formats
            default:
                result = responseObj.ToDefault();
                break;
        }

        // workaround because modifying the response removes the odata format
        var response = new Dictionary<string, object>
        {
            { "@odata.context", null },
            { "value", result }
        };

        return new Dictionary<string, object>?(response);
    }

[julealgon]

I won't pretend I have even a good suggestion here, but this:

In addition to reference classes we have a mapping table used to map the name from the table to the property name we want to expose (query params supports changing mapping set), this works in the same way as how you would make a DTO class, but ours is stored in the database and can be changed during runtime (this is also used by ADF).

Seems like absolute hell to me... at this point it feels like you are fighting the framework so much that it might not be worthwhile trying to use it at all.

Having said that, if I had such a crazy system, I would maybe look at how AutoMapper.OData works and maybe do something similar using your dynamic mapping mechanism, but even that is a stretch.

To be fair though, if I really was in your position, I would be pursuing some sort of simplification that avoided this in the first place.

[julealgon]

On the other hand, I would suggest looking into implementing custom output formatters in your API to add support to your various formats instead of implementing it inline like that at the controller action level.

I'm pretty sure that part should be completely independent of how the rest of OData works.