Bug in DateTime handling of Cosmos DB DocumentClient

user_689923 0
Viewed 853

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.)

azure-cosmosdb-sqlapi azure-cosmosdb c#
2 Answers

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
}
user_6899230

LINQ is removing/truncating the last '0' before 'Z' and hence the final query is working like:

query":"SELECT VALUE root FROM root WHERE (root[\"datetime\"] < \"2000-01-01T00:00:00.123456Z\") "

Thats why this is giving unwanted results. This is strange behavior and I don't think much can be done there.

You make the string as string dateTimeWithTrailingZero = "2000-01-01T00:00:00.1234561Z" and this will work fine.

Another way of doing the same is:

string dateTimeWithTrailingZero = "2000-01-01T00:00:00.1234560Z";
DateTime datetime = DateTime.Parse(dateTimeWithTrailingZero, CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal);

var result= client.CreateDocumentQuery(UriFactory.CreateDocumentCollectionUri("demoDb", "demoDocCollection"),"select * from c where c.datetime < '" + datetime.ToString("o") + "'").ToList();
user_137061310
Related