Search code examples
c#nosqlazure-cosmosdbquerying

Querying distinct values from NoSQL(Cosmos DB specifically) cannot convert to model type


Summary

In short I am trying to display all the distinct Ids. I am trying to understand why when I first ran my query (not being distinct) I was able to use them with my model as opposed to now. The error that I am getting is Cannot convert from System.String to AppName.Models.Student.

Example

For example displaying all ids from my collection (with repeated ids in documents) : "SELECT s.ClassId from Students s" worked fine, but when I tried to get the distinct values it couldn't convert them, with the query being SELECT DISTINCT VALUE s.ClassId from Students s"

This is my query to Cosmos DB:

   public List<T> Query(string query)
        {
            FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1, EnableCrossPartitionQuery = true };

            // Execute query via SQL
            IQueryable<T> QueryInSql = Client.CreateDocumentQuery<T>(
                    UriFactory.CreateDocumentCollectionUri(Database, Collection),
                    $"{query}",
                    queryOptions);

            // convert Iqueryable to a list 
            return QueryInSql.ToList();
        }

I changed my model to just include

public class Class
{
    public string ClassId {get; set;}
}

Sample collection with documents

{
   {
       "id":"abc",
       "studentName": "bob",
       "classId":"en" 
   },
   {
       "id":"bcd",
       "studentName": "billy",
       "classId":"sp"},
   {
       "id":"sdf",
       "studentName": "bart",
       "classId":"en"
   }
}

Solution

  • So, the error was in the query I passed in. When adding the VALUE to it, you get back

    ["en","sp"]
    
    

    However what I thought I as getting was

    [{"id": "en"}, {"id": "sp"}]