1

I have a cosmos db collection. I need to query all documents and return them in order of creation date. Creation date is a defined field but for historical reason it is in string format as MM/dd/yyyy. For example: 02/09/2019. If I just order by this string, the result is chaos.

I am using linq lambda to write my query in webapi. I have tried to parse the string and try to convert the string. Both returned "method not supported".

Here is my query:

var query = Client.CreateDocumentQuery<MyModel>(CollectionLink)
                            .Where(f => f.ModelType == typeof(MyModel).Name.ToLower() && f.Language == getMyModelsRequestModel.Language )
                            .OrderByDescending(f => f.CreationDate)
                            .AsDocumentQuery();

Appreciate for any advice. Thanks. It will be huge effort to go back and modify the format of the field (which affects many other things). I wish to avoid it if possible.

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
Chen Wang
  • 33
  • 7
  • I tried to construct a new string to yyyyMMdd format so I can simply compare the string. I got "unsupported ORDER BY clause" error. Exact error as described in https://vincentlauzon.com/2017/11/06/a-few-cosmosdb-query-limitations/. – Chen Wang May 05 '19 at 15:32
  • Hi,does my answer helps you? If so,you could mark it for answer. – Jay Gong May 15 '19 at 05:50

1 Answers1

1

Chen Wang.Since the order by does not support derived values or sub query(link),so you need to sort the derived values by yourself i think.

You could construct the MM/dd/yyyy to yyyymmdd by UDF in cosmos db.

udf:

function getValue(datetime){
    return datetime.substring(6,10)+datetime.substring(0,2)+datetime.substring(3,5);
}

sql: SELECT udf.getValue(c.time) as time from c

enter image description here

Then you could sort the array by property value of class in c# code.Please follow this case:How to sort an array containing class objects by a property value of a class instance?

Jay Gong
  • 23,163
  • 2
  • 27
  • 32
  • Thank you Jay. I ended up re-formatting the string just as you described. No other way around in my knowledge. – Chen Wang May 16 '19 at 03:13