How to query the device twin graph using the built-in `LastUpdatedOn` property from the BasicDigitalTwin?

Question

The BasicDigitalTwin holds a built-in property LastUpdatedOn.

As a developer, I want to run a query to find all the stale or fresh twins, example:

SELECT * FROM DIGITALTWINS WHERE LastUpdatedOn > '{yesterday}'

This query is syntactically correct but returns no elements.

https://github.com/Azure/azure-sdk-for-net/issues/33694

Answer 1

Ok, I had to dive into this one. Do you have any twin in your graph with a property LastUpdatedOn or $lastUpdateTime? Because no matter how I try, this does not seem to be the case when using BasicDigitalTwin. The name also should be $lastUpdateTime, because that's the name set on the property:

[JsonPropertyName(DigitalTwinsJsonPropertyNames.MetadataLastUpdateTime)]
public DateTimeOffset? LastUpdatedOn { get; internal set; }

But, when you dig further into where this is set (it's internal, can't be set by us), this happens in the BasicDigitalTwinJsonConverter. But it's not set by looking at the twin itself, it's set by looping through all the twin's properties and copying the $lastUpdateTime from the property's metadata.

My conclusion: this property LastUpdatedOn is only set by the SDK and doesn't exist in the graph. That's why you can't query on the property.

Answer 2

Hi @Uriel Kluk Greetings! I have reviewed the GitHub link you have posted and noticed that a solution has been shared on this issue. I am cross posting the solution here to benefit the other community members facing the issue. The functionality can be achieved by accessing the $metadata.$lastUpdateTime field.

Here is a sample code shared demonstrating how to utilize the field.

var time = DateTime.UtcNow.AddYears(-2);
var query = $"SELECT * FROM DIGITALTWINS WHERE $metadata.$lastUpdateTime > '{time.ToString("o")}'";
Console.WriteLine(query);
var q = dt.QueryAsync<JsonElement>(query);
await foreach(var item in q)
{
    Console.WriteLine(item);
}

I appreciate it if you can confirm the above suggested approach is useful. Thank you for reporting this issue and taking time in responding to the product team to arrive at a solution. I highly appreciate your contribution to the community.