Creating a multi-database Entity Framework Model

entity-framework multiple-databases sql-server synonym

Question

Is it OK to do something like

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

and after that change the edmx file of Entity Framework so that it reads this object just like any other table?

I ran a brief test to see if it would work for choosing and updating, and it seems to do so without issue. However, I was curious if there was any reason why I shouldn't be doing this.

By generating an edmx file referring to the second database, setting up the entities there, and then copying and pasting the definition into the first database's edmx file, I am able to get the table definition.

UPDATE

If anybody is interested, I described how I created an edmx file that spans many databases in my blog post here. It has scripts for merging edmx files and creating synonyms.

1
29
5/25/2011 3:38:15 AM

Accepted Answer

If you conducted a test and it was successful, you most likely discovered something new. It is not feasible to utilize a single model with two databases up to this point, I always responded to this kind of inquiry (with some more ugly workaround based on views hiding tables from the second database). I'm aware of two workarounds presently.

The one drawback to this strategy is that if you run model update using a database, any manual adjustments you made to the SSDL portion of your EDMX are always lost. This entails either developing EDMX manually (which is difficult work), or utilizing a tool or script that will incorporate your modifications upon each update from the database.

11
5/17/2011 8:04:04 PM

Popular Answer

Views may also be used for this (and a linked server if the other db is on a different server). By doing this, you won't need to manage or combine two different edmx files. I tested this out quickly to check whether updates, inserts, and deletes were possible, and they are. I've used this with a connected server to retrieve data from a second database on a separate server.

The information pertaining to dispersed transactions may be accurate, inaccurate, or a mix of both as I have no experience with them. The distributed transactions described in I ASSUME no longer apply if your two databases are on the same server.

When utilizing a connected server, there are a few things to remember.

  1. When you call and change the entities in the connected database tables,SaveChanges Make sure the two servers are set up to handle distributed transactions since this will attempt to initiate a distributed transaction on your context until someone understands how to stop it. (I would anticipate that using synonyms would also confirm this).
  2. Because ef attempts to get the new id via the associated server's identity fields, inserts on entities containing such columns throw an error.SCOPE_IDENTITY() It is also null. I'm not sure whether there's a workaround for this. On the connected server with identity columns, changing or removing entities wasn't problematic for me.

Within SQL Server A

  1. If the databases are already on ServerB, avoid creating a connected server.
  2. build a view in[ServerA].[MyDB] every table in[ServerB].[AnotherDB] You want access.

For EDMX

  1. Include your opinions in the edmx file.
  2. Remove the entity key setting from each designer property (including the actual pk)
  3. Set the real PK's entity key to default.
  4. As necessary, add associations.
  5. Save changes

Updates, Inserts, and Deletions

  1. To open your edmx file using an xml editor, right-click it.
  2. Explore theStorageModel -> Schema -> EntityContainer
  3. Locate and remove the entityset for your entity.DefiningQuery element
  4. Get thestore:Schema Set and delete an entity's attributestore: to make it fairSchema Keep its worth untouched.
  5. For each view from the connected server, repeat steps 3 and 4
  6. Save changes

I was required to make a few changes to the connected server since doing so results in a distributed transaction.ObjectContext before SaveChanges was prosperous.

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

You can definitely design a uniqueObjectContext and prevailSaveChanges to include all of this.



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