Simulating Cross Context Joins--LINQ/C#

Question

Here's the issue:

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.

Here's what I've tried.

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() > 0 ? query.First() : Guid.Empty; }  private static IQueryable<LOAN> GetLoans() {     using (var _baseDataContext = Instance)     {         return (from l in _baseDataContext.Loan.LOANs                 select l).AsQueryable();     } } 

In run time I get is

System.InvalidOperationException: The query contains references to items defined on a different data context

EDIT:

Working Solution:

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() > 0) ? query.First() : Guid.Empty; }  private static IEnumerable<LOAN> GetLoans() {     using (var _baseDataContext = Instance)     {         return (from l in _baseDataContext.Loan.LOANs                 select l).AsQueryable();     } } 

Answer 1

Maybe something like this can get you started in the right direction. I made a mock database with similar columns based on your column names and got some results.

    class Program {     static AccountContextDataContext aContext = new AccountContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True");     static LoanContextDataContext lContext = new LoanContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True");      static void Main()     {          var query = from a in aContext.ACCOUNTs                     join app in aContext.APPLICATIONs on a.GUID_ACCOUNT_ID equals app.GUID_ACCOUNT                     where app.GUID_APPLICATION.ToString() == "24551D72-D4C2-428B-84BA-5837A25D8CF6"                     select GetLoans(app.GUID_APPLICATION);          IEnumerable<LOAN> loan = query.First();         foreach (LOAN enumerable in loan)         {             Console.WriteLine(enumerable.GUID_LOAN);         }          Console.ReadLine();     }      private static IEnumerable<LOAN> GetLoans(Guid applicationGuid)     {         return (from l in lContext.LOANs where l.GUID_APPLICATION == applicationGuid select l).AsQueryable();     } } 

Hope this helps!