连接来自不同数据库的多个表 [英] Joining multiple tables from different databases

问题描述

我正在使用ASP.NET核心应用程序，其中使用实体框架与SQL Server数据库进行交互。到目前为止，我正在使用 Scaffold-DbContext 命令从SQL Server数据库中创建新模型。即使我们想基于多个表创建 DbContext ，我也可以在上述命令中使用 -t 标志来实现。

I am working on ASP.NET core application where I am using Entity Framework to interact with SQL server database. so far, I was using Scaffold-DbContext command to create new model from SQL server database. Even if , we wanted to create DbContext based on multiple tables, I could do that using -t flag in above command. Everything happening on single SQL database.

Scaffold-DbContext "Server=XXXXXXXXX;Database=XXXXXXXX;User Id=XXXXX;Password:XXXXXX" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -t Table1 Table2

来自 Scaffold-DbContext参数命令，我找不到指定不同数据库的选项。

From Scaffold-DbContext parameters commands, I couldn't find option to specify different databases.

在新方案中，我必须联接来自不同SQL数据库的2个表。有没有一种方法可以创建由来自不同数据库的2个表组成的模型。两个表都使用它们之间的一对一关系。

In new scenario, I have to join 2 tables from different SQL databases. Is there a way to create model which is consist of 2 tables from different databases. Both tables are using one-to-one relationship between them.

例如，DB1具有table1，DB2具有table2。有没有办法创建由这两个表（table1和table2）组成的 DbContext ？

For example- DB1 has table1 and DB2 has table2. Is there a way to create a DbContext which is consist of these 2 tables (table1 and table2)?

是还有其他方法可以实现来自2个不同数据库的2个表之间的联接吗？

Is there any other way to achieve join between 2 tables from 2 different database?

推荐答案

不，您不能使用EntityFramework做到这一点（既不是6.x也不是Core）。每个数据库都有一个DbContext，您只能在同一DbContext中进行联接。

No, you can't do that with EntityFramework (neither 6.x nor Core). A DbContext is per database and you can only do joins within the same DbContext.

您可以创建一个视图，并将该视图映射到所需的模型，但是必须使用iirc。视图映射仍然在EntityFramework Core的路线图上。

You could create a view and map the view to the models you needs, but iirc. mapping of views is still on the roadmap for EntityFramework Core. May work with EntityFramework 6.x though.

编辑：

至少可以与EntityFramework 6.x一起使用。您无法使用Linq / Fluent API做到这一点。不过，您可以执行原始查询。要点：项目必须与模型完全匹配，模型中不应缺少任何字段。临时映射到非实体在EntityFramework Core未来版本的路线图上

At least you can't doing it with Linq/Fluent api. You can execute raw queries though. The catch: The project must match the model exactly, there can't be any missing fields of the model. The Ad-hoc mapping to non-entities is on the roadmap for future versions of EntityFramework Core

这篇关于连接来自不同数据库的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！