如何使用linq查询从两个不同的edmx加入两个表

问题描述：

如何从两个不同的edmx使用linq查询来连接两个表
有一种方法可以从两个不同的edmx一次查询。

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.

谢谢。

答

更新

根据您的评论，EF无法在两个不同的上下文中解析组合的表达式树。

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

如果表中的记录总数相对较少，或如果可以将连接中的记录数减少到少量行（例如每个 .ToList（）/ .ToArray（）/。 AsEnumerable（）），并使用下面的 Linq join 。

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.

如其中昨天是一个 DateTime 只需从连接所需的两个数据库中选择一小组数据：

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:

可以在数据库中进行跨数据库连接吗？如果是这样，请查看使用Sql投影（如视图）进行加入，然后您可以在edmx中使用。

否则，您将需要执行加入手动迭代2个枚举，像分块 - 这并不完全平凡。

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.

原始答案

我相信你正在寻找Linq JOIN扩展方法

I believe you are looking for the Linq JOIN extension method

您可以加入任何2 IEnumerables ，如下所示：

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
                               );

其中：

加入关键表1例如表1的主键或常用的自然
键

加入表2的键，例如一个外键或常用的自然键

投影：你可以从table1和table2任何你想要的，例如
转换成新的匿名类，例如 new {Name = table1.Name，Data = table2.SalesQuantity}

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}

如何使用linq查询从两个不同的edmx加入两个表

相关推荐