Bug in DateTime handling of Cosmos DB DocumentClient

Question

This question relates to DocumentClient from Microsoft.Azure.DocumentDB.Core v2.11.2. (Update: the bug also exists in Microsoft.Azure.Cosmos.)

There seems to be a bug in the LINQ Provider for Cosmos DB when the query contains DateTime values with trailing zeros. Consider the following piece of code:

string dateTimeWithTrailingZero = "2000-01-01T00:00:00.1234560Z"; // trailing zero will be truncated by LINQ provider :-(
DateTime datetime = DateTime.Parse(dateTimeWithTrailingZero, CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal);

IQueryable<Dictionary<string, object>> query =
    client.CreateDocumentQuery<Dictionary<string, object>>(collectionUri)
        .Where(x => (DateTime) x["datetime"] <= datetime);

The result of query includes documents where the property datetime is e.g. "2000-01-01T00:00:00.1234567Z" (even though it should not).

The result of query does not include documents where datetime is "2000-01-01T00:00:00.1234560Z" (even though it should).

Is there any way I can use DocumentClient and LINQ to query DateTime properties correctly? (I know that using raw SQL works - for various reasons I must use LINQ/IQueryable.)

Answer 1

A workaround is to use a custom JsonConverter. Unfortunately, with DocumentClient, setting the JsonConverter in the constructor of DocumentClient does not work! The converter is only picked up properly when it is specified in the global (static) JSON.NET default settings (JsonConvert.DefaultSettings).

For the newer CosmosClient, it is necessary and sufficient to set a custom CosmosSerializer in the constructor. To write a custom CosmosSerializer which lets you specify a custom JsonSerializerSettings, you can decompile the internal class CosmosJsonDotNetSerializer and use that as the basis.

The custom JsonConverter looks like this:

/// <summary>
/// <see cref="JsonConverter" /> for Cosmos DB needed as long as the DateTime handling
/// problem has not been fixed. 
/// </summary>
public class CosmosDbDateTimeJsonConverter : IsoDateTimeConverter
{
    public CosmosDbDateTimeJsonConverter()
    {
        this.DateTimeFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK";
    }

    #region Overrides of JsonConverter

    /// <inheritdoc />
    public override bool CanRead => false;

    #endregion
}