What is the best way to manage creation/disposal of dataaccess adapters in a high volume enterprise web application. I am currently using the repository pattern with an adapter per entity repository. I create the adapter in the constructor and implement IDisposable to dispose of it.

Is this suitable or do I have to resort to using statement round every statement as shown in your documentation?

Thanks

What's shown in the docs is the best way to use the Adapters.

You shouldn't keep the Adapter around if it's not being used, especially in WebApps where you wouldn't know when the next postback would come, so only create it when needed and destroy it as soon as you finish dealing with it, which is done by the using block.

The problem with this is that I am using the LINQ repository pattern where I have a repository

public interface IRepository<T> { IQueryable<T> GetAll(); IQueryable<T> Find(Expression<Func<T, bool>> expression); T FindOne(Expression<Func<T, bool>> expression); }

I have an implementation of this repository for each entity. And above that a service layer for my business logic. Within the repository implementation I use Linqmetadata to construct the IQueryable expression. I was creating the adapter as part of the constructor and implemented IDisposable to dispose of it. However after your suggestion I wrapped the linqmetadata query bits in using (DataAccessAdapter adapter = new DataAccessAdapter()) statements. At runtime I got ObjectDisposed exceptions due to the dataaccessadapter being disposed before I could use it. The IQueryable expression gets passed up I don't want my service layer to have any knowledge of the ORM. Do you have any suggestions? We are currently evaluating to purchase so I would like to get over this hurdle.

Thanks

Linq queries are executed when they're enumerated, so disposing the linqmetadata when the query is created will obviously also dispose the adapter. The dataAccessAdapter instance will open/close connections when necessary and also dispose connections when closed (transactions as well), so you don't have to worry about the adapter being disposed or not: teh resources which should be disposed are disposed for you after the query has ran.

Linqmetadata has no resources to dispose other than the adapter enclosed, so you're safe there too.

Thanks Otis. But I am not sure if that answers my question. I realize I need to push the using statement into the service because it is at that point that I am enumerating, but I don't want to couple the srevice at all to an ORM implementation. Is there no other way to do it?

IMHO, I never liked the repository pattern. It is in essence sending deferred execution up the stack. I would rather the service layer ensure it is sending the right things (objects) rather than sending a piece of execution (that's what an IQueryable is up until it is enumerated). Why not get the object graph you want and then pass that up? If you are adamant about not passing LLBLGen entity objects up the stack, there is a nice template for generating POCO objects that works just fine. With LINQ to LLBLGen, you can query pretty much for anything, and then project it into these POCO objects in a single LINQ query.

I guess I am asking why you NEED to pass queryable objects up the stack. In fact, when you later choose to put your service layer on multiple machines (because I am sure your software will make millions and thus will need to scale well), what does an IQueryable mean across machine boundaries when a client that has no access to your database enumerates the queryable?

Thanks Seth. What you say makes sense. The reason I am doing it this way is it allows me to filter the data a number of times before I only ever query the database for the data that I need. I understand your point that I'll need to change this if I need to distribute my service layer and I will consider that seriously. I just want to know if it is possible to do what I need in LLBLGen in case I need to in the future. I was able to do this in LINQ to SQL which is what I migrated from. A code generator is all about flexibility and so far I really like LLBLGen. This in fact is the only issue I have encountered so far.

Quoting Otis on thread http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14769

"To elaborate a bit more on what Walaa said: the adapter only needs to dispose the connection object. It by default closes a connection after an action, which gives the underlying connection object back to the pool. The adapter will dispose the active connection object when it closes it, it also disposes parameters etc. if required.

I.o.w.: you're fine: the Dispose() call on DataAccessAdapter has no work left as the used connection has been disposed already. "

So does this mean it should be fine not to dispose the adapter. I am using SQL Server and Sybase database servers. According to the docs this really only poses an issue for SQL CE and Firebird.

Dispose() cleans up the same stuff as CloseConnection() does except for System.Transactions transactions. Closeconnection also disposes (indirectly) any active transaction, the parameters if required (oracle ODP.NET) and the open connection.

Dispose() cleans up System.Transactions transaction references, CloseConnection does not. That's about the difference there is between them.