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();
  }
}