ADO.Net Entity Framework across multiple databases

Question

In Linq2Sql you can connect a data context to multiple databases by just adding the database name to the source.

Is there a way to achieve this in Linq 2 Entities / ADO.net Entity Framework? Can the database name somewhere be added to the table name?

I tried to change the 'Schema' from dbo to MyDatabase.dbo, but the EF encapsulates this into

SELECT FROM [MyDatabase.dbo].[MyTable]

Trickery like changing 'Schema' to 'MyDatabase].[dbo' will be escaped into

SELECT FROM [MyDatabase]].[dbo].[MyTable]

(Mind the two ]].)

Caryopsis

Answer 1

First, this isn't officially supported.

The answer you link to for LINQ to SQL is just using the ability of the DB server to do heterogeneous queries. I don't see why that wouldn't work for the Entity Framework, also, as it's a DB server feature, not a feature of either framework. In other words, LINQ to SQL is still dealing with the connection as if only one database server were involved. Bear in mind, however, that not all DB servers can do this.

As far as what to change in the EDMX, look for the Schema attribute of the EntitySet node in the "SSDL content" section.

One caveat about this technique is that when you update your model from the database, the storage model is wiped out and replaced from scratch. So you would need to reapply these changes. This is not an issue in LINQ to SQL, because LINQ to SQL does not support automated updates from the database at all.

A better alternative would probably be to create a VIEW in the database which referenced the other database and map that view instead of mapping the table and the other database directly.

Mistymisunderstand

The view Only works well if each db is on the same database server, otherwise you're going cross db. In sql 2005+, i believe a synonym may work also but I'm not 100%. As for the changes being lost, if you stop using the design and have the four files edited manually this stops being an issue. – Clyve

I tried to change the Schema attribute from dbo to, for example OtherDatabase.dbo, to no avail. After these changes queries throw an exception 'invalid object name OtherDatabase.dbo.MyTable'. – Caryopsis

Nath, you would need to use a linked server (with either EF or LINQ-to-SQL) if on a different server. Sam, try the VIEW approach, then. – Mistymisunderstand

Craig, I need to switch between some databases during runtime, I got no idea how I would solve that using Views - so I'm stuck (again). – Caryopsis

Well, you can't do that with the LINQ to SQL technique, either. I think you're going to have to use a separate model and change the connect string. See blogs.msdn.com/adonet/archive/2008/11/25/… – Mistymisunderstand

Craig, the connect string is the source of my problem: I would not bother using a EF for every database, but they would need to share a connectiong string. A problem, since there is only one current database per connection string. – Caryopsis

Right. Two models = two connect strings. – Mistymisunderstand

Yeah, and two connection strings = distributed transactions = slowdown. Dang. Thanks for your feedback! – Caryopsis

Answer 2

If your database supports SQL Synonyms, you can merge the two database definitions into one file. I did that recently and posted how I did it here if you're interested.

Basically you create synonyms on databaseA pointing to databaseB, create a separate edmx file for each database, and then run a script to merge the edmx files into a single file which connects to whichever database has the synonyms setup.

Superfamily