DocumentDB how to LINQ query on _TS?

Question

I want to query the update timestamp _ts on documents to get documents that haven't mutated since a certain amount of time.

When I create a select query in the azure portal this works:

SELECT TOP 10 c.id FROM c WHERE c._ts < 6.35909919217878E+17

The wierd number is the Ticks created with a datetime object, see below.

But when I try to create it through LINQ it won't do because you don't have _ts but a Timestamp as a DateTime object. When I try to enter a full DateTime object to compare to the Timestamp it crashes saying it doesn't support it. So I try this:

DocRepo.Get(x => x.Timestamp.Ticks < CloseDate.Ticks);

This results to nothing and when I watch the query executed it has this as a select query:

SELECT * FROM root WHERE root[\"_ts\"][\"Ticks\"] < 6.35909943137688E+17

Is it possible to query on the _ts timestamp or do i have to have an extra updatedAt field to do it, which seems redundant.

Answer 1

You have a couple of problems with your queries. In your first query, you are comparing "Ticks" (one ten millionth of a second - see here) to the _ts value which will most likely return all the documents in your collection because the _ts value is a POSIX (Unix) time measured in seconds see here. They also aren't based on the same epoch. The Unix value starts at midnight 1,1,1970 where the Ticks start at midnight 1,1,0001 Therefore, the _ts value will always be much smaller than the Ticks value (not to mention off by 1,969 years!). You will need to convert your dates to their Unix time value. You could create an Extension method to help you do this:

    public static long ToUnixTime(this DateTime date)
    {
        var epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
        return (long)(date - epoch).TotalSeconds;
    }

As for the Linq statement, you can't (unfortunately) put a DateTime into a Linq query because a DateTime value won't convert to a const (which is the error you are getting). So, in both cases, you can't compare either the _ts value or TimeStamp value very easily.

So what to do? Well, in looking at the DocumentDB SDK, if you look at the definition of TimeStamp you will see the following:

    // Summary:
    //     Gets the last modified timestamp associated with the resource.
    [JsonConverter(typeof(UnixDateTimeConverter))]
    [JsonProperty(PropertyName = "_ts")]
    public virtual DateTime Timestamp { get; internal set; }

So by default, the SDK is converting the _ts value to a DateTime and exposing it through TimeStamp field. There are a couple of things you could do depending on what type your DocRepo is returning. If it is the default Document type, you could create a new class and inherit from the Docment type like this:

public class MyDocument : Document
{
    public long _ts
    {
        get; set;
    }
}

If it is your own custom class, then just add the _ts field to your class. Either way, if the _ts field is present, DocumentDB will populate the field. Then, if you add the ToUnixTime extension method you could compose your Linq query like this:

DocRepo.Get(x => x._ts < CloseDate.ToUnixTime());

It may not be an elegant solution and someone (hopefully) might come up with a better solution, but I have verified that it works against my own DocumentDB collection.

Hope this helps.