Creating an OData service using Logic Apps - Part 2 - Alessandro Moura

In the first post of this serie, I said I wanted to expose the tables of a database as an OData service using Logic Apps. To do that, we need to be aware of the specification for OData so our solution become as close as possible to this specification.

OData Specification

I went and searched for the OData specification and I found two interesting links:

OData Specification: http://www.odata.org
Wikipedia simplified version: https://en.wikipedia.org/wiki/Open_Data_Protocol

So we have the oficial version and a more friendly version to look for. In summary I’m going to accomplish the following things by the end of this serie of posts:

Expose the following HTTP Methods:
- GET – Read one or more records from the table specified
- POST – Insert a record in the table specified
- PUT – Update a record in the table specified
- DELETE – Delete a record in the table specified
For the GET operation we will accept most of the parameters that the SQL Connector supports as the image below:
For the POST and PUT we will accept a JSON that will be validate in a separate Azure Function
- Will do this in another post
For the DELETE method, we are going to accept only the ID of the record to be deleted in the URL
- Will do this is another post

Dealing with parameters by using Expressions

Now that we have a little better explanation of our goals, in this post we are going to enhance the GET Logic Apps to retrieve parameters from the URI. To do so, we are going to use Logic App Expressions.

Since the user can choose which parameters to send as they are not mandatory, we need to deal with the fact that the information can or can’t come in the request and that complicates a little bit our handling of the request.

To be able to handle this optional parameters, we are going to leverage the use of Logic Apps Expressions as explained in the documentation provided by Microsoft in the link below:

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language#expressions

So let’s start with the filter parameter and let’s create an expression to handle when we have or not the parameter filled in.

The idea is that we receive a request like: https://<server>/<logicappurl>?filter=<odata filter expression>

In a Logic Apps flow, we have access to all information sent by the triggers. In our case we are using a HTTP trigger to receive requests in our code, so we need to use the following syntax to obtain information from it:
trigger()['outputs'] | trigger().outputs | triggerOutputs

Any of this expression above will give us access to all information we are receiving. In this particular case we want to receive information that is coming in the URL, and I will use the following expression:
trigger()['outputs']['queries']['filter']

This expression will be configured in the Initialize variable we added in our previous flow, and the variable it’s being assigned to the Filter Query property on the SQL connector:

SQL Connector Filter property

When a request is made passing the parameter “filter”, everything work as expected, but when this parameter is not passed we start to receive the following error message on the execution of the Initialize variable action.

LogicAppExpressionError

As you can see the expression we built is not handling the possibility of the parameter not being there. To handle this situation, let’s change the expression.

trigger()['outputs']?['queries']?['filter']

As you can see we added the quotation mark indicating the information could be there or not, and the execution is not failing anymore on the Initialize variable, but it’s failing on the SQL connector execution due that the Filter property assigned does not contain a value.

SQL Connector Error

To solve this problem, we need to go back to the expression and make some other changes.

if(equals(coalesce(trigger()['outputs']?['queries']?['filter'],''),''),'1 eq 1',trigger()['outputs']?['queries']?['filter'])

Note that the expression became much more complex but it is achieving the definition of a default value “1 eq 1” to bring all records or to use the parameter passed in the URL.

This way we can have the following URL’s as below:

https://prod-06.eastus.logic.azure.com/workflows/.../SalesLT.Customer/?...&sig=xxx&filter=CustomerID eq 1