Making an Entity Framework Model span multiple databases

Question

Is it valid to do something such as

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

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

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.

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.

Answer 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.

Answer 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.

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.

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

1. 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).
2. 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.

On SQL Server A

1. create a linked server to ServerB (skip this if db's are on the same server).
2. create a view in [ServerA].[MyDB] for each table in [ServerB].[AnotherDB] you want to access

In EDMX

1. Add your views to the edmx file
2. Clear the entity key setting from each property in the designer (including the actual pk)
3. Reset the entity key for the actual pk
4. Add associations as needed
5. Save changes

For Updates/Inserts/Deletes

1. right click on your edmx file and open with xml editor
2. Navigate to the StorageModel -> Schema -> EntityContainer
3. Find the entityset for your entity and delete the DefiningQuery element
4. Find the store:Schema attribute on the entity set and remove store: so that it is just Schema. Leave its value alone.
5. Repeat steps 3 & 4 for each view from the linked server
6. 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.

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.