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

Uriel Kluk 21 Reputation points
2023-02-02T22:50:47.77+00:00

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

Azure Digital Twins
Azure Digital Twins
An Azure platform that is used to create digital representations of real-world things, places, business processes, and people.
217 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,142 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Matthijs van der Veer 4,376 Reputation points MVP
    2023-02-03T13:23:50.0266667+00:00

    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.

    0 comments No comments

  2. LeelaRajeshSayana-MSFT 13,456 Reputation points
    2023-02-21T14:11:16.33+00:00

    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.

    0 comments No comments