microsoft/query-builder-for-digital-twins

A C# query builder for Azure Digital Twins query language.

Azure Digital Twins Query Builder

The Azure Digital Twins (ADT) QueryBuilder provides a C# based fluent query builder that helps you build and query an Azure Digital Twin instance in an easy and predictable way with familiar C# based programming constructs. The QueryBuilder factory supports two flows we're identifying as the Typed flow and the Dynamic flow. The Typed flow starts with the method .From<T>() and uses generics constrained by the BasicDigitalTwin class provided by the ADT Client SDK and provided Linq-like predicates to create queries. The Dynamic flow supports two querying methods: .FromTwins() and .FromRelationships() and both support building queries in scenarios where the C# type of the model is not known, thus the syntax requires a bit more verbosity. More often than not, you'll likely find that it to be a better experience to use the Typed query flow over the Dynamic one that has niche use cases. For a quick comparison between the Dynamic and Typed flows read here.

Queries generated follows a grammar of custom SQL-like query language called Azure Digital Twins query language.

---

Prerequisites

There are some assumptions the Typed QueryBuilder is making so that it can be most useful (please refer to ADT SDK for more context):

• Digital Twin Definition Language (DTDL) models are represented in C# classes (C# models).
• C# models inherit from BasicDigitalTwin class supplied by ADT SDK.
• DTDL properties are mapped to C# properties in their corresponding models.
• C# model properties have JsonPropertyName attributes mapping to their DTDL model property names, following the standards of ADT classes.
• Enum values have EnumMember attributes mapping to the DTDL model enum values.
• Models have references to their relationships and the relationship classes extend BasicRelationship class supplied by ADT SDK.
• C# relationship classes set Name property at construction.

  Note: It's important to understand this because of the distinction in behavior between the Typed and Dynamic flows. In the Dynamic flow, you'll need to be cognizant of the json representation of the property name, whereas in the Typed flow, this is taken care of for you.

---

Typed Samples

Here are some samples of how the Typed flow can be used to construct complex queries:

var query = QueryBuilder
                .From<Building>()
                .Join<Building, Device>(b => b.HasDevices)
                .Join<Device, Sensor>(d => d.HasSensors)
                .Where<Building>(b => b.Id, ComparisonOperators.IsEqualTo, "ID")
                .Select<Sensor>();

var stringQuery = query.BuildAdtQuery();

/*
Generated query - Gets you all sensor twins in the specified building

SELECT sensor
FROM DIGITALTWINS building
JOIN device RELATED building.hasDevices
JOIN sensor RELATED device.hasSensors
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND IS_OF_MODEL(device, 'dtmi:microsoft:Device;1')
AND IS_OF_MODEL(sensor, 'dtmi:microsoft:Sensor;1')
AND building.$dtId = 'ID'
*/

var query = QueryBuilder
            .From<Building>()
            .WhereIn<Building>(b => b.Name, new string[] { "name1", "name2" });

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'IS_OF_MODEL' and 'IN' ADT query operator

SELECT building
FROM DIGITALTWINS building
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND building.name IN ['name1','name2']
*/

var query = QueryBuilder
            .From<Building>()
            .Top(5)
            .WhereStartsWith<Building>(b => b.Name, "name");

var stringQuery = query.BuildAdtQuery();


/*
Generated query - uses 'IS_OF_MODEL', 'TOP' and 'STARTSWITH' ADT query operator

SELECT Top(5) building
FROM DIGITALTWINS building
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND STARTSWITH(building.name, 'name')
*/

var query = QueryBuilder
            .From<Building>()
            .Count()
            .WhereStartsWith<Building>(b => b.Name, "name");

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'IS_OF_MODEL', 'TOP' and 'STARTSWITH' ADT query operator

SELECT COUNT()
FROM DIGITALTWINS building
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND STARTSWITH(building.name, 'name')
*/

var query = QueryBuilder
            .From<Building>()
            .Count()
            .WhereContains<Building>(b => b.Name, "ame");

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'IS_OF_MODEL', 'TOP' and 'CONTAINS' ADT query operator

SELECT COUNT()
FROM DIGITALTWINS building
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND CONTAINS(building.name, 'ame')
*/

var query = QueryBuilder
            .From<Building>("bldng")
            .Join<Building, ITSiteFunction>(b => b.HasITSiteFunction, "bldng", "itfunc", "rel")
            .Where<Building>(b => b.Id, ComparisonOperators.IsEqualTo, "ID")
            .Or(query => query
                .Where<Building>("count", ComparisonOperators.IsGreaterThan, 20, alias: "bldng")
                .And(q => q
                    .Where<Building>("count", ComparisonOperators.IsLessThan, 10, alias: "bldng")
                    .WhereEndsWith<BuildingHasITSiteFunctionRelationship>("maxPriority", "word")));

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses AND & OR

SELECT bldng
FROM DIGITALTWINS bldng
JOIN itfunc RELATED bldng.hasITSiteFunction rel
WHERE IS_OF_MODEL(bldng, 'dtmi:microsoft:Space:Building;1')
AND IS_OF_MODEL(itfunc, 'dtmi:microsoft:ITSiteFunction;1')
AND bldng.$dtId = 'ID' AND (bldng.count > 20
OR (bldng.count < 10 AND ENDSWITH(rel.maxPriority, 'word')))

var query = QueryBuilder
                    .From<Space>()
                    .WhereStartsWith<Space>(s => s.Name, "word")
                    .Or(query => query
                        .WhereIsOfModel<Space, Building>()
                        .WhereIsOfModel<Space, Floor>())
                    .Not(query => query
                        .WhereIsOfModel<Space, ConferenceRoom>());
/* 
Generated query - uses IS_OF_MODEL & OR
SELECT space FROM DIGITALTWINS space 
WHERE IS_OF_MODEL(space, 'dtmi:microsoft:Space;1') 
AND STARTSWITH(space.name, 'word') 
AND (IS_OF_MODEL(space, 'dtmi:microsoft:Space:Building;1') OR IS_OF_MODEL(space, 'dtmi:microsoft:Space:Floor;1'))
AND NOT IS_OF_MODEL(space, 'dtmi:microsoft:Space:ConferenceRoom;1')
*/

var query = QueryBuilder
                .From<BasicDigitalTwin>();
/* 
Generated query - select all twins
SELECT basicdigitaltwin 
FROM DIGITALTWINS basicdigitaltwin
*/

---

Methods

Methods supported in the Typed flow.

• QueryBuilder
  • From<TModel>()
    • Where<TModel>(propertySelector, operation, value)
    • Where<TModel>(propertyName, operation, value)
    • Where<TModel>(propertySelector, scalarOperator)
    • Where<TModel>(propertyName, scalarOperator)
    • WhereStartsWith<TModel>(propertySelector, value)
    • WhereStartsWith<TModel>(propertyName, value)
    • WhereEndsWith<TModel>(propertySelector, value)
    • WhereEndsWith<TModel>(propertyName, value)
    • WhereContains<TModel>(propertySelector, value)
    • WhereContains<TModel>(propertyName, value)
    • WhereIn<TModel>(propertySelector, values)
    • WhereIn<TModel>(propertyName, values)
    • WhereNotIn<TModel>(propertySelector, values)
    • WhereNotIn<TModel>(propertyName, values)
    • WhereIsOfModel<TBase,TDerived>()
    • Join<TJoinFrom,TJoinWith>(relationship)
    • Select<TSelect>()
    • Top(numberOfRecords)
    • Count()
    • And(conditions)
    • Or(conditions)
    • Not(conditions)
    • BuildAdtQuery()
  • CountAllDigitalTwins()
    • BuildAdtQuery()

---

Parameters

Name	Type	Description
TModel, TJoinFrom, TJoinWith, TSelect, TBase, TDerived	A Type that's a sub-type of Azure.DigitalTwins.Core.BasicDigitalTwin.	The C# model of the twin.
propertySelector	Expression<Func<TModel, object>>	Expression to select any property of TModel type.
propertyName	string	JSON property name of TModel type.
operation	ComparisonOperators	Operator for the condition.
scalarOperator	ScalarOperators	ADT scalar function.
value	object	Value against which the where condition is applied.
values	string[]	values against which the where condition is applied.
conditions	Action<AdtFilteredQuery<TQuery>>	An action used to apply a chain of filters to the query.

---

Operators

ComparisonOperators

• IsEqualTo
• IsGreaterThan
• IsGreaterThanOrEqualTo
• IsLessThan
• IsLessThanOrEqualTo
• NotEqualTo

ScalarOperators

• IS_BOOL
• IS_DEFINED
• IS_NULL
• IS_NUMBER
• IS_OBJECT
• IS_PRIMITIVE
• IS_STRING

---

Typed Samples Using LINQ Expressions

Here are some samples of how the Typed flow can be used to construct complex queries:

var query = QueryBuilder
                .From<Building>()
                .Join<Building, Device>(b => b.HasDevices)
                .Join<Device, Sensor>(d => d.HasSensors)
                .Where<Building>(b => b.Id == "ID")
                .Select<Sensor>();

var stringQuery = query.BuildAdtQuery();

/*
Generated query - Gets you all sensor twins in the specified building

SELECT sensor
FROM DIGITALTWINS building
JOIN device RELATED building.hasDevices
JOIN sensor RELATED device.hasSensors
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND IS_OF_MODEL(device, 'dtmi:microsoft:Device;1')
AND IS_OF_MODEL(sensor, 'dtmi:microsoft:Sensor;1')
AND building.$dtId = 'ID'
*/

var query = QueryBuilder
            .From<Building>()
            .Top(5)
            .Where<Building>(b => b.Name.StartsWith("name"));

var stringQuery = query.BuildAdtQuery();


/*
Generated query - uses 'IS_OF_MODEL', 'TOP' and 'STARTSWITH' ADT query operator

SELECT Top(5) building
FROM DIGITALTWINS building
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND STARTSWITH(building.name, 'name')
*/

var query = QueryBuilder
            .From<Building>()
            .Count()
            .Where<Building>(b => b.Name.Contains("ame"));

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'IS_OF_MODEL', 'TOP' and 'CONTAINS' ADT query operator

SELECT COUNT()
FROM DIGITALTWINS building
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND CONTAINS(building.name, 'ame')
*/

var query = QueryBuilder
            .From<Building>()
            .Join<Building, Device>(building => building.HasDevices)
            .Join<Device, Sensor>(device => device.HasSensors)
            .Where<Building>(building => (building.Id != null && building.Id == "name"))
            .Where<Device>(device => (device.Name != null && device.Name == "name"))
            .Select<Sensor>();

var stringQuery = query.BuildAdtQuery();

/*
Generated query - Gets you all sensor twins in the specified building and device

SELECT sensor
FROM DIGITALTWINS building
JOIN device RELATED building.hasDevices spacehasdevicesrelationship
JOIN sensor RELATED device.hasSensors devicehassensorsrelationship
WHERE IS_OF_MODEL(building, 'dtmi:microsoft:Space:Building;1')
AND IS_OF_MODEL(device, 'dtmi:microsoft:Device;1')
AND IS_OF_MODEL(sensor, 'dtmi:microsoft:Sensor;1')
AND (NOT IS_NULL(building.$dtId) AND building.$dtId = 'name'))
AND (NOT IS_NULL(device.name) AND device.name = 'name'))
*/

---

Methods

Methods supported in the Typed flow.

• QueryBuilder
  • From<TModel>()
    • Where<TModel>(expression)
    • Join<TJoinFrom,TJoinWith>(relationship)
    • Select<TSelect>()
    • Top(numberOfRecords)
    • Count()
    • BuildAdtQuery()
  • CountAllDigitalTwins()
    • BuildAdtQuery()

---

Operators

ComparisonOperators

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

ScalarOperators

• IS_BOOL
• IS_NULL
• IS_NUMBER
• IS_OBJECT
• IS_STRING
• NOT

LogicalOperators

• AndAlso
• OrElse

---

Dynamic Samples

Here are some samples of how the Dynamic flow can be used to construct complex queries:

Note: In comparing these nearly identical samples to the Typed flow, you'll want to take notice that while the Typed flow automatically applies the IS_OF_MODEL scalar function to filter for models based on the types used in the methods' generic parameters, the Dynamic flow does not, as it is not aware of any types. To filter for specific models, the Dynamic flow supports .Where(t => t.IsOfModel("dtmi:sometwinmodel;1"))

var query = QueryBuilder
                .FromTwins()
                .Join(b => b
                    .With("device")
                    .RelatedBy("hasDevices")
                    .Join(d => d
                        .With("sensor")
                        .RelatedBy("hasSensors")))
                .Where(b => b.TwinProperty("$dtId").IsEqualTo("ID"))
                .Select("sensor");

var stringQuery = query.BuildAdtQuery();

/*
Generated query - Gets you all sensor twins based on the root twin with the $dtId of 'ID'

SELECT sensor
FROM DIGITALTWINS twin
JOIN device RELATED twin.hasDevices
JOIN sensor RELATED device.hasSensors
WHERE twin.$dtId = 'ID'
*/

var query = QueryBuilder
            .FromTwins()
            .Where(b => b
                .TwinProperty("name")
                .IsIn(new string[] { "name1", "name2" }));

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'IN' ADT query operator

SELECT twin
FROM DIGITALTWINS twin
WHERE twin.name IN ['name1','name2']
*/

var query = QueryBuilder
            .FromTwins()
            .Top(5)
            .Where(b => b
                .TwinProperty("name")
                .StartsWith("name"));

var stringQuery = query.BuildAdtQuery();


/*
Generated query - uses 'TOP' and 'STARTSWITH' ADT query operator

SELECT Top(5) twin
FROM DIGITALTWINS twin
WHERE STARTSWITH(twin.name, 'name')
*/

var query = QueryBuilder
            .FromTwins()
            .Count()
            .Where(b => b
                .TwinProperty("name")
                .StartsWith("name"));

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'COUNT' and 'STARTSWITH' ADT query operator

SELECT COUNT()
FROM DIGITALTWINS twin
WHERE STARTSWITH(twin.name, 'name')
*/

var query = QueryBuilder
                .FromTwins()
                .Top(5)
                .Where(b => b
                    .TwinProperty("name")
                    .Contains("ame"));

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'TOP' and 'CONTAINS' ADT query operator

SELECT TOP(5) twin
FROM DIGITALTWINS twin
WHERE CONTAINS(twin.name, 'ame')
*/

var query = QueryBuilder
            .FromTwins("bldng")
            .Join(b => b
                .With("itfunc")
                .RelatedBy("hasITSiteFunction")
                .As("rel"))
            .Where(b => b
                .TwinProperty("$dtId")
                .IsEqualTo("ID")
                .And()
                .Precedence(p => p
                    .TwinProperty("count")
                    .IsGreaterThan(20)
                    .Or()
                    .Precedence(p => p
                        .TwinProperty("count")
                        .IsLessThan(10)
                        .And()
                        .RelationshipProperty("maxPriority", "rel")
                        .EndsWith("word"))));

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses AND & OR

SELECT bldng
FROM DIGITALTWINS bldng
JOIN itfunc RELATED bldng.hasITSiteFunction rel
WHERE bldng.$dtId = 'ID' AND (bldng.count > 20
OR (bldng.count < 10 AND ENDSWITH(rel.maxPriority, 'word')))

var query = QueryBuilder
            .FromTwins()
            .Where("startswith(twin.name, 'name1')");

var stringQuery = query.BuildAdtQuery();

/*
Generated query - uses 'STARTSWITH' ADT query operator

SELECT twin
FROM DIGITALTWINS twin
WHERE STARTSWITH(twin.name, 'name1')
*/

---

Methods

Methods supported in the Dynamic flow.

• QueryBuilder
  • FromTwins()
    • Where(filter)
    • Where(whereLogic)
    • Join(joinLogic)
    • Join(joinAndWhereLogic)
    • Select(aliases)
    • Top(numberOfRecords)
    • Count()
    • BuildAdtQuery()
  • FromRelationships()
    • Where(filter)
    • Where(whereLogic)
    • Select(aliases)
    • Top(numberOfRecords)
    • Count()
    • BuildAdtQuery()
  • CountAllDigitalTwins()
    • BuildAdtQuery()

Parameters

Name	Type	Description
TWhereStatement, CompoundWhereStatement, JoinWithStatement, JoinFinalStatement		These types are all part of the Where and Join methods' inner fluent syntax that aid in building and enforcing particular query semantics.
whereLogic	Func<TWhereStatement, CompoundWhereStatement>	Func to include WHERE logic in the query.
filter	string	A string filter query that can contain one or multiple conditions
joinLogic	Func<JoinWithStatement<TWhereStatement>, JoinFinalStatement<TWhereStatement>>	Func to include JOIN logic in the query.
joinAndWhereLogic	Func<JoinWithStatement<TWhereStatement>, CompoundWhereStatement<TWhereStatement>>	Func to include JOIN logic in the query with additional WHERE logic scoped to the JOIN.
aliases	params object[]	This can be one or many aliases to override and apply to the SELECT clause of the query.
numberOfRecords	int	The number of records to include in the a TOP query.

---

Operators

The operators used in the Dynamic flow are expressed as methods, but support remains the same for each.

---

Typed vs Dynamic Comparison

Feature	Typed	Dynamic
Query Twins Collection	✅	✅
Query Relationships Collection	❌	✅
Select Twin Properties	❌	✅
Select Relationship Properties	❌	✅
Use Property Selectors	✅	❌
Use Relationship Selectors	✅	❌

Note: While not a comparison between the Typed or Dynamic flows, it's worth noting that Joins cannot be used when querying the Relationships collection.

---

Change history

See CHANGELOG for change history of each version.

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

Security

For guidance on reporing security issues, please refer to the security section.

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.

query-builder-for-digital-twins's Issues

Configure/Setup CodeQL

We should add CodeQL to our CI for code quality assurance using the provided docs

[Bug]: New releases failing

Describe the bug

New releases are failing
On testing a new release it fails in the Sign packages step fails in the release workflow indicating that the workkflow needs to be upgraded

Which version of the tool was used?

No response

Provide the full query used. i.e. QueryBuilder.From<Space>()

No response

Steps To Reproduce

No response

[Bug] Dynamic flow: Join seems not possible between parent and 2 sibling twins

Describe the bug

Getting and error "Cannot use the alias: , because it's already assigned!" when trying to make a join between parent and its 2 sibling

Which version of the tool was used?

1.4.0

Provide the full query used. i.e. QueryBuilder.From<Space>()

var query = QueryBuilder .FromTwins("Room") .Join(a => a .With("Airconditioner") .RelatedBy("hasAirconditioner")) .Join(r => r .With("Refrigerator") .RelatedBy("hasRefrigerator")).Select("Airconditioner","Refrigerator");

Steps To Reproduce

Assume there are 2 twins, Room, Airconditioner and Refrigerator. Both Airconditioner and Refrigerator inherits a common model Appliances with some common properties.

Now try to run below query to get both Airconditioner and Refrigerator

 var query = QueryBuilder
                .FromTwins("Room")
                .Join(a => a
                    .With("Airconditioner")
                    .RelatedBy("hasAirconditioner"))
                .Join(r => r
                        .With("Refrigerator")
                        .RelatedBy("hasRefrigerator")).Select("Airconditioner","Refrigerator");

var stringQuery = query.BuildAdtQuery();

It returns an error "Cannot use the alias: Airconditioner, because it's already assigned!"

Missing README.MD in NuGet Gallery

This package in the NuGet Gallery is missing a README.MD

Link to resolve this issue: https://docs.microsoft.com/en-us/nuget/reference/msbuild-targets#packagereadmefile

[Feature]: Support SELECTing Properties and Relationships in Typed Query Builder

Is your feature request related to a problem? Please describe.

No.

Describe the feature

Remove type-constraint on SELECT workflow to allow users to select relationships and properties.

Additional context, alternatives considered, etc.

Typed Query Builder currently supports SELECTing (returning) digital twins only. However, the query language supports SELECTing relationships and properties as well. This behavior exists in the Dynamic Query Builder and should ideally be supported in the Typed Query Builder as well.

[Feature]: Support Expressions

Is your feature request related to a problem? Please describe.

No.

Describe the feature

Currently, Where conditions are built using the following syntax: .Where<Building>(b => b.Name, ComparisonOperators.IsEqualTo, "somebuildingname"). What would be nice is to support an expression like so: .Where<Building>(b => b.Name == "somebuildingname")

Additional context, alternatives considered, etc.

No response

[Feature]: Support multiple type with Select method by specifying properties to be selected

Is your feature request related to a problem? Please describe.

Yes, I need to select twin's property from 2 different type

Describe the feature

As there are only 2 overloads of Select method and both does not provide a way to select twin's property from multiple types, It may be required when they are joined using join method.

Additional context, alternatives considered, etc.

If I understood correctly then consider below query.

var query = QueryBuilder
                .From<Building>()
                .Join<Building, Device>(b => b.HasDevices)
                .Join<Device, Sensor>(d => d.HasSensors)
                .Where<Building>(b => b.Id, ComparisonOperators.IsEqualTo, "ID")
                .Select<Sensor>();

It only select Sensor.
What if one need Building and Device also. More specifically if specific properties from these type are required to be in select clause (like Device.Id, Building.Location, Sensor.Name) then it seems not possible.

Update to use beta/pre-release flow

Now that we have a solid stable release process, we need to incorporate a flow that allows us to release beta/pre-release packages.

Related to/blocked by: Support for pushing beta/pre-release packages

Configure/Setup Dependabot

We should follow the GitHub docs on onboarding Dependabot to our repo.