How to verify connection to the database in Entity Framework Core?

This article explains how to perform a very simple and lightweight database connection test for an existing database context.

A while ago, I needed to develop a simple API that I can poll from a load balancer to see whether:

1. An Azure service hosting it is up and

2. It has a connection to its SQL database

This simple API would be used for monitoring purposes - simple stuff, sure, but what's the best way to implement this without causing any unnecessary load to the database, since monitoring is going to be polling the site quite often?

Okay, so let's see how it's done!

Solution

Not too complicated, luckily. While there are other, perfectly valid options, I'll share my simple and lightweight way of verifying the database is functional and that my Entity Framework Core context can access it.

Essentially, the simplest T-SQL query I could find was just running "SELECT 1". That's not going burden your database much (well, essentially not at all!) but still verifies you actually DO have access.

Looking at the code, you'll just need to run the query on the database and then discard the results. We don't care about the return value, since the query will throw an exception if the execution is unsuccessful (and that'll show up in our monitoring!)

_ = await db.Database.ExecuteSqlRawAsync("SELECT 1");

Well, simple as that. Let me know if you find an even more lightweight way!