Add support for single entity retrieval instead of IEnumarable
Single \ First and so are currently not supported in Document DB
For example, the following code:
return _client.CreateDocumentQuery<TEntity>(_collection.SelfLink).SingleOrDefault(x => x.Id == id);
causes the error:
An exception of type 'Microsoft.Azure.Documents.Linq.DocumentQueryException' occurred in Microsoft.Azure.Documents.Client.dll but was not handled in user code
Additional information: Query expression is invalid, expression return type Entities.Models.Hierarchy is unsupported. Query must evaluate to IEnumerable.
Thank you for your feedback. I have discussed this in more detail with our team.
You are correct the workaround suggested to use .AsEnumerable().FirstOrDefault(). should not be a recommended work around. This way results in materializing all documents on the client first before getting the 1st document, which is not very efficient and the exact opposite of what you are trying to achieve.
Instead we recommend you use Take(1).AsEnumerable() and then .First() or .Single() or .FirstOrDefault() for Single() and First(). Take(1) is translated to SELECT TOP 1 and is processed server-side so more efficient than the previous suggestion and is what you are trying to achieve.
As to the original ask. The support for these operations can be done, but this work is not prioritized against the other work we want to deliver. I will move this back as unplanned but please note this is on our roadmap.
Thanks again for your suggestion and everyone’s feedback.
James Hancock commented
ComosDb should be targeting SQL Server Linq compatibility parity. This is a DUH one because First, FirstOrDeafult, Single and SingleOrDefault can easily be wrapped as outlined and won't cause fud. So it should be trivial to put these in the translator for linq.
Nathan Robinson commented
If you want to use this workaround, you need to use `.Take(2)` if you are using `.Single()` or `.SingleOrDefault()` or else `.Single` won't throw an exception when more than one record would have matched. `.Take(1)` works just fine with `.First()` or `.FirstOrDefault()`.
This is a simple request yet it's not being prioritized. Why can't we expect a modern DB to handle such requests? Please work on this.
Matthew Paul commented
Closing this off by suggesting '.AsEnumerable().FirstOrDefault()' is a precisely the hack we're wanting to avoid. Can you reopen?
Felix Schröter commented
In other cases than finding a single document by Id you can use .Take(1) on the IQueryable.
I ran into this too. A reasonable (though annoying) workaround is
.Where(x => x.Id == id).AsEnumerable().SingleOrDefault()
and this can be rewrapped as an extension method
Alexander Batishchev commented
FindAsync(id) is a must.