WHERE IN with Azure DocumentDB (CosmosDB) .Net SDK

Question

Having seen this question I'm not sure why the following code for my DocDb instance isn't working:

var userApps = _docs.CreateDocumentQuery(UriFactory.CreateDocumentCollectionUri(Constants.Databases.Applications.ID, Constants.Databases.Applications.Collections.USER_APPS),
        new SqlQuerySpec(@"SELECT r.appId FROM ROOT r WHERE r.userId = @userId", (@"@userId", userId).ToSqlParameters()))
    .ToList()
    .Select(s => (string)s.appId);
var query = _docs.CreateDocumentQuery<Document>(UriFactory.CreateDocumentCollectionUri(Constants.Databases.Applications.ID, Constants.Databases.Applications.Collections.APP_DEFINITIONS),
        new SqlQuerySpec(@"SELECT r.id, r.appName FROM ROOT r WHERE r.appId IN (@userApps)", (@"@userApps", userApps.ToArray()).ToSqlParameters()),
        new FeedOptions { EnableCrossPartitionQuery = true })
    .AsDocumentQuery();

When I execute this, though I know the data should be returning me back a result set, it comes back empty every time.

Troubleshooting so far

Variants of .Select()

Return strings that I string.Join in to a comma-separated list.

Eg:

var userApps = string.Join(@",", _docs.CreateDocumentQuery(UriFactory.CreateDocumentCollectionUri(Constants.Databases.Applications.ID, Constants.Databases.Applications.Collections.USER_APPS),
        new SqlQuerySpec(@"SELECT r.appId FROM ROOT r WHERE r.userId = @userId", (@"@userId", userId).ToSqlParameters()))
    .ToList()
    .Select(s => $@"'{s.appId}'");

Don't encapsulate IN parameter

Removing the () around the parameter spec in the query thinking maybe the SqlParameter spec was doing the array specification?

Eg: @"SELECT r.id, r.appName FROM ROOT r WHERE r.appId IN @userApps")

Ends up throwing "Syntax error, incorrect syntax near '@userApps'."

Validate via Azure Portal queries

Ran the (expected) SQL that this code should be running.

I get back my expected results without issue (ie: I know there is a result set for these queries as-written).

Debugger output for Query 1

AppIds are coming back from query 1.

Unsatisfactory workaround

Change query 2 to not be parameterized. Rather, inject the comma-separated list of IDs from query 1 in to it:

var userApps = string.Join(@",", _docs.CreateDocumentQuery(UriFactory.CreateDocumentCollectionUri(Constants.Databases.Applications.ID, Constants.Databases.Applications.Collections.USER_APPS),
        new SqlQuerySpec(@"SELECT r.appId FROM ROOT r WHERE r.userId = @userId", (@"@userId", userId).ToSqlParameter()))
    .ToList()
    .Select(s => $@"'{s.appId}'"));
var query = _docs.CreateDocumentQuery<Document>(UriFactory.CreateDocumentCollectionUri(Constants.Databases.Applications.ID, Constants.Databases.Applications.Collections.APP_DEFINITIONS),
        new SqlQuerySpec($@"SELECT r.id, r.appName FROM ROOT r WHERE r.appId IN ({userApps})"),
        new FeedOptions { EnableCrossPartitionQuery = true })
    .AsDocumentQuery();

Works perfectly but I'm not going to accept it as an answer to this problem as it goes against a couple decades-worth of SQL best practices and, frankly, shouldn't be a solution.

---

Here's my ToSqlParameters() extension method in case it's the culprit (this works everywhere else I've used it, though. Maybe something special is needed for arrays?):

public static SqlParameterCollection ToSqlParameters(this (string, object) parmDef) => new SqlParameterCollection(new[] { new SqlParameter(parmDef.Item1, parmDef.Item2) });

Thanks!

Answer 1

If you use a parameterized IN list, then it will be considered as a single value when the parameter is expanded.

For instance, for this query:

SELECT * FROM r WHERE r.item IN (@items) And @items is defined as "['val1', 'val2', 'val3']" will be interpreted as such:

SELECT * FROM r WHERE r.item IN (['val1', 'val2', 'val3']) which basically means that you're comparing r.item to a single value that is an array of three elements (i.e. equivalent to r.item = ['val1', 'val2', 'val3']).

To compare to multiple items, you need to use a parameter for each value. Something like this: SELECT * FROM r WHERE r.item IN (@val1, @val2, @val3])

A more convenient way to write this query is to use ARRAY_CONTAINS instead and pass the array of items as a single parameter. So the above query will be written like this:

SELECT * FROM r WHERE ARRAY_CONTAINS(@items, r.item)