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 ]].)
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.
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.
