Azure DocumentDb error "Query must evaluate to IEnumerable"

Question

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.

Answer 1

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

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

Answer 2

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.