CosmosDB Linq query with contains is not of type IDocumentQuery

Question

I have a project working woth CosmosDB. At first I used the preview for EFCore, but it really isn't mature enough so I decided to opt with Cosmonaut instead. I have a linq statement which basically looks if two properties contains a list of substrings - basically I'm trying to do something like:

SELECT * FROM c WHERE CONTAINS(c.Name, ListOfNames) AND CONTAINS(c.Producer, ListOfProducers);

Or a huge ass bunch of:

foreach(var name in nameList) {
    foreach(var producer in producerList){

        SELECT * FROM c WHERE c.Name == searchedName AND c.Producer == searchedProducer;
    }
}

This worked with the EFCore SQL adapter with the following Linq Query:

public async void Search(List<string> producers, List<string> names){

 await _store.Entity.Where(x => producers.Any(p => x.Producer.Contains(p)) && names.Any(w => x.Name.Contains(w))).ToListAsync()
}

However, this with the cosmonaut library (which wraps the DocumentDB client from cosmos) gives the following exception:

Input is not of type IDocumentQuery

Looking at the specs for Contains I can do this:

USE AdventureWorks2012;  
GO  
SELECT Name  
FROM Production.Product  
WHERE CONTAINS(Name, '"chain*" OR "full*"');  
GO

But doing the following in the CosmosDB data explorer yields 0 results:

SELECT * FROM c WHERE CONTAINS(c.Name, '"Test" OR "Test2"')

Whereas a regular contains does:

SELECT * FROM c WHERE CONTAINS(c.Name, "Test")

Maybe my strategy is just wrong. The main reason I want to combine it is to have better performance. My search domain is around 100.000 documents where I have a list of up to a 1000 of producer + names. So basically I want to see if I can find the given producer + name combination in my document list.

Best Regards

Answer 1

First of all you should not mix T-SQL and Cosmos SQL API. Cosmos db has sql-like query syntax but it doesn't support T-SQL(it's for MS SQL).

Secondly, CONTAINS in Cosmos SQL API is a string operator so you cannot use it for arrays.

I think you're looking for IN keyword.

So actually you need next query:

SELECT * FROM c WHERE (c.Name IN("Test", "Test2")) AND (c.Producer IN("Producer1", "Producer2"))

I have not used cosmonaut library but in Microsoft LINQ provider for Document DB your query should look like this:

var data = yourQueryable.Where(x => producers.Contains(x.Producer) && names.Contains(x.Name)).ToList();