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

Based on your test result, Odata may be designed as query first then apply the filter. — Tiny Wang
– Tiny Wang, Commented Aug 11, 2022 at 2:30
Then what i am missing? How to make it like prepare the query with Filter then hit. — lokanath das
– lokanath das, Commented Aug 11, 2022 at 5:44
I didn't find any configuration on Odata to set to realize your requirement, so I try to search for documents introducing how Odata worked, but failed again. Since you have test it by yourself, so I think that is how OData work, and it can't change. — Tiny Wang
– Tiny Wang, Commented Aug 11, 2022 at 5:50

julealgon · Accepted Answer · 2022-08-12 15:07:47Z

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.

i have some doubt. Earlier i tried with ODataQueryOptions<T>, passed as a param to Controller action method. I am using swagger, i can see a object is getting added to the endpoint parameter & its a huge json. it expect those i think, bec if i am not passing those its throwing some error.
In [EnableQuery] attribute process , Without feediterator can we materialize the data from cosmos Db? I am facing issue without it. Returning IQueryable only, throwing error. "To execute LINQ query please set allowSynchronousQueryExecution true or use GetItemQueryIterator to execute asynchronously"
@lokanathdas you are probably misusing ODataQueryOptions. Ignore what is exposed in Swagger since it is not supported there. All you need to do is to pass in OData query parameters in the querystring as normal, and they should be passed over to the ODataQueryOptions<T> instance in your controller. Then, you just have to call apply on that instance over your queryable before you call the iteration method (say, right after your Where call is today).
There was some Dll reference issue which was making the problem(some dependent dll issue). Now its clear. Thanks for the help.

Collectives™ on Stack Overflow

how to make odata query directly run on db sql query

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related