LINQ Datacontext Disposal Issues
- by Refracted Paladin
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);
}
