Different DataContext error. Why is this a problem?
The following code is causing a "This query contains references to items defined on a different data context" error. My 2 data contexts are created with 2 nested using blocks around the code that calls this method and displays its results on screen. The methods that this method calls only use the data context passed in to them,开发者_Go百科 they don't create any of their own. I have verified that they are ok by inserting a extra return statement right before the one in the method below and I don't get any problems, which leads me to believe the problem is in the LINQ statement on the return line... What am I doing wrong?
public static IQueryable<tblSurveyor> GetPossibleSurveyorsForSurvey(SurveyDataContext surveyContext,
FINDataContext finContext, int surveyID)
{
IQueryable<tblSurveyor> currentSurveyors =
GetSurveyorsForSurvey(surveyContext, surveyID);
tblSurvey currentSurvey = GetSurvey(surveyContext, surveyID);
tblLocContact facility = GetFacility(finContext, currentSurvey.FacilityID);
IQueryable<tblSurvey> surveysInState = GetSurveysInState(surveyContext, finContext,
facility.State);
return from task in surveyContext.tblSurveyor_Tasks
from surveys in surveysInState
from cSurveyor in currentSurveyors
from surveyors in surveyContext.tblSurveyors
where surveyors.SurveyorID != cSurveyor.SurveyorID &&
surveys.SurveyID == task.SurveyID &&
task.SurveyorID == surveyors.SurveyorID
select surveyors;
}
I've changed a few things, and most notable I got rid of the IQueryable variables and made them arrays. This was primairly to force enumeration as I went. This revealed that the problem (or at least one problem in in this method).
tblSurvey[] surveysInState = GetSurveysInState(surveyContext, finContext,
state).ToArray();
Here is that method's implementation. I still don't see the problem with it.
public static IQueryable<tblSurvey> GetSurveysInState(SurveyDataContext surveyContext,
FINDataContext finContext, string state)
{
return from survey in surveyContext.tblSurveys
from facility in finContext.tblLocContacts
where survey.FacilityID == facility.LocationID && facility.State == state
select survey;
}
You are mixing various DataContex in same query, that's the problem.
LINQ to SQL defer query execution until the data is enumerated, so if you mix datacontexts how would it resolve the query with a SQL statement? It can't resolve it.
A possible solution is to have all related data in the same datacontext or you could enumerate the data and mix it later.
Do either of these:
from surveys in surveysInState
from cSurveyor in currentSurveyors
Come from the FINDataContext or involve it at all? That could be a problem. Or, if you do an assignment from an object from this other data context at all. Additionally, the data context has a Log property; attach a logger to this property, and examine the SQL that it generates, that may give you some clues.
Thanks.
I fixed it. I still don't understand why I had the problem but oh well.
public static IQueryable<tblSurvey> GetSurveysInState(SurveyDataContext surveyContext,
FINDataContext finContext, string state)
{
string[] facility = (from f in finContext.tblLocContacts
where f.State == state
select f.LocationID).ToArray();
return from survey in surveyContext.tblSurveys
where facility.Contains(survey.FacilityID)
select survey;
}
精彩评论