CODEWITHSUNDEEP
c#.netazure-cosmosdbasp.net-core-3.1azure-cosmosdb-sqlapi
Rahul C
Rahul C

Reputation: 121

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

Upvotes: 12

Views: 4490

Answers (3)

Cristian Munoz Latapiat
Cristian Munoz Latapiat

Reputation: 141

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

Upvotes: 14

Marc Gravell
Marc Gravell

Reputation: 1064234

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

Upvotes: 2

Ramee Ahmed
Ramee Ahmed

Reputation: 11

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

Upvotes: -1

Related Questions