Azure Digital Twins - Fluent API v3.1 - MATCH the things!
Tags: azure, digitaltwins, csharp, dotnet
Published: 2022-10-09 11:07:13

Back in June of this year I [posted](https://dev.to/dazfuller/azure-digital-twins-building-a-fluent-api-in-net-6g3) about the Fluent API we built at [Intelligent Spaces](https://intelligentspaces.io) 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](https://learn.microsoft.com/azure/digital-twins/reference-query-clause-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.

```sql
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.

![Ontology structure showing query from organisation to asset and BIM properties](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/88obai3r0bfkskjiq1dm.png)

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.

```sql
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.

```csharp

// 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.

```csharp
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.

```sql
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](https://learn.microsoft.com/azure/digital-twins/reference-query-clause-match#assign-query-variable-to-relationship-and-specify-relationship-properties)) is specify a query name and then use this in a `WHERE` clause.

```csharp
// 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

```sql
-- 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](https://www.nuget.org/packages/Elastacloud.DigiTwin.SDK).

Don't forget to check out our other releases, such as [AdxUtilities](https://www.nuget.org/packages/AdxUtilities), or our Azure Digital Twin [connector for Spark](https://github.com/intelligentspaces/spark-digitaltwin-reader).

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](https://intelligentspaces.io/) website.