Cosmos: Translate subquery in filter condition

[NickSevens]

Consider this CosmosDb document structure:

{
    "Id": "f0609362-4008-4edc-ae1f-76ef1c0ef974",
    "Discriminator": "MyEntity",
    "Title": "Test entity 1",
    "id": "MyEntity|f0609362-4008-4edc-ae1f-76ef1c0ef974",
    "AuthorizedUsers": [
        {
            "Discriminator": "UserInfo",
            "DisplayName": "John Doe",
            "LoginName": "john.doe@contoso.com"
        },
        {
            "Discriminator": "UserInfo",
            "DisplayName": "Jane Doe",
            "LoginName": "jane.doe@contoso.com"
        }
    ]
}

When querying data I would like to perform a query which filters on AuthorizedUsers properties. E.g.:

await _dbContext.MyEntities
                .Where(s => s.AuthorizedUsers.Any(m => m.LoginName == "jane.doe@contoso.com"))
                .ToListAsync();

However, this results in a query translation error:

System.InvalidOperationException: The LINQ expression 'Where(\r\n source: DbSet, \r\n predicate: (s) => Any(\r\n source: AsQueryable(Property<ICollection>(s, "AuthorizedUsers")), \r\n predicate: (o) => o.LoginName == "jane.doe@contoso.com"))' could not be translated. Either rewrite the query in a form that can be translated

Question: is this something that is or should be possible to achieve? The same query can be executed on CosmosDB SQL syntax, like this:

SELECT VALUE c
FROM c
WHERE EXISTS(
    SELECT VALUE u
    FROM u IN c.AuthorizedUsers
    WHERE u.LoginName = "jane.doe@contoso.com"
)

[smitpatel]

CosmosDb also supports Exists operator. I could not find it docs (hence it hasn't implemented yet) but query runs correctly.

[NickSevens]

@smitpatel you mean there is an undocumented way to achieve the same result using EF als linq? Or what do you mean exactly?

[smitpatel]

I meant that it is not in cosmos docs so we did not know that such SQL was valid. Hence it does not work in EF Core yet.

[NickSevens]

EXISTS operator is described here: https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery#exists-expression In the meantime; is there an alternative way to achieve the same result with EFCore?

[smitpatel]

No work-around.

[AndriySvyryd]

Related to #16146

[NickSevens]

@AndriySvyryd @smitpatel are there any other operators that do translate into a subquery (JOIN, SELECT, ...)?

[smitpatel]

@NickSevens - We don't have translation of subcollections composition in Cosmos yet. Mainly Exists operator or a in root.Orders pattern (which is used for join).