In LINQ to SQL based Repositories, should we create a new DataContext within each method?

for example:

class repository {

     priva开发者_运维知识库te DataContext db = new DataContext();

     public IQueryable<Blah> someMethod(int id){
         return from b in db.Blah ... select b;
}

     public IQueryable<Blah> someMethod2(int id){
         return from b in db.Blah ... select b;
}

     public IQueryable<Blah> someMethod3(int id){
         return from b in db.Blah ... select b;
}

}

OR

Should we make a new DataContext WITHIN each of those methods?

I think we are having some errors once user load increases due to us only having ONE DataContext per Repository instance, is this an accurate assumption?

---

See also the answer to this question.

In short, especially if you are using a repository pattern, you should create and dispose of a datacontext for each unit of work. Typically I use something like:

public someclass someMethod(int id)
{
    using (var db = new SomeDataContext())
    {
        return db.FindMyClass(id);
    }
}

---

What I did personally, is make the repository disposable. You then get constructs like:

void DeleteCustomer(int id)
{
    using(var repos = GetRepos())
    {
        var customer = repos.GetAll<Customer>().Single(x => x.Id == id);
        repos.Delete(customer);
        repos.SaveChanges();
    }
}

This can be implemented by creating the context in the repository ctor, and disposing it in the Dispose() implementation.

You need to make sure you're not adding/changing/delete objects and selecting from the same context. A context is made to 'last' a unit of work.

You need to be careful of stuff like this though:

IQueryable<Customer> GetCustomers()
{
    using(var repos = GetRepos())
    {
        return repos.GetAll<Customer>();
    }
}

void Test()
{
    // This will throw an exception, because it extends the linq query
    // while the context is disposed.
    var customers = GetCustomers().Where(x => x.Id == 123); 
}

In that case it's better to move the repository outside as much as possible:

IQueryable<Customer> GetCustomers(MyRepository repos)
{
    return repos.GetAll<Customer>();
}

void Test()
{
    using(var repos = ...)
    {
        var customers = GetCustomers(repos).Where(x => x.Id == 123); 
    }
}

---

I know this is not quite the same but, in a heavily used asp.net site using old fashioned data adapters, I used to open the connection to the database on page init and close it on page prerender. However I found that when the site was under more load the pages began to crawl. I read somewhere that it is always best to open as late as possible and close as early as possible.

Now I open the context in each method, however due to linq 2 sql and its deferred execution I am not sure if this makes much difference.

I would run the SQL profiler during busy moments to see where the bottle neck lies...