LINQ query issue in DataContext

Question

 var activecommcontacts = 
   CorporateCalendarDataContext.ActiveCommunicationContacts.Where(p=>systemUserMinistries.Contains(p.MinistryId)).Select(p => p);                                  
    var distinctcomm = 
   CorporateCalendarDataContext.ActiveDistinctCommunicationContacts.Select(r => r);        

    IQueryable<ActiveDistinctCommunicationContact> options   
             = (from orig in distinctcomm   
                      let userid = orig.SystemUserId   
                      let name = orig.Name   
                      let nameandnumber = orig.NameAndNumber   
                      from b in activecommcontacts   
                      let actuserid = b.SystemUserId  
                      where userid == actuserid  
                      select new ActiveDistinctCommunicationContact  
                                 {  
                                     Name = name,  
                                     SystemUserId = userid,  
                                     NameAndNumber = nameandnumber  
                                 }).AsQueryable();        

I am getting a SystemInvalidOperationException with the message
"The query contains references to items defined on a different data context."

I even tried to work around it by doing it this way:

IQueryable<ActiveDistinctCommunicationContact> options = 
(from distinctcomm in CorporateCalendarDataContext.ActiveDistinctCommunicationContacts
 from activecommcontacts in activecommcontacts
 where activecommcontacts.SystemUserId == distinctcomm .SystemUserId
 select new 
    { 
        distinctcomm .SystemUserId, 
        distinctcomm .Name, 
        distinctcomm.NameAndNumber 
    }).AsQueryable();                                      

I am getting a SystemInvalidOperationException with the message

"The query contains references to items defined on a different data context."

I wanted to point out that ActiveDistinctCommunicationContact and ActiveCommunicationContact are VIEWS in the same datacontext CorporateCalendarDataContext.
I even refreshed my DBML in the designer.
But I keep getting this annoying exception "The Query contains references to items on a different data context". I am not understanding why this is happening when the views belong to the same data context. I went to some of these issues on stackoverflow.com but I just cannot get this query to work. Can you help please.

public List<int> GetSystemUserMinistryList() {
    var dc = new CorporateCalendar.Data.CorporateCalendarDataContext(); //"param1", "param2", "param3");
    List<int> userMinistries = new List<int>();
    var systemUserMinistries = dc.GetTable<CorporateCalendar.Data.ActiveSystemUserMinistry>();
    foreach (CorporateCalendar.Data.ActiveSystemUserMinistry activeSystemUserMinistry in systemUserMinistries) { 
        if (activeSystemUserMinistry.SystemUserId == this.Id) {
            userMinistries.Add(activeSystemUserMinistry.MinistryId);
        }
    }

    return userMinistries;
}

private static CorporateCalendarDataContext CorporateCalendarDataContext {
    get { 
        var dc = new CorporateCalendarDataContext(); //"param1", "param2", "param3");
        return dc;
    }
}

Answer 1

If I have understood your question and updates correctly...

I think it looks like you have the property:

private static CorporateCalendarDataContext CorporateCalendarDataContext {
    get {
        var dc = new CorporateCalendarDataContext(); //"param1", "param2", "param3");
        return dc;
    }
}

so every time you get that property you are getting a new context instance created so even though these two lines for example:

var activecommcontacts = CorporateCalendarDataContext.ActiveCommunicationContacts.Where(p=>systemUserMinistries.Contains(p.MinistryId)).Select(p => p);                                  
var distinctcomm = CorporateCalendarDataContext.ActiveDistinctCommunicationContacts.Select(r => r);  

are using the same property, they will have a different context, hence the exception you are seeing.

You need to keep the context around for longer than you are doing so either do this:

var context = CorporateCalendarDataContext;
var activecommcontacts = context.ActiveCommunicationContacts.Where(p=>systemUserMinistries.Contains(p.MinistryId)).Select(p => p);                                  
var distinctcomm = context.ActiveDistinctCommunicationContacts.Select(r => r);  
// This context should be used for anything that you want to join together in the same query ...      

or make that property store the context once it creates it, like this:

private static CorporateCalendarDataContext _context;
private static CorporateCalendarDataContext CorporateCalendarDataContext {
    get {
        if(_context == null)
            _context = new CorporateCalendarDataContext(); //"param1", "param2", "param3");
        return _context;
    }
}

Whatever you feel happier with and that fits your application design.

I am concerned that it is a static property, however, so that last approach may cause you additional issues if you keep the context for too long.

You need to be careful with data contexts and their lifetimes as they are tricky but there is a lot of information available on them. Take a look at this blog article, for example: Entity Framework Context Lifetime Best Practices and this post: How to decide on a lifetime for your objectcontext.