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.
.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}'");
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'."
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).
AppIds are coming back from query 1.
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!
NET SDK v2. x will be retired; the SDK and all applications using the SDK will continue to function; Azure Cosmos DB will simply cease to provide further maintenance and support for this SDK. We recommend migrating to the latest version of the . NET SDK v3 SDK.
Access Azure Cosmos DB Explorer Sign in to Azure portal. From All resources, find and navigate to your Azure Cosmos DB account, select Keys, and copy the Primary Connection String. Go to https://cosmos.azure.com/, paste the connection string and select Connect.
Get the MongoDB connection string to customizeIn an Internet browser, sign in to the Azure portal. In the Azure Cosmos DB blade, select the API. In the left pane of the account blade, click Connection String. The Connection String blade opens.
Now let's connect Azure Cosmos DB and Azure Functions for real: Create an Azure Functions trigger for Azure Cosmos DB in the Azure portal. Create an Azure Functions HTTP trigger with an Azure Cosmos DB input binding. Azure Cosmos DB bindings and triggers.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With