使用Azure DocumentDB(CosmosDB).Net SDK [英] WHERE IN with Azure DocumentDB (CosmosDB) .Net SDK

问题描述

见过这个问题我不确定为什么我的DocDb实例的以下代码不起作用:

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.

将我string.Join所在的字符串返回到逗号分隔的列表中.

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

例如:

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}'");

不要封装IN参数

在查询中删除参数规范周围的()可能是SqlParameter规范正在执行数组规范?

Don't encapsulate IN parameter

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

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

最终抛出语法错误，'@ userApps'附近的语法不正确."

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

运行(预期的)应运行此代码的SQL.

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 是是从查询1返回的.

AppIds are coming back from query 1.

将查询2更改为未进行参数化.而是将查询1中以逗号分隔的ID列表注入其中:

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();

工作完美，但我不会接受它作为此问题的答案，因为它与价值数十年的SQL最佳实践背道而驰，坦白地说，不应是一个解决方案

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.

如果是罪魁祸首，这是我的ToSqlParameters()扩展方法(不过，这在我用过的其他地方都可以使用.也许数组需要一些特殊的东西吗?):

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) });

谢谢！

推荐答案

如果使用参数化的IN列表，则在扩展参数时将其视为单个值.

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

例如，对于此查询: SELECT * FROM r WHERE r.item IN(@items) @items定义为"[['val1'，'val2'，'val3']"，则查询将被解释为: SELECT * FROM r WHERE r.item IN(['val1'，'val2'，'val3']) 这基本上意味着您正在将r.item与一个包含三个元素的数组的单个值进行比较(即等价于r.item = ['val1'，'val2'，'val3']).

For instance, for this query: SELECT * FROM r WHERE r.item IN (@items) And @items is defined as "['val1', 'val2', 'val3']" then the query 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']).

要与多个项目进行比较，您需要为每个值使用一个参数.像这样的东西: SELECT * FROM r WHERE r.item IN(@ val1，@ val2，@ val3])

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

写此查询的一种更方便的方法是改用ARRAY_CONTAINS并将项目数组作为单个参数传递.因此，上面的查询将这样写: SELECT * FROM r WHERE ARRAY_CONTAINS(@items，r.item)

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)

这篇关于使用Azure DocumentDB(CosmosDB).Net SDK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！