Opinion on reuse of db context in Linq

Question

I have a class that uses linq to access the database. Some methods call others. For example:

class UserManager
{
   public User[] getList()
   {
       using(var db = new MyContext())
       {
             return db.Users.Where(item => item.Active == false);
       }
    }
    public User[] addUser(string name)
    {
       using(var db = new MyContext())
       {
           db.Users.InsertOnSubmit(new User() { id = Guid.NewId(),  name = name, active = false ...});
       }
       return getList();
    }

...

In the call to addUser I am required to return the new list. (I know as it stands it isn't a great design, but I have eliminated detail for simplicity.) However, the call to getList creates a second data context.

I could pad this out with extra methods, viz:

public getList()
{
     using(var db = new MyContext())
        return getList(db);
}
public getList(MyContext db)
{
      ...
}

Then replace my call in addUser so as to keep the same data context.

I seem to see this type of thing a lot in my code, and I am concerned with the cost of creating and releasing all these data contexts. Does anyone have an opinion as to whether it is worthwhile putting in the extra work to eliminate the creation and deletion of these contexts?

Answer 1

Microsoft provides the following advice/recommendation to not reuse DataContext instances http://msdn.microsoft.com/en-us/library/bb386929.aspx

Frequently Asked Questions (LINQ to SQL)

Connection Pooling

Q. Is there a construct that can help with DataContext pooling?

A. Do not try to reuse instances of DataContext. Each DataContext maintains state (including an identity cache) for one particular edit/query session. To obtain new instances based on the current state of the database, use a new DataContext.

You can still use underlying ADO.NET connection pooling. For more information, see SQL Server Connection Pooling (ADO.NET).

Answer 2

It is ok to reuse for different parts of the same logical operation (perhaps by passing the data-context in as an argument), hut you shouldn't reuse much beyond that:

• it caches objects; this will grow too big very quickly
• you shouldn't share it between threads
• once you've hit an exception, it gets very unwise to reuse

Etc. So: atomic operations fine; a long-life app context; bad.

Answer 3

What I usually do is create a class the you could call something like DataManager with all data functions as members. This class creates an instance of MyContext on its constructor.

class DataManager
{
   private MyContext db;

   public DataManager() {
       db = new MyContext();
   }

   public User[] getList()
   {
       return db.Users.Where(item => item.Active == false);
   }

   public User[] addUser(string name)
   {
       db.Users.InsertOnSubmit(new User() { id = Guid.NewId(),  name = name, active = false ...});
       return getList();
   }
}

You create an instance of this class whenever you are doing a set of operations. On a Controller, for instance, you could have this class as a member. Just don't make a global var out of it, instantiate and dispose when you are done with it.