6

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

Community
  • 1
  • 1
Sam
  • 28,421
  • 49
  • 167
  • 247

2 Answers2

8

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.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • 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. – NikolaiDante Mar 04 '09 at 13:15
  • 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'. – Sam Mar 04 '09 at 13:27
  • 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. – Craig Stuntz Mar 04 '09 at 13:32
  • 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). – Sam Mar 04 '09 at 13:43
  • 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 http://blogs.msdn.com/adonet/archive/2008/11/25/working-with-large-models-in-entity-framework-part-2.aspx – Craig Stuntz Mar 04 '09 at 14:24
  • 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. – Sam Mar 04 '09 at 14:26
  • Right. Two models = two connect strings. – Craig Stuntz Mar 04 '09 at 14:48
  • Yeah, and two connection strings = distributed transactions = slowdown. Dang. Thanks for your feedback! – Sam Mar 09 '09 at 13:58
1

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.

Rachel
  • 130,264
  • 66
  • 304
  • 490