Connection Pool error LINQ to SQL

Question

Guys,

In Our project I have used LINQ to SQL for every kind of database interaction. Now from what I know , When I create and use DataContext object : it opens the connection , crates a transaction , perform the query and closes the connection.

Every now and then we are getting Connection pool error on our services and Server.

" 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. "

Any idea ? Am I completely off here ? Is this related to SQL Server itself ?

We are using LINQ , .Net 3.5 and Sql Server 2008

NOTE: NO DATAREADER IS BEING USED ANYWHERE IN SYSTEM.

Thanks

Answer 1

See here:

http://stackoverflow.com/questions/389822/when-should-i-dispose-of-a-data-context

I'm quoting Jon Skeet quoting someone else here, but it was a surprise to me as well, since we ran into a similar issue.

The logic that automatically closes the DataContext connection can be tricked into leaving the connection open. The DataContext relies on the application code enumerating all results of a query since getting to the end of a resultset triggers the connection to close. If the application uses IEnumerable's MoveNext method instead of a foreach statement in C# or VB, you can exit the enumeration prematurely. If your application experiences problems with connections not closing and you suspect the automatic closing behavior is not working you can use the Dispose pattern as a work around.

Basically, the mechanics of how the datacontext closes itself aren't bulletproof. I've since taken to closing it manually, which fixed our problem.