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.!!
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
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)STRING_SPLIT SQL Server function, if similar exists for CosmosDB - i.e. in (select value from STRING_SPLIT(@ids,','))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();}