Is it possible to gets sql connection leaks using LINQ?

Question

I belived it was not possible to get sql connection leaks when using LINQ, but perfmon tracing of NumberOfReclaimedConnections shows a high number and on high load we sometimes get exceptions like "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".

We do not use Dispose on the datacontexts, sincewe used defered loading. Several articles and blogpost tells me that this should not be a problem.

Still we gets these exceptions sometimes. But it can not be that every linq query we do keep the connection open, then we would have a lot more of the exceptions.

Edited

The application is a WCF service.

If you look at the documentation of Linq and most of the articles, they claim that the Dispose is not necessary to release the connections. They claim that DataCOntext only keep the connection open for the short time it need it.

Answer 1

Are you getting any deadlocks in your database? A quick look at the Activity Monitor should give you some indication.

What do you do to manage DataContext lifecycle - what sort of application have you written (a website, a Windows Client, other)?

Once used in a query or operation, a DataContext will keep a connection so that entities loaded can lazy load & etc, so it's imperative that you plan how you use DataContexts in your application.

WCF services.. In that case, I am a big fan of the "one context per request" approach. I'd encourage you to wrap your data operations within a using() statement so that the context is disposed when you're done.

Answer 2

When your DataContext is not disposed of and stays alive, the associated connection will stay alive too. Database connections are unmanaged resources and all unmanaged resources must be disposed of properly.

Even if you use delay-loading and do not have a well-defined scope, you should still clean up database connections at the end of a logical unit of work. In ASP.NET apps, the latest possible moment for this would be at the end of request processing - in the Application_EndRequest method of the Globals.asax file. In a WCF service, any active data context should be disposed of at the end of every service method call.

The documentation for this vague and while most of the time, you can get away with not disposing your DataContext, there do appear to be some scenarios where the data loaded from a connection is keeping the connection itself alive. The easiest way to confirm that this is happening in your case is to test it.

Answer 3

I found after some more searching I found this question and answer, where it says that linq can be fooled to leave a connection open..

I made this small test code that reproduces it. If I just replaces the Enumerator with foreach it works fine, but he Enumerator keeps the connections open.

public Organisation RunTestQuery2() { IEnumerable orgs = base.GetEntities().Take(5);

    var enumerator = orgs.GetEnumerator();
    int i = 0;


    while (enumerator.MoveNext())
    {
        var org = enumerator.Current;
        Debug.WriteLine(org.DescribingName);
        if (i == 3)
        {
           return org;
        }
        i++;
    }

    return null;
}

If I adds a call to dispose on the context, they dissappear.