Complex queries with OData

Complex queries with OData. In this section, we’re going to look at some more advanced queries and how we can integrate Cosmos DB very nicely with the REST interface that we’re creating.

Looking at what we have so far, you can see that we’ve already got our get commands. So we’ve got the get item async where we can return a single journey from the database. And we’ve got the get items async where we passing that query string and all those different select queries we looked at in the last section.

Going on to the controller, we can see where again, calling that get. But we’re returning everything. So there’s no way to pass this to say, can we get specific items? Can we start to return maybe all journeys that start in Starbucks or all journeys that are with a particular driver or with a particular car?

So how can we start to get those filters? If we pop up to the start up and then actually we’re going to jump over and add a new package. So we’re going to get to New Get. And there’s actually an OData package that exists and allows us to have those queries.

So you can see there we’ve got it. It’s Microsoft.asp.netcore.OData. And this will go into the REST interface and allow us to pass these select statements to select specific fields to be returned, but also the filters that allows you only return specific data and the sorts that also allow you to sort this data as well. Now, once that package is installed, you’ll just see you need to add the service of Add OData.

We’ll come into sorting out some of those errors shortly, such as this one where we just need to obviously add the using statement. And you’ll see on the controllers, we’re just setting some options to enable end to point routing. That’s just something to do with .netcore3 that I won’t go into too much detail here where it uses endpoints. We’re going to comment that out and instead use the MVC routes.

And the reason is because we can then, as well as enabling the dependency injection, we can pass those selects filters, order buys, and the expand allows us to get those child objects as well all as part of the query. So we’re going to go over to the journey controller now. And we’re going to add a new statement there including that OData reference.

And within the get, just to get through everything, we’re going to enable query. And that will then allow us to pass through that query. So let’s just make sure everything’s looking good as we build that and ensure that all the detail has been collected. Yep, that has built and succeeded.

So we’ll run this now and open up the browser and take a look at the queries that return. So you can see, by default, if I put the journey in there, all those different journeys that the data I’ve loaded in, you can see from the scroll bar on the right, quite how many there are are all loaded into there. But if I start putting the filter on there, I can look where the name equals Audi A7.

And you’ll just see that I’m putting single quotes, not double quotes around the text there. And now, it’s returning everything back because we’re doing that select start from c at the moment, not filtering any journey data. We’re returning out just a single vehicle that gets returned back from there.

Now, obviously, we want to pass it and put the filters within that select star from c where entity equals journey. But in this case, we’re just bringing back that single item that works well.