Azure cosmos dynamic query

Question

I need a help on to construct dynamic cosmos query using c#. In cosmos database having name like "test.a'lcarte", so in the code we are trying to construct the dynamic query like

string _queryText = "SELECT distinct value c.createdby FROM c where c.createdby in(" + "'test.a\\'lcarte'" + ") and c.isactive!=false";

'test.a'lcarte' can be replace with input as string runtime.

this is output of _queryText :

SELECT distinct value c.createdby FROM c where c.createdby in('test.a'lcarte')

, looks like backslash() with apostrophe escpaing backslash and due to this string is not going correct format.

So my question is how to constrcut string using backslash with apostrophe.

Note :

SELECT distinct value c.createdby FROM c where c.createdby in('test.a\\'lcarte') and c.isactive!=false

, query working fine in cosmos query editor and my intention to pass or construct the same query dynamically in C# code.

Answer 1

To retrieve a name containing special characters from a Cosmos collection, use \\ instead of special characters or by using verbatim string @ In the code below, the Replace function is used to escape single quotes in the input string by adding a backslash before them or verbatim @ before the string.

Using backslash \\:

class Program
{
    private const string EndpointUrl = "****";
    private const string PrimaryKey = "****";
    private const string DatabaseId = "newDb";
    private const string ContainerId = "newCont";

    static async Task Main()
    {
        using (var cosmosClient = new CosmosClient(EndpointUrl, PrimaryKey))
        {
            var database = await cosmosClient.CreateDatabaseIfNotExistsAsync(DatabaseId);
            var container = await database.Database.CreateContainerIfNotExistsAsync(ContainerId, "/id");

            string userInput = await GetNameFromDbAsync(container.Container);
            string escapedInput = EscapeSpecialCharacters(userInput);

            string queryText = $"SELECT DISTINCT VALUE c.name FROM c WHERE c.name IN('{escapedInput}') AND c.isactive != false";

            await ExecuteCosmosDbQuery(container.Container, queryText);
        }
    }

    static async Task<string> GetNameFromDbAsync(Container container)
    {
        var query = new QueryDefinition("SELECT TOP 1 c.name FROM c");
        var resultSet = container.GetItemQueryIterator<dynamic>(query);
        var response = await resultSet.ReadNextAsync();

        string name = response.FirstOrDefault()?.name?.ToString();

        return name;
    }

    static async Task ExecuteCosmosDbQuery(Microsoft.Azure.Cosmos.Container container, string queryText)
    {
        var queryDefinition = new QueryDefinition(queryText);
        var resultSet = container.GetItemQueryIterator<string>(queryDefinition);

        while (resultSet.HasMoreResults)
        {
            var response = await resultSet.ReadNextAsync();
            foreach (var item in response)
            {
                Console.WriteLine(item);
            }
        }
    }

    static string EscapeSpecialCharacters(string input)
    {
        return input.Replace("'", "\\'");
    }
}

Output: test.a'lcarte

Using verbatim symbol:

static async Task Main()
    {
        using (var cosmosClient = new CosmosClient(EndpointUrl, PrimaryKey))
        {
            var database = await cosmosClient.CreateDatabaseIfNotExistsAsync(DatabaseId);
            var container = await database.Database.CreateContainerIfNotExistsAsync(ContainerId, "/id");

            string userInput = await GetNameFromDbAsync(container.Container);

            string queryText = $@"SELECT DISTINCT VALUE c.name FROM c WHERE c.name IN('{EscapeSpecialCharacters(userInput)}') AND c.isactive != false";

            await ExecuteCosmosDbQuery(container.Container, queryText);
        }
    }

    static async Task<string> GetNameFromDbAsync(Container container)
    {
        var query = new QueryDefinition("SELECT c.name FROM c");
        var resultSet = container.GetItemQueryIterator<dynamic>(query);
        var response = await resultSet.ReadNextAsync();

        string name = response.FirstOrDefault()?.name?.ToString();

        return name;
    }

    static async Task ExecuteCosmosDbQuery(Microsoft.Azure.Cosmos.Container container, string queryText)
    {
        var queryDefinition = new QueryDefinition(queryText);
        var resultSet = container.GetItemQueryIterator<string>(queryDefinition);

        while (resultSet.HasMoreResults)
        {
            var response = await resultSet.ReadNextAsync();
            foreach (var item in response)
            {
                Console.WriteLine(item);
            }
        }
    }

    static string EscapeSpecialCharacters(string input)
    {
        return input.Replace("'", @"\'");
    }

Output: test.a'lcarte