Cosmos DB - Use ARRAY_CONTAINS in LINQ

Question

I have collection of documents in Cosmos DB. Document can have inner array of objects. So model look like this:

public class Document
{
    public string Id { get; set; }

    public IList<InnerDocument> InnerDocuments { get; set; } 
}

public class InnerDocument
{
    public string Type { get; set; }

    public string Created { get; set; }
}

I need to get all inner documents if at least one of them has certain type.

If I create query like this:

var innerDocument = new InnerDocument()
{
    Type = "foo"
};

context.CreateDocumentQuery<Document>(uri, feedOptions)
    .Where(d => d.id == "sample" && d.InnerDocuments.Contains(innerDocument));

it translate like this:

SELECT * FROM root 
WHERE (root[\"id\"] = "sample" 
    AND ARRAY_CONTAINS(root[\"innerDocuments\"], {\"type\":\"foo\"}))

but it returns nothing, because no inner document look like this (all inner documents has also Created) so I need to add third parameter to ARRAY_CONTAINS (which tell that only part match on document is enough) so it should look like this:

SELECT * FROM root 
WHERE (root[\"id\"] = "sample" 
    AND ARRAY_CONTAINS(root[\"innerDocuments\"], {\"type\":\"foo\"}, true))

My problem is that I did not figure out how to pass third parameter in linq. I also tried write IEqualityComparer, which always return true but with no effect (well efect was that I got exception..).

Do you have any idea how could I pass that param in linq?

Thanks.

Answer 1

as far as I know, unfortunately there is no LINQ equivalent for the ARRAY_CONTAINS (<arr_expr>, <expr> , bool_expr) overload. To achieve your scenarios, for now you can use SQL query. We are currently working on a set of changes that will enable LINQ for this scenario.

Edit: the available alternative is to use the Any operator with the filters on the property you want to match. For example, the SQL filter: ARRAY_CONTAINS(root.addresses, {"city": "Redmond"}, TRUE) is equivalent to this LINQ expression: addresses.Any(address => address.city == "Redmond")

Answer 2

My solution was slightly more of a hack than a solution, but it works temporarily until the full functionality for .Any() exists.

I use Expressions to dynamically build the Where predicate for my documents, allowing me pass in a CosmosSearchCriteria object which has a list of CosmosCriteria objects as below:

public class CosmosCriteria 
{

    public CosmosCriteria()
    {
        ContainsValues = new List<string>();
    }
    public CosmosCriteriaType CriteriaType { get; set; }
    public string PropertyName { get; set; }
    public string PropertyValue { get; set; }
    public ConvertedRuleComparitor Comparitor { get; set; }
    public DateRange Dates { get; set; }
    public List<string> ContainsValues { get; set; }
}

This allows me to query any property of the Contact model by essentially passing in the PropertyName and PropertyValue.

I haven't looked into the other workaround in here to see if I can make it work with my expression tree building, at the minute I can't afford the time to investigate.

    public async Task<CosmosSearchResponse<Model.Contact>> 
    GetContactsBySearchCriteriaAsync(int pageSize, long companyId, 
    CosmosSearchCriteria searchCriteria, string continuationToken = null)
    {
        var collectionName = CreateCollectionName(companyId, Constants.CollectionType.Contacts);
        var feedOptions = new FeedOptions { MaxItemCount = pageSize };
        if (!String.IsNullOrEmpty(continuationToken))
        {
            feedOptions.RequestContinuation = continuationToken;
        }
        var collection = UriFactory.CreateDocumentCollectionUri(
                Configuration.GetValue<string>(Constants.Settings.COSMOS_DATABASE_SETTING),
                collectionName);

        IOrderedQueryable<Model.Contact> documents = Client.CreateDocumentQuery<Model.Contact>(
            collection,
            feedOptions
        );

        documents = (IOrderedQueryable<Model.Contact>)documents.Where(document => document.deleted != true);
        bool requiresConcatenation = false;
        foreach (var criteria in searchCriteria.Criteria)
        {
            switch (criteria.CriteriaType)
            {
                case Constants.CosmosCriteriaType.ContactProperty:
                    // This is where predicates for the documents.Where(xxxx) 
                    // clauses are built dynamically with Expressions.
                    documents = AddContactPropertyClauses(documents, criteria);
                    break;
                case Constants.CosmosCriteriaType.PushCampaignHistory:
                    requiresConcatenation = true;
                    break;
            }
        }

        documents = (IOrderedQueryable<Model.Contact>)documents.AsDocumentQuery();

        /*
            From this point onwards, we have to do some wizardry to get around the fact that there is no Linq to SQL
            extension overload for the Cosmos DB function ARRAY_CONTAINS (<arr_expr>, <expr> , bool_expr).
            The feature is planned for development but is not yet ready. 
            Keep an eye on the following for updates:
                https://stackoverflow.com/questions/52412557/cosmos-db-use-array-contains-in-linq
                https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/11503872-support-linq-any-or-where-for-child-object-collect
        */
        if (requiresConcatenation)
        {
            var sqlString = documents.ToString();
            var jsonDoc = JsonConvert.DeserializeObject<dynamic>(sqlString); // Have to do this to remove the escaping
            var q = (string)jsonDoc.query;
            var queryRootAlias = Util.GetAliasNameFromQuery(q);

            if (queryRootAlias == string.Empty)
            {
                throw new FormatException("Unable to parse root alias from query.");
            }

            foreach (var criteria in searchCriteria.Criteria)
            {
                switch (criteria.CriteriaType)
                {
                    case Constants.CosmosCriteriaType.PushCampaignHistory:
                        q += string.Format(" AND ARRAY_CONTAINS({0}[\"CampaignHistory\"], {{\"CampaignType\":1,\"CampaignId\":{1}, \"IsOpened\": true }}, true) ", queryRootAlias, criteria.PropertyValue);
                        break;
                }
            }

            documents = (IOrderedQueryable<Model.Contact>)Client.CreateDocumentQuery<Model.Contact>(
                collection,
                q,
                feedOptions
            ).AsDocumentQuery();
        }

        var returnValue = new CosmosSearchResponse<Model.Contact>();
        returnValue.Results = new List<Model.Contact>();

        Console.WriteLine(documents.ToString());

        var resultsPage = await ((IDocumentQuery<Model.Contact>)documents).ExecuteNextAsync<Model.Contact>();
        returnValue.Results.AddRange(resultsPage);
        if (((IDocumentQuery<Model.Contact>)documents).HasMoreResults)
        {
            returnValue.ContinuationToken = resultsPage.ResponseContinuation;
        }

        return returnValue;
    }  

Hope this helps, or if someone has a better way, please do tell!

Dave

Answer 3

If I understand correctly, you wish to retrieve all documents that have any inner document in the array with a given property value ("foo" in this example).

Normally, you would use .Where(d => d.InnerDocuments.Any(i => i.Type == "foo")), but Any is not supported yet by the Cosmos LINQ provider.

Instead, you can use this construct as a work-around:

context.CreateDocumentQuery<Document>(uri, feedOptions)
    .Where(d => d.Id == "sample")
    .SelectMany(d => d.InnerDocuments.Where(i => i.Type == "foo").Select(i => d));

According to this thread Microsoft has recently started working on a real Any feature for the Cosmos LINQ provider.