New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SelectMany doesn't work after Select #91

Open
rosieks opened this Issue Jan 31, 2016 · 10 comments

Comments

Projects
None yet
4 participants
@rosieks
Copy link

rosieks commented Jan 31, 2016

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

This comment has been minimized.

Copy link

ghost commented Feb 1, 2016

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

This comment has been minimized.

Copy link

rosieks commented Feb 1, 2016

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

@ghost ghost added the investigating label Feb 3, 2016

@ibebbs

This comment has been minimized.

Copy link

ibebbs commented Oct 18, 2018

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 khdang self-assigned this Oct 22, 2018

@khdang

This comment has been minimized.

Copy link
Member

khdang commented Oct 23, 2018

@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

This comment has been minimized.

Copy link

ibebbs commented Oct 29, 2018

@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

This comment has been minimized.

Copy link

ibebbs commented Oct 29, 2018

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

@khdang

This comment has been minimized.

Copy link
Member

khdang commented Oct 29, 2018

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

This comment has been minimized.

Copy link

ibebbs commented Oct 29, 2018

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

@ibebbs

This comment has been minimized.

Copy link

ibebbs commented Jan 10, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment