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...