Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[QUERY] is it possible to query the DIGITALTWINS using LastUpdatedOn in the WHERE statement? #33694

Closed
uriel-kluk opened this issue Jan 26, 2023 · 13 comments
Closed

[QUERY] is it possible to query the DIGITALTWINS using LastUpdatedOn in the WHERE statement? #33694

uriel-kluk opened this issue Jan 26, 2023 · 13 comments
Assignees
@navba-MSFT
Labels
Client This issue points to a problem in the data-plane of the library. customer-reported Issues that are reported by GitHub users external to the Azure organization. Digital Twins question The issue doesn't require a change to the product in order to be resolved. Most issues start as that Service Attention This issue is responsible by Azure service team.

Comments

@uriel-kluk
Copy link

uriel-kluk commented Jan 26, 2023 •
edited

Library name and version

Azure.DigitalTwins.Core

Query/Question

Hello ADT Team,

I would like to run a query to discover fresh or stale twins.

The BasicDigitalTwin class contains the field LastUpdatedOn.

I am able to run a query and filter the result set:

        public async Task QueryNewTwins()
        {
            List<string> twinList = new List<string>();

            AsyncPageable<BasicDigitalTwin> queryResult = 
_client.QueryAsync<BasicDigitalTwin>("SELECT * FROM DIGITALTWINS");
            await foreach (BasicDigitalTwin item in queryResult)
            {
                if (item.LastUpdatedOn?.AddDays(1) > DateTime.Now)
                    Debug.WriteLine(item.Id);
            }
        }

But, I would like the internal database to filter for me without having to add a custom capability.
SELECT * FROM DIGITALTWINS WHERE LastUpdatedOn > '{yesterday}'

Is there a simple way to do this?

Best regards,
UK

Environment

No response
@ghost ghost added needs-triage This is a new issue that needs to be triaged to the appropriate team. customer-reported Issues that are reported by GitHub users external to the Azure organization. question The issue doesn't require a change to the product in order to be resolved. Most issues start as that labels Jan 26, 2023
@jsquire jsquire added Client This issue points to a problem in the data-plane of the library. needs-team-attention This issue needs attention from Azure service team or SDK team Digital Twins CXP Attention labels Jan 26, 2023
@ghost ghost removed the needs-triage This is a new issue that needs to be triaged to the appropriate team. label Jan 26, 2023
@ghost
Copy link

ghost commented Jan 26, 2023

Thank you for your feedback. This has been routed to the support team for assistance.

@navba-MSFT navba-MSFT self-assigned this Jan 27, 2023
@navba-MSFT
Copy link
Contributor

@uriel-kluk Thanks for reaching out to us and reporting this issue. Could you please let me know if the below sample helps ?

using Azure.DigitalTwins.Core;
using System;

class Program
{
    static async Task Main()
    {
        // Create a new DigitalTwinsClient instance
        var connectionString = "<Your connection string>";
        var client = new DigitalTwinsClient(connectionString);

        // Define the query
        var query = "SELECT * FROM digitaltwins WHERE LastUpdatedOn > @updatedOn";
        var parameters = new Dictionary<string, object>
        {
            { "@updatedOn", DateTime.Now.AddDays(-30) }
        };

        // Execute the query
        var queryResult = await client.QueryAsync(query, parameters);

        // Print the results
        foreach (var result in queryResult)
        {
            Console.WriteLine(result);
        }
    }
}

This sample code uses the Azure.DigitalTwins.Core SDK to query for digital twins that have been updated in the last 30 days (change the value of AddDays as per your requirement). The DigitalTwinsClient.QueryAsync method is used to execute the query, passing in the query string and the parameters. The results are then printed to the console.

Make sure to replace with your actual connection string.

@navba-MSFT navba-MSFT added needs-author-feedback More information is needed from author to address the issue. and removed needs-team-attention This issue needs attention from Azure service team or SDK team labels Jan 27, 2023
@uriel-kluk
Copy link
Author

uriel-kluk commented Jan 27, 2023 via email

@ghost ghost added needs-team-attention This issue needs attention from Azure service team or SDK team and removed needs-author-feedback More information is needed from author to address the issue. labels Jan 27, 2023
@navba-MSFT
Copy link
Contributor

@uriel-kluk Thanks for your reply. I am sharing this sample code link which has the sample queries and walkthrough information to use with the Azure Digital Twins query plugin.

Also here is the Comparison operators that are supported and can be used in Digital Twins Select clause:

=, !=: Used to compare equality of expressions.
<, >: Used for ordered comparison of expressions.
<=, >=: Used for ordered comparison of expressions, including equality.

Hope this helps.

@navba-MSFT navba-MSFT added needs-author-feedback More information is needed from author to address the issue. and removed needs-team-attention This issue needs attention from Azure service team or SDK team labels Jan 30, 2023
@uriel-kluk
Copy link
Author

uriel-kluk commented Jan 30, 2023 via email

@ghost ghost added needs-team-attention This issue needs attention from Azure service team or SDK team and removed needs-author-feedback More information is needed from author to address the issue. labels Jan 30, 2023
@navba-MSFT
Copy link
Contributor

@uriel-kluk Thanks for getting back and sharing your sample code snippet. Regarding the issue about the query returning the empty collection, would require some troubleshooting. Could you open a support ticket for that ?

You could leverage the below sample code which creates a DigitalTwinsClient instance and uses the GetDigitalTwinsAsync method to get a collection of digital twins. The digital twins are then sorted in descending order based on the LastUpdatedOn property using the OrderByDescending method.

using System;
using System.Linq;
using Microsoft.Azure.DigitalTwins;

// ...

async Task GetAndSortDigitalTwinsAsync()
{
    var client = new DigitalTwinsClient(new Uri("https://<your-dt-instance>.<region>.azuresmartspaces.net"), new AzureADCredential("<tenant-id>", "<client-id>", "<client-secret>"));
    var digitalTwins = await client.GetDigitalTwinsAsync();
    var sortedDigitalTwins = digitalTwins.OrderByDescending(dt => dt.LastUpdatedOn);
    
    foreach (var digitalTwin in sortedDigitalTwins)
    {
        Console.WriteLine($"Id: {digitalTwin.Id}");
        Console.WriteLine($"LastUpdatedOn: {digitalTwin.LastUpdatedOn}");
    }
}

Hope this helps.

@navba-MSFT navba-MSFT added needs-author-feedback More information is needed from author to address the issue. and removed needs-team-attention This issue needs attention from Azure service team or SDK team labels Jan 31, 2023
@ghost ghost added the no-recent-activity There has been no recent activity on this issue. label Feb 7, 2023
@ghost
Copy link

ghost commented Feb 7, 2023

Hi, we're sending this friendly reminder because we haven't heard back from you in 7 days. We need more information about this issue to help address it. Please be sure to give us your input. If we don't hear back from you within 14 days of this comment the issue will be automatically closed. Thank you!

@uriel-kluk
Copy link
Author

The query using the built-in LastUpdatedOn is not supported. We can close the issue, but I do suggest considering it as a new feature.

@ghost ghost added needs-team-attention This issue needs attention from Azure service team or SDK team and removed needs-author-feedback More information is needed from author to address the issue. labels Feb 7, 2023
@ghost ghost removed the no-recent-activity There has been no recent activity on this issue. label Feb 7, 2023
@navba-MSFT navba-MSFT added feature-request This issue requires a new behavior in the product in order be resolved. and removed question The issue doesn't require a change to the product in order to be resolved. Most issues start as that CXP Attention labels Feb 7, 2023
@navba-MSFT navba-MSFT removed their assignment Feb 7, 2023
@navba-MSFT navba-MSFT added the Service Attention This issue is responsible by Azure service team. label Feb 7, 2023
@ghost
Copy link

ghost commented Feb 7, 2023

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @johngallardo, @efriesner, @abhinav-gha, @Aashish93-stack, @sjiherzig, @Satya-Kolluri.

Issue Details

Library name and version

Azure.DigitalTwins.Core

Query/Question

Hello ADT Team,

I would like to run a query to discover fresh or stale twins.

The BasicDigitalTwin class contains the field LastUpdatedOn.

I am able to run a query and filter the result set:

        public async Task QueryNewTwins()
        {
            List<string> twinList = new List<string>();

            AsyncPageable<BasicDigitalTwin> queryResult = 
_client.QueryAsync<BasicDigitalTwin>("SELECT * FROM DIGITALTWINS");
            await foreach (BasicDigitalTwin item in queryResult)
            {
                if (item.LastUpdatedOn?.AddDays(1) > DateTime.Now)
                    Debug.WriteLine(item.Id);
            }
        }

But, I would like the internal database to filter for me without having to add a custom capability.
SELECT * FROM DIGITALTWINS WHERE LastUpdatedOn > '{yesterday}'

Is there a simple way to do this?

Best regards,
UK

Environment

No response

Author: uriel-kluk
Assignees: -
Labels:

Service Attention, Client, customer-reported, feature-request, needs-team-attention, Digital Twins
Milestone: -

@navba-MSFT
Copy link
Contributor

@johngallardo, @efriesner, @abhinav-gha, @Aashish93-stack, @sjiherzig, @Satya-Kolluri Could you please look into this request ? Thanks in advance

@johngallardo
Copy link
Member

johngallardo commented Feb 7, 2023 via email •
edited

@uriel-kluk
Copy link
Author

uriel-kluk commented Feb 7, 2023 via email

@navba-MSFT
Copy link
Contributor

navba-MSFT commented Feb 7, 2023 •
edited

@uriel-kluk Thanks for the update.

@navba-MSFT navba-MSFT self-assigned this Feb 20, 2023
@navba-MSFT navba-MSFT removed feature-request This issue requires a new behavior in the product in order be resolved. needs-team-attention This issue needs attention from Azure service team or SDK team labels Feb 20, 2023
@navba-MSFT navba-MSFT added the question The issue doesn't require a change to the product in order to be resolved. Most issues start as that label Mar 3, 2023
@github-actions github-actions bot locked and limited conversation to collaborators Jun 1, 2023
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees

@navba-MSFT navba-MSFT

Labels
Client This issue points to a problem in the data-plane of the library. customer-reported Issues that are reported by GitHub users external to the Azure organization. Digital Twins question The issue doesn't require a change to the product in order to be resolved. Most issues start as that Service Attention This issue is responsible by Azure service team.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@jsquire @johngallardo @uriel-kluk @navba-MSFT