How to query DateTimeOffset with DocumentDb

Question

Assume I insert records into Azure DocumentDb of the following model:

public class Message
{
    [JsonProperty(PropertyName = "tid")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "start")]
    public DateTimeOffset StartAt { get; set; }
}

They both automatically get stored as strings. I want to be able to query StartAt, so I added a RngeIndex on it. I used the Azure Portal to verify that the index works properly.

With that out of the way, I load up the DocumentDb .NET SDK and try the following query:

var since = DateTimeOffset.UtcNow.Subtract(duration);
return Client.CreateDocumentQuery<T>(Collection.DocumentsLink)
    .Where(m => m.AtStart > since)
    .AsEnumerable();

But I get the error

[DocumentQueryException: Constant of type 'System.DateTimeOffset' is not supported.]
   Microsoft.Azure.Documents.Linq.ExpressionToSql.VisitConstant(ConstantExpression inputExpression) +3204
   Microsoft.Azure.Documents.Linq.ExpressionToSql.VisitBinary(BinaryExpression inputExpression, TranslationContext context) +364
   Microsoft.Azure.Documents.Linq.ExpressionToSql.VisitBinary(BinaryExpression inputExpression, TranslationContext context) +349
   Microsoft.Azure.Documents.Linq.ExpressionToSql.VisitScalarLambda(Expression inputExpression, TranslationContext context) +230
   Microsoft.Azure.Documents.Linq.ExpressionToSql.VisitWhere(ReadOnlyCollection`1 arguments, TranslationContext context) +55
   Microsoft.Azure.Documents.Linq.ExpressionToSql.VisitMethodCall(MethodCallExpression inputExpression, TranslationContext context) +799
   Microsoft.Azure.Documents.Linq.ExpressionToSql.Translate(Expression inputExpression, TranslationContext context) +91
   Microsoft.Azure.Documents.Linq.ExpressionToSql.TranslateQuery(Expression inputExpression) +46
   Microsoft.Azure.Documents.Linq.SQLTranslator.TranslateQuery(Expression inputExpression) +20
   Microsoft.Azure.Documents.Linq.<ExecuteAllAsync>d__7.MoveNext() +177
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +179
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +66
   System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) +30
   Microsoft.Azure.Documents.Linq.<GetEnumeratorTAsync>d__10.MoveNext() +632

Is there a way to execute a type-safe query without changing the underlying model?

Andrew Liu Andrew Liu · Accepted Answer · 2015-11-13T19:21:42

You can perform range-query on dates, but not as a "type-safe" query.

This is because DocumentDB does not have a date time data type. Instead, DocumentDB adheres strictly to the JSON spec for supported data types (String, Number, Boolean, Array, Object and Null). As a result, you get an exception, Constant of type 'System.DateTimeOffset' is not supported, when trying to query directly on DateTimeOffset using the LINQ Provider.

By default, the DocumentDB Client SDK serializes datetime object properties as an ISO 8601 formatted string which looks something like this: 2014-09-15T23:14:25.7251173Z. Adding a range index on strings would allow you to perform string range queries on the date. You could serialize var since = DateTimeOffset.UtcNow.Subtract(duration); as an ISO 8601 string to perform the query in your code snippet.

Check out this blog post for a more in-depth discussion on working with dates. Please note that the blog is slightly out-of-date, as support for string range index and queries have been added since the time the blog post was initially authored.

How to query DateTimeOffset with DocumentDb

1 Answers