DocumentDB LINQ matches multiple child objects
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 another option.
eg.
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 is giving me the error Method 'All' is not supported.
.
I found examples that map a single property of a child object: LINQ query problem using Any in 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 is unclear how this approach can be extended to support matching of multiple child objects.
I found ARRAY_CONTAINS function there that can be used: Azure DocumentDB ARRAY_CONTAINS on subdocuments
But all the examples I've come across use SQL queries.
I haven't come across any documentation for ARRAY_CONTAINS
LINQ, only SQL .
<s> I tried the following SQL query to make sure 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" })
As per the comments on this answer , ARRAY_CONTAINS
only works with arrays of primitives, not objects. SO doesn't seem to be the right fit for what I want to achieve. C>
It looks like the comments on this answer are incorrect and I had syntax errors in my request. I needed to add double quotes around the property names.
Running this query returned 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
it seems to achieve what I want, so I am looking for how to use it using LINQ syntax.
source to share
Using .Contains
LINQ in a 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 .Where
chain calls to create a predicate chain AND
.
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 link predicates with OR
, then you need the Expression Predicate Builder Library.
source to share