How to track PostgreSQL queries using EntityFramework Core in Application Insights

In one of the projects I developed, I used PostgreSQL as a database and EF Core as an ORM(Object-relational mapping). Then we integrated Application Insights for the monitoring of the project’s metrics. After all, we realized that there is no database transaction metric in the transaction history. That is how the story started.

Application Insight not tracking Postgres SQL queries using Entity Framework Core · Issue #1771 ·…

Dependencies are only collected if System.Data.SqlClient or Microsoft.Data.SqlClient is used.

There is no generic collection of dependencies from Entity Framework — EF itself is not instrumented, the instrumentation is only at SqlClient.

I researched a lot on Google, and the answers were not enough. Therefore I decided to apply a different solution. The solution I have performed is to use EF Core’s interceptor interfaces to send manual telemetry during database requests.

I prepared the application below to explain the problem. Although it is not a great example, it will help us to understand the problem.

amuratgencay/PostgreSQL.ApplicationInsights

As you can see, the application is a showcase application that displays the products under the categories and the details of the product. What we need is to monitor the database transaction.

PostgreSQL Interceptor

Entity Framework gives us three interfaces for interception operations.

1. IDbCommandInterceptor: It allows interception of commands sent to a relational database.
2. IDbTransactionInterceptor: It allows interception of operations related to a database transaction.
3. IDbConnectionInterceptor: It allows interception of operations on a database connection.

PostgreSQL Interceptor

If you want to see the full code, you can check the GitHub repository.

Application Insights Extensions

I created an extension class below to generate PostgreSQLInterceptor’s telemetry client and configure the PostgreSQLInterceptor with EF Core.

How to show dependencies on the Application Map correctly?

We need to send dependency telemetry to show dependencies on the Application Map. Dependency telemetry obtains icon value according to the value of a telemetry type field in itself.

telemetryClient.StartOperation<DependencyTelemetry>(operationName);
dependencyHolder.Telemetry.Target = “localhost.5432”;
dependencyHolder.Telemetry.Type = “SQL”;
dependencyHolder.Dispose();

When I made the configurations above, I got the result we required.

Application Map

Performance Results

How to monitor the Database Requests?

We need to send a request telemetry to monitor database requests. I created a request telemetry for the process between opening and closing the database connection.

telemetryClient.StartOperation<RequestTelemetry>(“database request”)

Finally, I created an event telemetry to collect more detailed metric values during the database request.

var eventTelemetry = new EventTelemetry($”Database Event: {name}”);
eventTelemetry.Properties = new Dictionary<string, string>
{
   {"ConnectionId", connectionId.ToString()},
   {"SQL", query},
   {"Parameters", parameters}
};
telemetryClient.TrackEvent(eventTelemetry)

Notes

1. By measuring the time loss and network traffic caused by collecting metrics and sending them to application insights, you should ensure that you sent only the necessary data.
2. You have to be careful about the data disorder in asynchronous requests. I avoided this issue by using ConcurrentDictionary. Consider this situation in your more detailed metrics.

References:

1. PostgreSQL
2. EF Core
3. ORM
4. What is Azure Application Insights
5. Application Insights Entity Framework issue
6. Entity Framework Interceptors
7. IDBCommandInterceptor
8. IDBConnectionInterceptor
9. IDBTransactionInterceptor
10. ConcurrentDictionary