When will connection be closed in case of IEnumerable with using

Question

Suppose I have such pseudo code using some pseudo ORM (ok in my case it's Linq2Db).

static IEnumerable<A> GetA()
{
  using (var db = ConnectionFactory.Current.GetDBConnection())
  {
     return from a in db.A
            select a;
  }
}
static B[] DoSmth()
{
  var aItems = GetA();
  if (!aItems.Any())
    return null;
  return aItems.Select(a => new B(a.prop1)).ToArray();
}

When will Connection be closed in db? Would it be closed at all in that case? What connection would be closed - those in using statement or those in lambda expression? .NET compiler is creating anonymous class for lambdas, so it will copy connection to that class. When would that connection be closed?

Somehow I managed to get 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 materialized queries and exception disappeared. But I'm wondering how this thing works.

Answer 1

This is entirely dependent on your ORM. If disposing of ConnectionFactory.Current.GetDBConnection() closes the connection then you will never be able to enumerate the result. If it doesnt close the connection (and something else does) it might work depending on if someone else has closed the connection.

In any case you probably dont want to return an un-enumerated enumerable from something which creates and disposes the connection.

either enumerate the collection before closing it eg:

static IEnumerable<A> GetA()
{
  using (var db = ConnectionFactory.Current.GetDBConnection())
  {
     return (from a in db.A
            select a).ToArray();
  }
}

or control the connection at the level which does enumerate the results eg:

static IEnumerable<A> GetA(whatevertype db)
{
   return from a in db.A
          select a;
}
static B[] DoSmth()
{
  using (var db = ConnectionFactory.Current.GetDBConnection())
  {
    var aItems = GetA(db);
    if (!aItems.Any())
      return null;
    return aItems.Select(a => new B(a.prop1)).ToArray();
  }
}

Answer 2

after first call : in this call connection opened in using scope , without any fetching data at the end of usnig scope Closed.

var aItems = GetA();

but at this line :

if (!aItems.Any())

there isn't any open connection

Answer 3

Linq2db release connection when you close your connection object. In your example it happens when you leave using block and db object is disposed. This means that you cannot return query from connection scope - you need to fetch your data first or postpone connection disposal till you didn't get all required data.

Exception you observed was due to error in linq2db prior to 1.8.0 where it allowed query execution on disposed connection, which lead to connection leak. See https://github.com/linq2db/linq2db/issues/445 for more details. After that fix you cannot write code like GetA() in your example as you will get ObjectDisposedException when you will try to enumerate results.