Sub Sonic not closing connections

Question

I'm using Linq from SubSonic 3 like this:

for(int x; x < 100; x++) {
   var v = (from c in db.categories
            where c.parent == 10
            select c);

   if (v.Count() > 0) return null;

   category[] c = v.ToArray();
}

for some reason SubSonic is not closing the connections...so after a few runs of the above loop I run out of SQL connections in the pool or MySQL just refuses to allow more connections...I've tried this both with SS 3.0.3 and with SVN, and I keep getting these errors.

What should I be doing to close out the connections after I get a set of results?

Thanks

Answer 1

The problem - believe it or not - isn't SubSonic. It's the $*$&$ MySQL driver. We explicitly close off the connection when you do queries like this, but I've seen the MySQL driver completely ignore the closure in favor of some really, really lame attempts at optimization.

I don't know what to tell you here - I'm very sorry to say.

Answer 2

There seem to be some problems with the MySQL .NET library. A few days ago they fixed some of those issues with 6.2.2 related to releasing connections. But there is also a problem with SubSonic. I used the LINQ templates with MySQL to generate my classes. Whenever FirstOrDefault() or First() (the other similar functions probably have the same issue).

For a query such as:

var db = new MyDb("CONNECTIONSTRING_NAME");

var userExt = (from pe in db.PhoneExtensions
               where
                   pe.FirstName.ToLower() == firstName.ToLower() &&
                   pe.LastName.ToLower() == lastName.ToLower()
               select pe.Extension).FirstOrDefault();

This will cause the query to be executed and the reader will not be disposed.

The problem is in Linq.Structure.DbQueryProvider in the Project of T method.

while (reader.Read())
{
    yield return fnProjector(reader);
}
reader.Dispose();

The Dispose() never gets called when using FirstOrDefault() and other similar methods.

A Simple fix:

try
{
    while (reader.Read())
    {
        yield return fnProjector(reader);
    }
}
finally
{
    reader.Dispose();
}

Simple quick test showing the issue:

private class DbDataReader : System.IDisposable
{
    #region IDisposable Members

    public void Dispose() { }

    #endregion
}
private class DbQueryProvider
{
    private DbDataReader _reader;

    public bool IsReaderDisposed { get { return _reader == null; } }

    public DbQueryProvider()
    {
        _reader = new DbDataReader();
    }

    public IEnumerable<int> Project(int numResults)
    {
        int i = 0;
        while (i < numResults)
        {
            yield return i++;
        }
        _reader.Dispose();
        _reader = null;
    }

    public IEnumerable<int> ProjectWithFinally(int numResults)
    {
        int i = 0;
        try
        {
            while (i < numResults)
            {
                yield return i++;
            }
        }
        finally
        {
            _reader.Dispose();
            _reader = null;
        }
    }

}

[Test]
public void YieldReturn_Returns_TrueForIsReaderDisposed()
{
    const int numResults = 1;

    var qp1 = new DbQueryProvider();
    var q1 = qp1.Project(numResults);
    Assert.IsInstanceOf(typeof(int), q1.First());

    var qp2 = new DbQueryProvider();
    var q2 = qp2.Project(numResults);
    Assert.IsInstanceOf(typeof(int), q2.FirstOrDefault());

    var qp3 = new DbQueryProvider();
    var q3 = qp3.Project(numResults);
    Assert.IsInstanceOf(typeof(int), q3.Single());

    var qp4 = new DbQueryProvider();
    var q4 = qp4.Project(numResults);
    Assert.IsInstanceOf(typeof(int), q4.SingleOrDefault());

    Assert.IsTrue(qp1.IsReaderDisposed);
    Assert.IsTrue(qp2.IsReaderDisposed);
    Assert.IsTrue(qp3.IsReaderDisposed);
    Assert.IsTrue(qp4.IsReaderDisposed);
}

[Test]
public void YieldReturnFinally_Returns_TrueForIsReaderDisposed()
{
    const int numResults = 1;

    var qp1 = new DbQueryProvider();
    var q1 = qp1.ProjectWithFinally(numResults);
    Assert.IsInstanceOf(typeof(int), q1.First());

    var qp2 = new DbQueryProvider();
    var q2 = qp2.ProjectWithFinally(numResults);
    Assert.IsInstanceOf(typeof(int), q2.FirstOrDefault());

    var qp3 = new DbQueryProvider();
    var q3 = qp3.ProjectWithFinally(numResults);
    Assert.IsInstanceOf(typeof(int), q3.Single());

    var qp4 = new DbQueryProvider();
    var q4 = qp4.ProjectWithFinally(numResults);
    Assert.IsInstanceOf(typeof(int), q4.SingleOrDefault());

    Assert.IsTrue(qp1.IsReaderDisposed);
    Assert.IsTrue(qp2.IsReaderDisposed);
    Assert.IsTrue(qp3.IsReaderDisposed);
    Assert.IsTrue(qp4.IsReaderDisposed);
}

YieldReturnFinally_Returns_TrueForIsReaderDisposed passes but YieldReturn_Returns_TrueForIsReaderDisposed fails.

I have tested this on the project I am working on which will soon be in production and this seems to work without any problems. Tested with a connection pool max size of 5 and had no connection pool issues (never ran out of connections on my dev machine when doing 1 query at a time). I also found some issues in Extensions.Database related to type changing and assignments.

I forked the project on github, committed my changes and did a pull request, hopefully that gets to the right people.