Back in June of this year I posted about the Fluent API we built at Intelligent Spaces for Azure Digital Twins.
Since then we've been busy making extensive use of the API ourselves and looking at making improvements. This last week we've finished implementing one of the larger improvements to the API since introducing the Fluent API itself. And that's bringing in the MATCH clause.
The MATCH clause
Most of the time when writing queries against Azure Digital Twin you'll find yourself using the JOIN clause, which is pretty easy to use. You start with a twin on the left, and say you want to connect another twin on the right based on a relationship type, such as.
SELECT
building, level
FROM
digitaltwins building
JOIN level RELATED building.isPartOf
WHERE
building.$dtId = 'myBuilding'
These work great but have a few limitations. You can't join more than 5 deep, there are no OUTER JOIN semantics, and you can only query on the twins, so if there's something about the relationship (other than it's type) that you want to filter on, you're going to have to do this in the client code.
So, lets say we have a graph where we want to get from an owning organisation, down to the assets inside of rooms, but also want to get to the assets Building Information Modelling (BIM) data. In our ontology we have the following.
There's a couple of things here which are going to stop us from using the JOIN clause, the primary one being that the query goes 6 deep and we have a limit of 5. But we can write this using a MATCH clause pretty easily as follows.
SELECT
asset, bimproperties
FROM
digitaltwins
MATCH
(company)-[:owns]->(building)-[:isPartOf]->(level)-[:isPartOf]->(room)-[:servedBy]->(asset)<-[:hasBimProperties]-(bimproperties)
WHERE
company.$dtId = 'myOrg'
I say easily, but you can see that we've got arrows in there, relationships prefixed with colons, and it gets even more fun when we introduce hops -[query_variable:relationship_name*min_hops..max_hops]-.
This gets complex pretty quickly and becomes easy to really mess things up. Miss that : and suddenly our relationship name becomes a query variable, and our join goes wrong.
The Fluent API
The MATCH clause provides a really powerful way to navigate the digital twin graph, but it's syntax is not natural SQL and, as we've just seen, it's pretty easy to get it wrong. This was one of the primary reasons for why we wanted bring it in to our Fluent API. To do this we introduced a new method called Match, and it brings in all of the functionality of the MATCH clause. Importantly the API also performs validation at run time on what has been specified, as you can't mix the JOIN clause with the MATCH clause, so it guards against this.
We have also introduced the BaseGraphInstanceFluentRelationship type which can be used in one of 2 ways. The first is directly, so you can create a new instance and specify the relationship name. The second way is to derive new relationship types from it and set the relationship name as part of its default constructor.
Using directly means that, where there not existing C# models that describe the relationships you don't have to create entirely new ones to use the API. But they are pretty simple, so you can't do much with them other than use them for specifying the relationship name.
If you derive and create new types, then you can use these with the Where methods to also filter your queries based on relationship properties, and that gets a lot more powerful.
// Used directly
var owns = new BaseGraphInstanceFluentRelationship("owns")
// Or as an override
public class Owns : BaseGraphInstanceFluentRelationship
{
[JsonPropertyName("start")]
public DateTime StartOfLease { get; set; }
[JsonPropertyName("end")]
public DateTime EndOfLease { get; set; }
public Owns()
{
RelationshipName = "owns"
}
}
Using the JsonPropertyName attribute allows us to map POCO properties to field names in the graph.
So, lets re-build our query using the MATCH clause above using the Fluent API.
IEnumerable<(Room, Asset, BimProperties)> results = await new FluentGraph(graph)
.WithFluent()
.From<Company>(ModelVerificationType.IsOfModel)
.Match<Building, Owns>()
.Match<Room, IsPartOf>(modelVerificationType: ModelVerificationType.IsOfModel, minHops: 2)
.Match<Asset, ServedBy>(modelVerificationType: ModelVerificationType.IsOfModel)
.Match<BimProperties, HasBimProperties>(direction: MatchDirection.RightToLeft)
.WhereId<Company>("myOrg")
.Project<Room, Asset, BimProperties>();
You can see we're starting the same way using From method, but then jump into using Match which, using derived types, takes two type arguments, the first is the model we're connecting to, and the second is the relationship type.
The full method signature allows us to provide a query variable, minimum number of hops, maximum number, join direction, and model verification type. There are also overrides allowing us to specify multiple relationship types as the MATCH clause lets us union different relationship names such as -[:owns|leases|rents]- (that syntax just gets more complex right).
The above Fluent API builds and executes the following Digital Twin query.
SELECT
room
, asset
, bimproperties
FROM
digitaltwins
MATCH
(company)-[:owns]->(building)-[:isPartOf*2]->(room)-[:servedBy]->(asset)<-[:hasBimProperties]-(bimproperties)
WHERE
IS_OF_MODEL(company, 'dtmi:digitaltwins:rec_3_3:agents:Company;1')
AND IS_OF_MODEL(room, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND IS_OF_MODEL(asset, 'dtmi:digitaltwins:rec_3_3:core:Asset;1')
AND company.$dtId IN ['myOrg']
There's a slight change here compared to the original. We're setting a minimum number of hops from building to room. Because we've not specified a maximum number then it's a specific number of hops, so "there are 2 hops from building to room".
But we've now got type safety, compile time safety, and runtime validation of the query. If we specified right-to-left as the join direction then that's what we get, and there is always a : before the relationship name.
One of things we can also do (for matches which don't include a hop) is specify a query name and then use this in a WHERE clause.
// Change the building match statement as follows
.Match<Building, Owns>("o")
// Add the WHERE clause
.Where(
WhereClause.Comparison<Owns, DateTime>(
"o",
o => o.StartOfLease,
DateTime.UtcNow.Date.AddDays(-7),
ComparisonOperatorType.GreaterThan
)
)
Here we specifying the query variable as o and then using it in the where clause to filter only those buildings whose lease has started within the last 7 days. This generates the Digital Twin query (parts of it) like this
-- Match
MATCH (company)-[o:owns]->(building)
-- Where
company.$dtId IN ['myOrg'] AND o.start > '2022-10-02T00:00:00.0000000Z'
You can see that the Fluent API has converted the POCO property name to start because of the JsonPropertyName attribute, and generated a correctly formatted date time value. In there Where method, because we said that the property is a DateTime we can only use the DateTime properties, and can only provide a DateTime value for comparison, otherwise it's a compile time error and we don't get to deploy the code.
Current state
With the introduction of the Match clause we can now use the Fluent API for all methods of querying the Digital Twin graph (at the time of writing), in a method which is interface driven and so lets us unit test code without connecting to a Digital Twin instance. With greater protection against misspellings, queries that update as the twin model version changes, and better compile time safety.
If you want to try it out yourself, we have our SDK published as a NuGet package over at nuget.org.
Don't forget to check out our other releases, such as AdxUtilities, or our Azure Digital Twin connector for Spark.
Or, come and check us out directly if you want to see what we can do for your spaces using Azure Digital Twins, over at our Intelligent Spaces website.
Top comments (0)