相同的DbContext，使用EF Core的多个数据库

问题描述

在我的应用程序中，我有一些数据存储在不同的数据库中（例如，出于历史原因，身份和用户信息分别存储在与内容相关的内容中）。

In my App, I have some data which are stored on different databases (e.g. identity and User Info are stored separately to Content-related stuff for historical reasons).

但是我希望能够利用EF Core的'.include'方法，但是由于数据存储在不同的DB中，有没有办法在同一DbContext中查询两个数据库？

However I would like to be able to get advantage of the '.Include' method of EF Core, but as the data is stored in a different DB, is there a way to query two databases in the same DbContext?

推荐答案

我看到的一个选择是使用视图将所有信息从第二数据库返回到本地数据库。

One option I am seeing is to use views to get all the information from the 2nd database back to the local database.

如果使用SQL Server作为本地数据库：

If using SQL Server as a local database:

1。创建数据库链接 或

（如果数据库在同一实例上，则可以跳过此步骤）

(This step can be skipped if the database is on the same instance)

SQL Server语法：

SQL Server Syntax:

    sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
       [ , [ @provider= ] 'provider_name' ] [ , [ @datasrc= ] 'data_source' ]
       [ , [ @location= ] 'location' ] [ , [ @provstr= ] 'provider_string' ]
       [ , [ @catalog= ] 'catalog' ]

示例：

EXEC sp_addlinkedserver
   @server=N'RemoteServer',
   @srvproduct=N'',
   @provider=N'SQLNCLI',   -- SQL Server Native Client
   @datasrc=N'RemoteIp\instance1';

天蓝色示例：

------ Configure the linked server
-- Add one Windows Azure SQL DB as Linked Server
EXEC sp_addlinkedserver
  @server='RemoteServer', -- here you can specify the name of the linked server
  @srvproduct='',
  @provider='sqlncli', -- using SQL Server Native Client
  @datasrc = 'myServer.database.windows.net',   -- add here your server name
  @location = '',
  @provstr = '',
  @catalog = 'myDatabase'-- add here your database name as initial catalog(you cannot connect to the master database)

-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'RemoteServer',
    @useself = 'false',
    @rmtuser = 'myLogin',             -- add here your login on Azure DB
    @rmtpassword = 'myPassword'-- add here your password on Azure DB
EXEC sp_serveroption 'RemoteServer', 'rpc out', true;

您甚至可以链接到Excel文件中的数据

You can even link to data from Excel files

2。在本地数据库中创建视图

CREATE VIEW UserObjects AS SELECT Id, UserId, Name, Description FROM RemoteServer.myDatabase.dbo.UserObjects

3。在DbContext中将UserObjects引用为普通表（视图支持INSERT / UPDATE / DELETE，只要它是从单个源表中构建的）

3. Reference UserObjects as a normal table in the DbContext (views support INSERT/UPDATE/DELETE as long as it is built from a single source table See MS Doc)

public DbSet<User> Users {get;set;}
public DbSet<UserObject> UserObjects {get;set;}

然后将远程表用作本地表：

and then use the remote table as a local one:

var user  = await _DbContext.Users.Where(u => u.FirstName = "bob").Include(u => u.Objects);

[警告注意事项] 视图，因此将其链接为外键约束。 ，它可能会或多或少地获得相同的性能提升，但没有约束部分...原因：

[Warning note] There is no way to have a Primary Key on a view, and therefore to link it as a foreign key constraint. It does not seem possible to add an index to a view either, which could have more or less the same performances gain, but not the constraint part... Reason:

视图必须仅引用与视图位于同一数据库中的基表

:(因此，即使您使用必需的 WITH SCHEMABINDING 创建视图，以下操作也将不起作用：

:( Therefore the following will not work, even if you create the view with the required WITH SCHEMABINDING :

CREATE UNIQUE INDEX IDX_UserObjects_PK
    ON UserObjects (Id);
CREATE INDEX IDX_UserObjects_UserId
    ON UserObjects (UserId);

这篇关于相同的DbContext，使用EF Core的多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！