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).

[andyamacdonald]

@NickSevens did you ever happen to find a work around for this? I'm in exactly the same position and it doesn't look like native support has been added yet. I could be missing something though.

@smitpatel is this something you have on the roadmap?

[athinadev]

Please see this too, it should be the same error
#23704

[roji]

@Aford73 raw SQL support has been implemented in Cosmos, so you can use that for the queries which requires unsupported EF features (see docs).

[Aford73]

@Aford73 raw SQL support has been implemented in Cosmos, so you can use that for the queries which requires unsupported EF features (see docs).

Many thanks @roji we were aware of the raw SQL support, this can be used only if we will expose a dedicated endpoint (that won't benefit from all the features that OData adds: select, filter, top, skip, etc.) for filtering employees based on the email:

Example of possible endpoint format: v2/employees/findByEmail?value=aaa@aaa.com&ownership=Organization

any features like select, top, skip, will require bespoke development on this dedicated endpoint.

[roji]

@Aford73 that's true, though note that EF Core does allow you to compose LINQ operators over raw SQL - not sure exactly what it would look like with OData, but I'm assuming it's possible. In any case, we're not currently planning on working on this for 7.0 (the issue is in the Backlog milestone), so it may be a good idea to look at workarounds.

[Aford73]

@Aford73 that's true, though note that EF Core does allow you to compose LINQ operators over raw SQL - not sure exactly what it would look like with OData, but I'm assuming it's possible. In any case, we're not currently planning on working on this for 7.0 (the issue is in the Backlog milestone), so it may be a good idea to look at workarounds.

Thanks vey much for the response @roji we'll take a look at the LINQ possibilities

If anyone else has gone down this route it would be interesting to hear your findings?

[cpalmisciano]

I am running into the same issue querying array fields of any type, in EF 6.0.
Is it a work around, other than raw sql?

[sekarcse]

Hi Guys,

We are also facing same kind of issue.

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

Do we have any solutions for this?

[truman303]

@roji @NickSevens I tried the FromRawSql approach with an EXISTS clause, however the data I get back seems to be nested inside a "c: ..." tag, instead of returning the array at the root. I think this may be due to EFCore wrapping the SQL in a "SELECT c FROM (...) c", which I cannot seem to change to "SELECT VALUE c FROM ...". More detail here: https://stackoverflow.com/questions/73386712/how-do-i-access-the-value-using-cosmosqueryableextensions

The result is that I get no data back, however when I copy the EFCore translated query from the log into Data Explorer and modify it to "SELECT VALUE c.." then it works as expected.

UPDATE
This was sorted out by using the string format-like method of passing parameters. See post above for more details.

[gabolarraguivel]

Hello! Is this planned to be fixed with EF Core 7.0?

[roji]

This issue is in the Backlog milestone. This means that it is not going to happen for the 7.0 release. We will re-assess the backlog following the 7.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[gabolarraguivel]

I think this is one of the most important things to fix, because there is no way of querying nested objects aside of using FromSqlQuery.

Forget about using LINQKit or another library that makes querying easier.

I will move from using EF Core to Azure Cosmos DB Repository until this critical feature is implemented.

[wbuck]

I’ve found that using the CosmosClient’s Linq provider is able to translate queries like this. EF Core is a great piece of tech, but it still needs some time with Cosmos in order to generate the correct expression trees.

My recommendation is to switch to using the CosmosClient Linq provider (which is also recommended by the devs who created the cosmos client library) over EF Core for the time being.

[hasmMarques]

Hi,
I have a document that looks like this;

{
    "id": "test",
    "version": "test",
    "applicableProducts": [
        "test0",
        "test1"
    ],
    "deletedTimestamp": null
}

Using EF 7.0 wit a LINQ query expression it get translated into this;

 'DbSet<FirmwareInformation>()
          .Where(x => x.DeletedTimestamp == null && x.ApplicableProducts
              .Any(Inner => Inner == "test0"))'

When the query is executed I get this error:

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.InvalidOperationException: The LINQ expression 'Inner => Inner == "test0"' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Do you have any solution for this besides using the the CosmosClient?

[wbuck]

@hasmMarques try using CountAsync instead.

(await DbSet<FirmwareInformation>()
          .Where(x => x.DeletedTimestamp == null && x.ApplicableProducts)
          .Where(inner => inner == "test0")
          .CountAsync()) > 0;

[hasmMarques]

@wbuck thanks for your reply.
Unfortunately the result is the same

dbug: 26-Jul-23 08:43:00.920 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query)
      Compiling query expression:
      'DbSet<FirmwareInformation>()
          .Where(x => x.DeletedTimestamp == null && x.ApplicableProducts
              .Any(Inner => Inner == "test0"))
          .Count()'
dbug: 26-Jul-23 08:43:01.106 CoreEventId.ContextDisposed[10407] (Microsoft.EntityFrameworkCore.Infrastructure)
      'DpsDbContext' disposed.
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.InvalidOperationException: The LINQ expression 'Inner => Inner == "test0"' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
         at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)

[hasmMarques]

Hi all,

could you please inform me if the issue that I detailed here has been resolved in EF8 or EF9, or if a resolution is still pending.

[roji]

I can confirm that the query types listed on this issue should be fully working on EF 9.0 (this is a specific case of querying over nested collections, #16926, #25364). If memory serves, preview.7 - to be released in the next few days - should already work, so once that's released can you please test with it and report back on this issue?

[hasmMarques]

I can confirm that the query types listed on this issue should be fully working on EF 9.0 (this is a specific case of querying over nested collections, #16926, #25364). If memory serves, preview.7 - to be released in the next few days - should already work, so once that's released can you please test with it and report back on this issue?

Hi Roji,

These are excellent news.
Yes, once EF9 is released, we will test this and report the outcome.
Thank you for your support.