Entity Framework Core leaving many connections in sleeping status

Error:Timeout expired. The timeout period elapsed prior to obtaining aconnection from the pool. This may have occurred because all pooledconnections were in use and max pool size was reached.

This is almost always a connection leak. And here the fact that your queries are short-running, and you see idle connections on the server confirms it. Somewhere you're leaving an open connection.

A DbContext will open/close the underlying connection, and return it to the pool on Dispose. But if you start a transaction on a connection and don't commit or rollback, the connection will be segregated in the pool and won't be reused. Or if you return an IEnumerable or a DataReader that never gets iterated and disposed, the connection can't be reused.

Look at the "sleeping" sessions to see what their last query was, and cross-reference that with your code to track down the call site that leaked the connection. First try the DMVs, eg

select s.session_id, s.open_transaction_count, ib.event_infofrom sys.dm_exec_sessions scross apply sys.dm_exec_input_buffer(s.session_id,null) ib

Or start an Extended Events trace if necessary.

connection leaks cause such problems, connections probably is not getting closed properly unless Garbage collector is used to trash all those dangling connections using IDisposable, finally clause can be added to ensure that connections are closed after its use.

link is helpful to understand this issue.

As far as entity framework concerned, max pool size can be achieved by keeping many object in database context, while you can materialize them using FirstOrDefault or ToList functions, as queries can hold connections to database server

Note that DbContext implements IDisposable.

Best practice (for many reasons, not just connection management) is to new up your DbContext in a using statement:

using(MyContext context = new MyContext()){    // do your work}

I wrote a small library that assists you in implementing and enforcing a pattern such as this.