Reputation: 732
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.
Upvotes: 3
Views: 4853
Reputation: 5004
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.
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.
Upvotes: 5