none
join on two tables from different DbContext and schema RRS feed

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



    • 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


    Wednesday, March 30, 2016 8:48 AM
    Moderator
  • 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
    Moderator
  • 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
    Moderator