title: Simulating Cross Context Joins--LINQ/C#
body: <p>Here's the issue:</p>
  
  <p>I have 2 data contexts that I would like to do a join on. Now I know that LINQ doesn't allow joins from one context to another, and I know that 2 possible solutions would be to either create a single datacontext or to have 2 seperate queries (which is what I'm doing for now).  However what I would like to do is to "simulate" a join.</p>
  
  <p>Here's what I've tried.</p>
  
  <pre><code>using (var _baseDataContext = Instance)
  {
      var query = from a in _baseDataContext.Account.ACCOUNTs
                  where a.STR_ACCOUNT_NUMBER.ToString() == accountID
                  join app in _baseDataContext.Account.APPLICATIONs on a.GUID_ACCOUNT_ID equals
                              app.GUID_ACCOUNT
                  join l in GetLoans() on app.GUID_APPLICATION equals l.GUID_APPLICATION
                  select l.GUID_LOAN;
  
      return query.Count() &gt; 0 ? query.First() : Guid.Empty;
  }
  
  private static IQueryable&lt;LOAN&gt; GetLoans()
  {
      using (var _baseDataContext = Instance)
      {
          return (from l in _baseDataContext.Loan.LOANs
                  select l).AsQueryable();
      }
  }
  </code></pre>
  
  <p>In run time I get is </p>
  
  <p>System.InvalidOperationException: The query contains references to items defined on a different data context</p>
  
  <p>EDIT:</p>
  
  <p>Working Solution:</p>
  
  <pre><code>using (var _baseDataContext = Instance)
  {
      var query = from a in _baseDataContext.Account.ACCOUNTs
                  where a.STR_ACCOUNT_NUMBER.ToString() == accountID
                  join app in _baseDataContext.Account.APPLICATIONs on a.GUID_ACCOUNT_ID equals
                             app.GUID_ACCOUNT
                  join l in GetLoans() on app.GUID_APPLICATION equals l.GUID_APPLICATION 
                  select l.GUID_LOAN;
  
       return (query.Count() &gt; 0) ? query.First() : Guid.Empty;
  }
  
  private static IEnumerable&lt;LOAN&gt; GetLoans()
  {
      using (var _baseDataContext = Instance)
      {
          return (from l in _baseDataContext.Loan.LOANs
                  select l).AsQueryable();
      }
  }
  </code></pre>