how to make odata query directly run on db sql query

Question

I am currently developing an Api. Where I have a GET endpoint. where I am using OData Query attribute i.e. [EnableQuery] .

I have added the filter in the Program.cs . Its in .Net 6

builder.Services.AddControllers(options =>
{
    options.Filters.Add(new EnableQueryAttribute()
    {
        AllowedQueryOptions = AllowedQueryOptions.All,
        AllowedOrderByProperties = null,
        AllowedLogicalOperators = AllowedLogicalOperators.All,
    });
}).AddOData(o => o.Select().Filter().SetMaxTop(100).OrderBy().Count());

So issue is, while querying to database, Odata filter not getting applied, instead it etch all records then applying the filter. So in the real scenario where there is huge record count its a bottleneck situation. How to implement the filters before hitting to the database? I am using Iqueryable.

In the repository layer i am doing as below

public async Task<IEnumerable<T>> GetItemsAsync(CancellationToken cancellationToken)
      {
          var queryable1 = _samplecontainer.GetItemLinqQueryable<abc>();
          var queryable2 = queryable1.Where(_ => _.sampleType== "generic").ToFeedIterator();
          List<T> results = new List<T>();
          while (queryable2.HasMoreResults)
          {
              FeedResponse<T> response = await queryable2.ReadNextAsync();

              results.AddRange(response.ToList());
          }

          return results; // Here it returns all data.
      }

What I am missing here?

GitHub Issue

Answer 1

The EnableQueryAttribute is an action filter that runs on top of your controller method. This means that it will operate on the results of your controller.

Right now, you are returning a IEnumerable that is materialized via a ToList call. The moment you do that, you are leaving the database and fetching the results. Then, the query options you define will operate on these materialized results (they will still work for the most part, but will be done in memory instead of in the database).

To allow OData operations to work on the database layer, you cannot materialize the query before returning from your controller: instead, return IQueryable<T> from the controller and make sure to avoid any calls that will materialize your results: materialization will be taken care of by the EnableQuery filter.

If for any reason you don't have the option of returning an IQueryable without materializing the results, you can always opt into passing a ODataQueryOptions<T> into your controller action, and then manually using that to apply the operations into an existing IQueryable anywhere you want. This can be useful when dealing with legacy codebases when introducing OData, or if you really want to have more fine grained control of the application process itself.

Remember that you should never use both approaches at the same time however: either stick to EnableQuery (usually a lot simpler, but a bit more limiting), or inject the ODataQueryOptions<T> into the controller and manually call Apply. Don't do both or you'll end up with duplicate filtering.