DocumentDB unsupported query

Question

I try to use dictionary in IQueryable but I received run time error ,I know the problem occur because in real time IQueryable is not familiar with that object , I try to convert IQueryable to IEnumerable , but I have problem with the execution of the query. May someone can give me a hint how to execute that function ?

I have the following code:

Dictionary<String, int> coursesType= new Dictionary<string, int>();      
var userQuery = _client.CreateDocumentQuery<ObjectModel.Student>(uriStudentCollection, options).
                    Where(x =>coursesType.ContainsKey(x.MainCourse) 
                    && !x.Courses.ContainsKey(requestCourse)).OrderBy(x => x.Grade).AsDocumentQuery();
                var feedResponse = await userQuery.ExecuteNextAsync<ObjectModel.Student>();

                foreach (var ad in feedResponse.AsEnumerable())
                {
                    results.Add(ad);
                }

UPDATE STATUS: I STILL NOT RECEIVED ANSWER TO MY QUESTION

***UPDATE : I add example of my doc.

    {
    "id": "a5d7f123-80d5-5094-84fb-08c3bc4ccp972",
    "StudentName": "Philip",
"Courses": {
        "Math": {
            "id": "Math",
            "Grade": "98",
            "Place": "NYC"
}
},
"Rank":"AA"
}

UPDATE NUMBER 3

I write the following query :

 SqlQuerySpec q = new SqlQuerySpec()
            {
                QueryText = "SELECT * FROM c WHERE (CONTAINS(LOWER(c[\"courseName\"]),@text) OR CONTAINS(LOWER(c[\"courseDescription\"]),@text) ) AND (udf.CourseContainsKey(c[\"Courses\"],@courseId)=false)",
                Parameters = new SqlParameterCollection()
                {
                    new SqlParameter("@text", text),
                     new SqlParameter("@courseId", courseId)
                }
            };

When I write the query like that, the query work fine, but IF I add the ORDER BY command to the query I received empty set....

"SELECT * FROM c WHERE (CONTAINS(LOWER(c[\"courseName\"]),@text) OR CONTAINS(LOWER(c[\"courseDescription\"]),@text) ) AND (udf.CourseContainsKey(c[\"Courses\"],@courseId)=false) ORDER BY c[\"courseName\"] ASC"

Thanks

Thanks,

MAK

Answer 1

{"Method 'ContainsKey' is not supported."}

Based on your query, you could use the following code:

var userQuery = _client.CreateDocumentQuery<ObjectModel.Student>(uriStudentCollection, options).
                    Where(x =>coursesType.Keys.Contains(x.MainCourse) 
                    && !x.Courses.Keys.Contains(requestCourse)).OrderBy(x => x.Grade).AsDocumentQuery();

Additionally, if you enable Cross partition query, you would get the following error:

Cross partition query with TOP/ORDER BY or aggregate functions is not supported.

If the filter could not executed on CosmosDB side, I assumed that you need to pull the records from azure side, then filter on your client side. Additionally, here is a similar issue, you could refer to here.

UPDATE:

Sample document:

{
  "id": "1ba6178b-7c22-440a-a4a2-25b4bc636b30",
  "MainCourse": "b",
  "Grade": "B",
  "Courses": {
    "a": "a",
    "b": "b"
  }
}

Query:

SELECT * FROM root WHERE ((root["MainCourse"] IN ("a", "b")) AND (root["Courses"]["a"] != null)) ORDER BY root["Grade"] ASC

Modify your C# code as follows:

!x.Courses.Keys.Contains(requestCourse)

//To

x.Courses[requestCourse]==null

UPDATE2:

For filtering a specific course name not contains within the Courses property, I assumed that you could use User-defined functions, here is the code snippet, you could refer to it:

udf.CourseContainsKey:

function CourseContainsKey (courses,courseName) { 
   if(courses==undefined||courseName==undefined)
   return false;

   return courses.hasOwnProperty(courseName);
}

Test:

UPDATE3:

I have not found any better way to create the query with the UDF, you could follow the code below to create your document query:

var query = $"SELECT* FROM root WHERE (root[\"MainCourse\"] IN({String.Join(",", coursesType.Keys.Select(k=>$"\"{k}\"").ToArray())})) AND udf.CourseContainsKey(root[\"Courses\"],\"{requestCourse}\")=false ORDER BY root[\"Grade\"] ASC";
var items=client.CreateDocumentQuery<CourseSample>(UriFactory.CreateDocumentCollectionUri(DatabaseId, DocumentCollectionId), sqlExpression:query).ToList();