Simulating Cross Context Joins--LINQ/C#

Solution 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!

Solution 2:

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.