Latest binaries, 3.1

Hi, I've been converting a large project to llblgen (primarily now in linq) over the last 2 years or so. I've recently noticed that connections appear to remain open, regardless of whether i ToList() them etc.

I normally do something like this:

public class MyObject() {

   public MyObjectEntity EntityProperty {get;set;}

   public MyObject(MyObjectEntity entity) {
           EntityProperty = entity;
   }

   public SomeColumnName {get {return EntityProperty.SomeColumnName;} set {... = value}}

   public List<MyObject> Gets(..) {

       var query = new LinqMetaData().Where(x=>x .....);
       var list = new List<MyObject>();
       foreach (var i in query.ToList())
            list.Add(new MyObject(i));
       return list;
  }
}

This has worked extremely well for me, I minimise the amount of time I spend working with the database, I can manage my objects knowing that I have direct access to the underlying entities. The object properties are retrieved directly from the entity. I've tried a number of other ways to achieve this, but this works best for me. I can't inherit your entities as my objects already inherit other base classes.

But now it seems connections are remaining open - I was under the assumption that after your framework had retrieved the entity, it would close the connection?

BTW: Be gentle - > I'm sure it's not the correct way to do it. I have to deal with enormous traffic, and behind this is a complex system that deals with maintaining, marshalling and caching these objects. I'm able to serve 100s of complex pages a second on a single server like this, with almost nothing in terms of memory usage.

Which database is this? Are you using the Adapter or the SelfServicing template set?

O sorry, yeah self-servicing. Is that the reason? Should I convert to adapter for any reason?

Right, well I'll wrap everything in transaction scopes then?

You should check whether the connection remains open and why to know what you have to do. If you want more control on the connection you could use a .Net Transaction to enclose the call and dispose it at the end. Read this for more info.

If a connection was closed beforehand, it's closed afterwards. This is in general the case with selfservicing, unless you're using a transaction.

I.o.w. the connection should never stay open. How do you test this btw? Be aware that with connection pooling, the physical connection between client and database server isn't actually closed by the ADO.NET providers (any of them, they all use this trick), but simply 'reset'. When a new ADO.NET connection is created (the DbConnection object) it gets a 'live', open physical connection mapped into itself by the ADO.NET provider. This makes connection pooling very efficient, but indeed it will look like physical connections stay open. The connections at the .NET level are closed however.

Hi Otis,

Here's my reasoning: (I'm refering to processes as seen in SQL Serv 2008 r2 Activity Manager, when i say 'connections')

-- Queries had begun timing-out, freezing.

-- Never happened before, but a lot of new work had been done (I'd began persisting the entities in memory. (They persist for approximately 3 minutes, depending on usage))

-- Server running tons off Linq LLBLGEN stuff, had 10-15 connections open, runs fine.

-- Devt box showed 100+ connections with this new model. I was watching them open fast as my code began running, when they hit the 100 mark - the system froze and those time-out errors appeared. It confirmed that something was clogging the whole thing up.

-- The system will freeze at around 100 open connections, at that point i can either wait a long time for them to clear or recycle the pool (this is what made me think something odd was afoot)

-- I inspected the queries that Management studio showed as 'last query' - they were three or for relatively harmless queries.. I think along the lines of: (can't be sure as I can only see projected SQL code, but 90% sure)

var x = new linqmetadata().entity.where(x=>x...).ToList();

-- I tested this query on a simple page, refreshing the page over and over. The connections began to stack up.. Wrapped the query inside a for loop with 1000s iterations. Bang, it froze.

-- I wrapped the linq calls in a transaction and the whole problem went away. The 1000 iteration loops worked, as did the entire system.

I've never seen this before, I tried updating to the lastest libs, same issue.. One thing, I'm dealing with a LOT of data in a complex upgrade / update cycle.. the code sets unusually high demand on the llblgen libraries. Perhaps this issue only shows when literally thousands of queries are thrown out a second, though.. that doesn't explain why they persist for ages after, or until i recycle the .net pool, or that 1000 loop.

Cheers in advance, Frank.

Devt box showed 100+ connections with this new model

What is this new model? What is different form the way you did before?b