Cosmos DB SQL API NOT IN operator taking a List WithParameter(List<t>) not working

user_4338221 0
Viewed 1270
var ids = IdsList.Select(pID => pID.ID).ToArray();

            var response= await MyService.GetByMyQuery(
                 new QueryDefinition(
                     "SELECT * FROM p WHERE p.id NOT IN(@ids)"
                 )
                 .WithParameter("@ids", string.Join(",", ids)),
            );

So this is not working. The operator returns all the items, instead of just the ones not in the list. In the cosmos DB SQL query editor i can easily do

SELECT * FROM p WHERE p.id NOT IN("id1","id2")

and it returns the expected results without any problems. SO i guess that the problem is in the code layer, the way I'm passing the ids to the WithParameter() menthod.

Any insights is greatly appreciated.

azure-cosmosdb sql c#
1 Answers

The problem

Your C# code is not sending multiple values as the @ids parameter, but a single string value, effectively like this:

SELECT * FROM p 
WHERE p.id NOT IN("id1, id2")

Since this compound id does not exist then it returns all items like you observed.

Solution

It may be possible with IN keyword as well, but I do know for sure that this pattern will work:

SELECT * FROM p 
WHERE NOT ARRAY_CONTAINS(@ids, p.id)

NB! Correct me if I'm mistaken, but most likely this condition will NOT be served by index. So, you may want to reconsider your design, unless your real case would have an additional good indexable predicate, or else it will be slow and costly.

user_3313250
Related