Health Check Ping with EF Core and MSSQL
Sometimes it is quite useful to be able to send a query to the MSSQL server independently of its schema; for example to check if the server is principally reachable and the connection information as well as the credentials are correct.
This is also very useful for the health checks in ASP.NET Core or Azure App Service, to check if the state is healthy while generating the least possible database load.
So the goal is to send the server a message that it can answer immediately without dependencies. For this you can use a special feature, where all databases have this feature implemented in some way.
In the case of the MSSQL server it is
So the server is sent a query that has only a SELECT, where the numeric value also corresponds to the answer. So a
SELECT 1 becomes the answer
There must be a database on the server to connect to, but no tables must be deployed.
In addition to handling with entities, the Entity Framework can also work directly with Raw SQL. For this purpose the method ExecuteSqlRawAsync can be used.
Since we are in principle only interested in the execution (an exception should therefore be thrown if it does not work) but not the result (here 1), the following basic implementation is sufficient.
This method can now be used for pings.