使用Azure DocumentDB(CosmosDB).Net SDK

本文介绍了使用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) });

谢谢！

userApps