Linq to SQL - How should I manage database requests?

Question

I have studied a bit into the lifespan of the DataContext trying to work out what is the best possible way of doing things.

Given I want to re-use my DAL in a web application I decided to go with the DataContext Per Business Object Request approach.

My idea was to extend my L2S entities from the dbml file to retrieve information the database creating a separate context per request e.g.

public partial class AnEntity
{
    public IEnumerable<RelatedEntity> GetRelatedEntities()
    {
        using (var dc = new MyDataContext())
        {
            return dc.RelatedEntities.Where(r => r.EntityID == this.ID);
        }
    }
}

In terms of returning the Entities...do I need to return POCOs at this point or is it ok to simply return the business object returned from the query? I understand that if I was to try access properties of the returned entity (after the DataContext has been disposed) it would fail. However, this is the reason I have decided to implement these type of methods e.g.

Instead of:

AnEntity entity = null;
using (var repo = new EntityRepo())
{
    entity = repo.GetEntity(12345);   
}
var related = entity.RelatedEntities; // this would cause an exception

In theory I should be able to do:

AnEntity entity = null;
using (var repo = new EntityRepo())
{
    entity = repo.GetEntity(12345);   
}
var related = entity.GetRelatedEntities();

Given the circumstances of my particular app (needs to work in a windows service & web application) I would like to know if this seems a plausible approach, whether there are obvious flaws and if there are better approaches for what it is I am trying to do.

Thanks.

Answer 1

Generally speaking, as long as you are not calling a single DataContext object using more than one thread, you should be OK. In other words, use one DataContext object per thread, and do not share data or state between different DataContext objects.

The remaining multi-threaded issues have to do with concurrency in the database, not threading operations.

Other than these caveats, your approach seems sound. You can either use partial classes to implement your business methods, or you can add a business layer between the Linq to SQL classes and your repository.

Answer 2

You can get away with this:

var repo = new EntityRepo();

entity = repo.GetEntity(12345);   

var related = entity.RelatedEntities;

See this StackOverflow post for an explanation why not disposing your context doesn't cause connection leaks or stuff like that.

The repository and context will get cleaned up by the garbage collector when the entities that were fetched by them fall out of scope (when building a website, at the end of the request).

Edit: MSDN documents that connections are opened as late as possible and closed as soon as possible. So skipping the using doesn't causes connection pool problems.