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.

Mackenzie

The main downside I see is the need to manually manipulate the edmx, and losing the ability to update the edmx automatically to pull in db schema changes. There may be other downsides as well. – Pentecostal

@carmainc: That is a minor annoyance, but to me it beats having to manually create links between the two data contexts and having to hit the server multiple times to get the linked data. Instead I just have to update my 2nd edmx file and copy/paste the new information over. – Mackenzie

Rachel, I came across your post. I'm wondering if you had encountered an issue aside from the ones already listed in your post... I'm considering your approach for the ERP being developed at the company I work for... Thank you!!! – Notation

Glad you like it! I haven't encountered any problems with it yet, but I also haven't used it for anything advanced since I'm fairly new to EF myself. The only thing that I know needs fixing one day is the Merge script needs to account for deleted items. I know how to fix it, but I haven't bothered to actually update the script since I haven't had a need for it yet – Mackenzie

If you alter your concrete model entities, for ie adding a Tostring() method, any update of the edmx also overwrites these changes. So the ability to update the edmx, is something I give up ages ago... :) – Anastatius

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.

Coffeng

The fact you're unaware of it makes me think there's something wrong with the idea... I thought you knew everything about EF! :) Thank you though – Mackenzie

This is more about knowing features of database server and I really don't know everything about EF. I'm still learning ... – Coffeng

I actually put together a script that will merge the edmx files without overwriting changes you've done. So my final result is one project holding my working edmx, one project to hold my 2nd database's edmx, and a 3rd project to run a script that will merge edmx#2 with edmx#1. – Mackenzie

@Rachael, that is exactly what I just implemented (merging of two edmxs into a new, third one), I estimated it could work, but didn't have any proof until now. :-) – Trantham

I think the answer should mention adding more schemas to the list of schemas where EF will search for objects to add - as the solution. – Amoy

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.

Overmatch

That was what I didn't want to do... it is a whole lot of extra work. You need to create your views, create your PKs, find and modify the DefiningQuery, and re-create any links that exist on your linked database. I'd rather just update my EDMX file and run a merge script (makes maintenance simple and easy, even for those who don't know much about EF). Good information for if your database doesn't support synonyms though. – Mackenzie

Answer 3

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

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

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:

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

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

        Database.SetInitializer<TestSynonymContext>(null);

        base.OnModelCreating(modelBuilder);
    }

Tantalus