CODEWITHSUNDEEP
c#azure-cosmosdb
Learner
Learner

Reputation: 3

'IN' gives empty result - c# and cosmos

I am trying to query the cosmos and the query works. The query looks like

'Select * from c where c.id IN ('123', '456')'.

Now in my c# code, I get empty result. The c# code looks like :

public void GetValue(IEnumerable<string> ids, string s)
        {
            StringBuilder sb = new();
            _ = sb.Append("SELECT t.id FROM t ")
                .Append("WHERE t.id IN (@items) ")
                .Append("AND t.state != @state");

            var queryDefinition = new QueryDefinition(sb.ToString())
                .WithParameter("@items", ids)
                .WithParameter("@state", s);

           var results = GetQueryResults<TableName>(queryDefinition); // Get Empty Result
          // Some logic based on results
        }

// GetQueryResults query the container and gets the result for the tableName.

SO, I was able to conclude that the 'IN' query syntax is incorrect. Can anyone help me out.

Upvotes: 0

Views: 117

Answers (1)

Palle Due
Palle Due

Reputation: 6292

The problem is here:

.Append("WHERE t.id IN (@items) ")

The list cannot be parameterized. One possiblity is to add the list items as separate parameters. There is an example of that here.

EDIT I found the solution here

var querySpec = new SqlQuerySpec {
    QueryText = "SELECT t.Id FROM t WHERE ARRAY_CONTAINS(@Ids, t.Id)",
    Parameters = new SqlParameterCollection {
        new SqlParameter { 
            Name = "@Ids",
            Value = ids
        }
    }
}

Upvotes: 2

Related Questions