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

I favor creating a separate data context that contains just the two tables you want to join. But I suppose you could maintain a temporary table (containing data from the first context) in the second context, and then join the temporary table.

Answer 2

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!

Answer 3

This is the "work around" that we have found...

We built our tables from the other database out manually and if it is on the same server then we prefixed the table name with:

<DatabaseName>.<SchemaName>.<YourTableName>

if they are on a linked server then you have to prefix it with the server name as well:

<ServerName>.<DatabaseName>.<SchemaName>.<YourTableName>

This will allow you to do joins and still return an non executed IQueryable... which is what we wanted. The other 2 ways in involve joining in-memory IEnumerables which means your pull all records for each before doing the join (above) and doing an IQueryable join using a contains method which has limitations...

Hopefully in the future the DataContext will be built smart enough to know that if the servers are linked then you can do joins between two different ones.