Limit Connections to Database with Entity Framework C#

Question

I have an Azure database that is limited to 60 concurrent connections. My issue is that I have several crawlers that populate this database to be consumed by our website using Entity Framework.

It does not take long for multiple crawlers to exhaust all of the connections and error out. I have tried setting the max pool size value in the connection string, but this does not seem to enforce any restrictions on the actual database connections.

I could wrap the DbContext in a singleton, but then I would be limiting the entire crawler to a single connection.

Are there any other ways to achieve this?

Answer 1

You are trying to limit concurrent access to a shared resource. Usually a Semaphore is used for this.

To do this across a single box or a single app you could use Semaphore or SemaphoreSlim

However if the clients are running on different boxes the natural place to place the semaphore would be the SQL Server itself. See this article for one way of implementing it. The app would need to call the proc to acquire the lock before accessing the database using the DbContext. The semaphore limit would need to be 59 to keep one connection for reading the semaphore.