how to make odata query directly run on db sql query?

[LokanathCalyx]

trafficstars

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.

What I am missing here?

stackoverflow:- https://stackoverflow.com/questions/73302837/how-to-make-odata-query-directly-run-on-db-sql-query?noredirect=1#comment129476737_73302837

[habbes]

@LokanathCalyx have you tried adding the [EnableQuery] attribute directly on the controller action that handles the endpoint, e.g.:

[EnableQuery]
public IQueryable Get()
{
  ...
}

[LokanathCalyx]

Yes. Its there. The issue is, when i apply a filter(e.g. $filter= status eq 'active') , so it fetch all record to the Api from Database then apply the filter & returns only those records. But while i have a bigger volume, its always an issue.

[habbes]

@LokanathCalyx what database are you using? Are you using EF Core? Could you share more sample code, like what DB driver you're using, how your controller looks like, what your request looks like, etc.

Also, additional clarifications:

• does OData return the correctly filtered data even though it filters after fetching, or does it not filter at all?
• do other query options beside $filter work, e.g., $select, $expand? Do they return the expected results, and do they transform the query sent to the db?

[LokanathCalyx]

1. My database is Cosmos & not using EF. using GetItemLinqQueryable<> to generate the query.

Code sample from the repository layer

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.
      }

Where in controller Layer [EnableQuery]. I am not doing any Override over the EnableQueryAttribute.

2. Yes after fetching all data it returns filtered data, as expected.
3. Yes $top, $Skip all works as expected. Again after fetching all data then top is getting applied.

[julealgon]

@habbes please consider converting this type of issue into a discussion (instead of labeling them with a "question" tag). Otherwise there is little purpose to having discussions enabled in the repo in the first place.

[LokanathCalyx]

Does Odata support what i am looking for? Or i am missing something? Summary of query:- Want to add filters,top,skip etc while preparing the Sql query for Cosmos Db, then want to hit to the Database. So i can only fetch the data based on the query, instead of fetching all data then filtering in application.

[julealgon]

Of course @LokanathCalyx . The way OData queries work is that they operate at the IQueryable layer, so whatever query option you add will end up being converted into a raw database query by the provider you are using.

If you use EFCore's Cosmos provider, this should be straightforward to do. Just keep in mind that Cosmos doesn't support the full set of operations that a fully fledged relational database supports, so some operations will probably just fail at runtime as they won't be able to be translated into SQL for cosmos.

Basically, your scenario is actually the most common one with OData to begin with, and one of its strongest points.

BTW, your stackoverflow link is broken.

[julealgon]

@LokanathCalyx I've replied to your SO question. Let us know if that makes sense to you.

[habbes]

@LokanathCalyx the GetItemsAsync method that you have shown above returns an Task<IEnumerable<T>> not an IQuerayble. Your implementation seems to fetch all the data and store in a List<T>. So what you return is a list that already contains all the data in memory. If this is what you return from your controller, then OData will simply apply the filter transformations on top of this in-memory list.

If your controller returns an IQueryable, then OData will apply query transformations on the IQueryable based on the $filter, $select, $top, $skip and other query options. Then it will be up to the data provider to fetch the right data from the data source based on this query. This seems to be what you are asking for. The short answer is that yes, it is possible.

Have you tried to return the queryable directly from GetItemsAsync method, without copying the data to a list?

[habbes]

@habbes please consider converting this type of issue into a discussion (instead of labeling them with a "question" tag). Otherwise there is little purpose to having discussions enabled in the repo in the first place.

Thanks for pointing this out @julealgon. Looking at the current state of the Discussions page, I don't think we have done a good job monitoring it. I'll raise that up.

[LokanathCalyx]

Agree, Efcore would have been bit easier to handle IQueryable. But i can't at this moment. Tried both approach , have some issue:- With ODataQueryOptions<T>:- 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.

With [EnableQuery]:- 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"

[julealgon]

Agree, Efcore would have been bit easier to handle IQueryable. But i can't at this moment.

You are using CosmosDB right? Are you aware that there is a native EFCore provider for it? Or do you mean you can't use EFCore for some other reason? Notice that using the EFCore provider is even the recommended approach from Microsoft to interface with Cosmos from .NET from a simplicity/productivity standpoint.

I'm just asking because if you could migrate to using EFCore you'd probably be able to make this whole situation much simpler for yourself.

With ODataQueryOptions:- i have some doubt. Earlier i tried with ODataQueryOptions, 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.

There is no native support for ODataQueryOptions<T> in swagger, so forget what you see being emitted there. What it basically does is convert your querystring OData parameters into this strongly typed model that can be applied to a IQueryable.

It is not a body parameter.

If you are seeing an error when using it, feel free to post it here.

With [EnableQuery]:- 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"

I have no idea what that error is to be honest. It seems like it could be its own side question though, so I wouldn't necessarily recommend diving into it here.

From what I'm reading on your posts, it seems you have almost no experience with OData. If that's the case, I'd strongly recommend going through all the blog posts and MSDN docs before attempting to use it. The framework is easy to use if you know what you are doing, but it has a lot of small details that you need to be aware of.

[LokanathCalyx]

Agree, Efcore would have been bit easier to handle IQueryable. But i can't at this moment.

You are using CosmosDB right? Are you aware that there is a native EFCore provider for it? Or do you mean you can't use EFCore for some other reason? Notice that using the EFCore provider is even the recommended approach from Microsoft to interface with Cosmos from .NET from a simplicity/productivity standpoint.

I'm just asking because if you could migrate to using EFCore you'd probably be able to make this whole situation much simpler for yourself.

With ODataQueryOptions:- i have some doubt. Earlier i tried with ODataQueryOptions, 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.

There is no native support for ODataQueryOptions<T> in swagger, so forget what you see being emitted there. What it basically does is convert your querystring OData parameters into this strongly typed model that can be applied to a IQueryable.

It is not a body parameter.

If you are seeing an error when using it, feel free to post it here.

With [EnableQuery]:- 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"

I have no idea what that error is to be honest. It seems like it could be its own side question though, so I wouldn't necessarily recommend diving into it here.

From what I'm reading on your posts, it seems you have almost no experience with OData. If that's the case, I'd strongly recommend going through all the blog posts and MSDN docs before attempting to use it. The framework is easy to use if you know what you are doing, but it has a lot of small details that you need to be aware of.

Yes, i am using it for first time & due to existing code & we had some dependency on Swagger , so got confused a bit when i didn't see it on UI.

[LokanathCalyx]

Issue is resolved now. As per my scenario , ODataQueryOptions<T> was best fit for me. As I had to perform some task post materializing the data. Both the approach are easy to implement , as i had some other dll issue, which was actually causing the delay to resolve.

Note:- $expand & $select will throw 500 . As it need change in the Method/ implementation where we fetch the data. IEnumerable<T> won't be serving the request. it need IEnumerable<dynamic> will be a fit as the T will be a different schema where as with $select & $expand it can vary .