EF4 cross database relationships

Question

I was wondering if EF4 support cross-databse relationships? For instance:

db1

Author
  Id
  Name

db2

Posts
  Id
  Content
  db1.Author.Id

What ideally I need to do to get this relation in my ef4 model?

Do you guys have any idea?

Thanks

Answer 1

I've found this entry in Microsoft Connect that answers the question about the support given at this moment by EF (actually it is not supported yet).

Also found a thread in Social MSDN about this concern.

Other links on Stack Overflow:

• ADO.Net Entity Framework across multiple databases
• Entity framework 4 and multiple database

In summary, the only given alternatives are:

1. Using views in EF

2. Use NHibernate instead

Answer 2

If your database supports Synonyms, you can trick EF to span multiple databases. I wrote up how to do it here.

Basically you end up with an edmx file per database, and a script which merges them into a single edmx file. Synonyms are used to reference one database from another by using the actual table name, so EF doesn't throw a fit when you try to access database2.table from database1. You still have to setup links between the two databases manually in the EF model, but once setup they'll stay even if you re-run the merge script.

Scripts to setup Synonyms and to merge the edmx files are posted in the link

Answer 3

I recently began a project that uses entity framework with two databases, one Oracle and one SQL Server. I could not find any information regarding cross-database or multiple database support in the entity framework.

Most posts from the MS Entity framework team are a couple of years old and indicate that including two databases in a single model is not a feature that will be included soon. I would be interested in having a concrete answer on whether it was included in 2010 myself although I suspect the answer is no.

Currently out project gets around this limitation by having a separate entity model for each database. This has solved the problem for the majority of the scenarios we've encountered thus far in the project.

In cases where we've needed to query the data from the two databases at the same time, we simply created a view in one or the other databases. Since we're using Oracle and SQL Server, this view would utilize either a Linked Server (SQL) or a DBLink (Oracle).

The disadvantage of views in the entity framework is we've had to spent more time than I expected getting the primary keys working.

Hope this helps.