DocumentDB LINQ match multiple child objects

Question

I have a simple document.

{
    Name: "Foo",
    Tags: [
        { Name: "Type", Value: "One" },
        { Name: "Category", Value: "A" },
        { Name: "Source", Value: "Example" },
    ]
}

I would like to make a LINQ query that can find these documents by matching multiple Tags.

i.e. Not a SQL query, unless there is no other option.

e.g.

var tagsToMatch = new List<Tag>()
{
    new Tag("Type", "One"),
    new Tag("Category", "A")
};

var query = client
    .CreateDocumentQuery<T>(documentCollectionUri)
    .Where(d => tagsToMatch.All(tagToMatch => d.Tags.Any(tag => tag == tagToMatch)));

Which gives me the error Method 'All' is not supported..

I have found examples where a single property on the child object is being matched: LINQ Query Issue with using Any on DocumentDB for child collection

var singleTagToMatch = tagsToMatch.First();

var query = client
    .CreateDocumentQuery<T>(documentCollectionUri)
    .SelectMany
    (
        d => d.Tags
            .Where(t => t.Name == singleTagToMatch.Name && t.Value == singleTagToMatch.Value)
            .Select(t => d)
    );

But it's not obvious how that approach can be extended to support matching multiple child objects.

I found there's a function called ARRAY_CONTAINS which can be used: Azure DocumentDB ARRAY_CONTAINS on nested documents

But all the examples I came across are using SQL queries.

This thread indicates that LINQ support was "coming soon" in 2015, but it was never followed up so I assume it wasn't added.

I haven't come across any documentation for ARRAY_CONTAINS in LINQ, only in SQL.

~~I tried the following SQL query to see if it does what I want, and it didn't return any results:~~

SELECT  Document
FROM    Document
WHERE   ARRAY_CONTAINS(Document.Tags, { Name: "Type", Value: "One" })
AND     ARRAY_CONTAINS(Document.Tags, { Name: "Category", Value: "A" })

According to the comments on this answer, ARRAY_CONTAINS only works on arrays of primitives, not objects. SO it appears not to be suited for what I want to achieve.

It seems the comments on that answer are wrong, and I had syntax errors in my query. I needed to add double quotes around the property names.

Running this query did return the results I wanted:

SELECT  Document
FROM    Document
WHERE   ARRAY_CONTAINS(Document.Tags, { "Name": "Type", "Value": "One" })
AND     ARRAY_CONTAINS(Document.Tags, { "Name": "Category", "Value": "A" })

So ARRAY_CONTAINS does appear to achieve what I want, so I'm looking for how to use it via the LINQ syntax.

this isn't too hard. You need for the class Tag to implement IComparable which compares multiple tags. — jdweng
@jdweng And the DocumentDB LINQ provider understands how to convert that custom IComparable in to the right SQL query? — user310988
There is some indication that Contains is supported, so I'm wondering if .Where(d => d.Tags.Contains(singleTagToMatch)) works? Because if it does, we can eventually build dynamically && predicate expression similar to what you have used inside the SQL query. — Ivan Stoev
@IvanStoev That's a very good thought thanks! I'll do a bit of investigation and see what .Contains produces. — user310988
@IvanStoev calling query.ToString translates the query to the SQL it'll send to the back end. And looking at that SQL .Where(d => d.Tags.Contains(singleTagToMatch)); produces WHERE ARRAY_CONTAINS(root["Tags"], {"Name":"Type","Value":"One"}) which is perfect! I'm now investigating dynamiclly building LINQ predicates. — user310988

Ivan Stoev Ivan Stoev · Accepted Answer · 2017-06-27T12:46:51

Using .Contains in the LINQ query will generate SQL that uses ARRAY_CONTAINS.

So:

var tagsToMatch = new List<Tag>()
{
    new Tag("Type", "One"),
    new Tag("Category", "A")
};

var singleTagToMatch = tagsToMatch.First();

var query = client
    .CreateDocumentQuery<T>(documentCollectionUri)
    .Where(d => d.Tags.Contains(singleTagToMatch));

Will become:

SELECT * FROM root WHERE ARRAY_CONTAINS(root["Tags"], {"Name":"Type","Value":"One"})

You can chain .Where calls to create a chain of AND predicates.

So:

var query = client.CreateDocumentQuery<T>(documentCollectionUri)

foreach (var tagToMatch in tagsToMatch)
{
    query = query.Where(s => s.Tags.Contains(tagToMatch));
}

Will become:

SELECT * FROM root WHERE ARRAY_CONTAINS(root["Tags"], {"Name":"Type","Value":"One"}) AND ARRAY_CONTAINS(root["Tags"], {"Name":"Category","Value":"A"})

If you need to chain the predicates using OR then you'll need some expression predicate builder library.

DocumentDB LINQ match multiple child objects

1 Answers