How Long to keep a LINQ-to-SQL DataContext Open?

Question

I'm new to linq-to-sql (and sql for that matter), and I've started to gather evidence that maybe I'm not doing things the right way, so I wanted to see what you all have to say.

In my staff allocation application I allow the user to create assignments between employees and projects. At the beginning of the application, I open up a linq-to-sql data context to my management database. Throughout the program, I never let that data context go. As a matter of fact, most of the form constructors take this data context as one of their arguments.

I kinda thought that this was the way to do things until I read through another SO question where the asker was discussing repetitively re-creating the data context throughout his program and then "attaching" the entities to the new data contexts as needed. This would help me get around the problem I've been having wherein things are "sneaking" into my database.

So where would you use the first style (and don't be ashamed to say never), and where would you use the second style?

Answer 1

If you are writing a web application in, say, ASP.NET MVC, or a web service, you will be recreating the DataContext each time, as the application is "stateless" between page GETs and POSTs.

If you are writing a Winforms or WPF application, you can do it the same way, although holding a DataContext open can be easier to do, since Winforms applications are stateful (i.e. you have a container for the DataContext to live).

In general, it is probably sensible to open a DataContext each time you need to complete a "unit of work." The DataContext itself is fairly lightweight, so opening one for each "transaction" is not that big of a deal. This practice is also consistent with software layers in enterprise applications (i.e. Database, DAL, Service Layer, Repository, etc.), and helps to enforce separation of concerns between the requisite layers.

Answer 2

The generally recommended way of doing things is to create a new DataContext for each atomic operation. DataContext's are actually quite cheap to instantiate, and are very well suited to rapid turnover.

As a general rule of thumb, I tend to instantiate a DataContext, perform a CRUD operation, then dispose of it again. This could be the updating of a single entity, or inserting a load of objects. Do whatever makes the most sense for your scenario.

Just be careful if you're passing entities from your context around, as exceptions will be thrown if you try to enumerate or retrieve related data - it's best practice to transform the LINQ entities into independent objects (for example, a Person LINQ entity could be transformed into a PersonResult, which is consumed by the logic layer of your solution).

Hope that helps!