Cosmos db - linq to sql - query records that have exactly all input parameters in a nested array

Question

I'm trying to figure out how I can do this query using LinqToSql for cosmos db:

SELECT c FROM c
JOIN relation IN c.tagRelations
WHERE 
c.tenantId = '<<tenant-id>>'
and c.IsDeleted = false
AND ARRAY_CONTAINS(c.tags, 'tag-id')
AND ARRAY_LENGTH(c.tagRelations)>= 0
AND relation.source = 'tag-id'
AND (
    ARRAY_LENGTH(relation.target) != 2
    OR ARRAY_LENGTH(SetIntersect(relation.target, ['related-1', 'related-2'])) != 2
)

In our code we can't directly write this query, because the api expects an Expression<Func<T, bool>>. So the expression I came up with was:

var tagId = "tag-id";
var RELATED_TAG_IDS = new []{"related-1", "related-2"};
var EXPECTED_NUMBER_OF_RELATED_TAGS = RELATED_TAG_IDS.Length;

var results = await db.Get<DatabaseDocument>(record
    => record.TenantId == "<<tenant-id>>"
    && record.IsDeleted == false
    && record.Tags.Contains(tagId)
    && record.TagRelations.Any(relation
        => relation.Source == tagId
        && (
                  relation.Target == null
              ||  relation.Target.Count() != EXPECTED_NUMBER_OF_RELATED_TAGS
              || !relation.Target.Any(relTag => !RELATED_TAG_IDS.Contains(relTag))
            )
    )
    ,
    sorting => sorting.OrderByDescending(record => record.UploadTimestamp), ctoken);

The above query does not return the expected records if the number of targets in the database equals the expected number of targets, and one (or more) differ in value.

So if anyone can explain how to get it working correctly, I would be very grateful.

PS: As far as I can figure out it is not possible to call the SetIntersect function from inside C# using linq-to-sql.

PS: An xample of the data structure should be:

{
   "tenantId": "<<tenant-id>>",
   "IsDeleted": false,
   "tags": ["tag-id", "tag-id-2", "tag-id-3"],
   "tagRelations": [
       { "source": "tag-id", "target": ["tag-id-2", "tag-id-3"] },
       { "source": "tag-id-2", "target": ["tag-id-3"] }
       { "source": "tag-id-3", "target": [] }
   ],
   ...
}

PS: What I need to do is find all the records that have a specific source tag, and an incorrect set of target tags. So it could have no targets, more or less targets, or the same number of targets yet different values.

PS: we use nuget package "Microsoft.Azure.Cosmos" 3.31.2.

Answer 1

Cosmos db - linq to sql - query records that have exactly all input parameters in a nested array

I used GetItemLinqQueryable<> to create LINQ queryable collection of objects in the container. By using this we can query using LINQ expressions. Some expressions are shown in below code in var query. It filters the data using where clause with && operator.

Code I tried with:

public async Task<IEnumerable<DatabaseDocument>> ExecuteQueryAsync()
{
    var tagId = "tag-id";
    var RELATED_TAG_IDS = new[] { "related-1", "related-2" };
    var EXPECTED_NUMBER_OF_RELATED_TAGS = RELATED_TAG_IDS.Length;

    var query = container.GetItemLinqQueryable<DatabaseDocument>(allowSynchronousQueryExecution: true)
        .Where(record =>
            record.TenantId == "Tenant-1" &&
            record.IsDeleted == false &&
            record.Tags.Contains(tagId) &&
            record.TagRelations.Any(relation =>
                relation.Source == tagId &&
                (relation.Target == null ||
                 relation.Target.Count() != EXPECTED_NUMBER_OF_RELATED_TAGS ||
                 relation.Target
                     .Where(relTag => RELATED_TAG_IDS.Contains(relTag))
                     .Count() != EXPECTED_NUMBER_OF_RELATED_TAGS)
            )
        );

    var results = query.ToList();

    return results;
}

• SetIntersect is used in LINQ while filtering documents from Cosmos DB container.

• relation.Target represents the list of related tags in a TagRelation object.

• RELATED_TAG_IDS is an array of related tags that want to find Target list.

• Where clause filters Target list to include only tags that are in the RELATED_TAG_IDS array. It finds intersection between them.

Sample Input:

{
    "TenantId": "Tenant-1",
    "IsDeleted": false,
    "Tags": [
        "tag-id",
        "tag-id-2",
        "tag-id-3"
    ],
    "TagRelations": [
        {
            "Source": "tag-id",
            "Target": [
                "tag-id-2",
                "tag-id-3"
            ]
        },
        {
            "Source": "tag-id-2",
            "Target": [
                "tag-id-3"
            ]
        },
        {
            "Source": "tag-id-3",
            "Target": []
        }
    ]
}

Output:

{ "TenantId": "Tenant-1", "Source": "tag-id", "Target": ["tag-id-2", "tag-id-3"] }