CODEWITHSUNDEEP
c#linq.net-coreazure-cosmosdb
rekcul
rekcul

Reputation: 366

StringComparison.OrdinalIgnoreCase not working in CreateDocumentQuery().Where() for CosmosDB query

Here is what I have and what I want to do

I have a Cosmos DB with a container, lets call the container "Projects". Within that container I have hundred of project documents which have the following structure (example in json):

{
 "name": "Death Star - Software update",
 "number": "133701",
 "customer" : ""
}

To make the data available for any type of client I use an http-trigger azure function (.Net Core 2.1). The azure function has an input binding to the cosmos db, several security mechanisms and expects one or several get parameters called "Search" so the request url could look like this:

https://www.someUrlToMyAzureStuff.com/api/GetProjects?Search=Death&Search=update

The idea is, that the function allows to search for keywords and what ever project contains those keywords in its properties will be returned.

Here is short code example for the azure function:

[FunctionName("GetProjects")]
public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequest request,
    [CosmosDB(databaseName: "ProjectsDatabase", collectionName: "ProjectsContainer",
            ConnectionStringSetting = "someConnectionString")] DocumentClient projectsContainer,
    ILogger log)
{
    try
    {
        if (CheckSecurityStuff...)
        {
            // The httpGetParameters contains a List<string> that contains the "Search" Get parameters from the Url
            var httpGetParameters = RESTUtility.RetrieveHttpGetParametersFromRequest(request);
            var projects = await CosmosDataProvider.GetProjectsByFiltersAsync(projectsContainer, httpGetParameters.UrlParams);                  

            return new OkObjectResult(projects);
        }
        else
        {
            return new BadRequestResult();
        }
    }
    catch (Exception)
    {
        return new BadRequestResult();
    }
}

And here is the CosmosDataProvider.GetProjectsByFiltersAsync function where I face some strange behaviour:

        internal static async Task<List<Project>> GetProjectsByFiltersAsync(DocumentClient projectsContainer, List<string> filters)
        {
            return await Task.Run(() =>
            {
                var uri = UriFactory.CreateDocumentCollectionUri("ProjectsDatabase", "ProjectsContainer");
                var feedOptions = new FeedOptions { EnableCrossPartitionQuery = true };

                var projects = projectsContainer.CreateDocumentQuery<Project>(uri, feedOptions)
                    .Where(project => project.Name.Contains(filters[0]) ||
                                      project.Number.Contains(filters[0]) ||
                                      project.Customer.Contains(filters[0])).ToList();

                foreach (var filter in filters.Skip(1))
                {
                    projects = projects.Where(project =>
                                      project.Name.Contains(filter) ||
                                      project.Number.Contains(filter) ||
                                      project.Customer.Contains(filter)).ToList();
                }

                return projects;
            }
        }

So far so good: If I send a request with the search parameters "Death" and "update" the function returns the above mentioned project. Which works fine, but its only returned when the keywords matching the case sensitive. So what I want to do is to make the search ignoring the case. So I started using the:

Contains(string, StringComparison)

with StringComparison.OrdinalIgnoreCase. Which looks like this:

        internal static async Task<List<Project>> GetProjectsByFiltersAsync(DocumentClient projectsContainer, List<string> filters)
        {
            return await Task.Run(() =>
            {
                var uri = UriFactory.CreateDocumentCollectionUri("ProjectsDatabase", "ProjectsContainer");
                var feedOptions = new FeedOptions { EnableCrossPartitionQuery = true };

                var projects = projectsContainer.CreateDocumentQuery<Project>(uri, feedOptions)
                    .Where(project => project.Name.Contains(filters[0], StringComparison.OrdinalIgnoreCase) ||
                                      project.Number.Contains(filters[0], StringComparison.OrdinalIgnoreCase) ||
                                      project.Customer.Contains(filters[0], StringComparison.OrdinalIgnoreCase)).ToList();

                foreach (var filter in filters.Skip(1))
                {
                    projects = projects.Where(project =>
                                      project.Name.Contains(filter) ||
                                      project.Number.Contains(filter) ||
                                      project.Customer.Contains(filter)).ToList();
                }

                return projects;
            }
        }

My expectation is that I search for "death" and "uPdAte" and get the project. But Cosmos DB does not return it.

Questions

-> Do you have any idea why the StringComparison does not work within a CreateDocumentQuery where condition?

-> As the Contains(string, StringComparison) is only available in .Net Core, could it be a conflict with the Azure SDK .Net which might uses the .Net Framework?

(By the way: I also tried to replace the "Contains" function with "IndexOf" or a "RegEx.IsMatch", both failing with an exception that they are not supported within a LINQ Where statement) Exception details:

One or more errors occurred. (Method 'IndexOf' is not supported., Windows/10.0.18363 documentdb-netcore-sdk/2.3.0)

-> Any other idea how I can replace the contains function if there is no solution?

Upvotes: 1

Views: 1361

Answers (1)

Mark Brown
Mark Brown

Reputation: 8763

Update. Cosmos now supports case insensitive queries and Contains now can utilize the index. Can read more about both of these improvements in the announcement blog post

Unfortunately there are a couple of issues here.

The first issue is Cosmos DB is not case insensitive. To do string equality you would need to write the strings into Cosmos in a second property as all upper case and then use .ToUpper() on the string for equality. If you try to use the ToUpper() in Cosmos in the where it will not use the index so you'll get poor performance. The second, bigger issue is Contains() also will not use the index. We recently updated our query troubleshooting doc here on these two system functions. https://learn.microsoft.com/en-us/azure/cosmos-db/troubleshoot-query-performance#understand-which-system-functions-utilize-the-index

Right now we recommend in this scenario is customers use Azure Search on top of Cosmos and do their text searches that way. In the future we'll be working on providing a free text search and case insensitive indexing but no ETA on that at this time.

Hope this is helpful.

Upvotes: 1

Related Questions