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();
    }
}

Throw

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!

Anderson

I tried returning IEnumerable<LOAN> previously but recieved a casting error as expected. However I had not thought of returning an IEnuerable<LOAN> AsQueryable. – Throw

one potential problem with this approach is that you aren't doing a pure join, therefore if you need to add another join off of the loan context your wont be able to from what I can see. – Throw

Okay worked it out. We can remove the where clause on the Method join and instead of doing a select from the Method just do a pure join. The trick here is the method has to return an IEnumerable<T>.AsQueryable This allows us to do cross context joins, and thus far I'm not seeing any performance hits. – Throw

Answer 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.

Saucedo

While that is a good idea however in this case that is not an option. Thank you for the suggestion however. – Throw

Answer 3

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.

Sailfish

creating a seperate datacontext where just need the two tables is a possiblity, however this isn't an isolated need, its just a single example of a need for this solution. I want to avoid creating an isolated datacontext everytime I need to meet this need. – Throw

Answer 4

You could create a view in database B that represents the table in database A. Then in context B model that view so you can join tables

Create View vw_Stuff AS
Select prop1,
       prop2 
from otherDb..Stuff

Riyal