Azure-cosmos-dotnet-v2: SelectMany doesn't work after Select

[rosieks]

I have such query:

.Select(x => x.Document).SelectMany(x => x.Members.Select(m => new { TripId = x.TripId, UserId = m.UserId }))

And it's translated to such SQL:
SELECT VALUE {TripId: root["Document"]["TripId"], UserId: m["UserId"]} FROM root JOIN m IN root["Members"] WHERE STARTSWITH(root["id"], "TripMembers") which is incorrect - this part is inccorrect: root["Members"]

When I use that query:

.SelectMany(x => x.Document.Members.Select(m => new { TripId = x.Document.TripId, UserId = m.UserId }))

then it's translated to correct SQL:

SELECT VALUE {TripId: root["Document"]["TripId"], UserId: m["UserId"]} FROM root JOIN m IN root["Document"]["Members"] WHERE STARTSWITH(root["id"], "TripMembers")

[ghost]

Does the second query you included give you the correct results? If so, then use this instead of the first example.

Is there an issue?

[rosieks]

Yes - the second one is working, but the first doesn't (or at least doesn't work correctly) - and that's the issue.

[ibebbs]

Hi, has there been any development here? I'm currently being hit by this issue and cannot use the workaround described above.

I'm using version 2.1.3 of the 'Microsoft.Azure.DocumentDB' package published just a couple of days ago (some 990 days after this issue was raised!!)

I'd be happy to investigate the issue myself (I have some experience writing LINQ providers) if you can provide access to the source?

Thanks

[khdang]

@ibebbs just to confirm it is the same issue, can you provide the LINQ expression that is not translated correctly for you? and its translation? We're finalizing an improvement change that will enable a large set of scenarios.

[ibebbs]

@khdang I have created a reproduction of the issue in new repository here. As per the readme, the query:

var query = _client
  .CreateDocumentQuery<Container>(_collection.SelfLink)
  .Select(container => container.Pupil)
  .SelectMany(pupil => pupil.Classes
    .Where(@class => @class.Id == CosmosDB101.Id)
    .Select(@class => pupil));

Generates: SELECT VALUE root["Pupil"] FROM root JOIN class IN root["Classes"] WHERE (class["Id"] = "501ffc8e-272d-4f26-bebb-f5ce8ce1c095")

But should generate: SELECT VALUE root["Pupil"] FROM root JOIN class IN root["Pupil"]["Classes"] WHERE (class["Id"] = "501ffc8e-272d-4f26-bebb-f5ce8ce1c095")

FWIW, in this example I am trying to create a query of pupils that attend a specific class. I would like to have achieved this with the following query:

var query = _client
  .CreateDocumentQuery<Container>(_collection.SelfLink)
  .Select(container => container.Pupil)
  .Where(pupil => pupil.Classes.Any(@class => @class.Id == CosmosDB101.Id));

But this isn't supported as per this feedback (which, interestingly, has been 'started' since I reported the issue). As such I'm using the suggested workaround above but this is also failing in this scenario as I have shown.

[ibebbs]

FYI, this issue also seems to be related to / duplicated by #246 and #208

[khdang]

Thanks. I'm finalizing the changes to support Any and nested Where->Count as in the issue you reported and some other scenarios. I'm also looking at this and the two mentioned issues and will have the change needed for them to work.

[ibebbs]

@khdang that's great news. Any idea when we might see a new SDK with the fixes? No massive rush but I'd like to be able to plan when I can re-investigate the issue.

Also, are you aware of if/when the source of the SDK might be published? It'd be really good to be able to look into these kinds of issue myself and, hopefully, provide a PR rather than an issue.

[garfbradaz]

@ibebbs v3 matey is here:

https://github.com/Azure/azure-cosmos-dotnet-v3/tree/master/Microsoft.Azure.Cosmos/src/Linq

[ibebbs]

Fantastic news! I'll check it out ASAP, thanks.

[khdang]

The fix for this and #208 is under review. It will be released in the release after it's merged.

[khdang]

This has been fixed with the latest release 2.4.0.