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.
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.
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.
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.
SaveChangeson 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).
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
[ServerA].[MyDB]for each table in
[ServerB].[AnotherDB]you want to access
store:Schemaattribute on the entity set and remove
store:so that it is just
Schema. Leave its value alone.
Because using a linked server creates a distributed transaction I had to do a couple of things on the
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.