CODEWITHSUNDEEP
c#linq-to-sqldatacontext
James
James

Reputation: 82096

When can I dispose of my DataContext?

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?

Upvotes: 1

Views: 615

Answers (2)

Mark Byers
Mark Byers

Reputation: 838066

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)
    {
        ...
    }
}

Upvotes: 4

user226722
user226722

Reputation:

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.

Upvotes: 3

Related Questions