When can I dispose of my DataContext?

Question

Take the following example:

MyDataContext context = new MyDataContext(); // DB connection established.
MyTableRecord myEntity = myDataContext.Table.FindEntity(12345); // retrieve entity

Say my entity has relationships with other tables which I would access via

foreach (var record in MyEntity.RelatedTable)

Do I need to keep my DataContext alive after the 2nd line in order to access the properties of the entities or is it safe enough to dispose of?

I understand Linq to SQL uses delayed execution hence I am wondering if it only uses delayed execution when you initially retrieve the entity or whether it uses this when accessing the related table records aswell.

Example

var userRepo = new UserRepository(); // creates new DataContext
var auditRepo = new AuditRepository(); // creates new DataContext
var activeUsers = userRepo.FindActiveUsers();
foreach (var user in activeUsers)
{
    // do something with the  user
    var audit = new Audit();
    audit.Date = DateTime.Now;
    audit.UserID = user.ID;
    auditRepo.Insert(audit);
}

My insert method in my repo calls SubmitChanges. So is the above acceptable, or is this a waste of a connection. Should I realistically do:

var userRepo = new UserRepository();
var activeUsers = userRepo.FindActiveUsers();
foreach (var user in activeUsers)
{
    // do something with user
    var audit = new Audit();
    audit.Date = DateTime.Now;
    audit.UserID = user.ID;
    user.Audits.Add(audit);
    userRepo.Save();
}

To re-use the already open DataContext? What would you do in situations where you open a high-level datacontext and then had to do some processing low level, should I pass the userRepo down or should I create a separate Repository?

Answer 1

You don't need to dispose of the DataContext as it's a lightweight object and all internal db connections are cached. If you do dispose of it you won't be able to access the related entities.

Answer 2

To access fields in other tables you will need to keep the DataContext alive. Also, don't call dispose directly, use the using keyword.

using (MyDataContext context = new MyDataContext()) // DB connection established.
{
    MyTableRecord myEntity = myDataContext.Table.FindEntity(12345); // retrieve entity

    foreach (var record in MyEntity.RelatedTable)
    {
        ...
    }
}

Answer 3

Check hear if you hawe webproject: http://www.west-wind.com/weblog/posts/246222.aspx