OData and Cosmos DB

Question

I'm looking to implement OData v4 for as a querying tool in an ASP.NET Core application I'm working on, and our backing persistence store is Cosmos DB. So far, I haven't figured out a way to make OData queries run against the DocumentQuery IQueryable interface without encountering some sort of exception or error.

I was wanting to know if there was a "clean" way to utilize OData against Cosmos Document DB (not the Table API), and if so, how? So far, all I've found is an unofficial library that's using Framework v4.6, but nothing official, and all documentation I've found about implementing OData has almost exclusively been ran against Entity Framework or in-memeory data store.

You write a dynamic OData service connected to any data store. There's an example : https://stackoverflow.com/questions/29981931/dynamic-odata-service-in-c-sharp-from-runtime-data-layer that uses SQL Server directly w/o EF nor anything special. You can replace SQL calls be CosmosDB call. — Simon Mourier, Feb 05 '19 at 19:05
Hmmm... I'm not super familiar with OData to begin with, so this will take a bit to unpack. I'm not quite sure where all the filtering, selecting, etc. is taking place here. My understanding was that your odata endpoint should just return an IQueryable and it would handle the rest, whereas what you send me seems to be doing it in code. Is there something I'm missing here? — Free Radical, Feb 05 '19 at 19:56
So the first issue I've run into is that the Request.ODataFeature() call is returning an object that isn't initialized. — Free Radical, Feb 05 '19 at 20:56
What would you like to see demonstrated exactly? Any sample code? — Simon Mourier, Feb 06 '19 at 09:17
Also, bear in mind that the OData model (Edm) proposed by ASP.NET/WebApi is a structured Entity/Property model (check http://odata.github.io/WebApi/), so how to you go from unstructured/hierarchical JSON (CosmosDB) to a tabular OData Edm model (beyond any technical details)? BTW that's why CosmosDB only supports OData for Table API — Simon Mourier, Feb 06 '19 at 10:40
OData is built around a metamodel of your data. A relational database has a schema and this makes it possible to make a generic OData provider on top of a relational database. However, Cosmos DB just stores JSON. There is no schema. This means that you will have to build your own OData metamodel. I don't believe that there is a "plug-and-play" solution to OData on top of Cosmos DB but if it existed it would be built around a metamodel (could be POCO classes). Cosmos DB without "something" on top does not provide that. — Martin Liversage, Feb 07 '19 at 14:32
Even though Cosmos doesn't utilize a hard schema, I am using strongly typed models that each have their own collection. A company has a company collection, a user has a user collection, and so on. What I'd like to be able to do is pull the IQueryable back from the DocumentCollection and run the ODataQuery against it. — Free Radical, Feb 11 '19 at 06:49
The current issue I am running into now is that when I am attempt to run the select query against the IQueryable, I get an error stating that the property does not exist. When I turn the IQueryable into an AsyncEnumerable and then a IEnumerable, I am able to handle the select, but this obviously means I'm pulling the entire collection back from Cosmos, which is unacceptable. — Free Radical, Feb 11 '19 at 06:53

score 1 · Accepted Answer · answered May 01 '19 at 22:43

1

I know this isn't exactly the most insightful answer to my question, but the correct answer here is really to just not attempt to do this. If you're ever in a position where someone asks you to try to force together two technologies that don't really go together, say no and deal with the consequences.

answered May 01 '19 at 22:43

Free Radical

374
1
4
14

2

What about this then ? https://devblogs.microsoft.com/odata/integrating-cosmos-db-with-odata-part-1/ – Mathematics Aug 06 '20 at 08:19

score 1 · Answer 2 · answered Apr 29 '21 at 07:34

OData integration with Cosmos is not that hard if you're using the SQL api and you only need the basic stuff like $orderby, $top and $skip. It's a matter of generating correct SQL.

If you need more than that it gets a bit harder. Anyway, I did some simple testning with this NuGet lib. It seems to work from my simple tests at least.

        var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
        var select = oDataToSqlTranslator.Translate(odataQueryOptions, TranslateOptions.SELECT_CLAUSE);
        var where = oDataToSqlTranslator.Translate(odataQueryOptions, TranslateOptions.WHERE_CLAUSE);
        var order = oDataToSqlTranslator.Translate(odataQueryOptions, TranslateOptions.ORDERBY_CLAUSE);
        var top = oDataToSqlTranslator.Translate(odataQueryOptions, TranslateOptions.TOP_CLAUSE);
        var all = oDataToSqlTranslator.Translate(odataQueryOptions, TranslateOptions.ALL);
        
        log.LogInformation("SQL select => " + select);
        log.LogInformation("SQL where  => " + where);
        log.LogInformation("SQL order  => " + order);
        log.LogInformation("SQL all    => " + all);

Given this URL as input:

http://localhost:7071/api/v1/invoices/customer/20?$top=2&$select=CustomerId&$filter=InvoiceNumber eq 'xxx'&orderby=brand

The logs shows this:

SQL select => SELECT c.CustomerId FROM c 
SQL where  => WHERE c.InvoiceNumber = 'xxx' 
SQL order  => ORDER BY c.InvoiceNumber ASC 
SQL all    => SELECT TOP 2 c.CustomerId FROM c WHERE c.InvoiceNumber = 'xxx' ORDER BY c.InvoiceNumber ASC

score 0 · Answer 3 · answered Feb 11 '19 at 18:12

0

How about something like - convert OData to SQL, and then execute it on the CosmosDB https://github.com/Azure/azure-odata-sql-js

answered Feb 11 '19 at 18:12

Edward Ribbery

37
5

OData and Cosmos DB

3 Answers3

Linked