16

I am having issues in trying to query my Azure DocumentDb storage account when attempting to retrieve a single record. This is my WebAPI code:

// Controller...
public AccountController : ApiController {
    // other actions...

    [HttpGet]
    [Route("Profile")]
    public HttpResponseMessage Profile()
    {
        var userId = User.Identity.GetUserId();
        var rep = new DocumentRepository<UserDetail>();
        var profile = rep.FindById(userId);

        if (profile == null)
            return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Profile not found");

        return Request.CreateResponse(HttpStatusCode.OK, profile);
    }
}

// Repository
public class DocumentRepository<TEntity> : IDocumentRepository<TEntity> where TEntity : IIdentifiableEntity
{
    private static DocumentClient _client;
    private static string _databaseName;
    private static string _documentsLink;
    private static string _selfLink;

    public DocumentRepository()
    {
        _client = new DocumentClient(new Uri(ConfigurationManager.AppSettings["DocumentDbEndpointUrl"]), ConfigurationManager.AppSettings["DocumentDbAuthKey"]);
        _databaseName = ConfigurationManager.AppSettings["DocumentDbDatabaseName"];
        var _database = ReadOrCreateDatabase();

        var collection = InitialiseCollection(_database.SelfLink, EntityName);
        _documentsLink = collection.DocumentsLink;
        _selfLink = collection.SelfLink;
    }

    // other methods...

    public TEntity FindById(string id)
    {
        return _client.CreateDocumentQuery<TEntity>(_documentsLink).SingleOrDefault(u => u.Id.ToString() == id);
    }
}

It is this FindById method which causes the following issue:

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
Foo.Models.DocumentDbEntities.UserDetail is unsupported. Query must evaluate to IEnumerable.

I don't understand what this error means, or how I fix it. I don't wish to return an IEnumerable or any descendant class as this method will return either 0 or 1 records. It works if I remove the SingleOrDefault clause, and change the return type to an IQueryable, however this is not what I want.

Community
  • 1
  • 1
Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339

2 Answers2

30

SingleOrDefault() is not supported, yet in the LINQ provider.

Change this to .Where(u => u.Id.ToString() == id).AsEnumberable().FirstOrDefault();

ocuenca
  • 38,548
  • 11
  • 89
  • 102
Ryan CrawCour
  • 2,704
  • 1
  • 19
  • 25
  • Hmmm. It worked this morning, however I now need to use `AsEnumerable` before the `Where` other wise I get errors. Nothing has changed otherwise. This seems odd. Current working code: `return Client.CreateDocumentQuery(DocumentsLink).AsEnumerable().Where(u => u.Id.ToString() == id).FirstOrDefault();` This is not ideal due to the performance hit of retrieval of all collections and filtration on the request-side. – Rory McCrossan Nov 04 '14 at 17:17
  • That is strange indeed. You definitely don't want the AsEnumerable before the Where. What errors do you get if you put it the way I have it above? – Ryan CrawCour Nov 11 '14 at 21:43
  • 6
    Is there any documentation on what Linq statements are supported by the Linq Provider? – Madu Alikor Dec 05 '14 at 11:36
  • Worth mentioning is that there is no need changing to FirstOrDefault() after calling AsEnumerable(). SingleOrDefault() still works and should be used if that is the intended behavior. – disco Oct 04 '16 at 07:43
  • 2
    typo in ```AsEnumberable```. It should be ```AsEnumerable```, as a lazy devleoper, i noticed when I copy & paste. – Siva Kandaraj Jul 17 '18 at 05:43
1

I can't say why Ryan's syntax stopped working for you, but you should be able to work around it without the extra performance hit by using a CreateDocumentQuery<>() overload with an explicitly-defined query string instead of using .Where():

string query = string.Format("SELECT * FROM docs WHERE docs.id = \"{0}\"", id);
return _client.CreateDocumentQuery<TEntity>(DocumentsLink, query).AsEnumerable().FirstOrDefault();

You might need to play with the query a little, but something of that form ought to work.

Andrew Davis
  • 11
  • 1
  • Thank you for the answer. I've since found the issue is because I am using Guids, which have issues with LINQ lambdas in DocumentDb at the moment. Selecting by using SQL syntax does indeed fix the issue. Ryans solution definitely did work initially, I can only assume this is a hangup of the issues with LINQ. – Rory McCrossan Nov 06 '14 at 16:51
  • How exactly are you converting the guid to a string? I think DocumentDB plays better with Guid.ToString("N") than the default Guid.ToString(). You're probably right that it's due to the bug in the linq query provider, but it might be worth trying .ToString("N"). – Andrew Davis Nov 07 '14 at 17:09
  • If you are having issues with GUIDs please make sure you are running the latest .NET SDK on NuGet. 0.9.1-preview. There were some fixes in this new version specifically around GUIDs and LINQ – Ryan CrawCour Nov 11 '14 at 21:45