'Parameterized Where IN clause does not work with CosmosClient QueryDefinition Object

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

c++sharp.netazure-cosmosdbasp.net-core-3.1azure-cosmosdb-sqlapi


Solution 1:[1]

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,','))

Solution 2:[2]

This is working for me, where @ids is an array type:

List<long> ids = new List<long>();
            ids.Add(712300002201);
            ids.Add(712300002234);
            string querySql = @"Select * from Employee where  ARRAY_CONTAINS(@ids, Employee.Id )";
            QueryDefinition definition = new QueryDefinition(query).WithParameter("@ids", ids);

Solution 3:[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();}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Jeremy Caney
Solution 3 Ramee Ahmed

Related Questions