Azure Digital Twins is an amazing platform service which allows you to model virtual representations of a real world space. In Intelligent Spaces we pull in data from BIM sources to create a digital twin and combine that with real world context from IoT sensors to optimize spaces for business use.
Digital Twins has a Query API through which we can query the twin using the Digital Twin Query Language which provides a SQL type language for querying what is a graph database. Microsoft also provides SDKs we can use instead of having to use the Query API directly.
Creating queries
So, what does a typical query look like? Well, lets says we want to query our digital twin to find all rooms in a building which have a maximum occupancy of 10 or greater, or the name contains the word "meeting". But we also want to make sure that the "room" twins we get back are definitely rooms.
SELECT building, level, room
FROM digitaltwins building
JOIN level RELATED building.isPartOf
JOIN room RELATED level.isPartOf
WHERE building.$dtId = 'HeadOffice'
AND IS_OF_MODEL(room, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND (
room.maximumOccupancy >= 10
OR CONTAINS(room.name, 'meeting')
)
Hand writing this means that we're likely to make a mistake. Plus, it means that wherever we use the model information we're going to have to remember to change it in all places we've used it if we update the version.
The Fluent API
To help improve on this situation we decided to create a Fluent API. This way we could have an easy to assembly query builder and compile time safety. But we also wanted to use the POCOs we built to hold the output to bring in the model information. That way, if we update the version later we only have to change it in one place. So, what does this actually look like (including some POCO objects to work with).
public class BuildingModel : BaseGraphInstanceFluentModel
{
public override string TwinModelType => "dtmi:digitaltwins:rec_3_3:core:Building;1";
}
public class LevelModel : BaseGraphInstanceFluentModel
{
public override string TwinModelType => "dtmi:digitaltwins:rec_3_3:core:Level;1";
}
public class RoomModel : BaseGraphInstanceFluentModel
{
public override string TwinModelType => "dtmi:digitaltwins:rec_3_3:core:Room;1";
[JsonPropertyName("name")]
public string Name { get; set; }
[JsonPropertyName("maximumOccupancy")]
public int MaximumOccupancy { get; set; }
}
dt.WithFluent()
.From<BuildingModel>()
.Join<LevelModel, BuildingModel>("isPartOf")
.Join<RoomModel, LevelModel>("isPartOf", ModelVerificationType.IsOfModel)
.WhereId<BuildingModel>("HeadOffice")
.Where(WhereClause.Or(
WhereClause.Comparison<RoomModel, int>(r => r.MaximumOccupancy, 10, ComparisonOperatorType.GreaterThanOrEqual),
WhereClause.Function<RoomModel>(r => r.Name, "meeting", FunctionType.Contains)
))
.Project<BuildingModel, LevelModel, RoomModel>();
This produces the follwing Digital Twin SQL statement.
SELECT buildingmodel, levelmodel, roommodel
FROM digitaltwins buildingmodel
JOIN levelmodel RELATED buildingmodel.isPartOf
JOIN roommodel RELATED levelmodel.isPartOf
WHERE IS_OF_MODEL(roommodel, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND buildingmodel.$dtId IN ['HeadOffice']
AND (
roommodel.maximumOccupancy >= 10
OR CONTAINS(roommodel.name, 'meeting')
)
We have 3 POCOs in this example which inherit from a BaseGraphInstanceFluentModel class. That base class gives us a number of features and some common properties covering things like $dtId, $eTag, and $metadata. It also takes care of generating the model aliases. The JsonPropertyName attributes in the POCO classes are used to map the C# properties to the properties in the digital twins, the FluentAPI uses these to generate the correct property names for the query.
We also included a few helper methods such as WhereId as this is such a common query piece that it didn't make sense to keep writing it out in full every time.
As you can see, the API is making extensive use of generics and property accessors. This way when we say we want to query a property we get that property, and if we say we're doing a comparison on an integer then it's a compile time error to use a non-integer property or value.
The final part of the builder is the Project method. This is essentially the SELECT part of the query. In this case we're selecting all of the models we've used in the builder, but it can also be a subset of them. But we also wanted a little more.
Projecting to a different type
One of the things we often do with the output is to take only certain properties, so we added a version which lets us project an output to a different POCO.
public class ExampleOutput
{
public string BuildingId { get; set; }
public string LevelId { get; set; }
public string RoomId { get; set; }
public string RoomName { get; set; }
public int MaximumOccupancy { get; set; }
}
dt.WithFluent()
.From<BuildingModel>()
.Join<LevelModel, BuildingModel>("isPartOf")
.Join<RoomModel, LevelModel>("isPartOf", ModelVerificationType.IsOfModel)
.WhereId<BuildingModel>("HeadOffice")
.Where(WhereClause.Or(
WhereClause.Comparison<RoomModel, int>(r => r.MaximumOccupancy, 10, ComparisonOperatorType.GreaterThanOrEqual),
WhereClause.Function<RoomModel>(r => r.Name, "meeting", FunctionType.Contains)
))
.Project<BuildingModel, LevelModel, RoomModel, ExampleOutput>((b, l, r) => new ExampleOutput
{
BuildingId = b.ExternalId,
LevelId = l.ExternalId,
RoomId = r.ExternalId,
RoomName = r.Name,
MaximumOccupancy = r.MaximumOccupancy
});
This time we get a different Digital Twin query statement generated which looks like this.
SELECT
buildingmodel.$dtId AS BuildingId
, levelmodel.$dtId AS LevelId
, roommodel.$dtId AS RoomId
, roommodel.name AS RoomName
, roommodel.maximumOccupancy AS MaximumOccupancy
FROM digitaltwins buildingmodel
JOIN levelmodel RELATED buildingmodel.isPartOf
JOIN roommodel RELATED levelmodel.isPartOf
WHERE IS_OF_MODEL(roommodel, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND buildingmodel.$dtId IN ['HeadOffice']
AND (
roommodel.maximumOccupancy >= 10
OR CONTAINS(roommodel.name, 'meeting')
)
This time instead of querying for the full models, we're returning just the properties we want and aliasing them so that we can match them to the new output type. Again, if we try to project out a string field to an integer then it's a compile time error.
Generic types for when not everything is modelled
Last, but not least, we added in the capability to have "generic" types as well. These are models you can use when you want to join from one twin to another where you only care about the id, but you don't have a full POCO define. So if we didn't have a "level" model (weird, I know), then we could something like this instead.
var level = new GenericGraphInstanceModel("level");
dt.WithFluent()
.From<BuildingModel>()
.Join(level, "isPartOf")
.Join<RoomModel>(level, "isPartOf", ModelVerificationType.IsOfModel)
.WhereId<BuildingModel>("HeadOffice")
.Where(WhereClause.Or(
WhereClause.Comparison<RoomModel, int>(r => r.MaximumOccupancy, 10, ComparisonOperatorType.GreaterThanOrEqual),
WhereClause.Function<RoomModel>(r => r.Name, "meeting", FunctionType.Contains)
))
.Project<BuildingModel, RoomModel, GenericGraphInstanceModel>(level);
And from this code we get the following query.
SELECT buildingmodel, roommodel, level
FROM digitaltwins buildingmodel
JOIN level RELATED buildingmodel.isPartOf
JOIN roommodel RELATED level.isPartOf
WHERE IS_OF_MODEL(roommodel, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND buildingmodel.$dtId IN ['HeadOffice']
AND (
roommodel.maximumOccupancy >= 10
OR CONTAINS(roommodel.name, 'meeting')
)
As you can see, it's almost exactly the same as the original. But this gives us the flexibility to use intermediate twins without having to model everything.
The results of all of these queries are Task<IEnumerable<>>, in the case where we're projecting Building, Level, and Room then we get an Task<IEnumerable<(BuildingModel, LevelModel, RoomModel)>> result, giving us typed access to all of the values returned. And naturally Task because the Project method is asynchronous.
Wrapping up
This is a pretty quick wrap up of the functionality. Built into the Fluent API is the capability to use all of the functions and operators available in the language specification, the capability to perform exact matches on the IS_OF_MODEL function.
This has been rolled out in our applications now, giving us more assurance over the queries we're executing, knowing that we haven't accidentally misspelt an alias or function name, and removing lots of code where we're simply creating strings to put into queries. And the API itself is extensively unit tested to give us further confidence still.
Coming up next is looking at bringing relationship information into the API to remove the manual "isPartOf" aspect of the query builder.
We've also made the package available as a NuGet package if you want to try it out yourself.
Top comments (3)
Hello there, and thank you for your sharing!
I am new to DT, and I am trying to create a DT on Azure and interact with it. For now Azure wants me to register as a "pay as you go" user( I don't know why), and I wonder if there is a method that plays with it for free? thank you!
So Azure doesn’t have a free tier, the PaYG subscription is it really. If you don’t use anything you don’t pay.
They do have student accounts where you get a small amount free per month. If you have an MSDN license you get free credit as well. Otherwise I seem to remember that if you sign up you get free credit for the first month.
azure.microsoft.com/en-us/free/sea...
Thank you!