1

Today I got an exception when I run my application from the below code

dbContext.ManageTasks.Where(x => x.IsDeleted == false).ToList();

Error is

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I got a solution from this discussion : How can I solve a connection pool problem between ASP.NET and SQL Server?

Solution is : if I close current connection string object, then this error will be gone. 

SqlConnection myConnection = new SqlConnection(ConnectionString);
myConnection.Open();
// some code
myConnection.Close();

The above code for ADO.NET, not Entity Framework.

How can I close the connection string object in Entity Framework?

Community
  • 1
  • 1
Ramesh Rajendran
  • 37,412
  • 45
  • 153
  • 234
  • 2
    with a 'using' block https://msdn.microsoft.com/en-us/data/jj729737.aspx – Aram Tchekrekjian Apr 21 '15 at 10:03
  • 2
    You are not "closing the connection string". You are closing the connection to the database. At least that's what you should be doing. – germi Apr 21 '15 at 10:06
  • XY problem. OP has mistakenly used a long-lived global data context instead of using many short-lived contexts. Don't fix this specific problem. You need to change your architecture so that the context is not a long-lived global. – spender Apr 21 '15 at 10:16
  • http://stackoverflow.com/questions/888185/entity-framework-context-as-static – spender Apr 21 '15 at 10:21

2 Answers2

6

I'm a bit unsure as to why you would need the use of "SqlConnection". DbContext is a disposable class. I found the safest way to clean up is by using the "using" tag. E.g.

using(DbContext _context = new MyContext())
{
     \\Your Work here
}

This will automatically call _context.Dispose() when it reaches the closing curly bracket which in effect closes your connection.

The alternative would be:

DbContext _context = new MyContext();
_context.ManageTasks.Where(x => x.IsDeleted == false).ToList();
_context.Dispose();

This does mean it's possible to miss the dispose call if an exception occurs.

Hope this helps.

Useful Link

In order to handle transaction scope and to help dispose your objects, you may want to look at the repository pattern and the unitOfWork pattern. Or both.

Further to this, I know use DependencyInjection to handle a lot of connections. I just ensure they are Scoped (per web request) :)

Heberda
  • 830
  • 7
  • 29
  • You are missing the `try ... finally` part of `using`. – Patrick Hofman Apr 21 '15 at 10:09
  • it's not good for me, because i have created the object globally for context class `TimeSheetEntity dbContext = new TimeSheetEntity()` – Ramesh Rajendran Apr 21 '15 at 10:10
  • A "try" is not linked in any way to the using statement, granted it can be used but that's a developer decision based on what code is being run inside the using statement. – Heberda Apr 21 '15 at 10:11
  • Ramesh, use the alternative. You can call .Dispose() at any time you like. Then I would re-instantiate dbContext in the constructor. – Heberda Apr 21 '15 at 10:12
  • @RameshRajendran That's your mistake. **The context class should not be long-lived**. You need to redesign your app so that the context is non-global. Create a context per logical database operation. **Don't use a global context**. – spender Apr 21 '15 at 10:13
  • The alternative option is better for me. – Ramesh Rajendran Apr 21 '15 at 10:14
  • But i can't do it non globally. because i need to create new object to separate methods. So it will go to broke some performance. So i can't do that. – Ramesh Rajendran Apr 21 '15 at 10:15
  • @RameshRajendran Yes you can. The other option is to carry on with a design that doesn't work. That would be stupid. – spender Apr 21 '15 at 10:18
  • Agree with @spender a lot. You shouldn't make it global as in my answer. If you use the alternative, make sure to use a `try ... finally` block. – Patrick Hofman Apr 21 '15 at 10:18
  • Oh definitely, the using statement is by far the preferred solution. In fact I actually use the UnitOfWork Pattern: http://www.asp.net/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application – Heberda Apr 21 '15 at 10:30
4

You are probably keeping a global variable dbContext somewhere in your class. You should not do that!

You could use the IDisposable interface that DbContext implements and use using statements. You initialize a new DbContext whenever you need one:

using (YourDbContext dbContext = ...)
{
    // do some actions
}

// context will be closed

The benefit of using using is that it will close and dispose, even if the code inside will throw an exception.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325