LINQ/C# Simulation of Cross Context Joins

c# datacontext entity-framework linq

Question

Here is the problem:

I want to do a join on 2 different data contexts. Now that I'm aware that LINQ forbids joins across contexts, I can choose between two alternative solutions: either build a single datacontext or run two separate queries (which is what I'm doing for the time being). On the other hand, I want to "simulate" a join.

Here are the things 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();
    }
}

When I run, I receive is

The query includes references to objects specified on a separate data context, which is a System.InvalidOperationException.

EDIT:

Working Answer:

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();
    }
}
1
21
5/3/2016 1:44:35 AM

Accepted Answer

Perhaps anything along these lines might point you in the correct path. Based on the names of your columns, I created a dummy database and obtained some findings.

    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 is useful!

15
5/23/2009 10:31:11 PM

Popular Answer

The "workaround" that we have discovered is this.

If the other database is on the same server, we prefixed the table name with: when manually building our tables from it.

<DatabaseName>.<SchemaName>.<YourTableName>

You must also precede it with the server name if they are on a connected server:

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

This will enable joins while still allowing you to return an IQueryable that hasn't been performed, which is what we needed. The other 2 methods entail using the contains method to join an IQueryable, which has drawbacks, and joining in-memory IEnumerables, which requires pulling all records for each before the join (as was done above).

Hopefully, in the future, the DataContext will be developed intelligently enough to understand that you can do joins between two separate servers if they are connected.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow