Different DataContext error. Why is this a problem?

Question

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, 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;
    }

Answer 1

Hey,

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.

Answer 2

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;
    }

Answer 3

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.