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, Jun 27 '17 at 10:22
@jdweng And the DocumentDB LINQ provider understands how to convert that custom IComparable in to the right SQL query? — , Jun 27 '17 at 10:23
There is some [indication](https://learn.microsoft.com/en-us/azure/cosmos-db/documentdb-sql-query) 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, Jun 27 '17 at 10:57
@IvanStoev That's a very good thought thanks! I'll do a bit of investigation and see what .Contains produces. — , Jun 27 '17 at 12:18
@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. — , Jun 27 '17 at 12:29

score 9 · Accepted Answer · edited Jun 27 '17 at 13:37

9

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.

edited Jun 27 '17 at 13:37

answered Jun 27 '17 at 12:46

Ivan Stoev

195,425
15
312
343

1

Thanks for the great answer, this solved the problem for me. I hope you don't mind, I've expanded answer to give more detail and examples. – Jun 27 '17 at 13:38
1

You are welcome, glad it helped :) And sure (actually thank you), explanations are not my best strength :) – Ivan Stoev Jun 27 '17 at 13:42
So how does this work if you only want one of the elements (name or value) to match? I tried overriding Equals on the Tag class and with EqualityComparer on one of the elements/properties and this does not work. – carens Oct 18 '17 at 16:27
@carens The things you tried work only in LINQ to Objects. I can't test now, but you could try combining `Select` + `Contains`, e.g. `query.Where(s => s.Tags.Select(t => t.Name).Contains(tagToMatch.Name));` – Ivan Stoev Oct 18 '17 at 16:39
I get an error saying the Select method is not supported. I tried a bit with SelectMany, but then I get duplicate results. – carens Oct 18 '17 at 17:20

DocumentDB LINQ match multiple child objects

1 Answers1