join on two tables from different DbContext and schema

Question

In an app with EF6.0, I have two DbContext's created by model first, Schema1.DbContext1 and Schema2.DbContext2.

Now I need to do a join on two entities, EntityA from Schema1.DbContext1 and EntityB from Schema2.DbContext2.

Answer 1

I'm using EF6 with model first, need to join two tables which are in two different DbContext and on different schemas.

Is this possible?

No not that I know about.

Answer 2

Wow. But there must be a way.... 

Answer 3

Hi silverbyte1,

>> I'm using EF6 with model first, need to join two tables which are in two different DbContext and on different schemas.

According to your description, you could define two query from different DbContext, and then combine them.  I create a simple demo for your reference.

using (var dbt = new TestContext())
 using (var db = new DemoContext())
            {
                var query = from p in db.Projects
                           select p;
                var tquery = from q in dbt.Banks
                             select q;
                var result = from a in query
                             join b in tquery on a.ProjectId equals b.BankName
                             select new
                             {
                                 a.ProjectId,
                                b.BankAddress
                            };                          
            }

I hope it’s helpful to you.

Best regards,

Cole Wu

Answer 4

You can't query from two different contexts, it throws an error:

"Additional information: The specified LINQ expression contains references to queries that are associated with different contexts"

Answer 5

Hi silverbyte1,

Sorry, my mistake. try modify the code this. adding ToList() at the end of query, which could retrieve the data from the database.

using (var dbt = new TestContext())
            using (var db = new DemoContext())
            {
                var query = (from p in db.JoinTables
                            select p).ToList();
                var tquery = (from q in dbt.Banks
                             select q).ToList();
                var result = from a in query
                             join b in tquery on a.Id equals b.Id
                             select new
                             {
                                ta = a.TableAddress,
                                ba = b.BankAddress,
                                pn = a.TableName,
                                bn = b.BankName
                             };
                foreach (var item in result)
                {
                    Console.WriteLine("{0} -- {1}", item.ta, item.bn);
                }
                Console.ReadKey();

Best regards,

Cole Wu

Answer 6

Thanks, but that's not what I'm looking for.

Your example runs the join in memory, and not on DB, which is much more optimal.

Answer 7

Hi silverbyte1,

>>Your example runs the join in memory, and not on DB, which is much more optimal.

if you want to run the join on database, you could use raw SQL statement (which don't need different DBContext ) like this:

var query = db.Database.SqlQuery<UnionQuery>("select a.Tag,b.BankName from MyDb.dbo.Areas a, Test.dbo.Bank b where a.Id = b.Id");
                foreach (var item in query)
                {
                    Console.WriteLine("{0} - {1}", item.Tag, item.BankName);
                }
                Console.ReadKey();

UnionQuery

  public class UnionQuery
    {
        public string Tag { get; set; }
        public string BankName { get; set; }
    }

Best regards,

Cole Wu