Parameterized Where IN clause does not work with CosmosClient QueryDefinition Object

Question

I am trying to write a parameterized query that has IN clause.

For Ex :

Working code

Input string : "'guid1','guid2','guid3'"

public List<Employee> GetEmployeeIds(string ids){
QueryDefinition query =new QueryDefinition(@"Select * from Employee where Employee.Id in ("+ ids+")");
var result = GetDetails(query,cosmosClient);
return result;
}

Result: It returns the expected result

Non-working code

Input string : "'guid1','guid2','guid3'"

public List<Employee> GetEmployeeIds(string ids){
QueryDefinition query =new QueryDefinition(@"Select * from Employee where Employee.Id in ( @ids )")
                  .WithParameter("@ids", ids);
var result = GetDetails(query,cosmosClient);
return result;
}

Result: It returns 0

NuGet package used for above code: Microsoft.Azure.Cosmos 3.8.0

Note: I have tried all the options which are mentioned in this link but it does not work with CosmosClient QueryDefinition Object WHERE IN with Azure DocumentDB (CosmosDB) .Net SDK

Any help on this is highly appreciated.

Thanks in advance.!!

Monetta

This is answered here. #70137068 – Fugato

Answer 1

This is working for me, where @ids is a list type, and instead of using the IN keyword I use the ARRAY_CONTAINS function:

List<long> ids = new List<long>();
ids.Add(712300002201);
ids.Add(712300002234);

var querySql = @"Select * from Employee where ARRAY_CONTAINS(@ids, Employee.Id)";
var queryDefinition = new QueryDefinition(query).WithParameter("@ids", ids);

Cumulative

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Perceval

Answer 2

I'm guessing that your ids value is something like "12,42,94,7". As a string parameter @ids, the expression in (@ids) is broadly the same as in ('12,42,94,7'), which won't match any values, if the values are the individual numbers 12, 42, 94 and 7. When you used the simple contatenated version, the meaning was different - i.e. in (12,42,94,7) (note the lack of quotes), which is 4 integer values, not 1 string value.

Basically: when parameterizing this, you would need to either

1. use multiple parameters, one per value, i.e. ending up with in (@ids0, @ids1, @ids2, @ids3) with 4 parameter values (either by splitting the string in the C# code, or using a different parameter type - perhaps params int[] ids)
2. use a function like the STRING_SPLIT SQL Server function, if similar exists for CosmosDB - i.e. in (select value from STRING_SPLIT(@ids,','))

Miscarry

Hi Marc, Thank you for your reply..!! I tried this option but seems does not work with the QueryDefinition object of CosmosClient. – Monetta

@RahulC which? I gave two suggestions; which did you try? – Miscarry

Hi have tried the first suggestion and the second STRING_SPLIT does not exist for CosmosDB – Monetta

Apologies, the first suggestion worked, I was passing the wrong string values with multiple parameters. Thanks!! – Monetta

Answer 3

Have you tried looking into the question asked below.

Azure Cosmos DB SQL API QueryDefinition multiple parameters for WHERE IN

I think ids are being treated as a single string therefore the results are not returning.

Alternatively, you could try using Microsoft.Azure.DocumentDB.Core package and make use of Document Client to write LINQ queries like in the code snippet below.

using (var client = new DocumentClient(new Uri(CosmosDbEndpoint), PrimaryKeyCosmosDB)){

            List<MyClass> obj= client.CreateDocumentQuery<List<MyClass>>(UriFactory.CreateDocumentCollectionUri(databaseName, collectionName))
                    .Where(r => ids.Contains(r.id))
                    .AsEnumerable();}

Jurkoic

Hi Ramee, Microsoft.Azure.DocumentDB.Core package is deprecated now. – Monetta