LINQ Datacontext Disposal Issues

Question

I am getting a Cannot access object: DataContext after it's been disposed in the below DAL method. I thought that I would be okay calling dispose there. result is an IEnumurable and I thought it was IQueryable that caused these kinds of problems.

What am I doing wrong? How SHOULD I be disposing of my DataContext. Is there something better to be returning then a DataTable? This is a Desktop app that points at SQL 2005.

Example method that causes this error -->

---

    public static DataTable GetEnrolledMembers(Guid workerID)
    {
        var DB = CmoDataContext.Create();

        var AllEnrollees = from enrollment in DB.tblCMOEnrollments
                           where enrollment.CMOSocialWorkerID == workerID || enrollment.CMONurseID == workerID
                           join supportWorker in DB.tblSupportWorkers on enrollment.EconomicSupportWorkerID
                               equals supportWorker.SupportWorkerID into workerGroup
                           from worker in workerGroup.DefaultIfEmpty()
                           select
                               new
                               {
                                   enrollment.ClientID,
                                   enrollment.CMONurseID,
                                   enrollment.CMOSocialWorkerID,
                                   enrollment.EnrollmentDate,
                                   enrollment.DisenrollmentDate,
                                   ESFirstName = worker.FirstName,
                                   ESLastName = worker.LastName,
                                   ESPhone = worker.Phone
                               };

        var result = from enrollee in AllEnrollees.AsEnumerable()
                     where (enrollee.DisenrollmentDate == null || enrollee.DisenrollmentDate > DateTime.Now)
                     //let memberName = BLLConnect.MemberName(enrollee.ClientID)
                     let lastName = BLLConnect.MemberLastName(enrollee.ClientID)
                     let firstName = BLLConnect.MemberFirstName(enrollee.ClientID)
                     orderby enrollee.DisenrollmentDate ascending, lastName ascending
                     select
                         new
                         {
                             enrollee.ClientID,
                             //MemberName = memberName,
                             LastName = lastName,
                             FirstName = firstName,
                             NurseName = BLLAspnetdb.NurseName(enrollee.CMONurseID),
                             SocialWorkerName = BLLAspnetdb.SocialWorkerName(enrollee.CMOSocialWorkerID),
                             enrollee.EnrollmentDate,
                             enrollee.DisenrollmentDate,
                             ESWorkerName = enrollee.ESFirstName + " " + enrollee.ESLastName,
                             enrollee.ESPhone
                         };
        DB.Dispose();

        return result.CopyLinqToDataTable();
    }

partial class where I create the DataContext -->

---

partial class CmoDataContext
{
    public static bool IsDisconnectedUser
    {
        get
        {
            return Settings.Default.IsDisconnectedUser;
        }
    }

    public static CmoDataContext Create()
    {
        var cs = IsDisconnectedUser ? Settings.Default.CMOConnectionString : Settings.Default.Central_CMOConnectionString;
        return       new CmoDataContext(cs);
    }

Answer 1

You're calling CopyLinqToDataTable after disposing the DataContext. Since LINQ uses deferred execution, it only executes the query when result is enumerated, after the DataContext is disposed.

You should put your DataContext in a using block that contains the return statement.
This will dispose the DataContext after calling CopyLinqToDataTable, avoiding the problem.

For example:

using(var DB = CmoDataContext.Create()) {
    //Execute queries
    return result.CopyLinqToDataTable();
}

The using statement generates a finally block that disposes the DataContext at the end of the using block. (Even if an exception is thrown)

Answer 2

You should use something like ToList in queries before calling Dispose

Answer 3

Can't you just do this instead?

...
DataTable dt = result.CopyLinqToDataTable(); 
DB.Dispose(); 
return dt;