How to join two table from two different edmx using linq query

edmx entity-framework linq

Question

How to join two table from two different edmx using linq query.. Is there a way to query from 2 different edmx at a time.

Thanks.

1
3
6/6/2013 6:07:53 AM

Popular Answer

Update

As per your comment, EF wasn't able to parse a combined Expression tree across 2 different contexts.

If the total number of records in the tables is relatively small, or if you can reduce the number of records in the join to a small number of rows (say < 100 each), then you can materialize the data (e.g. .ToList() / .ToArray() / .AsEnumerable()) from both tables and use the Linq join as per below.

e.g. where yesterday is a DateTime selecting just a small set of data from both databases required for the join:

var reducedDataFromTable1 = context1.Table1
                                    .Where(data => data.DateChanged > yesterday)
                                    .ToList();
var reducedDataFromTable2 = context2.Table2
                                    .Where(data => data.DateChanged > yesterday)
                                    .ToList();
var joinedData = reducedDataFromTable1
                         .Join(reducedDataFromTable2,
                               t1 => t1.Id,    // Join Key on table 1
                               t2 => t2.T1Id,  // Join Key on table 2
                               (table1, table2) => ... // Projection
                               );

However, if the data required from both databases for the join is larger than could reasonably expected to be done in memory, then you'll need to investigate alternatives, such as:

  • Can you do the cross database join in the database? If so, look at using a Sql projection such as a view to do the join, which you can then use in your edmx.
  • Otherwise, you are going to need to do the join by manually iterating the 2 enumerables, something like chunking - this isn't exactly trivial. Sorting the data in both tables by the same order will help.

Original Answer

I believe you are looking for the Linq JOIN extension method

You can join any 2 IEnumerables as follows:

var joinedData = context1.Table1
                         .Join(context2.Table2,
                               t1 => t1.Id,    // Join Key on table 1
                               t2 => t2.T1Id,  // Join Key on table 2
                               (table1, table2) => ... // Projection
                               );

Where:

  • Join Key on table 1 e.g. the Primary Key of Table 1 or common natural key
  • Join Key on table 2, e.g. a Foreign Key or common natural key
  • Projection : You can whatever you want from table1 and table2, e.g. into a new anonymous class, such as new {Name = table1.Name, Data = table2.SalesQuantity}
5
6/6/2013 4:01:03 PM


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