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

CodePudding user response：

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

CodePudding user response：

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();}