LINQ Datacontext Data Warehouse Issues
I get Cannot access object: DataContext after it been disposed
in the following DAL method. I thought I'd be fine if you wanted to be there. result
is IEnumurable
, and I thought it was IQueryable
, caused such problems.
What am I doing wrong? HOW TO DO I delete mine DataContext
. Is there something better to come back and then DataTable
? It is a desktop application that points to SQL 2005.
An example of a method that raises 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 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);
}
a source to share
You call CopyLinqToDataTable
after deleting DataContext. Because LINQ uses deferred execution, it only executes the query when result
enumerated after the DataContext is placed.
You have to put your DataContext in a block using
containing a statement return
.
This will output the DataContext after the call CopyLinqToDataTable
, avoiding the problem.
For instance:
using(var DB = CmoDataContext.Create()) {
//Execute queries
return result.CopyLinqToDataTable();
}
The statement using
generates a block finally
that provides a DataContext at the end of the block using
. (Even if an exception is thrown)
a source to share