Asked by:
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
andSchema2.DbContext2
.Now I need to do a join on two entities,
EntityA
fromSchema1.DbContext1
andEntityB
fromSchema2.DbContext2
.
- Edited by silverbyte1 Wednesday, March 30, 2016 9:42 AM
Tuesday, March 29, 2016 9:42 PM
All replies
-
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.Wednesday, March 30, 2016 3:28 AM -
Wow. But there must be a way....
Wednesday, March 30, 2016 7:50 AM -
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
- Edited by Zhanglong WuMicrosoft contingent staff Wednesday, March 30, 2016 8:49 AM
Wednesday, March 30, 2016 8:48 AM -
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"
- Edited by silverbyte1 Wednesday, March 30, 2016 10:00 AM
Wednesday, March 30, 2016 9:47 AM -
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
Thursday, March 31, 2016 1:20 AM -
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.
- Edited by silverbyte1 Thursday, March 31, 2016 9:43 AM
Thursday, March 31, 2016 9:39 AM -
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
Thursday, April 7, 2016 9:39 AM