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

Question

for example:

class repository {

     private 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?

A similar question, asked in the context of the Entity Framework, but still worth a look as the DataContext and ObjectContext do very similar jobs: http://stackoverflow.com/questions/457369/should-i-share-the-entity-framework-context-or-create-a-new-context-for-each-oper — itowlson, Dec 22 '09 at 20:18

score 4 · Answer 1 · edited May 23 '17 at 10:33

4

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);
    }
}

edited May 23 '17 at 10:33

Community

1
1

answered Dec 22 '09 at 20:28

Eric King

11,594
5
43
53

What do you mean SomeDataContext()? Does this mean a class you made that inherits from DataContext? So then you make the methods for getting data that you need inside of there instead of in the Repository? – MetaGuru Dec 22 '09 at 21:01
No, that's the actual datacontext, not a derived class. Sorry if the fake naming was confusing. – Eric King Dec 23 '09 at 00:48

Rippo · Answer 2 · 2009-12-22T21:35:09.213

1

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

edited Dec 22 '09 at 21:35

answered Dec 22 '09 at 20:16

Rippo

22,117
14
78
117

That has to do with opening and closing connection. Linq-to-sql and Entity Framework close the connection right after completing the query. Combined with connection pooling this makes for a pretty fast and scalable solution. Note that connections are far shorter lived than the context itself. – Sander Rijken Dec 22 '09 at 20:36
So the data context follows the pattern of opening the connection late and closing early? My last point regarding the sql profile still stands. – Rippo Dec 22 '09 at 21:33

score 1 · Answer 3 · answered Dec 22 '09 at 20:34

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); 
    }
}

What benefits does this give you? From my experience if you cant guarantee that you may want to do more work on the context then using "using" can throw unexpected runtime errors. — Rippo, Dec 22 '09 at 21:37
Yep, you could consider skipping the using altogether. I think that's rather safe, because it closes the connection as soon as it's done with it (so it doesn't keep a connection open), and the GC will clear it when the context + query falls out of scope. — Sander Rijken, Dec 22 '09 at 22:03

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

3 Answers3