My Experience with OData

The Simplest Use

To begin with, we need a Web service. I'll create it using ASP.NET Core. To use OData, we need to install the Microsoft.AspNetCore.OData NuGet package. Now we must configure it. Here is the content of the Program.cs file:

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services
    .AddControllers()
    .AddOData(opts =>
    {
        opts
            .Select()
            .Expand()
            .Filter()
            .Count()
            .OrderBy()
            .SetMaxTop(1000);
    });

var app = builder.Build();

// Configure the HTTP request pipeline.

app.UseAuthorization();

app.MapControllers();

app.Run();

In the AddOData method, we specify which operations of all possible in OData we allow.

Of course, OData is designed to work with data. Let's add some data to our application. The data definition is very simple:

public class Author
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Required]
    public string LastName { get; set; }

    public string? ImageUrl { get; set; }

    public string? HomePageUrl { get; set; }

    public ICollection<Article> Articles { get; set; }
}

public class Article
{
    [Key]
    public int Id { get; set; }

    public int AuthorId { get; set; }

    [Required]
    public string Title { get; set; }
}

I'll use Entity Framework to work with it. The test data is created using Bogus:

public class AuthorsContext : DbContext
{
    public DbSet<Author> Authors { get; set; } = null!;

    public AuthorsContext(DbContextOptions<AuthorsContext> options)
        : base(options)
    { }

    public async Task Initialize()
    {
        await Database.EnsureDeletedAsync();
        await Database.EnsureCreatedAsync();

        var rnd = Random.Shared;

        Authors.AddRange(
            Enumerable
                .Range(0, 10)
                .Select(_ =>
                {
                    var faker = new Faker();

                    var person = faker.Person;

                    return new Author
                    {
                        FirstName = person.FirstName,
                        LastName = person.LastName,
                        ImageUrl = person.Avatar,
                        HomePageUrl = person.Website,
                        Articles = new List<Article>(
                            Enumerable
                                .Range(0, rnd.Next(1, 5))
                                .Select(_ => new Article
                                {
                                    Title = faker.Lorem.Slug(rnd.Next(3, 5))
                                })
                        )
                    };
                })
        );

        await SaveChangesAsync();
    }
}

As a storage for data, I will use the in-memory Sqlite. Here is the configuration in the Program.cs:

...

var inMemoryDatabaseConnection = new SqliteConnection("DataSource=:memory:");
inMemoryDatabaseConnection.Open();

builder.Services.AddDbContext<AuthorsContext>(optionsBuilder =>
    {
        optionsBuilder.UseSqlite(inMemoryDatabaseConnection);
    }
);

...

using (var scope = app.Services.CreateScope())
{
    await scope.ServiceProvider.GetRequiredService<AuthorsContext>().Initialize();
}

...

Now the storage is ready. Let's create a simple controller that returns data to the client:

[ApiController]
[Route("/api/v1/authors")]
public class AuthorsController : ControllerBase
{
    private readonly AuthorsContext _db;

    public AuthorsController(
        AuthorsContext db
        )
    {
        _db = db ?? throw new ArgumentNullException(nameof(db));
    }

    [HttpGet("no-odata")]
    public ActionResult GetWithoutOData()
    {
        return Ok(_db.Authors);
    }
}

Now at /api/v1/authors/no-odata, we can have the following result:

[
  {
    "id": 1,
    "firstName": "Fred",
    "lastName": "Kuhlman",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/54.jpg",
    "homePageUrl": "donald.com"
  },
  {
    "id": 2,
    "firstName": "Darrel",
    "lastName": "Armstrong",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/796.jpg",
    "homePageUrl": "angus.org"
  },
  ...
]

Naturally, there is no OData support yet. But how difficult is it to add it?

Basic Support of OData

It is easy. Let's create one more endpoint:

[HttpGet("odata")]
[EnableQuery]
public IQueryable<Author> GetWithOData()
{
    return _db.Authors;
}

As you can see, differences are minimal. But now, you can use OData in your queries. For example, the query /api/v1/authors/odata?$filter=id lt 3&$orderby=firstName gives the following result:

[
  {
    "id": 2,
    "firstName": "Darrel",
    "lastName": "Armstrong",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/796.jpg",
    "homePageUrl": "angus.org"
  },
  {
    "id": 1,
    "firstName": "Fred",
    "lastName": "Kuhlman",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/54.jpg",
    "homePageUrl": "donald.com"
  }
]

Great! But there is a small drawback. Our method or controller returns IQueryable<> object. In practice, usually we want to return several variants of responses (e.g., NotFound, BadRequest, ...) What can we do?

It turns out that OData implementation works fine with wrapping IQueryable<> object into Ok:

[HttpGet("odata")]
[EnableQuery]
public IActionResult GetWithOData()
{
    return Ok(_db.Authors);
}

It means that you can add any validation logic into your controller actions.

Paging

As you probably know, OData allows you to get only some particular page of the full result. It can be done using skip and top operators (e.g., /api/v1/authors/odata?$skip=3&$top=2). You must not forget to call the SetMaxTop method while configuring OData in Program.cs. Otherwise, using the top operator may result in the following error:

The query specified in the URI is not valid. 
The limit of '0' for Top query has been exceeded.

But for the full use of the paging mechanism, it is very useful to know how many pages you have in total. We need our endpoint to additionally return total number of items, corresponding to the given filter. OData supports the count operator for this purpose: (/api/v1/authors/odata?$skip=3&$top=2&$count=true). But if we simply add $count=true to our query, that does nothing. In order to get the desired result, we need to configure EDM (entity data model). But first, we must know the address of our endpoint.

Let's say that we want our data to be accessible at /api/v1/authors/edm. This endpoint will return objects of type Author. In this case, OData configuration in the Program.cs file will look like this:

builder.Services
    .AddControllers()
    .AddOData(opts =>
    {
        opts.AddRouteComponents("api/v1/authors", GetAuthorsEdm());

        IEdmModel GetAuthorsEdm()
        {
            ODataConventionModelBuilder edmBuilder = new();

            edmBuilder.EntitySet<Author>("edm");

            return edmBuilder.GetEdmModel();
        }

        opts
            .Select()
            .Expand()
            .Filter()
            .Count()
            .OrderBy()
            .SetMaxTop(1000);
    });

Please note that the route for our components (api/v1/authors) equals to the prefix of the address of our endpoint, and the name of the entity set equals to the rest of this address (edm).

Final touch is adding ODataAttributeRouting attribute to the corresponding method of the controller:

[HttpGet("edm")]
[ODataAttributeRouting]
[EnableQuery]
public IQueryable<Author> GetWithEdm()
{
    return _db.Authors;
}

Now this endpoint for the request /api/v1/authors/edm?$top=2&$count=true will return the following data:

{
  "@odata.context": "http://localhost:5293/api/v1/authors/$metadata#edm",
  "@odata.count": 10,
  "value": [
    {
      "Id": 1,
      "FirstName": "Steve",
      "LastName": "Schaefer",
      "ImageUrl": "https://cloudflare-ipfs.com/ipfs/
                   Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/670.jpg",
      "HomePageUrl": "kylie.info"
    },
    {
      "Id": 2,
      "FirstName": "Stella",
      "LastName": "Ankunding",
      "ImageUrl": "https://cloudflare-ipfs.com/ipfs/
                   Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/884.jpg",
      "HomePageUrl": "allen.name"
    }
  ]
}

As you can see, the @odata.count field contains number of data items corresponding to the query filter. That is what we wanted.

In general, the question of correspondence between EDM and specific endpoint appeared to be rather complex for me. If you wish, you may try to investigate it out yourself by documentation or by examples.

You may get some help from the debugging page, which can be enabled as follows:

if (app.Environment.IsDevelopment())
{
    app.UseODataRouteDebug();
}

Now at /$odata, you can see which endpoints you have and which models are associated with them.

JSON Serialization

Have you noticed what kind of change happened to the data we returned after we added EDM? All property names now start with a capital letter (before it was firstName, an now it is FirstName). It can be a big problem for JavaScript clients where there is a difference between capital and lowercase letters. We must somehow control names of our properties. OData uses the classes of the System.Text.Json namespace for data serialization. Unfortunately, using the attributes of this namespace gives nothing:

[JsonPropertyName("firstName")]
public string FirstName { get; set; }

It looks like OData takes the names of the properties from EDM, not from the class definition.

The OData implementation suggests two approaches for solving this problem in case of using EDM. The first one allows to turn on "lower camel case" for the whole model using the call of EnableLowerCamelCase method:

IEdmModel GetAuthorsEdm()
{
    ODataConventionModelBuilder edmBuilder = new();

    edmBuilder.EnableLowerCamelCase();

    edmBuilder.EntitySet<Author>("edm");

    return edmBuilder.GetEdmModel();
}

Now we have the following data:

{
  "@odata.context": "http://localhost:5293/api/v1/authors/$metadata#edm",
  "@odata.count": 10,
  "value": [
    {
      "id": 1,
      "firstName": "Troy",
      "lastName": "Gottlieb",
      "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                   Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/228.jpg",
      "homePageUrl": "avery.net"
    },
    {
      "id": 2,
      "firstName": "Mathew",
      "lastName": "Schiller",
      "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                   Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/401.jpg",
      "homePageUrl": "marion.biz"
    }
  ]
}

It is good. But what if we need more granular control over the JSON properties names? What if we need some property in JSON to have a name that is not allowed for property names in C# (e.g., @odata.count)?

It can be done through the EDM. Let's rename homePageUrl to @url.home:

IEdmModel GetAuthorsEdm()
{
    ODataConventionModelBuilder edmBuilder = new();

    edmBuilder.EnableLowerCamelCase();

    edmBuilder.EntitySet<Author>("edm");

    edmBuilder.EntityType<Author>()
        .Property(a => a.HomePageUrl).Name = "@url.home";

    return edmBuilder.GetEdmModel();
}

Here, we'll face an unpleasant surprise:

Microsoft.OData.ODataException: The property name '@url.home' is invalid; 
property names must not contain any of the reserved characters ':', '.', '@'.

Let's try something simpler:

edmBuilder.EntityType<Author>()
        .Property(a => a.HomePageUrl).Name = "url_home";

Now it works:

{
    "url_home": "danielle.info",
    "id": 1,
    "firstName": "Armando",
    "lastName": "Hammes",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/956.jpg"
},

Unpleasant, of course, but what can you do.

Data Transformation

Until now, we have provided the user with data directly from the database. But usually in large applications, it is customary to divide between classes responsible for storing information and classes responsible for providing data to the user. At least, it allows to change these classes relatively independently. Let's see how this mechanism works with OData.

I'll create simple wrappers for our classes:

public class AuthorDto
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string? ImageUrl { get; set; }

    public string? HomePageUrl { get; set; }

    public ICollection<ArticleDto> Articles { get; set; }
}

public class ArticleDto
{
    public string Title { get; set; }
}

I'll use AutoMapper for transformations. I'm not very familiar with Mapster, but I know that it can work with Entity Framework too.

For AutoMapper, we must configure corresponding transformations:

public class DefaultProfile : Profile
{
    public DefaultProfile()
    {
        CreateMap<Article, ArticleDto>();
        CreateMap<Author, AuthorDto>();
    }
}

and register it at the start of our application (I use here AutoMapper.Extensions.Microsoft.DependencyInjection NuGet package):

builder.Services.AddAutoMapper(typeof(Program).Assembly);

Now I can add one more endpoint to my controller:

...

private readonly IMapper _mapper;
private readonly AuthorsContext _db;

public AuthorsController(
    IMapper mapper,
    AuthorsContext db
    )
{
    _mapper = mapper ?? throw new ArgumentNullException(nameof(mapper));
    _db = db ?? throw new ArgumentNullException(nameof(db));
}

...

[HttpGet("mapping")]
[EnableQuery]
public IQueryable<AuthorDto> GetWithMapping()
{
    return _db.Authors.ProjectTo<AuthorDto>(_mapper.ConfigurationProvider);
}

As you can see, it is easy to apply the transformation. Unfortunately, the result contains expanded list of articles:

[
  {
    "id": 1,
    "firstName": "Edward",
    "lastName": "O'Kon",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/1162.jpg",
    "homePageUrl": "zachariah.info",
    "articles": [
      {
        "title": "animi-sint-atque"
      },
      {
        "title": "aut-eum-iure"
      }
    ]
  },
  ...
]

It means that we are unable to apply expand OData operation. But it is easy to fix. Let's change AutoMapper configuration for AuthorDto:

CreateMap<Author, AuthorDto>()
    .ForMember(a => a.Articles, o => o.ExplicitExpansion());

Now for /api/v1/authors/mapping, we get the correct result:

[
  {
    "id": 1,
    "firstName": "Spencer",
    "lastName": "Cummerata",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/286.jpg",
    "homePageUrl": "woodrow.info"
  },
  ...
]

And for /api/v1/authors/mapping?$expand=articles:

InvalidOperationException: The LINQ expression '$it => new SelectAll<ArticleDto>{
Model = __TypedProperty_1,
Instance = $it,
UseInstanceForProperties = True
}
' could not be translated.

Yes, a problem. But AutoMapper gives us another way to work with OData. There is the AutoMapper.AspNetCore.OData.EFCore NuGet package. With it, I can implement my endpoint like this:

[HttpGet("automapper")]
public IQueryable<AuthorDto> GetWithAutoMapper(ODataQueryOptions<AuthorDto> query)
{
    return _db.Authors.GetQuery(_mapper, query);
}

Note that we don't augment our method with the EnableQuery attribute. Instead, we collect all OData query parameters in the ODataQueryOptions object and apply all required transformations "manually".

This time everything works fine: the request without expansion:

[
  {
    "id": 1,
    "firstName": "Nathan",
    "lastName": "Heller",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/764.jpg",
    "homePageUrl": "jamarcus.biz",
    "articles": null
  },
  ...
]

and the request with expansion:

[
  {
    "id": 1,
    "firstName": "Nathan",
    "lastName": "Heller",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/764.jpg",
    "homePageUrl": "jamarcus.biz",
    "articles": [
      {
        "title": "quidem-nulla-et"
      }
    ]
  },
  ...
]

Additionally, there is one more advantage of this approach. It allows to use standard JSON tools to control serialization of our objects. For example, we can remove null values from our results like this:

builder.Services
    .AddJsonOptions(configure =>
    {
        configure.JsonSerializerOptions.DefaultIgnoreCondition = 
                                        JsonIgnoreCondition.WhenWritingNull;
        configure.JsonSerializerOptions.PropertyNamingPolicy = 
                                        JsonNamingPolicy.CamelCase;
    });

Furthermore, we can set JSON property names through usual attributes:

[JsonPropertyName("@url.home")]
public string? HomePageUrl { get; set; }

Now we can use such a name:

[
  {
    "id": 1,
    "firstName": "Edward",
    "lastName": "Schmidt",
    "imageUrl": "https://cloudflare-ipfs.com/ipfs/
                 Qmd3W5DuhgHirLHGVixi6V76LhCkZUz6pnFt5AJBiyvHye/avatar/1046.jpg",
    "@url.home": "justen.com"
  },
  ...
]

Additional Data

If our database fields exactly match to the fields of our resulting data, we have no problems. But this is not always the case. Frequently, we would like to return transformed and processed data from the storage. In this case, we may face several situations.

First of all, the transformation may be simple. For example, I want to return not the first and last name separately, but the full name of the author:

public class ComplexAuthor
{
    [Key]
    public int Id { get; set; }

    public string FullName { get; set; }
}

We can configure AutoMapper for this class like this:

CreateMap<Author, ComplexAuthor>()
    .ForMember(d => d.FullName,
        opt => opt.MapFrom(s => s.FirstName + " " + s.LastName));

In this case, we get the desired result:

[
  {
    "id": 1,
    "fullName": "Lance Rice"
  },
  ...
]

Furthermore, we still can filter and sort data by our new field (/api/v1/authors/nonsql?$filter=startswith(fullName,'A')):

[
  {
    "id": 4,
    "fullName": "Andre Medhurst"
  },
  {
    "id": 6,
    "fullName": "Amber Terry"
  }
]

The reason we still can do it is that our simple expression (s.FirstName + " " + s.LastName) can be easily converted into SQL. Here is the query that Entity Framework generated for me in this case:

SELECT "a"."Id", ("a"."FirstName" || ' ') || "a"."LastName"
      FROM "Authors" AS "a"
      WHERE (@__TypedProperty_0 = '') OR (((("a"."FirstName" || ' ') || 
      "a"."LastName" LIKE @__TypedProperty_0 || '%') AND 
      (substr(("a"."FirstName" || ' ') || "a"."LastName", 1, 
      length(@__TypedProperty_0)) = @__TypedProperty_0)) OR (@__TypedProperty_0 = ''))

That is why filtering and sorting still work.

But obviously, not every transformation can be translated into SQL. Let's say for some reason we want to calculate the hash of the full name:

public class ComplexAuthor
{
    [Key]
    public int Id { get; set; }

    public string FullName { get; set; }

    public string NameHash { get; set; }
}

Now our AutoMapper configuration looks like this:

CreateMap<Author, ComplexAuthor>()
    .ForMember(d => d.FullName,
        opt => opt.MapFrom(s => s.FirstName + " " + s.LastName))
    .ForMember(
        d => d.NameHash,
        opt => opt.MapFrom(a => string.Join(",", SHA256.HashData
               (Encoding.UTF32.GetBytes(a.FirstName + " " + a.LastName))))
    );

Let's try to get our data:

[
  {
    "id": 1,
    "fullName": "Julius Haag",
    "nameHash": "66,19,82,19,233,224,181,226,111,125,241,228,81,6,
                 200,47,5,112,248,30,186,26,173,91,83,73,9,137,6,158,138,115"
  },
  {
    "id": 2,
    "fullName": "Anita Wilderman",
    "nameHash": "196,131,191,35,182,3,174,193,196,91,70,199,22,173,72,54,
                 123,73,110,83,254,178,19,129,219,24,137,197,83,158,76,209"
  },
  ...
]

Interesting. Despite the fact that the resulting expression cannot be expressed in SQL terms, but the system still continues to work. It looks like Entity Framework knows what can be evaluated on the server side.

Now let's try to filter our data by this new field (nameHash): /api/v1/authors/nonsql?$filter=nameHash eq '1'.

InvalidOperationException: The LINQ expression 'DbSet<Author>()
.Where(a => (string)string.Join<byte>(
separator: ",",
values: SHA256.HashData(__UTF32_0.GetBytes(a.FirstName + " " + 
a.LastName))) == __TypedProperty_1)' could not be translated.

Here, we can no longer avoid converting our expression to SQL. And, since it can't be done, we get the error message.

In this case, we can't rewrite the expression in such a way that it can be converted into SQL. But we can prohibit filtering and sorting by this field. There are several attributes to do it: NonFilterable and NotFilterable, NotSortable and Unsortable. You can use any of them:

public class ComplexAuthor
{
    [Key]
    public int Id { get; set; }

    public string FullName { get; set; }

    [NonFilterable]
    [Unsortable]
    public string NameHash { get; set; }
}

I'd prefer to return Bad Request if the user tries to filter by this field. But mere adding of these attributes does nothing. Filtering by nameHash leads to the same error. We have to validate our request manually:

[HttpGet("nonsql")]
public IActionResult GetNonSqlConvertible(ODataQueryOptions<ComplexAuthor> options)
{
    try
    {
        options.Validator.Validate(options, new ODataValidationSettings());
    }
    catch (ODataException e)
    {
        return BadRequest(e.Message);
    }

    return Ok(_db.Authors.GetQuery(_mapper, options));
}

Now when we try to filter, we get the following message:

The property 'NameHash' cannot be used in the $filter query option.

It is better. Although the property name returned to the user starts with a small letter (nameHash), not with a capital one (NameHash).

I wonder how things are going with changing property names using the JsonPropertyName attribute in general? For example, I want my property to have name name:

[JsonPropertyName("name")]
public string FullName { get; set; }

Can I filter by name now (/api/v1/authors/nonsql?$filter=startswith(name,'A'))? It turns out that I can't:

Could not find a property named 'name' on type 'ODataJourney.Models.ComplexAuthor'.

What if we return to EDM? To do this, it is enough to add the ODataAttributeRouting attribute to the controller method:

[HttpGet("nonsql")]
[ODataAttributeRouting]
public IActionResult GetNonSqlConvertible(ODataQueryOptions<ComplexAuthor> options)

And update our model:

...

edmBuilder.EntitySet<ComplexAuthor>("nonsql");

edmBuilder.EntityType<ComplexAuthor>()
    .Property(a => a.FullName).Name = "name";

...

Now we can filter by name:

{
  "@odata.context": "http://localhost:5293/api/v1/authors/$metadata#nonsql",
  "value": [
    {
      "name": "Leona Bauch",
      "id": 3,
      "nameHash": "56,114,131,251,22,63,188,105,37,55,74,232,36,181,152,
                   24,9,111,131,55,229,89,164,181,230,158,109,163,206,137,147,173"
    },
    {
      "name": "Leo Schimmel",
      "id": 7,
      "nameHash": "78,48,88,216,170,3,241,99,96,251,10,176,45,187,250,58,
                   240,215,104,159,26,158,217,244,93,219,183,119,206,40,130,102"
    }
  ]
}

But as you can see, the data structure has changed. We get OData wrapper. In addition, we have returned to the restriction on property names described above.

In the end, let's look at one more type of data transformation. So far, we transformed data using AutoMapper. But in this case, we can't use the request context. AutoMapper transformations are described in a separate file where there is no access to the information from a request. But sometimes, it can be very important. For example, we may want to make another Web request based on the data received in the request and change our resulting data using the response. In the following example, I use a simple foreach loop to represent some server-side data processing:

[HttpGet("add")]
public IActionResult ApplyAdditionalData(ODataQueryOptions<ComplexAuthor> options)
{
    try
    {
        options.Validator.Validate(options, new ODataValidationSettings());
    }
    catch (ODataException e)
    {
        return BadRequest(e.Message);
    }

    var query = _db.Authors.ProjectTo<ComplexAuthor>(_mapper.ConfigurationProvider);

    var authors = query.ToArray();

    foreach (var author in authors)
    {
        author.FullName += " (Mr)";
    }

    return Ok(authors);
}

Naturally, there is no OData support here. But how can we add it? We don't want to lose the ability to filter, sort and paginate.

Here is one possible approach. We can apply all OData operations except select. In this case, we still work with full ComplexAuthor objects. After that, we transform these objects and then we apply the select operation, if it was requested. This will allow us to get from the database only a small number of records corresponding to our filter and page:

[HttpGet("add")]
public IActionResult ApplyAdditionalData(ODataQueryOptions<ComplexAuthor> options)
{
    try
    {
        options.Validator.Validate(options, new ODataValidationSettings());
    }
    catch (ODataException e)
    {
        return BadRequest(e.Message);
    }

    var query = _db.Authors.ProjectTo<ComplexAuthor>(
        _mapper.ConfigurationProvider);

    var authors = options
        .ApplyTo(query, AllowedQueryOptions.Select)
        .Cast<ComplexAuthor>()
        .ToArray();

    foreach (var author in authors)
    {
        author.FullName += " (Mr)";
    }

    var result = options.ApplyTo(
        authors.AsQueryable(),
        AllowedQueryOptions.All & ~AllowedQueryOptions.Select
    );

    return Ok(result);
}

ODataQueryOptions object allows us to specify which OData operations should be applied. Using this opportunity, we will divide the application of OData operations into two stages, between which we insert our processing.

This approach has its drawbacks. First of all, we lose the ability to change property names using JSON attributes. It can be fixed with EDM, but in this case, we'll change the data shape and get the OData wrapper.

In addition, the problem with the expand operation returns. Our ComplexAuthor class is quite simple, but we can add a property to it that returns articles:

public ICollection<ArticleDto> Articles { get; set; }

The GetQuery method we used earlier from the AutoMapper.AspNetCore.OData.EFCore NuGet package does not allow to apply OData operations partially. And without it, I could not make the system to expand the Articles property correctly. Finally, I have got this incomprehensible error:

ODataException: Property 'articles' on type 'ODataJourney.Models.ComplexAuthor' 
is not a navigation property or complex property. Only navigation properties 
can be expanded.

Maybe someone will be able to overcome it.

Conclusion

Despite the fact that OData provides a fairly simple way to add powerful data filtering operations to your Web API, it turns out to be very difficult to get everything you want from the current Microsoft implementation. It looks like that when you implement one thing, something else falls off.

Let's hope I just don't understand something here, and there is a reliable way to overcome all these difficulties. Good luck!

P.S.: You can find the source code for this article on GitHub.

You can read more of my articles on my blog.

History

• 21^st November, 2022: Initial version