创建一个实体框架模型跨越多个数据库

Is it valid to do something such as

做这样的事有效吗

CREATE SYNONYM [dbo].[MyTable] FOR [AnotherDatabase].dbo.[MyTable]

创建同义词(dbo)。[MyTable][AnotherDatabase].dbo。[MyTable]

and then modify Entity Framework's edmx file to read this object like it would any other table?

然后修改实体框架的edmx文件来读取这个对象，就像其他表一样?

I did a quick sample test and it seems to work fine for selecting and updating, but I wanted to know if there was any reason why I shouldn't be doing this

我做了一个快速的示例测试，它对于选择和更新似乎很有效，但是我想知道是否有什么理由不应该这样做

I am getting the table definition by creating an edmx file pointing to the 2nd database, building out the entities there, then copy/pasting the definition into the 1st database's edmx file.

我通过创建指向第二个数据库的edmx文件来获得表定义，在那里构建实体，然后将定义复制/粘贴到第一个数据库的edmx文件中。

UPDATE

更新

If anyone is interested, I wrote up what I did to make an edmx file span mulitple databases here. It includes scripts for generating synonyms and merging edmx files.

如果有人感兴趣的话，我在这里写下了如何创建一个edmx文件跨区的mulitple数据库。它包含用于生成同义词和合并edmx文件的脚本。

3 个解决方案

#1

If you made a test and it worked you probably showed something nobody else know about. Till now I always answered this type of question: It is not possible to use single model with two databases (with some more ugly workaround based on views hiding tables from the second database). Now I know two workarounds.

如果你做了一个测试，它成功了你可能展示了一些其他人不知道的东西。到目前为止，我一直回答这类问题:不可能在两个数据库中使用单个模型(基于视图对第二个数据库隐藏表)。现在我知道有两个办法。

The only disadvantage of this approach is that all changes made manually to SSDL part of your EDMX are always lost if you run Update model from database. This means either manual development of EDMX (which is quite hard work) or using some tool / script which will add your changes after each update from database.

这种方法的惟一缺点是，如果从数据库运行Update model，则手工对EDMX的SSDL部分进行的所有更改总是丢失。这意味着要么手工开发EDMX(这是相当困难的工作)，要么使用一些工具/脚本，在每次从数据库更新后添加更改。

#2

You can also do this with views (and a linked server if the other db is on a different server). This will keep you from having to manage/merge two separate edmx files. I've used this with a linked server for reading data from a second db on a different server but ran a few quick tests to see if updates/inserts/deletes were possible and they are.

您还可以使用视图(如果另一个db在另一个服务器上，还可以使用链接服务器)来实现这一点。这将使您不必管理/合并两个独立的edmx文件。我已经在一个链接服务器上使用了它，用于从另一个服务器上的第二个db读取数据，但是运行了一些快速测试，看看是否有可能进行更新/插入/删除。

I have zero experience with distributed transactions so the info related to distributed transactions may be good, bad, or a little bit of both. If your two db's are on the same server I ASSUME distributed transactions no longer apply.

我对分布式事务没有任何经验，因此与分布式事务相关的信息可能是好的、坏的，或者两者兼而有之。如果您的两个db在同一个服务器上，我假设分布式事务不再适用。

There are a couple of things to keep in mind when using a linked server.

在使用链接服务器时，需要记住一些事情。

When you modify the entities in the linked db tables and call SaveChanges on your context, this will try to start a distributed transaction so unless anyone knows how to stop that, you need to make sure the two servers are setup to handle distributed transactions. (I would assume this would be true using synonyms too).
当您修改链接的db表中的实体并在上下文中调用SaveChanges时，这将尝试启动一个分布式事务，因此除非有人知道如何停止该操作，否则您需要确保这两个服务器已经设置好以处理分布式事务。(我认为使用同义词也是正确的)。
Inserts on entities with identity columns on the linked server throw an exception because ef tries to get the new id using SCOPE_IDENTITY() and it is null. I don't know if there is a way around this. I didn't have any problems updating or deleting entities on the linked server with identity columns.
将标识列插入到链接服务器上的实体时会抛出一个异常，因为ef试图使用SCOPE_IDENTITY()获取新的id，它是null。我不知道是否有办法解决这个问题。我没有任何问题更新或删除与身份列相连的服务器上的实体。

On SQL Server A

在SQL Server

create a linked server to ServerB (skip this if db's are on the same server).
创建一个到ServerB的链接服务器(如果db在同一台服务器上，跳过这个)。
create a view in [ServerA].[MyDB] for each table in [ServerB].[AnotherDB] you want to access
在[ServerA]中创建一个视图。[MyDB]用于[ServerB]中的每个表。你想要访问

In EDMX

在EDMX

Add your views to the edmx file
将视图添加到edmx文件
Clear the entity key setting from each property in the designer (including the actual pk)
从设计器中的每个属性清除实体键设置(包括实际的pk)
Reset the entity key for the actual pk
重置实际pk的实体键
Add associations as needed
根据需要添加关联
Save changes
保存更改

For Updates/Inserts/Deletes

更新/插入/删除操作

right click on your edmx file and open with xml editor
右键单击您的edmx文件并使用xml编辑器打开
Navigate to the StorageModel -> Schema -> EntityContainer
导航到StorageModel ->模式-> EntityContainer。
Find the entityset for your entity and delete the DefiningQuery element
查找实体的entityset并删除DefiningQuery元素
Find the store:Schema attribute on the entity set and remove store: so that it is just Schema. Leave its value alone.
找到实体集上的store:Schema属性并删除store:因此它只是Schema。别管它的价值。
Repeat steps 3 & 4 for each view from the linked server
对每个来自链接服务器的视图重复步骤3和4
Save changes
保存更改

Because using a linked server creates a distributed transaction I had to do a couple of things on the ObjectContext before SaveChanges was successful.

因为使用链接服务器创建分布式事务，所以在SaveChanges成功之前，我必须在ObjectContext上做一些事情。

ctx.Connection.Open();
ctx.ExecuteStoreCommand("set xact_abort on");
ctx.SaveChanges();
ctx.Connection.Close();

You can probably create a custom ObjectContext and override SaveChanges to add this stuff in.

您可以创建一个自定义ObjectContext并覆盖SaveChanges来添加这些内容。

#3

I've found that this trick with synonyms works perfectly with the "Code first" approach without any manipulation with edmx files!

我发现，同义词的这种技巧在“代码优先”方法中可以很好地工作，而不需要对edmx文件进行任何操作!

The only thing you have to do, is to "bind" your class to appropriate synonym in the OnModelCreating method of your DataContext.

惟一要做的就是将类“绑定”到DataContext的onmodelcreation方法中相应的同义词。

For example, if I have a synonym to table Personnel in another DB (and the class name is also Personnel), and synonym's name is "myschema.MySynonym" then the OnModelCreating method should looks like:

例如，如果我对另一个DB中的表人员有一个同义词(并且类名也是人员)，同义词的名称是“myschema”。那么onmodelcreation方法应该如下:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("myschema");

        modelBuilder.Entity<Personnel>()
            .ToTable("MySynonym");

        Database.SetInitializer<TestSynonymContext>(null);

        base.OnModelCreating(modelBuilder);
    }

#1

#2